DB2 Version 9.7 for Linux, UNIX, and Windows


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


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).


The privileges held by the authorization ID of the statement must include SYSCTRL or SYSADM authority.


Read syntax diagramSkip visual syntax diagram
>>-ALTER BUFFERPOOL--bufferpool-name---------------------------->

   | '-DEFERRED--'  '-DBPARTITIONNUM--db-partition-number-'        '-+-----------------+--AUTOMATIC-' |   
   |                                                                 '-number-of-pages-'              |   
   +-ADD DATABASE PARTITION GROUP--db-partition-group-name--------------------------------------------+   
   |                                 '-BLOCKSIZE--number-of-pages-'                                   |   


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.
Indicates whether or not the buffer pool size will be changed immediately.
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.
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.
Specifies a new size for the buffer pool, or enables or disables self tuning for this buffer pool.
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.
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.