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

  • I know the concept of Buffer Pool is unique to an entire database server instance, and that you can't create separate buffer pools if you don't install a new instance. Is it any different in DB2?
    – Marian
    Commented Feb 12, 2013 at 9:18
  • I forgot to specify, I was talking about my environment, which is MS SQL Server.
    – Marian
    Commented Feb 12, 2013 at 9:50
  • I do not know really...I think I read that I can create additional buffer pool but I need confirmation for this and also some guide?If someone knows?
    – Dejan
    Commented Feb 12, 2013 at 9:56
  • Well, @Mat can help you, I saw one of his answers is treating buffer pools in DB2.
    – Marian
    Commented Feb 12, 2013 at 10:06
  • 1
    Yes you can create more bufferpools. You can create as many as DB2 allows (not sure the limit) and as long as you have memory for them to use. Of course more objects means more management. But it is not unreasonable to create a new bufferpool if the need warrants it. Commented Feb 12, 2013 at 14:15

1 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.

  • Chris thank you for answer. In mean time I found that I can do it with ADMIN_MOVE_TABLE what is better option??? I will call it on this way CALL ADMIN_MOVE_TABLE (MAX (name of shema), TICKET, TICKETTS, ????, ????, null, null, null, null, null, MOVE ) What should I put in the place of index tablespace and LOB tablespace, that is how to determine what is my index tablespace and LOB tablespace for my TICKET table? Also I will made backup of database so in case somethng is wrong I suppose restore will return to its previpous tablespace MAXTS,right?
    – Dejan
    Commented Feb 12, 2013 at 14:48
  • TICKETTS in new wanted Table space and MAXTS is current tablespace
    – Dejan
    Commented Feb 12, 2013 at 14:50
  • thank you Chris for such great and detailed response. I have just one more question sorry- in this case when I do ADMIN_MOVE_TABLE are indexes in this case will be moved automatically to this new tablespace for indexes? Because I have about 30 indexes for TICKET table
    – Dejan
    Commented Feb 13, 2013 at 9:07
  • Yes. Indexes are automatically moved AFTER the table and data is moved. There is a parameter that will change the order to move table, create indexes, and then load data. There are pros and cons both ways. See DB2 Information Center: publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/… Commented Feb 13, 2013 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.