| 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 |
| 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.2009 | 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. |
| leafcoal.sql Published 11.4.2016 | This script reduces the number of leaves on a PeopleSoft tree by coalescing leaves on the same node into ranged leaves. It updates the version on tree definition so the tree is automatically recached. Only leaves on the same node are compressed if there is no intermediate value on the column referenced in the tree structure record and does not result in a range overlapping with another leaf on another node. No change is made to the tree node structure. |
| 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. |
| psredundant.sql psredundantfix.sql Posted 26.12.2016 | The first script reports on redundant indexes that are subsets of other indexes in the PeopleSoft data dictionary (rather than the database catalogue). The second script uses a similar query to identify the redundant indexes, and then adds them to an Application Deisgner project, marks them invalid, generates extended statistics and makes the redundant invisible. |
| 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. |
| ReUseCand.sql ReUseCandASH.sql Released 24.10.2014 | These scripts profile Application Engine batch timings over the last 7 days to show the steps that spent the most time on compilation and which might benefit most from enabling ReUseStatement. The second script additionally uses Active Session History (ASH) data to determine how much time the database spend on hard parse on these steps. See also blog posting (TBC). |
| 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. |
