Creating buffer pools

Use the CREATE BUFFERPOOL statement to define a new buffer pool to be used by the database manager.

Before you begin

There needs to be enough real memory on the computer for the total of all the buffer pools that you created. The operating system also needs some memory to operate.

About this task

On partitioned databases, you can also define the buffer pool to be created differently, including different sizes, on each database partition. The default ALL DBPARTITIONNUMS clause creates the buffer pool on all database partitions in the database.

Procedure

To create a buffer pool using the command line:

Get the list of buffer pool names that exist in the database.
Issue the following SQL statement:
```
SELECT BPNAME FROM SYSCAT.BUFFERPOOLS
```
Choose a buffer pool name that is not currently found in the result list.
Determine the characteristics of the buffer pool you are going to create.
Ensure that you have the correct authorization ID to run the CREATE BUFFERPOOL statement.
Issue the CREATE BUFFERPOOL statement.
A basic CREATE BUFFERPOOL statement is:
```
    CREATE BUFFERPOOL buffer-pool-name
      PAGESIZE 4096
```

Results

If there is sufficient memory available, the buffer pool can become active immediately. By default new buffer pools are created using the IMMEDIATE keyword, and on most platforms, the database manager is able to acquire more memory. The expected return is successful memory allocation. In cases where the database manager is unable to allocate the extra memory, the database manager returns a warning condition stating that the buffer pool could not be started. This warning is provided on the subsequent database startup. For immediate requests, you do not need to restart the database. When this statement is committed, the buffer pool is reflected in the system catalog tables, but the buffer pool does not become active until the next time the database is started. For more information about this statement, including other options, see the CREATE BUFFERPOOL statement.

If you issue a CREATE BUFFERPOOL DEFERRED, the buffer pool is not immediately activated; instead, it is created at the next database startup. Until the database is restarted, any new table spaces use an existing buffer pool, even if that table space is created to explicitly use the deferred buffer pool.

Example

In the following example, the optional DATABASE PARTITION GROUP clause identifies the database partition group or groups to which the buffer pool definition applies:

    CREATE BUFFERPOOL buffer-pool-name 
      PAGESIZE 4096
      DATABASE PARTITION GROUP db-partition-group-name

If this parameter is specified, the buffer pool is created only on database partitions in these database partition groups. Each database partition group must currently exist in the database. If the DATABASE PARTITION GROUP clause is not specified, this buffer pool is created on all database partitions (and on any database partitions that are later added to the database).

For more information, see the CREATE BUFFERPOOL statement.