Oakies Blog Aggregator

Automatically Identifying Stored Statements & Using DBMS_STATS in PeopleSoft Cobol

It is not possible to reference a long column in a Oracle database trigger, but it is possible to reference LOBs. From PeopleSoft v9 Applications, the long columns have become CLOBs. Hence, it is now possible to have a database trigger fire on insert into the Stored Statement table and so automatically make certain changes to Stored Statements as they are loaded by Data Mover. Previously, these changes could be made by a PL/SQL script, but you had to remember to run in after any Stored Statements were reloaded. I have published a new script (gfc_stmtid_trigger.sql) on my website that creates two such database triggers.

Trigger GFC_STMTID

In PeopleSoft for the Oracle DBA (listing 11-23, page 280), I proposed a PL/SQL procedure to add identifying comments to stored statements (see blog entry: Identifying Application Engine Source Code), so that the statements can be identified in traces or Oracle Enterprise Manager.

Trigger GFC_STMTSTATS

PeopleSoft Cobol programs do not use the PeopleSoft DDL models that Application Engine users when it processes the %UPDATESTATS macro. In another blog posting, I showed how to change the stored statements to call the wrapper package, so that the program uses the Oracle supplied dbms_stats procedure instead of the depreciated ANALYZE command. This trigger automatically replaces the %UPDATESTATS macro in the stored statement with a call to the wrapper package.

Controlling How PeopleSoft Cobol Collects Statistics

In previous postings, I have proposed locking statistics on temporary working storage tables and changing the DDL model for %UpdateStats to call my own PL/SQL Package.  That works for Application Engine programs, but PeopleSoft COBOL can also update object statistics, and they use a different mechanism.

In the case of the Global Payroll calculation engine, GPPDPRUN, the run control component has a secondary page with a check box to enable statistics collection during the process.

However, to get streamed processing in Payroll (where the population of employees is broken into ranges of employee IDs that are each processed by a different concurrent process) to work effectively I change the working storage tables to be Global Temporary Tables, and then because the different physical instances would still share statistics (Global Temporary Tables Share Statistics Across Sessions) I delete and lock the statistics on these tables.

If the payroll calculation is run with the Update Statistics option it generates the following error.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
Application Program Failed
Action Type : SQL UPDATE
In Pgm Section : SQLRT: EXECUTE-STMT
With Return Code: 38029
Error Message : ORA-38029: object statistics are locked
Stored Stmt : GPPSERVC_U_STATS
SQL Statement : ANALYZE TABLE PS_GP_PYE_STAT_WRK ESTIMATE STATISTICS

COBOL issued an ANALYZE command, and did not use the DDL model defined in the table PSDDLMODEL.  However, the command came from a stored statement, in this case GPPSERVC_U_STATS.  The stored statement is defined as follows in the gppservc.dms.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
STORE GPPSERVC_U_STATS
%UPDATESTATS(PS_GP_PYE_STAT_WRK)
;

So, the expansion of %UPDATESTATS in the stored statement to the ANALYZE command is hard coded somewhere in the delivered executable code.  I would not suggest attempting to change that. 

However, it is perfectly possible to change the stored statement to call the wrapper package (www.go-faster.co.uk/scripts/wrapper848meta.sql).

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
STORE GPPSERVC_U_STATS
BEGIN wrapper.ps_stats(p_ownname=>user, p_tabname=>'PS_GP_PYE_STAT_WRK'); END;;

