The ALTER BUFFERPOOL statement is used to modify the characteristics or behavior of a buffer pool. There are a number of reasons to use the ALTER BUFFERPOOL statement, for example, to enable self-tuning memory.
The ALTER BUFFERPOOL statement can modify a buffer pool
in the following ways:
- Modify the size of the buffer pool on all members or
on a single member
- 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--' '-MEMBER--member-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 database will allocate the required
memory from the system at activation time.
- MEMBER member-number
- Specifies
the member on
which the size of the buffer pool is modified. An exception entry
is created in the SYSCAT.BUFFERPOOLEXCEPTIONS catalog view. The member 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 members except
those that have an exception entry in SYSCAT.BUFFERPOOLEXCEPTIONS.
- 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. On subsequent database activations, the buffer pool size
is based on the last tuning value that is determined by the self-tuning
memory manager (STMM). The STMM enforces a minimum size for automatic
buffer pools, which is the minimum of the current size and 5000 pages.
To determine the current size of buffer pools that are enabled for
self tuning, use the MON_GET_BUFFERPOOL routine and examine the current
size of the buffer pools. The size of the buffer pool is found in
thebp_cur_buffsz monitor element. When AUTOMATIC
is specified, the MEMBER 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 member in the database partition group that
does not already have the buffer pool defined, the buffer pool is
created on the member 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, based on the NPAGES value 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.
NUMBLOCKPAGES
is not supported in a DB2® pureScale®
environment (SQLSTATE 56038).
- 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.
BLOCKSIZE is not supported in a
DB2 pureScale environment
(SQLSTATE 56038).
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, 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 syntax alternatives are
supported for compatibility with previous versions of DB2 and
with other database products. These alternatives are non-standard and should
not be used.
- DBPARTITIONNUM or NODE can be specified in place of MEMBER except when
the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.
- NODEGROUP can be specified in place of DATABASE PARTITION GROUP