Oracle SQL Scripts for PeopleSoft

aeid.sql

Published 10.9.2008

This script adds identification comments containing the name of object.  It directly modifies the PeopleTools tables for Application Engine steps and PeopleSoft SQL objects.  These comments will be seen in database tools and traces.  So now when your database tools find a problem statement, it is easy to find the source.

NB: You will need to edit this script to specify which Application Engine programs and SQL objects to which you want to add comments.  This script is not perfect.  It does not handle very long statements.  It should be run in SQL*Plus, and you should review the output and only commit if satisfied.

In PeopleSoft for the Oracle DBA (listing11-23, page 280) is script called stmtid.sql that adds an identifying comments to the stored statements used by PeopleSoft COBOL programs.

aereplace.sql(updated 7.11.2009) This script has been moved into the ptutil package.
awr_snap_trigger.sql

Published 27.6.20088

In PeopleSoft for the Oracle DBA (listing 9-39, pages 203-4),  I proposed a trigger on PSPRCSRQST to collect Statspack snapshots exactly when PeopleSoft processes start and finish.  This is a similar idea but for AWR snapshots.
calc_opt_comp.sql

Updated 30.1.2014

This script calculates the optimal index compression for all the indexes on a named table (you will need to change the script to specify the table) by using the ANALYZE INDEX VALIDATE STRUCTURE command.  However, it works on each physical segment (table, partition, or subpartition) and reports the optimal compression for the whole object.  Otherwise Oracle only reports on the last segment analyzed.
colaudit.sql

Updated 28.3.2004

This script performs a series of checks on the PeopleTools tables that are not done by PeopleSoft's DDDAUDIT and SYSAUDIT reports. In particular it report on differences in the definitions, number or position of columns in tables, indexes or views. The script also generates a project in Application Designer that holds all the objects that may need to be rebuilt.
deltempstats.sql

Updated 14.12.2009

This script creates a trigger that will delete pre-existing Optimizer statistics on a non-shared instance of a temporary record when that instance is allocated to a new instance of an Application Engine program.

Updated 14.12.2009: Added test to checks that statistics are locked, and if not, lock them.

gfc_ddlcap.sql

Published 25.3.2010

This script creates a DDL trigger that captures the DDL to recreate objects that are recursively dropped when PeopleTools drops and recreates a table so that these objects can be restored.  This trigger is related to psft_ddl_lock, and can also be tested with the same script: psft_ddl_lock_test.sql
  • see blog entry:
gfc_stmtid_trigger.sql

Updated 10.1.2010

This script creates two triggers on the table PS_SQLSTMT_TBL, which holds the stored statements used by PeopleSoft Cobol programs, that fire on insert.
  • Trigger gfc_stmtid adds an identifying comment into the stored statement in the same way as stmtid.sql (published in PeopleSoft for the Oracle DBA, listing11-23, page 280), but the trigger adds the comments when the statements are loaded with Data Mover, rather than remembering to run it afterwards (see blog entry: Identifying Application Engine Source Code).
  • Trigger gfc_stmtstats replaces the %UPDATESTATS macro in stored statements with a call to the wrapper package, providing the same control in Cobol programs as in Application Engine programs.
gfc_temp_table_type.sql

Published 25.6.20099

This script creates a trigger that switches a non-shared instance of a temporary record from a normal table to a Global Temporary Table is restart is disabled or switches it back to a normal table if restart is enabled.  The trigger will create the Global Temporary Table and any indexes on it if necessary, and will rename the tables as necessary.

NB: This package requires psftapi package.  The PSFT_DDL_LOCK trigger is highly recommended.

globtemp.sql

globtemp-undo.sql

Updated 9.12.2007

These scripts add and remove the PeopleSoft DDL overrides required for Application Designer to generate DDL to build temporary records as Oracle Global Temporary Tables.
locktemprecstats.sql

Updated 5.2.2009