In all there are 5 statements in HR 9.0 that call %UPDATESTATS that all relate to GPPDPRUN.  I generated a data mover script to replace them with calls to my replacement package using the following SQL.script.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
set head off feedback off long 5000
spool updatestats_after.dms
select 'STORE '||pgm_name||'_'||stmt_type||'_'||stmt_name
||CHR(10)
||'BEGIN wrapper.ps_stats(p_ownname=>user,p_tabname=>'''
||substr(stmt_text
, INSTR(stmt_text,'(')+1
, INSTR(stmt_text,')')-INSTR(stmt_text,'(')-1
)
||'''); END;;'
from ps_sqlstmt_tbl
where stmt_text like '%UPDATESTATS(%'
/
spool off

I have only tested this against HR9.0. Note that long columns such as PS_SQLSTMT_TBL.STMT_TEXT only become CLOBs when the application version reaches 9.0.   You cannot use the LIKE operation on the long column.

Controlling How PeopleSoft Cobol Collects Statistics

In previous postings, I have proposed locking statistics on temporary working storage tables and changing the DDL model for %UpdateStats to call my own PL/SQL Package.  That works for Application Engine programs, but PeopleSoft COBOL can also update object statistics, and they use a different mechanism.

In the case of the Global Payroll calculation engine, GPPDPRUN, the run control component has a secondary page with a check box to enable statistics collection during the process.

However, to get streamed processing in Payroll (where the population of employees is broken into ranges of employee IDs that are each processed by a different concurrent process) to work effectively I change the working storage tables to be Global Temporary Tables, and then because the different physical instances would still share statistics (Global Temporary Tables Share Statistics Across Sessions) I delete and lock the statistics on these tables.

If the payroll calculation is run with the Update Statistics option it generates the following error.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
Application Program Failed
Action Type : SQL UPDATE
In Pgm Section : SQLRT: EXECUTE-STMT
With Return Code: 38029
Error Message : ORA-38029: object statistics are locked
Stored Stmt : GPPSERVC_U_STATS
SQL Statement : ANALYZE TABLE PS_GP_PYE_STAT_WRK ESTIMATE STATISTICS

COBOL issued an ANALYZE command, and did not use the DDL model defined in the table PSDDLMODEL.  However, the command came from a stored statement, in this case GPPSERVC_U_STATS.  The stored statement is defined as follows in the gppservc.dms.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
STORE GPPSERVC_U_STATS
%UPDATESTATS(PS_GP_PYE_STAT_WRK)
;

So, the expansion of %UPDATESTATS in the stored statement to the ANALYZE command is hard coded somewhere in the delivered executable code.  I would not suggest attempting to change that. 

However, it is perfectly possible to change the stored statement to call the wrapper package (www.go-faster.co.uk/scripts/wrapper848meta.sql).

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
STORE GPPSERVC_U_STATS
BEGIN wrapper.ps_stats(p_ownname=>user, p_tabname=>'PS_GP_PYE_STAT_WRK'); END;;

In all there are 5 statements in HR 9.0 that call %UPDATESTATS that all relate to GPPDPRUN.  I generated a data mover script to replace them with calls to my replacement package using the following SQL.script.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
set head off feedback off long 5000
spool updatestats_after.dms
select 'STORE '||pgm_name||'_'||stmt_type||'_'||stmt_name
||CHR(10)
||'BEGIN wrapper.ps_stats(p_ownname=>user,p_tabname=>'''
||substr(stmt_text
, INSTR(stmt_text,'(')+1
, INSTR(stmt_text,')')-INSTR(stmt_text,'(')-1
)
||'''); END;;'
from ps_sqlstmt_tbl
where stmt_text like '%UPDATESTATS(%'
/
spool off

I have only tested this against HR9.0. Note that long columns such as PS_SQLSTMT_TBL.STMT_TEXT only become CLOBs when the application version reaches 9.0.   You cannot use the LIKE operation on the long column.

Method 4 dynamic sql in pl/sql

Using ANYDATASET and pipelined functions to achieve native Method 4 Dynamic SQL. August 2007

Introduction to 8i analytic functions

Oracle 8.1.6 introduces a wide range of powerful new "windowing" functions. February 2002

The CPU Costing Model: A Few Thoughts Part IV (Map of the Problematique)

It’s called the CPU Costing model because among other things, it includes the time associated with performing CPU operations.   The CPU Costing model formula once again:   (sum of all the single block I/Os x average wait time for a single block I/O +  sum of all the multiblock I/Os x average wait time for [...]

Overcoming the limitations of set row

Techniques to avoid superfluous updates with SET ROW. June 2007

Flexible pipelined functions

Combining pipelined functions, polymorphism and multi-table inserts. August 2007

tweet …

For people that have asked - I'm not blogging simply because I'm feverishly working on a tool of mine that I'm very fond of :)

Guess what it does from the following next two screenshots ...

tool_lines2

tool_prof1

Tuning pl/sql file i/o

Various techniques for unloading data to flat-file faster. February 2008