Update 17/01/2018
Add some more fields and functionnality, as i start using it to :
- Have a (cheap) picture of the running packages at a moment.
- Report and select the top 5 packages that should be looked at
IFCID401 enables what IBM calls "Statement level monitoring" , with the column StmId provided (QW0401ID) , you can find easily your culprit SQL text.
SELECT STATEMENT FROM SYSIBM.SYSPACKSTMT WHERE
COLLID = Given_Collid
AND NAME = Given_Pkg_Name
AND HEX(CONTOKEN) = Given_PkgToken
AND STMT_ID = Given_StmId
(Your sql text will be truncated depending on the program you use to select, If using SPUFI change the column width default - DSNTEP2 default is 120 bytes for a char column)
IFCID400 must be started, otherwise the data returned is not complete
This Rexx first starts IFCID400, then starts IFCID401.
I don't have a technical explication of the purpose of the switch on IFCID400 (and the same for IFCID318 for the dyn. statement cache). So if some one knows, please drop me a line ... Thanks
When a package is just executed, we have an image of its (statistics > 0) , but when there is a long time after its execution, the package is still readable but with all stats field = 0.
You have here the picture of the same package (with 2 sql statements = 2 records) at different iteration
Get the code here : (Update 27/05/2020)
https://github.com/ndt98/Db2z/blob/master/ifc401.rex
JCL to execute the RExx above
//IFC401 EXEC PGM=IKJEFT01,DYNAMNBR=25,ACCT=SHORT, 00040000
// REGION=4096K 00050000
//* 2 E PARAMETRE NB ITERATION 00060027
//* 3 E PARAMETRE NB SECONDS WAIT BETW. ITERATION 00060127
//* 4 E PARAMETRE GPMIN (FILTER) 00060227
//STEPLIB DD DSN=SYSPRM.WSYNGUD.LOAD,DISP=SHR 00061000
//SYSEXEC DD DISP=SHR,DSN=SYSPRM.WSYNGUD.REXX 00070000
//SYSUDUMP DD SYSOUT=* 00120000
//SYSTSPRT DD SYSOUT=* 00130000
//SYSOUT DD SYSOUT=* 00140000
//SYSTSIN DD * 00150000
IFC401 SYSTMP.WSYYYXX DBP0 5 300 10 00151027
No comments:
Post a Comment