Thursday, September 15, 2011

DB2 for z/OS Version 10: Moving Objects from One Buffer Pool to Another

Over the past 15 years or so, I have written magazine articles, and presented, and blogged on the topic of DB2 for z/OS buffer pools and their impact on application performance (two of my more recent blog entries in this category were posted in September and October of last year). Often, the message boils down to this: bigger buffer pool configuration = good. That said, there are times when enlarging a DB2 buffer pool configuration is not feasible. There may already be some pressure on the memory resource of your z/OS LPAR (as indicated, in my book, by a demand paging rate in excess of 10 per second during busy processing times -- check your z/OS monitor). There could be an organizational impediment to making your buffer pool configuration larger, in that the approval process for such a system change takes so long as to make you reluctant to put in the request. Whatever the reason, when you need to (or want to) make do with the DB2 buffers you have, you can often improve application response time and throughput by moving table spaces and/or indexes from one buffer pool (a pool with a high rate of disk read I/Os per second) to another (the target pool presumably being one with a relatively low rate of read I/Os per second). There are other reasons, as well, for moving a database object to a different buffer pool: you might want to use a buffer pool to manage parallelism for dynamic queries (as mentioned in the blog entry I posted last week), or you might isolate an object in its own pool for monitoring purposes, or you might be changing an object's page size (more on this momentarily). Whatever your situation might be, I want to let you know via this blog entry that some of the rules pertaining to changing the buffer pool assignment for a table space or an index changed with DB2 10 for z/OS. I'll use the rest of this entry to describe these changes.

