History matters when tuning DB2 for LUW
IBM_Optim 27000269HS Visits (2992)
I totally love performance, and I like the whole process of trying to find the issues. There is nothing more cool than being the hero-of-the-hour by finding the extreme I/O usage, the I/O hot spots, the file system contention, the logical control unit bottlenecks, the horrible buffer pool management, the poor SQL, or any combination of the above.
But what makes it so interesting is the tooling surrounding performance. To be more specific, the fact that I can now tune DB2 for LUW in the fashion as I would tune DB2 for z/OS. Heresy, you say? Please hear me out.
All through the 1990's and on through today, I've been using Tivoli OMEGAMON XE for tuning DB2 for z/OS. This tool is great for finding just about any issue or bottleneck, including looking at historical data. In 2003, I had to support DB2 for LUW, and I felt like I was stepping back in time. My biggest complaint was the fact that I had to set up scripts to pull down activity. And finding historical information on DB2 for LUW activity? Forget about it.
But that was then.
What we have now for DB2 for LUW is, quite simply, amazing. DB2 Performance Expert is a monitoring tool any DB2 person would just love. I have my main console, which is strikingly similar to the OMEGAMON classic view. There is the drill down capability in DB2 Performance Expert where I look for troubleshooting issues. But what do I really like? The fact that I can now look at historical information without a script or agent running on the monitored server!
Doing the Monday morning tasks - you on-call DBAs know what I'm talking about here - are made so much easier with DB2 Performance Expert. I can look at what happened over the weekend so that neither I nor my manager get blindsided by applications or by my end users. In addition, I can find problems in the here-and-now without physically issuing snapshots either at the command line processor or within a script. I can find the long running SQL and easily find the tables it's going after. By knowing the tables, I can go to the next level - is this a one-off SQL situation or is this a table that needs an index, or is this a table that needs to be isolated either in a bufferpool or using file system placement? DB2 for LUW will not be the product that you "poke a stick" at for tuning.
Will DB2 Performance Expert give you the desire to hang on to your turn in the on-call rotation for another week? I can't answer that. But finding the issues simply and easily with this tool will make you the hero-of-the-hour.