This script deletes and locks optimiser statistics on tables that correspond to PeopleSoft temporary records.
mvtrunc_lock.sql

Updated 1.11.2006

This script implements a DDL trigger to prevent TRUNCATE operations on tables with Materialized View logs.The script contains a test script that will check whether the trigger is working properly. 
See the PeopleSoft DBA Blog entry, 'Truncating a Table does not affect the Materialized View Log ' for a full description.
ptutil.sql

Released 7.11.2009

 

This script creates a PL/SQL package gathers a number of my PeopleTools utilities.  The package header includes comments that explain how to use these utilities.
  • ae_regexp_replace searches and replaces strings in Application Engine steps, and can build a Application Designer project that can be used to migrate the changed steps.  Note that it does not handle PeopleCode steps which are held in a different format. (originally published 24.9.2009)
  • view_compare compares SQL text definition of views in PeopleSoft with that in Oracle database.  The output is an Application Designer project that can be used to rebuilt the views (published 7.11.2009)

Please note that this package is completely unsupported by Oracle (who would say you shouldn't use it) or by me.  If you use it, you do so entirely at your own risk.

second_client_info.sql

Published 1.7.2008

This script implements an AFTER LOGON trigger to set the CLIENT_INFO on the secondary session on an application server or Application Engine process to the client info on the primary session.
See the PeopleSoft DBA Blog entry, 'Sequence Number Allocation in PeopleSoft' for more details.
psft_ddl_lock.sql

psft_ddl_lock_test.sql

Updated 2.6.2011

Replaces t_lock.sql

This script implements a DDL trigger to prevent accidental lost of database objects that are not managed by PeopleTools, but which are related to tables specified in PeopleTools.  It checks for indexes, triggers, primary key constraints, materialized view logs, and materialized views.  It also checks whether the table is partitioned, clustered, index organised, or global temporary.  A separate test script is provided to check whether the trigger is working properly. 
See the PeopleSoft DBA Blog entry, DDL Triggers to prevent loss of database objects not managed by PeopleTools for a full description.

Updated 2.6.2011 Now includes package to set a package variable that disables DDL behaviour for current session only.  Renamed from T_LOCK to PSFT_DDL_LOCK
Update 13.11.2007: PeopleSoft now creates descending indexes again.  T_LOCK was incorrectly preventing indexes with descending columns from being dropped because it thought they were function-based indexes.
Update 6.7.2009: Handle non-shared instances of temporary tables, and protect shadow GTT tables created by gfc_temp_table_type trigger

temptblinstances.sql

Updated 5.2.2009

This script reports on missing and excess instances of PeopleSoft temporary tables.
tr_moreinst.sql

Updated 26.2.2009

When an Application Engine program fails to obtain a private instance of a temporary record and is forced to use the shared instance it writes a entry to the message log.  This query reports on records where more temporary instances may be required.
trace_trigger.sql

Updated 17.4.2009

This is a new version of the trigger to enable and disable trace when a process begins so that all cursors are reports by Application Engine programs run by PSAESRV process.
wrapper848.sql

ddlora-wrapper.dms

Updated 18.12.2007

Earlier this year I wrote about changes to the DDL Models in PeopleTools 8.48, and I proposed a method of selectively using Optimizer Dynamic Sampling as opposed to the %UpdateStats macro.  More recently I have noticed that the Oracle appear to have  swapped over the DDL models for estimating or computing statistics on a table.  Surely another mistake in the DDL models.

I have put these SQL and Data Mover scripts on the website, so that you can easily implement my recommendations for DDL models in PeopleTools >= 8.48.

Updated 2.4.2009: On partitioned tables, the wrapper flushes monitoring statistics to the database and only collects statistics on partitions where the statistics are stale.
Updated 12.2.2009: Wrapper now collects statistics on locked permanent tables with FORCE=>TRUE option.

wrapper848meta.sql

Published 25.6.2009

Enhanced version of wrapper848.sql, that permits different parameters to specified to the Oracle dbms_stats package for different PeopleSoft Records.

NB: This package requires psftapi package.

gfcbuild.sql

Updated 14.2.2008

The presentation Configuring Global Payroll for Optimal Performance recommends physically partitioning Global Payroll result tables to match the logical streaming, and converting working storage tables to Oracle Global Temporary tables. Application Designer cannot generate the DDL for partitioned tables. This script will generate scripts that will rebuild payroll result tables with partitioning.

This concept is explained in more detail in the document Configuring and Operating Streamed Processing in PeopleSoft Global Payroll, including a step-by-step guide on implementation.

Oracle PL/SQL Packages for PeopleSoft

gfc_arch_mgmt_pkg.sql

Updated 19.1.2013

GFC_ARCH_MGMT: Segment Management Utility

This script creates a PL/SQL package with procedures to assist with partition-wise archive and purge operations.  It would in conjuction with the Partition Management Utility.

See manual

gfc_defrag_pkg.sql

Updated 19.1.2013

GFC_DEFRAG: Free Space Defragmentation Utility

This script creates a PL/SQL package with procedures to defragment free space in a tablespace or data file and trim it from the end of the data file.

See manual

psftapi.sql

Updated 17.4.2009

This script creates a PL/SQL package with a number of procedures, including
  • store the current process instance in a package variable,
  • write free-format messages to the PeopleSoft message log,
  • update the session module and action with the Oracle supplied package DBMS_APPLICATION_INFO, so that it can be seen in in monitoring tools such as Oracle Grid Control

There is also a trigger to set the module and action to the process name, process instance and current status.

 

General Oracle SQL Scripts

agg_stats.sql

agg_stats11.sql

This script limits the range of errors in statistics on composite partitioned tables as described in documents on gathering cost-based optimizer statistics on partitioned objects in 10g and 11g.  There is a separate version for 11g and higher so that table preferences can be used.
fk_check_index.sql

Updated 28.10.2007

This script checks for foreign key constraints that do not have a supporting index.  This can be a cause of TM lock contention.  It also builds a create index script to add the missing indexes.
See the Go-Faster Oracle Blog entry, ' TM locking: Checking for Missing Indexes on Foreign Key Constraints' for a full description.
gfclogin.sqlThis script can be put into the $ORACLE_HOME/sqlplus/admin directory and called from the glogin.sql script to put useful information about the database, session and the PeopleSoft database.
pctfree_advice.sql

Updated 19.1.2009

This script uses ANALYZE TABLE ... LIST CHAINED ROWS INTO ... to detect tables with chained or migrated rows.  It then reports on such rows, and produces a set of suggested commands to rebuild the tables with appropriate values for PCTFREE and to rebuild the indexes.  See Go-Faster Oracle Blog entry, Detecting and Fixing Row Migration for a full description.

(5.11.2009) See also Tanel Poder's blog: Core IT for Geeks and Pros: Detecting and Fixing Row Migration

Unix Shell Scripts for PeopleSoft

tuxcycle.sh

Updated 14.8.2008

This script recycles the PSAPPSRV processes within an application server.  Hence it is possible to clear the cache without shutting down the whole application server.  NB:  This script does not recycle the publish and subscribe servers.
See the PeopleSoft DBA Blog entry, How to Clear the Application Server Cache Without Shutting it Down for a full description.
psft.sh

Updated 7.6.2010

This wrapper script can be incorporated into the PeopleSoft Process Scheduler configuration, so that you can execute a Unix shell script or command from the process scheduler.  The script correctly updates the status of the request depending upon the return code from the called command.  Any standard or error channel outputs are returns to the report repository..

See the PeopleSoft DBA Blog entry, Running Unix Commands and Scripts from the PeopleSoft Process Scheduler for a full description.

Application Designer Projects

Aggregate & Purge Application Engine Batch Timings (GFC_TIMINGS_ARCH)

Published 4.10.2009

This Application Designer project contains an Application Engine program (GFC_TIM_ARCH) that aggregates batch timings data collected by Application Engine programs to separate tables, and purges the original data from the database.

See the PeopleSoft DBA Blog entry, Aggregating & Purging Batch Timings for a full description.

Purge PeopleSoft Run Control(GFC_RC_ARCH)

Published 26.8.2010

This Application Designer project contains an Application Engine program (GFC_RC_ARCH) that purges run controls that no longer exist in the Process Scheduler request table.

See PeopleSoft Run Control Purge Utility  for a full description.

Global Payroll Reporting Table(GFC_GPRPTGEN)

Published 23.6.2011

This Application Designer project contains an Application Engine program (GFC_GPRPTGEN) that incrementally maintains reporting tables that hold data extracted from GP_RSLT_ACUM and GP_RSLT_PIN.  The zip file also contains some SQL scripts to set up meta-data.
Batch Modeller (GFC_SLEEP)

Published 23.3.2011

This Application Designer project contains the Application Engine programs required to model an on-line batch.  See Modelling a Complex Batch Schedule in PeopleSoft document or presentation.

Monitoring Scripts

The following scripts and utilities have been developed by Go-Faster to provide remote performance monitoring. They are provided on this website without explanation or support.

awr_wait

Updated 19.6.2010

Thumbnail of AWR wait chartThis spreadsheet connects directly to a database to query the wait event information from DBA_HIST_WAIT for the last 7 days (this is the information collected by collected by AWR).  The data is used to create a graph.  Click on the thumbnail on the left for an example.

You can download a zip file containing an example spreadsheet, and also a copy of the query embedded in the spreadsheet.  You will need to create an ODBC data source to connect to your database.

Updated 19.6.2010: Adjustment to the query used in the spreadsheet.  AWR snapshots happen at slightly different times on different RAC nodes, although the have the same snapshot ID.  This was causing multiple rows for the same snapshot in the pivot tables, and the charts did not show correctly aggregated wait events across RAC nodes.

See also Go-Faster Oracle Blog entry, Graphing AWR Data in Excel

For an example of that takes this idea much further see Tanel Poder's blog: Core IT for geeks and pros: Advanced Oracle Troubleshooting @OOW 2008 presentation slides and scripts

Tuxedo Scripts

tuxmon

Updated 22.3.2005

The tuxmon script monitors the behaviour of a Tuxedo application server running on a Unix system. 

NB: From PeopleTools 8.44, the data collected by Tuxmon can be obtained from the PeopleSoft Performance Monitor, and there is no point in using Tuxmon on these systems.

The scripts to receive and analyse the output are not available.

latest updates:
22.3.2005 - enhanced to monitor shared memory queues with ipcs command.

tuxmon.nt

Released 6.1.2005

This is a primitive Windows version of the tuxmon script.  It uses a simple batch script that can be run from the Windows scheduler.  An awk program formats the output file.  They can also be sent to monitor@go-faster.co.uk for analysis.
tkmailemThe tkmailem script is used to process and e-mail TKPROF output for batch processes that are set to trace by a trigger.

Testing Scripts

lwr.sql

lwrpkg.sql

Updated 7.1.2009

This test script (lwr.sql) was used to generate a scenario in which concurrent truncate operations cause Oracle to wait on events local write wait and enq: RO - fast object reuse.  It creates a packaged PL/SQL procedure (lwrpkg.sql).  There are procedures to generate and populate test tables, to move data back and forth between and these tables truncating data are each copy, and procedure to create jobs run by the Oracle job scheduler to execute these multiple instances of these procedures concurrently. 

See the PeopleSoft DBA Blog entry, TBC for a full description.

Caveat: All the scripts available from this website are provided without warranty or support. SQL are designed to be run from SQL*Plus against a PeopleTools 8 database on Oracle. There is no guarantee that any of the content of this website is error free, rather that there are certainly errors and inconsistencies!

 Go-Faster Consultancy Ltd. 2014