Sunday, April 30, 2017

DB2 for z/OS: Something You Might Not Know About Large Page Frames in a Post-DB2 10 Environment

I well remember the first time I saw it. August, 2016. I was looking at output from the DB2 for z/OS command -DISPLAY BUFFERPOOL(ACTIVE) DETAIL, issued on a DB2 11 system, that a client had sent to me for review. I appreciated the fact that I could see in this command output information about DB2's use of large real storage page frames for page-fixed buffer pools (in a DB2 10 environment, one had to issue -DISPLAY BUFFERPOOL -- for an individual pool, versus for all active pools -- with an odd syntax to get this information). I saw pools for which PGFIX(YES) had been specified, and observed that, as expected, DB2 showed that the preferred real storage frame size for these pools was 1 MB. Then I noticed something I couldn't explain: there was a buffer pool, with a PGFIX(YES) specification, and DB2 was telling me, 

DSNB546I  - PREFERRED FRAME SIZE 4K

Huh? Why would DB2 prefer 4 KB-sized page frames for that pool? PGFIX(YES) buffer pools are supposed to be backed by 1 MB page frames, right? The indicated preference for 4 KB page frames was not the result of the pool being smaller than 1 MB: the pool's VPSIZE value was several thousand, and all it takes is 256 buffers of 4 KB apiece to fill a 1 MB frame. I continued through the -DISPLAY BUFFERPOOL output, and got even more confused. There were some PGFIX(YES) pools for which 1 MB was the indicated page frame size preference, and other PGFIX(YES) pools for which 4 KB was seen to be the preferred real storage page frame size. There did not appear to me to be any rhyme or reason for this variance. I moved on, and ultimately forgot about the strange-looking DISPLAY BUFFERPOOL output.

Fast forward about seven months, and I'm talking to a group of people about DB2 for z/OS buffer pools and large real storage page frames. I told my audience that DB2 will automatically seek to allocate a PGFIX(YES) buffer pool using 1 MB page frames. "That's not true," said a person in the room. I was pretty sure that this guy was wrong on that point, but in the interest of time I told him, "Let's talk about this offline." Over the next couple of days, this individual and I exchanged e-mails on the topic, and he made a very good argument in support of his contention and I tried some things on an IBM-internal DB2 for z/OS subsystem and, by golly, the man was right.

