DB2 Administrative SQL Cookbook: Finding Statements in the Package Cache using a lot of Temporary Space

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

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.