My favorite performance items in DB2 LUW v9.7 - #1: MON_GET_PKG_CACHE_STMT

DB2 LUW 9.7 is a pretty impressive release.   It's been out for a while now, and we've seen a huge number of customers moving there to take advantage of the slate of great features like Oracle compatibility and index / temp / LOB compression, to name a few.   No raindrops on roses or whiskers on kittens, but over the next few posts, I'm going to go over 'a few of my favorite (performance) things' in DB2 9.7 - and there are quite a few of them!

Top of my list is actually a bit of a dark horse feature which lets monitoring fanatics like me get a look into a whole new area of system performance.    It's #1 for me because it fills a need that was quite challenging to meet with other monitoring mechanisms.  The new SQL table function MON_GET_PKG_CACHE_STMT gives not only per-statement information about dynamic SQL (along the lines of the dynamic SQL snapshot admin view SNAPDYN_SQL and friends) but also does the same for static SQL!   Behold - the long-awaited "static SQL snapshot" is here!   Ok, it's not exactly a snapshot - the SQL interface gives that away pretty quickly - but it does fit in with the SQL-based strategic direction for DB2 monitoring developed over the past few releases.

Many performance investigations end up in an analysis of individual statement performance - execution counts, elapsed & CPU times, bufferpool hit ratios, direct IO counts, lock & other wait times, etc.   Prior to 9.7, getting this kind of detail about static SQL required an event or activity monitor, or lighter-weight but less accurate wizardry using regular snapshots and db2pd.   Now, you can get all this information (plus SQL statmeent text, even!)  with MON_GET_PKG_CACHE_STMT.

The table function takes four arguments.  The impatient among us can cut to the chase with the broadest selection of data (both static and dynamic, from all packages, on all members) with 
    SELECT * FROM TABLE(MON_GET_PKG_CACHE_STMT( null,null,null,-2 )) as T
Details available here in the Information Center

It returns a small avalanche of data if you pick up everything with a 'SELECT *'.  This can be useful, particularly if you're funnelling the monitor data to another regular table for later analysis.   Otherwise, for adhoc monitoring from the command line, it can be a lot to absorb, so you may want to put your favorite more selective query into a script that you can run when you need.  This can help reduce the cost of collecting some of the more expensive columns, such as STMT_TEXT and COMP_ENV_DESC, if you don't need them.

But wait, you say - I don't use static SQL.   Well, you might and not realize it.   If you run any SQL stored procedures,  most statements within these are compiled to static SQL sections, which are now easily monitored with MON_GET_PKG_CACHE_STMT.

This is great stuff - give it a try if you haven't yet!

Recent Stories
Tips for getting the best INSERT performance

Statistics Event Monitors - terrible name, terrific feature

Measuring OVERHEAD and TRANSFERRATE on DB2 LUW - the why & how