Modifying buffer pools

There are a number of reasons why you might want to modify a buffer pool, for example, to enable self-tuning memory. To do this, you use the ALTER BUFFERPOOL statement.

Before you begin

The authorization ID of the statement must have SYSCTRL or SYSADM authority.

About this task

When working with buffer pools, you might need to do one of the following tasks:

Enable self tuning for a buffer pool, allowing the database manager to adjust the size of the buffer pool in response to your workload.
Modify the block area of the buffer pool for block-based I/O.
Add this buffer pool definition to a new database partition group.
Modify the size of the buffer pool on some or all database partitions.

To alter a buffer pool using the command line, do the following:

To get the list of the buffer pool names that already exist in the database, issue the following statement:
```
    SELECT BPNAME FROM SYSCAT.BUFFERPOOLS
```
Choose the buffer pool name from the result list.
Determine what changes must be made.
Ensure that you have the correct authorization ID to run the ALTER BUFFERPOOL statement.
Note: Two key parameters are IMMEDIATE and DEFERRED. With IMMEDIATE, the buffer pool size is changed without having to wait until the next database activation for it to take effect. If there is insufficient database shared memory to allocate new space, the statement is run as DEFERRED.
With DEFERRED, the changes to the buffer pool will not be applied until the database is reactivated. Reserved memory space is not needed; the database manager allocates the required memory from the system at activation time.
Use the ALTER BUFFERPOOL statement to alter a single attribute of the buffer pool object. For example:
```
    ALTER BUFFERPOOL buffer pool name SIZE number of pages
```

The buffer pool name is a one-part name that identifies a buffer pool described in the system catalogs.
The number of pages is the new number of pages to be allocated to this specific buffer pool.

The statement can also have the DBPARTITIONNUM <db partition number> clause that specifies the database partition on which the size of the buffer pool is modified. If this clause is not specified, the size of the buffer pool is modified on all database partitions except those that have an exception entry in SYSCAT.BUFFERPOOLDBPARTITIONS. For details on using this clause for database partitions, see the ALTER BUFFERPOOL statement.

Changes to the buffer pool as a result of this statement are reflected in the system catalog tables when the statement is committed. However, no changes to the actual buffer pool take effect until the next time the database is started, except for successful ALTER BUFFERPOOL requests specified with the default IMMEDIATE keyword.

There must be enough real memory on the computer for the total of all the buffer pools that you have created. There also needs to be sufficient real memory for the rest of the database manager and for your applications.