An In-Depth Look at Buffer Pool Monitoring and Tuning

IBM recently released a Redpaper, "IBM DB2 11 for z/OS Buffer Pool Monitoring and Tuning."

While this document is intended for DB2 systems administrators, it could also be useful for those who specialize in z/OS system tuning. As someone who knows a bit about tuning buffer pools and the challenges this process entails, I'm excited to see IBM devote a Redpaper to this topic.

I've yet to read the whole thing, but the material I've been through is excellent. This isn't surprising given that the author, Jeff Berger, is one of IBM's top performance specialists.

Here's the chapter list:

            Chapter 1. Introduction
            Chapter 2. Prefetch and RID pool
            Chapter 3. Buffer pool writes
            Chapter 4. Data sharing
            Chapter 5. Workfiles
            Chapter 6. Assigning page sets to buffer pools
            Chapter 7. Improving CPU performance
            Chapter 8. Index and data organization

The first five chapters provide a great introduction. You'll learn what buffer pools are and how the tuning knobs are used to control their behavior. This information is very valuable for newcomers, or anyone in need of a buffer pool refresher course.

I believe that splitting up buffer pools is essential to effectively tune them. When I was consulting, I found that many customers solely rely upon the main buffer pool (BP0). My approach was to set up separate pools for random and sequential access -- a buffer pool for sort work files. However, in Chapter 6, Jeff makes a recommendation that I hadn't considered: assigning buffer pools based on sequential insert activity. As I said, he's the expert.

When I was tuning buffer pools, my focus was on reducing physical I/O, so I appreciated the information in Chapter 7, which covers the settings you should use to reduce CPU consumption. The final chapter examines when you should consider reorganizing index and data spaces.

I know from experience that setting up multiple buffer pools isn't simple, but the performance benefits it provides are worth the effort. This Redpaper offers valuable information to help you navigate this process.

Please post your experiences and/or questions on buffer pool tuning.

New Stored Procedure Simplifies EXPLAIN Maintenance

Managing EXPLAIN tables can be time-consuming. DB2 developers often create EXPLAIN tables and allow them to exist on the system for years without being upgraded to include the latest columns or support the newer table types that have been introduced in recent DB2 releases. Because these tables aren't current, they're incapable of utilizing key features that are designed to help diagnose the access paths DB2 has chosen and why.

In short, if you're a developer and you rely on EXPLAIN tables that are based on older DB2 releases, you're missing out.

A new stored procedure, SYSPROC.ADMIN_EXPLAIN_MAINT, is designed to simplify the process of upgrading existing EXPLAIN tables. It can also be used to create the new tables that are introduced with each release of DB2. (See, for example, DSN_PREDICATE_SELECTIVITY, a new table in DB2 11.) So if you have an old set of tables, this procedure will make sure they're current. And if you're missing any of the latest tables, it will create them for you.

IBM is releasing this new procedure through maintenance APAR PI05200 (NEW STORED PROCEDURE SYSPROC.ADMIN_EXPLAIN_MAINT) for DB2 11 only. It was closed for customer testing on April 25 and will now generate PTF UI17423.

To learn more about this new stored procedure and view a sample program used to call it, see the DB2 11 Managing Performance Guide (updated April 2014).