How to create Buffer Pool in Database dedicated only for ONE BIG table?

Question

I have table TICKET with 400K records in database (DB2).

I wish to create one huge buffer pool which will be dedicated only to this one big table for faster response. What are the steps to do it?

Also at the moment I have one Buffer Pool which coovers whole Table space with all the tables (about 200) in database! what will happen then with that my specific table in that old firstly created buffer pool? should that table stay in first buffer pool or how to remove from that buffer pool?? Also are there some risks for this action???

Thank you

Answer 1

1. Create the new bufferpool.
2. Create a new tablespace that is associated with that bufferpool.
3. Create a copy of the table you want and place it in the new tablespace.
4. Copy the data from the original table to the new table.
5. 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.