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.

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.

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 (www2.go-faster.co.uk/scripts/wrapper848meta.sql).

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.

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.