SQL Reference

ALTER BUFFERPOOL

The ALTER BUFFERPOOL statement is used to do the following:

• modify the size of the buffer pool on all partitions (or nodes) or on a single partition
• turn on or off the use of extended storage
• add this buffer pool definition to a new nodegroup.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

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

Syntax

>>-ALTER--BUFFERPOOL--bufferpool-name--------------------------->
 
>-----+-+--------------------+--SIZE--number-of-pages--+-------><
      | '-NODE--node-number--'                         |
      +-+-NOT EXTENDED STORAGE-+-----------------------+
      | '-EXTENDED STORAGE-----'                       |
      '-ADD NODEGROUP--nodegroup-name------------------'
 

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.

NODE node-number

Specifies the partition on which size of the buffer pool is modified. The partition must be in one of the nodegroups for the buffer pool (SQLSTATE 42729). If this clause is not specified, then the size of the buffer pool is modified on all partitions on which the buffer pool exists that used the default size for the buffer pool (did not have a size specified in the except-on-nodes-clause of the CREATE buffer pool statement).

SIZE number-of-pages

The size of the buffer pool specified as the number of pages. ⁵⁶

EXTENDED STORAGE

If the extended storage configuration is turned on ⁵⁷ , pages that are being migrated out of this buffer pool, will be cached in the extended storage.

NOT EXTENDED STORAGE

Even if the extended storage configuration is turned on, pages that are being migrated out of this buffer pool, will NOT be cached in the extended storage.

ADD NODEGROUP nodegroup-name

Adds this nodegroup to the list of nodegroups to which the buffer pool definition is applicable. For any partition in the nodegroup that does not already have the bufferpool defined, the bufferpool is created on the partition using the default size specified for the bufferpool. Table spaces in nodegroup-name may specify this buffer pool. The nodegroup must currently exist in the database (SQLSTATE 42704).

Notes

• 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 nodegroups will use the default buffer pool.
• 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.

⁵⁶

The size can be specified with a value of (-1) which will indicate that the buffer pool size should be taken from the BUFFPAGE database configuration parameter.

⁵⁷

Extended storage configuration is turned on by setting the database configuration parameters NUM_ESTORE_SEGS and ESTORE_SEG_SIZE to non-zero values. See Administration Guide for details.