I'll start by informing you of one thing that hasn't changed: if your DB2 subsystem is running in standalone mode (i.e., it's not a member of a DB2 data sharing group), and the object being moved from one buffer pool to another is a table space, and the buffer pool to which the table space will be assigned has the same page size as the pool to which the table space is currently assigned, and there are no already-existing "pending" definition changes for the table space or any of its associated indexes (pending object definition changes were introduced with DB2 10 in new-function mode -- more on this later) then 1) the ALTER TABLESPACE statement with the BUFFERPOOL specification can be issued at any time, and 2) the change will take place when the table space's data sets are next opened after the ALTER TABLESPACE statement has been issued (a common means of closing and reopening a table space's data sets is to issue a -STOP DATABASE command for the table space, followed by a -START DATABASE command). This behavior is the same as that which you'd see in a DB2 9 environment (or in a DB2 V8 system).

OK, now for the DB2 10-introduced changes.


More flexibility for DB2 data sharing sites. I like this change a lot. Prior to DB2 10, if one wanted to reassign a table space or an index to a different buffer pool, the target object had to be in the stopped state at the time of the issuance of the ALTER TABLESPACE or ALTER INDEX statement with the BUFFERPOOL specification. If you're a DBA, that's not so good from a convenience standpoint, as stopping a heavily-accessed table space or index may not be do-able in prime time, even if you issue the -STOP DATABASE command with the AT(COMMIT) option to utilize drain locking. You might have to do the -STOP at night and/or during a weekend. Yuck. Enter DB2 10 (in conversion mode, no less!), and that restriction is gone: you can issue the buffer pool reassigning ALTER TABLESPACE or ALTER INDEX statement at any time, even when DB2 is running in data sharing mode -- the target object does NOT have to be in the stopped state. The change will take effect, as before, when the object's data sets are next opened following the issuance of the ALTER statement.


Reassigning a table space to a buffer pool with a different page size. Before DB2 10, the only way to move a table space from a 4K buffer pool to (for example) an 8K pool (that is, to change the table space's page size) was to unload data from the table space, drop it, re-create it with the larger-page buffer pool specified, and re-load the previously unloaded data. Cumbersome. With DB2 10 (in new-function mode), you can effect this change for a universal table space (one that is not an XML table space) or a LOB table space via an ALTER TABLESPACE statement (specifying the buffer pool with the larger or smaller page size), and the change will take effect the next time the table space (in its entirety) is REORGed  with SHRLEVEL CHANGE or REFERENCE (this kind of table space definition modification is known as a "pending" change -- something new introduced with DB2 10, and a continuation of the online schema change capabilities introduced with DB2 V8 and expanded with subsequent DB2 releases). By the way, DB2 10 (in new-function mode) also enables conversion of a simple, segmented, or traditional table-controlled range-partitioned table space to a universal table space via ALTER and REORG.


Pending versus immediate definitional changes. For indexes, the DB2 10 change pertaining to buffer pool reassignment has to timing as it relates to the change actually taking effect. If the change is of the pending variety (new with DB2 10 in new-function mode), it will take effect when the index (in its entirety) is next reorganized via REORG INDEX with SHRLEVEL CHANGE (or via REORG TABLESPACE with SHRLEVEL CHANGE or REFERENCE -- and the REORG TABLESPACE route is required if there are also pending definition changes in existence for the table space associated with the index). Here is what would make an ALTER INDEX with a BUFFERPOOL specification a pending change (and ALL of the following conditions would have to be true):
  • The index's data sets have already been created.
  • The index is defined on a table in a universal table space, or on an XML table or an auxiliary table associated with a base table that is in a universal table space.
  • There are pending definition changes in existence for the index or the associated table space, or the buffer pool to which the index will be reassigned has a page size that is different from that of the index's current buffer pool (index page sizes other than 4K were introduced with DB2 9).
If any of the above conditions is not true, the index definition change is immediate.

For a pending change, buffer pool reassignment takes place the next time the entire index is reorganized via REORG INDEX with SHRLEVEL CHANGE or REORG TABLESPACE with SHRLEVEL CHANGE or REFERENCE.

If the change is of the immediate variety, and the page size of the "to" buffer pool is the same as that of the "from" buffer pool, reassignment of the index to the pool specified in the ALTER INDEX statement will take place when the index's data sets are next opened after the issuance of ALTER INDEX.

If it's an immediate change and the "to" buffer pool has a page size that is different from that of the "from" buffer pool, the index is placed in REBUILD-pending status and the buffer pool reassignment takes place when the index is rebuilt.

There you have it. I hope that this information will be useful for your buffer pool reassignment changes in a DB2 10 environment (or for DB2 10 migration planning, if you're currently using DB2 9 or DB2 V8).


  1. Thanks again Robert. Another article that has helped me out. I was attempting to add a couple new columns to a table was get an SQLCODE = -20385 with reason 2. I could see the TS was in AREOR state. Ran a reorg, still same state, since was in a test environment, unloaded then performed a load/replace. TS is still in AREOR state. Found your article here and ran REORG SHRLEVEL REFERENCE. Clears up my AREOR state and I am able to add the new columns. Upon further investigation I did find that a predecessor had changed the BP page size, but never followed through to completion.

    1. Yes, the ALTER to change the associated table space's page size (by assigning it to a buffer pool with a different buffer size), being a pending DDL change, would have placed the table space in AREOR status, and with the table space in that state an immediate ALTER such as adding columns to a table in the table space would not be allowed, as you discovered. Running the online REORG applied the pending change and removed the AREOR status, thereby allowing a subsequent ALTER to add columns to a table in the table space (a REORG with SHRLEVEL NONE does not accomplish in the case what an online REORG accomplishes).

      In the future, before executing an immediate ALTER action (such as adding columns to a table or making an allowable change to a column's data type specification), you could check the SYSPENDINGDDL catalog table to see if there are any pending changes for the associated table space awaiting materialization, or you check on this by issuing a -DISPLAY DATABASE command with the ADVISORY(AREOR) option. If you saw that the table's table space had pending DDL changes awaiting materialization, you could either materialize those changes via a REORG with SHRLEVEL CHANGE or SHRLEVEL REFERENCE, or you could issue an ALTER TABLESPACE statement for the table space with the DROP PENDING CHANGES option. Then you could successfully execute the desired immediate ALTER action.


  2. I was attempting to alter tablespace to PBG from segmented and got SQLCODE = -20385 with reason 8. But the reason 8 states
    "An ALTER TABLE statement with a DROP COLUMN clause is not allowed if the table definition is in an incomplete state".But my attempt was to convert to pbg not "drop column" then why would it give reason code 8?
    there was no advisory reorg,no pending states for tablespace,index,table.Not sure what would be reason ?

    1. Good question. In spite of what you see in the documentation regarding reason code 8 for SQL error code -20385 (, I don't think it applies exclusively to a situation in which ALTER TABLE DROP COLUMN is involved. I think that information is an "and" that follows from the first part of the reason code 8 explanation - that first part is, "A pending option is not allowed if the table contained in the table space or associated with the index is in an incomplete state." The reasons for a table being in an incomplete state are described on this page of the online Db2 for z/OS documentation: If the table in the table space you tried to alter is NOT in an incomplete state, per the information in the previously referenced documentation page, I don't know why you got the -20385. In that case you might need to open a case with IBM Support to determine why this happened.