April 08, 2014

DB2 Subsystem Tuning Tips

Many, many years ago, I was a DB2 systems programmer. As part of my training, I was sent to an IBM DB2 systems tuning course. That type of education cost several thousand dollars back then, but with the knowledge I'd gained, my employer easily recouped its investment in the form of reduced CPU consumption on our system.

One basic thing I learned was what dispatching priority the different DB2 address spaces needed to be. I find it interesting that the current DB2 installation guide doesn't address these settings. (Hint: IRLM should always be the highest.)

Fortunately, the recently released IBM Redbook, "Subsystem and Transaction Monitoring and Tuning with DB2 11 for z/OS," covers many of the same topics I learned in the classroom back when -- even DB2 subsystem address spaces. In section 3.2 IBM recommends that IRLM run at SYSSTC to ensure it's at a higher priority than the other address spaces (MSTR, DBM1 and DIST).

If you haven't worked with traces, Section 2 ("DB2 traces") lists different trace types. Something to keep in mind about traces that I didn't find in this Redbook is that starting some traces creates more overhead than starting others. The most costly trace to start is Class 7 Lock Details, so only use this trace in an extreme situation. (And yes, this is another thing I learned in my old systems tuning course.)

But back to the Redbook. Section 2.3.5 (“Loading IFCID field descriptions into a table”) covers the steps involved in creating tables that contain IFCID records as well as field descriptions. (Note: For more, see what I wrote about this topic some years back.)

Here are just a few of the chapters that focus on monitoring the subsystem:

            Chapter 4  : System address space CPU time

            Chapter 5  : EDM Pools

            Chapter 6  : Data Set Open and Close

            Chapter 7  : Log Activity

            Chapter 8  : IRLM, Locking, and Latching

            Chapter 9  : The zIIP engine and DB2

            Chapter 10: Buffer Pools and Group Buffer Pools

            Chapter 11: DDF Activity

            Chapter 12: Workfiles, RID, and Sort Pools

            Chapter 13: Virtual and Real Storage

This Redbook is worth your time even if you aren't a DB2 systems programmer. For instance, DB2 developers could use this information to help analyze and improve application performance. I also appreciate that the publication notes DB2 version numbers and offers version-specific performance tips. This material gives readers an idea of how various changes will impact their systems regardless of the release they're on.

If you've read this Redbook, please offer your impressions in comments.