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.
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.
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).
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.
set head off feedback off long 5000
select 'STORE '||pgm_name||'_'||stmt_type||'_'||stmt_name
where stmt_text like '%UPDATESTATS(%'
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.