- Create the new bufferpool.
- Create a new tablespace that is associated with that bufferpool.
- Create a copy of the table you want and place it in the new tablespace.
- Copy the data from the original table to the new table.
- Drop the old table.
While you can associate tablespaces with different bufferpools using the
ALTER TABLESPACE statement, tables can only be associated with one tablespace. You can't issue
ALTER TABLE and switch the tablespace. So you unfortunately have to create a new table and move the data from one to the other.
EDIT: Also note that any indexes you have associated with the old table will also get dropped and have to be re-created.
EDIT: Code Example, note for this I am assuming a 4K tablespace. Please substitute your correct size if need be (ie, 4, 8, 16, or 32 K).
db2 create bufferpool TICKET_BP immediate size 250 automatic pagesize 4k;
--note I list automatic storage here. We prefer to use that. Plus, IBM is pushing to move
-- in that direction. This TS is for the table.
db2 create large tablespace TICKETTS pagesize 4k managed by automatic storage bufferpool TICKET_BP;
--this TS is for indexes.
db2 create large tablespace TICKET_IND_TS pagesize 4k managed by automatic storage bufferpool TICKET_BP;
db2 create large tablespace TICKET_LOB_TS pagesize 4k managed by automatic storage bufferpool TICKET_BP;
-- now you can run the stored proc to move the data. It essentially does the same thing as what I specified above, except that it can move the data during normal work loads, so it should have less impact on locking and such, but it may take longer I'm guessing.
db2 "call syscproc.admin_move_table('MAX','TICKET','TICKETTS','TICKET_IND_TS','TICKET_LOB_TS','','','','','','MOVE');
And yes, most definitely take backups before and after. Also note that if you allow any transactions during the data movement period you may lose those transactions if you have to rollback.