0

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

Stefke
  • 141
  • 8
  • 19

1 Answers1

1

I think this article will help you: http://www.ibm.com/developerworks/data/library/techarticle/0212wieser/index.html

Moving your large table into a different buffer pool may increase performance, but it depends on your use case. A relevant quote from the article:

Having more than one buffer pool can preserve data in the buffers. For example, you might have a database with many very-frequently used small tables, which would normally be in the buffer in their entirety to be accessible very quickly. You might also have a query that runs against a very large table that uses the same buffer pool and involves reading more pages than the total buffer size. When this query runs, the pages from the small, very frequently used tables are lost, making it necessary to re-read them when they are needed again. If the small tables have their own buffer pool, thereby making it necessary for them to have their own table space, their pages cannot be overwritten by the large query. This can lead to better overall system performance, albeit at the price of a small negative effect on the large query.

If you do decide to do this, you can only have one buffer pool per tablespace, so you would need to move your large table into its own tablespace. The article gives examples of creating tablespaces and buffer pools.

A table can be moved to another tablespace with ADMIN_MOVE_TABLE. I don't think it is risky. It captures changes that may be made to the source table during moving. The only thing it does is disable a few (rarely used) actions on the source table during moving.

You assign a buffer pool to a tablespace by specifying it in the CREATE TABLESPACE or ALTER TABLESPACE statement.

  • Hello, thanks for response. Yes I know I can have only one buffer poll per table space I meant to have two Buffer Pools and Two TSpaces. I have now MAXBP and MAXTS with all tables. TICKET is currently in MAXTS. I would like to create TICKETBP and TICKETTS. But how to dedicate that TICKETTS to this TICKET table?? All smaller tables I would like to keep in MAXTS. Should I move TICKET table from MAXTS to TICKETTS is that risky? And how to do it? I do not know.. I couldn't see guide for that in your link.. Regards – Stefke Feb 12 '13 at 10:14
  • Thank you dan. This looks greate. Right way to call this is using CALL method??correct me if I wrong?? CALL ADMIN_MOVE_TABLE (MAX (name of shema), TICKET, TICKETTS, ????, ????, null, null, null, null, null, MOVE ) yes?? 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? – Stefke Feb 12 '13 at 10:47
  • can you help me with this one maybe? – Stefke Feb 13 '13 at 08:55
  • @Stefke, your questions about how to do it seem answered straightforwardly by the documentation. I suggest you try it (try it first with a test table if you are worried) then ask a new question if you have a specific problem. –  Feb 13 '13 at 10:54