(DB2 v8.2 ESE/AIX v5.3)
by default all our application tables located in one TS now (USERSPACE1). We want to move large table(s) to separate TS(s) with their own buffer pool(s). How do we choose sizes of those bufferpools?

Another question:
some databases have bufferpool size setting =-1 and with BUFFPAGE=from 50,000 to 100,000,
others have buffer pools size =1000 or 40,000.
What is the better choice?

Thanks in advance

MarkhamDBA, I believe BUFFPAGE is or has been deprecated and shouldn't be used. Stick with the number of pages.

In V9.5 you can use the Automatic parameter with SIZE and DB2 will adjust the size according to workload.

If you can't use this, I don't know of a formula but you can make sum educated guesses.

How are the large tables accessed? If it is a tablespace scan, you need the bufferpool large enough to handle all the pages (if you want them left in the bufferpool). If it is Index access, then only a few pages (index and table) will be read into the bufferpool (regardless of the table size).

How many of the large tables will be accessed at the same time (assumming table space or partition scanning)? If one at a time, then the size of the largest table space would be a starting point.

Basically how many table and/or index pages do you want left in the bufferpool to reduce I/O?

Also keep in mind the various thresholds which will initiate taskes to remove pages.

PS Another scheme you might think sounds the same but is a little backward from your approach. That is to split out your Small tables (especially code type tables) into a separate bufferpool sized so that all the pages of all the tables will say resident. Since they are code tables, there will be little if any Changes made to the pages and DB2 will not be in any hurry to remove the pages. Then all your other data (medium and large) can be in one (or two) other 'as large as you can make them' bufferpools(s).

thanks for a prompt response.

- do you think using db2pd would be more effective - we will need to do less guessing and it will give straight numbers on how buffer pools are used?

- is it a general practice to create separate TSs and dedicated bufferpools for large (10-30 million rows) and heavily used (select, update, delete, insert - OLTP environment) tables?

thank you