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

Lead Database Administrator
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home

Leave a Reply

Your email address will not be published. Required fields are marked *

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