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.
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. You can also use a value
of -1, which indicates that the size of the
buffer pool should be the value found in the buffpage database
configuration parameter.
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.