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.
FOR TABLE SPACES AND INDEXES
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.
FOR TABLE SPACES
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).
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).