Here's the deal: in a DB2 10 for z/OS environment, 1 MB page frames will be preferred for a buffer pools for which PGFIX(YES) has been specified. In a DB2 11 (or later) system, that may be the case. Why "may," instead of "will?" Because DB2 11 introduced the FRAMESIZE option for the -ALTER BUFFERPOOL command. That option lets you tell DB2 of your preference for page frame size for a buffer pool, and for a PGFIX(YES) pool your preference becomes DB2's preference (assuming that there are enough buffers, per the pool's VPSIZE value, to fill at least one of the page frames of the size indicated via the FRAMESIZE specification). As DB2 10 did not have a FRAMESIZE option for ALTER BUFFERPOOL, that specification was, in essence, implicitly conjoined with the PGFIX specification -- when you requested, in a DB2 10 system, that a pool's buffers be fixed in the z/OS LPAR's real storage, you were also implicitly requesting allocation of the pool's buffers in 1 MB real storage page frames (subject to the availability of such page frames in the LPAR).

DB2 11 for z/OS introduced a choice for real storage page frame size preference for a page-fixed buffer pool: 1 MB or 2 GB. The FRAMESIZE option was added to the -ALTER BUFFERPOOL command to enable explicit specification of the preferred page frame size for a pool. It was made pretty clear that use of 2 GB page frames for a buffer pool required a FRAMESIZE(2G) specification for the pool. Lots of people (myself included) assumed that specifying FRAMESIZE(1M) was not required if one wanted a page-fixed buffer pool to be allocated using 1 MB page frames. We in this camp believed that FRAMESIZE(1M) was automatically specified "under the covers" in a DB2 11 system when an -ALTER BUFFERPOOL command with PGFIX(YES) was issued. As it turns out, that was a FALSE ASSUMPTION on our part. The facts are these: first, in a DB2 11 (or later) system, 1 MB will be the preferred real storage page frame size, even without a FRAMESIZE(1M) request, for a pool that was page-fixed and in-use in the DB2 10 environment, prior to the migration of the system to DB2 11. Second, for a pool newly allocated in a DB2 11 (or later) environment, an explicit FRAMESIZE(1M) specification is required if one wants 1 MB to be the preferred real storage page frame size for a pool.

To put this another way: as part of the migration of a DB2 10 system to DB2 11, existing page-fixed buffer pools were "migrated" to the DB2 11 environment with an automatic FRAMESIZE(1M) specification added to the pools' definition. That makes sense, because it preserves the behavior of the pools in the DB2 11 environment that was in effect in the DB2 10 system. For new pools allocated in a DB2 11 (or later) system, because requesting a page frame size preference is a separate action from requesting page-fixed buffers, you have to explicitly specify FRAMESIZE(1M) or FRAMESIZE(2G) in order make the preferred frame size 1 MB or 2 GB, respectively.

Here are your take-aways:

  1. If your DB2 for z/OS environment is at Version 11 (or later), issue the command DISPLAY BUFFERPOOL(ACTIVE) DETAIL, and examine the output for each pool. Do you see any pools for which the PGFIX attribute is YES, and the indicated PREFERRED FRAME SIZE is 4K? If yes, it is likely that those pools were in use before the system was migrated to DB2 11. If you want 1 MB page frames to be used for the pools, alter them with a FRAMESIZE(1M) specification.
  2. Even if all of the PREFERRED FRAME SIZE attributes for your buffer pools are as you want them to be, give some thought to altering each pool (or at least each pool for which PGFIX(YES) is an attribute) with the appropriate FRAMESIZE specification. I think that there is value in having an explicit FRAMESIZE specification for each of your buffer pools (or, again, at least each of the page-fixed pools), even if that explicit frame size preference is the same as the one that is in effect by default for a pool. This serves to get you into the "make it clear by making it explicit" mind set, and that can be a sound aspect of your buffer pool management strategy.
As always, thanks for visiting the blog.

4 comments:

  1. Wow - that's dangerous, we have 200Gb LFAs in 320Gb lpars to back our buffer pools, when migrating to v11 we would have run into trouble trying to back this with 4k frames on top of the LFA allocation. Thanks for the tip-off!

    ReplyDelete
    Replies
    1. I think you may have misinterpreted what I wrote. It appears that you are concerned that your PGFIX(YES) buffer pools, currently backed by 1 MB page frames, will be backed by 4 KB frames when the system is migrated to DB2 11 from DB2 10. That is NOT the case. A PGFIX(YES) buffer pool in a DB2 10 system will basically get FRAMESIZE(1M) implicitly added to its definition when the system is migrated to DB2 11. A PGFIX(YES) buffer pool will have a "preferred frame size" of 4 KB in a DB2 11 environment if a) the pool either did not exist when the system was at the DB2 10 level OR it did exist when the system was at the DB2 10 level but as a PGFIX(NO) pool, AND b) the pool was not explicitly altered with a FRAMESIZE(1M) specification in the DB2 11 environment.

      Robert

      Delete
  2. Hello Robert,

    I have the following situation:
    1) Created a PBG (maxpartition 20)
    2) Inserted 10 million rows
    This resulted in 10 partitions
    3) Deleted 2 million rows (with FETCH FIRST 2000000 ROWS ONLY;

    REORG_DROP_PGB_PARTS is enabled.

    4) Did run a reorg with inline stats => RC0008
    11:52:31.40 DSNUSVAL - INVALID DATA SET OR PARTITION NUMBER 9
    SPECIFIED FOR TABLESPACE PAULDB.BENCHMRK

    Is this a bug or works as designed?

    ReplyDelete
    Replies
    1. Could you provide the error message received for the REORG (DSNUSVAL is a CSECT, as opposed to an error message)? Also, what did your REORG TABLESPACE utility control statement look like?

      Robert

      Delete