Each database requires at least one buffer pool. However, depending on your needs you may choose to create several buffer pools, each of a different size, for a single database. The CREATE, ALTER, and DROP BUFFERPOOL statements allow you to create, change, or remove a buffer pool. You can specify which data is cached in a buffer pool with the CREATE TABLESPACE and ALTER TABLESPACE statements.
The buffpage configuration parameter specifies the size of any buffer pool, if the buffer pool's size is specified as -1 in the SYSCAT.BUFFERPOOLS catalog view. (Otherwise this parameter is ignored.) A buffer pool's size can be set with the DDL statements ALTER BUFFERPOOL or CREATE BUFFERPOOL.
A new database has a default buffer pool called IBMDEFAULTBP with a size determined by the platform. Once a database is created or migrated, then other buffer pools can be created for it.
When working on your database design, you may have determined that tables with 8 KB page sizes were best. As a result, you should create a buffer pool with an 8 KB page size (along with one or more table spaces with the same page size).
In a partitioned database environment, each buffer pool for a database has the same default definition on all database partitions (unless it was otherwise specified in the CREATE BUFFERPOOL statement, or the buffer pool's size was changed for a particular database partition with the ALTER BUFFERPOOL statement).
When you create a table space with a page size of 4 KB and do not assign it to a specific buffer pool, the table space is assigned to the default buffer pool. If you create a table space with a page size greater than 4 KB (8 KB, 16 KB, 32 KB) you should assign it to a buffer pool that uses a page size that is the same. If this buffer pool is currently not active, DB2 will attempt to assign the table space to an active buffer pool that uses an identical page size (if one is available). This assignment, if made, is temporary. When the database is activated again, and the originally specified buffer pool is active, then DB2 assigns the table space to that buffer pool.
You cannot use the ALTER TABLESPACE statement to add the table space to a buffer pool that uses a different page size.
When creating or altering buffer pools, the total memory that is required by all buffer pools must be available to the database manager so that all of the buffer pools can be allocated when the database is started. Should this memory not be available when a database is started, the Database Manager attempts to start the default buffer pool (IBMDEFAULTBP) and one of each buffer pool defined with a different page size, but only with a minimal size of 16 pages each. The size of this minimal buffer pool can be overridden with the registry variable DB2_OVERRIDE_BPF. See Appendix E, DB2 Registry and Environment Variables for more information on this and other registry and environment variables. A warning message is returned with each failed attempt to start a buffer pool; the database continues in this operational state until its configuration is changed and the database can be fully restarted.
The reason for allowing the database manager to start with minimal-sized values is to allow you to connect to the database. You can then immediately reconfigure the buffer pool sizes; or, to perform other critical tasks. Do not consider operating the database for an extended time in such a state.
| Note: | Although the size and attributes associated with the default buffer pool can be changed, it cannot be dropped. Also, there is a minimum size for each buffer pool that is based on the platform being used. |
There are advantages to having a large amount of memory allocated to buffer pools. For example, larger buffer pool sizes:
If any of the following conditions apply to your system, you should use only a single buffer pool:
If your system is not constrained by these conditions, then consider using more than one buffer pool for the following potential performance improvements: