1. I updated the Buffer Pool in database configure parameter
update db cfg for sample using buffpage 5000

2. Then I went to control center to check the Buffer Pool in SAMPLE, but I found the buffer pool size was still 1000, not the new 5000

3. I created a new buffer pool for SAMPLE in Control Center
Creat bufferpool BPNEW size 2000

4. Then I checked the Buffer Pool parameter, it was still 5000

Is there any relationship between them?

Any help would be greatly appreciated!

In previous versions of DB2 (prior to version 8), you could create or alter a buffer pool using a default size using buffpage, and the value of buffpage would be taken as the default. In Version 8, you should explicitly specify the size of your buffer pools, using the SIZE keyword on the ALTER BUFFERPOOL or CREATE BUFFERPOOL SQL statements. The change can be immediate or deferred to restart depending on which parameters are used. Also note that the Control Center caches information and sometimes you need to use the refresh option on the menu.

Thanks you, Marcus_A!

Just now I did some tests on DB2 UDB 7.1 & 8.1, and searched documents, I think I should understand buffer pool in this way:

1. When I create a database, the BUFFPAGE size is the same as IBMDEFAULTBP, and database will use IBMDEFAULTBP for the database

2. the BUFFPAGE parameter of db is only used when one of defined buffer pool size -1.

3. When I want to bind the buffer pool to one table which is very large, I should put this table in one specific tablespace and bind the tablespace with specific buffer pool. Could I bind one table directly with one buffer pool?

4. Once I define 2 or more buffer pool, and startup the database, the operation system will allocation memory to satisfy all the defined buffer pool.

Do I understand right? Hopefully you guys shed some light.

Buffer pools can only be allocated to tablespaces, not tables. Once a database is activated (either via the activate command or a connection to the database) all bufferpool memory in that database is allocated.

Bufferpool size and allocation is a very important performance configuration parameter. It is often better to have one large buffer pool instead of multiple smaller ones, especially in an OLTP database. However if you want multiple buffer pools, here is my suggestion:

BP1 - small and medium size tables and all indexes
BP2 - very large tables that are frequently accessed via tablespace scan, probably defined as 16K or 32K page size.

Note that you need a separate bufferpool for each page size (4K, 8K, 16K, or 32K) used in the tablespace.

The default values for buffer pool size are too small for a production system. On most database servers, the buffer pools should be at least 50% of total physical memory available on the machine (allowing for memory used by other DB2 resources, the OS or other processes).

I would recommend using the database configuration assistant on the Control Center to come up with a good starting point for bufferpools and other DB2 memory allocations.

Marcus_A , thanks you so much, you made me more clear about buffer pool.