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
Best Answer
While you can associate tablespaces with different bufferpools using the
ALTER TABLESPACE
statement, tables can only be associated with one tablespace. You can't issueALTER 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).
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.