Posted by: steve.rees
on Jun 09, 2010
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 actaully 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!
Posted by: steve.rees
on May 16, 2010
Tagged in: Untagged
I've been meaning to start a blog on DB2 LUW performance for a while, and, well, now seems like a pretty good time. Why would I want to blog? Never mind that - much more importantly, why would anyone be interested in what I have to say? Good question!
I've been part of the DB2 LUW development team in Toronto for about 20 years, and the last 13 or so in performance. Over the years, I've done a lot of benchmarking of DB2, worked with a lot of customers and partners on improving performance, plus done a ton of core performance work on new releases (including the last couple of years on DB2 pureScale.) It's a great job, and I think it's given me a pretty good 'inside & outside' view on the kinds of performance topics that are relevant to DB2 uesrs - both for existing releases, and for V-next.
One of my particular interests is monitoring and tuning of DB2 - understanding the dozens of metrics and configuration parameters, and getting the most out of them. Over the past several years, I've had the good fortune to present about this at a number of IDUG and RUG meetings. This will be a frequent topic for my blog, and with all the changes in this area in the past few releases, there is no shortage of raw materieal. :-)
I'll also be digging into performance best practices & frequently asked questions - tips & tricks for getting the best performance out of all kinds of areas of DB2 - SQL, applications, utilities, storage & memory configuration - the works.
So, welcome to my performance blog. Hopefully over the coming entries, there are some useful tidbits here to help make your monitoring & tuning process a bit easier, or to help you get a bit more 'oomph' out of your system.
