Assigning database objects to buffer pools

How you assign data to buffer pools can have a significant impact on performance.

About this task

Objects in the catalog and directory always use the following buffer pools, and the assignments cannot be changed: BP0, BP8K0, BP16K0, and BP32K.

It is best to separate the buffer pools for DB2® catalog and directory from the buffer pools for user data to isolate catalog and directory activities. In most cases, choose buffer pools other than the default buffer pools for user data, user indexes, and work files.

To assign database objects to particular buffer pools:

• For table spaces and indexes, issue one of the following SQL statements and specify the BUFFERPOOL option:
  • CREATE TABLESPACE
  • ALTER TABLESPACE
  • CREATE INDEX
  • ALTER INDEX
  • CREATE DATABASE
  • ALTER DATABASE
• For work files, issue an ALTER TABLESACE statement to change the buffer pool assignment of that table spaces. BP0 is the default buffer pool for sorting. It has a default size of 20000 and a minimum size of 2000. As with any other buffer pool, you can use the ALTER BUFFERPOOL command to change the size of BP0.

Results

The buffer pool is allocated when the table space or index that is assigned to it is first opened.