DB2 Package-Level Statistics and Batch Tuning

(Originally posted 2011-12-05.)

I don’t know how many years it’s been since DB2 Version 8 was shipped but I’ve FINALLY added support for some really useful statistics that became available with that release.

As so often happens I was caused to open up my code because of some customer data that exposed a problem in it: The customer sent DB2 Version 8 SMF 101 Accounting Trace data that contained Unicode. In particular DB2 Package names were showing up as apparent garbage. Hexdumping some records showed this field to be Unicode.

The first step was to tolerate Unicode. In my case I translate it in the REXX I do my actual reporting in. (I could’ve done it in the assembler and maybe one day I will – but it makes the job more complex.) There is a field in the Product Section (QWHSFLAG) that has the value x’80’ if the record contains Unicode (and 0 if it doesn’t).

But this post isn’t really about Unicode. It isn’t about the longer names that are supported in Version 8, either. It’s about some nice "new" statistics you also get at the Package level. (And nothing significant has happened to the 101 record since Version 8.) As I had the code open I took the opportunity to exploit these new numbers. I’ve not written about them  before so now is a good time to extol their virtues – despite the arrival of Versions 9  and 10.

So this post is about DB2 performance at the package or program level. "Program" would be the application code or not-specifically-DB2 term: An application program calling DB2 generally uses a package with the same name. I’ll use package in the rest of this post because it’s the DB2 term.

Buffer Pool Statistics

The Accounting Trace record has very nice buffer pool statistics at the Plan / Buffer Pool level. But the real problem for a batch job is "which program / DB2 package is driving the traffic?" We’ve always had the ability to say which packages the time was spent in and which components of response time for those packages are dominant. And indeed the major packages might be spending lots of time waiting for Synchronous Buffer Pool I/O or Read (or Write) Asynchronous I/O. (I see that quite often.)

What we didn’t know until Version 8 is how the buffer pools are performing for those top packages. So these statistics are really handy.

Note: There’s only one set of buffer pool statistics for each package. That is, you can’t tell which buffer pools are accessed by which package.

SQL Statistics

At the plan level we see the number of, for example, singleton selects, cursor opens, fetches under cursor, updates, inserts and deletes. So we might, for instance, gain some insight into why a batch job step is seeing a large amount of Synchronous Database I/O Time: Perhaps it’s because of a plethora of singleton selects so Prefetch doesn’t really happen.

What we couldn’t do, prior to Version 8, is see this at the package level. Now we can we’re able to find the package / program that’s behaving this way. So we stand a better chance of fixing it.

As an experiment I summed up counts of the different SQL statement at the package level and compared it to the field QPACSQLC (which was there at the package level long before Version 8). This field is the number of SQL statements. Usually they’re the same but in a significant proportion of cases the sum is less than QPACSQLC. One valid explanation is that the difference includes commits and aborts (which there aren’t statistics for at the package level). I bolded "includes" because this isn’t the whole explanation. If you take out the plan-level commit and abort counts (fields QWACCOMM and QWACABRT) you sometimes still have a discrepancy. I’ll have to research why this might be.

But, as I say, the reason for this post about old (but not obsolete) statistics is that I think y’all will find them really handy. And especially for batch steps and indeed any DB2 application where the transaction comprises multiple packages / programs.