The ALTER BUFFERPOOL statement is used to do the following:
- Modify the size of the buffer pool on all database partitions
or on a single database partition
- Enable or disable automatic sizing of the buffer pool
- Add this buffer pool definition to a new database partition group
- Modify the block area of the buffer pool for block-based I/O
Invocation
This statement can be embedded
in an application program or issued interactively. It is an executable
statement that can be dynamically prepared only if DYNAMICRULES run
behavior is in effect for the package (SQLSTATE 42509).
Authorization
The privileges held by the
authorization ID of the statement must include SYSCTRL or SYSADM authority.
Syntax
>>-ALTER BUFFERPOOL--bufferpool-name---------------------------->
.-IMMEDIATE-.
>--+-+-----------+--+-------------------------------------+--SIZE--+-number-of-pages----------------+-+-><
| '-DEFERRED--' '-DBPARTITIONNUM--db-partition-number-' '-+-----------------+--AUTOMATIC-' |
| '-number-of-pages-' |
+-ADD DATABASE PARTITION GROUP--db-partition-group-name--------------------------------------------+
+-NUMBLOCKPAGES--number-of-pages--+----------------------------+-----------------------------------+
| '-BLOCKSIZE--number-of-pages-' |
'-BLOCKSIZE--number-of-pages-----------------------------------------------------------------------'
Description
- bufferpool-name
- Names the buffer pool. This is a one-part name. It is an SQL identifier
(either ordinary or delimited). It must be a buffer pool described
in the catalog.
- IMMEDIATE or DEFERRED
- Indicates whether or not the buffer pool size will be changed
immediately.
- IMMEDIATE
- The buffer pool size will be changed immediately. If there is
not enough reserved space in the database shared memory to allocate
new space (SQLSTATE 01657), the statement is executed as DEFERRED.
- DEFERRED
- The buffer pool size will be changed when the database is reactivated
(all applications need to be disconnected from the database). Reserved
memory space is not needed; the DB2® database
will allocate the required memory from the system at activation time.
- DBPARTITIONNUM db-partition-number
- Specifies the database partition on which the size of the buffer
pool is modified. An exception entry is created in the SYSCAT.BUFFERPOOLDBPARTITIONS
system catalog view. The database partition must be in one of
the database partition groups for the buffer pool (SQLSTATE 42729).
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.
- SIZE
- Specifies a new size for the buffer pool, or enables or disables
self tuning for this buffer pool.
- number-of-pages
- The number of pages for the new buffer pool size. If the buffer
pool is already a self-tuning buffer pool, and the SIZE number-of-pages clause
is specified, the alter operation disables self-tuning for this buffer
pool.
- AUTOMATIC
- Enables self tuning for this buffer pool. The database
manager adjusts the size of the buffer pool in response to workload
requirements. If the number of pages is specified, the current buffer
pool size is set to that value unless the deferred keyword is also
specified, in which case the number of pages will be ignored. Note
that the self-tuning memory manager (STMM) enforces a minimum size
for automatic buffer pools, and that any specified size is a one-time
setting - on subsequent database activations, the buffer pool size
is based on the last tuning value. To determine the current size of
buffer pools that are enabled for self tuning, use the GET
SNAPSHOT command and examine the current size of the buffer
pools (the value of the bp_cur_buffsz monitor
element). When AUTOMATIC is specified, the DBPARTITIONNUM clause cannot
be specified (SQLSTATE 42601).
- ADD DATABASE PARTITION GROUP db-partition-group-name
- Adds this database partition group to the list of database partition
groups to which the buffer pool definition is applicable. For any
database partition in the database partition group that does not already
have the buffer pool defined, the buffer pool is created on the database
partition using the default size specified for the buffer pool. Table
spaces in db-partition-group-name may specify
this buffer pool. The database partition group must currently exist
in the database (SQLSTATE 42704).
- NUMBLOCKPAGES number-of-pages
- Specifies the number of pages that should exist in the block-based
area. The number of pages must not be greater than 98 percent of the
number of pages for the buffer pool, as reported in NPAGES in SYSCAT.BUFFERPOOLS
(SQLSTATE 54052). Specifying the value 0 disables block I/O. The actual
value of NUMBLOCKPAGES used will be a multiple of BLOCKSIZE.
- BLOCKSIZE number-of-pages
- Specifies the number of pages in a block. The block size must
be a value between 2 and 256 (SQLSTATE 54053). The default value is
32.
Notes
- Only the buffer pool size can be changed dynamically (immediately).
All other changes are deferred, and will only come into effect after
the database is reactivated.
- If the statement is executed as deferred, the following is true:
Although the buffer pool definition is transactional and the changes
to the buffer pool definition will be reflected in the catalog tables
on commit, no changes to the actual buffer pool will take effect until
the next time the database is started. The current attributes of the
buffer pool will exist until then, and there will not be any impact
to the buffer pool in the interim. Tables created in table spaces
of new database partition groups will use the default buffer pool.
The statement is IMMEDIATE by default when that keyword applies.
- There should be enough real memory on the machine for the total
of all the buffer pools, as well as for the rest of the database manager
and application requirements.
- Syntax alternatives: The following
are supported for compatibility with previous versions of DB2 and with other database products.
These alternatives are non-standard and should not be used.
- NODE can be specified in place of DBPARTITIONNUM
- NODEGROUP can be specified in place of DATABASE PARTITION GROUP