Know what these numbers have in common? They indicate the number of 4K buffer pools allocated for four different production DB2 for z/OS subsystems that I analyzed over the past couple of years. I believe that there are quite a few sites where a plethora of 4K buffer pools have been defined for a given DB2 subsystem. That's not illegal or anything (DB2 allows you to have as many as 50 different 4K pools for a single subsystem), but it does make the task of managing and monitoring a buffer pool configuration more complex and time-consuming. When I'm in a meeting with an organization's DB2 for z/OS DBAs, and we're looking over information for a DB2 environment, and I see lots of 4K buffer pools defined and I ask about that, I might see sheepish grins and a few eye-rolls, and hear words like, "Well, you know, we just added a buffer pool here and a buffer pool there over the years, for this reason and that, and we ended up with what you see." Recently, I've been pleased to see DBAs at more than one company engaged in consolidation of 4K buffer pools (the number of 8K, 16K, and 32K buffer pools is usually not a matter of concern -- you can only have as many as 10 of each, and most systems I've seen have fewer than that number allocated). These DBAs are reassigning database objects (table spaces and indexes) from lots of smaller 4K pools to a smaller number of larger 4K pools (moving objects from one buffer pool to another got easier with DB2 10, especially for organizations running DB2 in data sharing mode, as I pointed out in a blog entry on the topic I wrote a few years ago). At one site I visited earlier this year, they've taken an interesting approach to managing the size of some of the larger 4K pools they are using for consolidation purposes: they are allocating buffers for these pools in chunks of 524,288. Why? Because that's the number of 4K buffers that can fit into a 2 GB page frame (DB2 11 allows 2 GB page frames to be requested for pools defined with PGFIX(YES), with those very large frames being made available through a specification for the LFAREA parameter in the IEASYSxx member of a z/OS system's SYS1.PARMLIB data set). When the DBAs enlarge one of these pools, it will be enlarged by 524,288 buffers (or a multiple thereof), so as to get one more 2 GB page frame for the pool (or a few more, depending on the size increase).
So, if you're looking to rationalize and simplify the 4K buffer pool arrangement for a DB2 subsystem (or if your organization is just getting started with DB2 for z/OS and you're looking for a reasonable initial set-up -- I got this question a few days ago from a DBA at such a site), what kind of 4K buffer pool configuration might make sense for you? Here are my thoughts on the subject:
- Ideally, the only database objects (table spaces and indexes) in buffer pool BP0 will be those associated with the DB2 catalog and directory.
- You should dedicate a 4K buffer pool to the 4K table spaces in the work file database (just as you should dedicate a 32K buffer pool to the 32K table spaces in the work file database). Organizations very often use BP7 for this purpose, because (in a non-data sharing DB2 system) the work file database is named DSNDB07. The "7" in BP7 is then a handy reminder of the role of this buffer pool.
- You should have a default 4K buffer pool for table spaces used by applications, and a default pool for indexes defined on those table spaces.
- You might want to have a 4K buffer pool that you would use to "pin" objects in memory (i.e., cache objects in memory in their entirety). You might decide to do this for certain table spaces and indexes that a) are accessed very frequently by important programs and b) are not particularly large (it would take a lot of buffers to pin a really big table space or index in memory). Note that a "pinning" buffer pool should be defined with PGSTEAL(NONE), so as to let DB2 know that you want to use the pool to completely cache objects assigned to it.
- You might want to have a 4K pool that you would use for monitoring and diagnostic purposes. Suppose, for example, that this pool is BP12, and you have a table space for which you want access activity information. You could then assign that table space temporarily to BP12 and know that the associated information provided by your DB2 monitor or via the DB2 command -DISPLAY BUFFERPOOL(BP12) DETAIL pertains to that one table space. That is a cheaper (in terms of CPU cost) and easier way to get pretty detailed object-specific access activity information versus turning on one or more DB2 performance trace classes.
- Beyond this basic set-up, you could also consider assigning table spaces that are accessed in a mostly random fashion to a buffer pool that's different from a pool used for table spaces that see a lot of large page scans (and associated indexes would likewise go in two different buffer pools). This would depend, of course, on your being able to determine this division of objects based on predominant access patterns.
All told, you might end up with something like 6 to 8 different 4K buffer pools. I'd prefer that to having 15 or 20 or 25 or more 4K pools. There is often goodness in simplicity.
