Posted by Purpose
To find recently executed statements that have used a lot of temporary space in Db2. Sometimes temp space will fill up, and particularly if temp space is not on its own file system, it can cause database-wide issues. This statement can help identify which statements have executed recently and done the most work with temporary tables.
Version
Tested on 10.5. Should work on 9.7 and up.
Statement
SELECT
INSERT_TIMESTAMP,
EXECUTABLE_ID,
SUBSTR(STMT_TEXT,1,10) as STATEMENT,
POOL_TEMP_DATA_L_READS+POOL_TEMP_XDA_L_READS+POOL_TEMP_INDEX_L_READS TMP_READS
FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T
ORDER BY TMP_READS DESC FETCH FIRST 20 ROWS ONLY WITH UR;
Sample Output
INSERT_TIMESTAMP EXECUTABLE_ID STATEMENT TMP_READS -------------------------- ------------------------------------------------------------------- ---------- -------------------- 2019-10-01-19.18.59.184619 x'0100000000000000387601000000000000000000020020191001191859184620' SELECT INS 4771453 2019-10-01-17.58.06.586198 x'01000000000000005D6F01000000000000000000020020191001175806586198' with ts as 1655880 2019-10-01-17.38.43.853128 x'0100000000000000776E01000000000000000000020020191001173843853128' SELECT R.C 314887 2019-10-01-20.37.47.002517 x'0100000000000000217A01000000000000000000020020191001203747002517' INSERT INT 266654 2019-10-01-18.58.16.185937 x'0100000000000000D77301000000000000000000020020191001185816185937' SELECT CAT 159383 2019-10-01-19.44.30.267098 x'01000000000000001A7801000000000000000000020020191001194430267099' SELECT CAT 123196 2019-10-01-20.37.48.221975 x'0100000000000000237A01000000000000000000020020191001203748221976' DELETE FRO 101141 ...
Restrictions
The package cache is frequently evicting statements. Depending on the size of the package cache and the number of statements that run against your system, you may or may not see the statements you need. To guarantee you catch statements, you would have to use an activity event monitor that was activated prior to the issue with temp space occurring, or a package cache eviction event monitor.
Modifications
This statement returns data in a tabular format for ease of reading on the screen. To see the whole statement, expand the number of characters in the SUBSTR on STMT_TEXT or return the whole thing.
The statement as presented here only looks at dynamic statements. To instead look at static statements, use an ‘S’ instead of a ‘D’ in parameters passed into the MON_GET_PKG_CACHE_STMT function.
References
IBM Db2 Knowledge Center entry on MON_GET_PKG_CACHE_STMT
