DB2 Administrative SQL Cookbook: Finding Statements in the Package Cache using a...

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

• Author Details
• Certifications and Badges
• Twitter

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA

Ember Crooks

https://datageek.blog/

Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 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 for XTIVIA , leading a team of Db2 DBAs.

My Tweets