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!
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.
I have been mulling over how to better communicate and interact with DB2 users on my areas of expertise and responsibility for a while now and someone at a recent conference suggested that I start blogging on the IDUG website.... so I think I will try this out for a while and see if it is effective as a means of both disseminating information as well as getting some feedback from the real world.
A little about me, I have been with IBM since 1984 and in the IBM Toronto Lab since 1992 when DB2 for Unix project was first transferred over to Toronto.. I have been developing in the DB2 for LUW product ever since and some of the things that bear my thumbprints are aliases, the compiler Explain facility, and the package cache. Along the way, I have dabbled in many areas such as security and DDL, etc. I am now a senior architect in the DB2 for LUW development organization and my current areas of responsibility include workload management, monitoring, and general SQL processing.
This blog is going to primarily focus on these areas although I hope to be able to react to question or comments as we go along to keep things interesting.
I will start blogging in earnest in the New Year but feel free to submit topics or questions of interest along the way. Otherwise, I will just start with whatever is on my mind :)
Welcome back, checkout my latest blog on DATA CAPTURE option and its effects on logging. It is available here. Your comments and suggestions are most welcome.
I have recently done a recovery and realized the importance of DSNTIAUL where UNLOAD is not of much help. If you want to find out the details about it. Check it here
Ten Steps for Archiving Data in IBM DB2 on Mainframe Systems
This Informatica whitepaper outlines the challenges of managing data growth in IBM DB2 on mainframe systems and the limitations of conventional solutions. Link to register: http://vip.informatica.com/?elqPURLPage=7761.
Blog Tracker
Get e-mail updates when new entries are posted to this blog
