Although each database requires at least one buffer pool, you can create several buffer pools, each of a different size or with a different page size, for a single database that has table spaces of more than one page size.
You can use the ALTER BUFFERPOOL statement to resize a buffer pool.
A new database has a default buffer pool called IBMDEFAULTBP, with a default page size that is based on the page size that was specified at database creation time. The default page size is stored as an informational database configuration parameter called pagesize. When you create a table space with the default page size, and if you do not assign it to a specific buffer pool, the table space is assigned to the default buffer pool. You can resize the default buffer pool and change its attributes, but you cannot drop it.
After you create or upgrade a database, you can create additional buffer pools. If you create a database with an 8-KB page size as the default, the default buffer pool is created with the default page size (in this case, 8 KB). Alternatively, you can create a buffer pool with an 8-KB page size, as well as one or more table spaces with the same page size. This method does not require that you change the 4-KB default page size when you create the database. You cannot assign a table space to a buffer pool that uses a different page size.
If, when you create a buffer pool with the CREATE BUFFERPOOL statement, you do not specify a size, the buffer pool size is set to AUTOMATIC and is managed by DB2. To change the bufferpool size later, use the ALTER BUFFERPOOL statement.
In a partitioned database environment, each buffer pool for a database has the same default definition on all database partitions, unless it was specified otherwise in the CREATE BUFFERPOOL statement, or the bufferpool size for a particular database partition was changed by the ALTER BUFFERPOOL statement.
The self-tuning memory manager (STMM) is ideal for tuning systems that have multiple buffer pools.
When you create a buffer pool or alter a buffer pool, 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 starts. If you create or alter buffer pools while the database manager is online, additional memory should be available in database global memory. If you specify the DEFERRED keyword when you create a new buffer pool or increase the size of an existing buffer pool, and the required memory is unavailable, the database manager executes the changes the next time the database is activated.
If this memory is not available when the database starts, the database manager uses only the system buffer pools (one for each page size) with a minimal size of 16 pages, and a warning is returned. The database continues in this state until its configuration is changed and the database can be fully restarted. Although performance might be suboptimal, you can connect to the database, re-configure the buffer pool sizes, or perform other critical tasks. When these tasks are complete, restart the database. Do not operate the database for an extended time in this state.
To avoid starting the database with system buffer pools only, use the DB2_OVERRIDE_BPF registry variable to optimize use of the available memory.