SQL Reference

CREATE BUFFERPOOL

The CREATE BUFFERPOOL statement creates a new buffer pool to be used by the database manager. Although the buffer pool definition is transactional and the entries will be reflected in the catalog tables on commit, the buffer pool will not become active until the next time the database is started.

In a partitioned database, a default buffer pool definition is specified for each partition or node, with the capability to override the size on specific partitions or nodes. Also, in a partitioned database, the buffer pool is defined on all partitions unless nodegroups are specified. If nodegroups are specified, the buffer pool will only be created on partitions that are in those nodegroups.

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

>>-CREATE--BUFFERPOOL--bufferpool-name-------------------------->
 
      .-ALL NODES------------------------.
>-----+----------------------------------+---------------------->
      |            .-,-----------------. |
      |            V                   | |
      '-NODEGROUP-----nodegroup-name---+-'
 
>----SIZE--number-of-pages----+-----------------------------+--->
                              '-| except-on-nodes-clause |--'
 
          .-PAGESIZE--4096------------.
>----*----+---------------------------+--*---------------------->
          '-PAGESIZE--integer--+----+-'
                               '-K--'
 
     .-NOT EXTENDED STORAGE--.
>----+-----------------------+---*-----------------------------><
     '-EXTENDED STORAGE------'
 
except-on-nodes-clause
 
|---EXCEPT ON----+-NODE--+-------------------------------------->
                 '-NODES-'
 
        .-,---------------------------------------------------------------.
        V                                                                 |
>----(-----node-number1--+--------------------+---SIZE--number-of-pages---+---)->
                         '-TO--node-number2---'
 
>---------------------------------------------------------------|
 

Description

bufferpool-name

Names the buffer pool. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The bufferpool-name must not identify a buffer pool that already exists in a catalog (SQLSTATE 42710). The bufferpool-name must not begin with the characters "SYS" or "IBM" (SQLSTATE 42939).

ALL NODES

This buffer pool will be created on all partitions in the database.

NODEGROUP nodegroup-name, ...

Identifies the nodegroup or nodegroups to which the buffer pool definition is applicable. If this is specified, this buffer pool will only be created on partitions in these nodegroups. Each nodegroup must currently exist in the database (SQLSTATE 42704). If the NODEGROUP keyword is not specified, then this buffer pool will be created on all partitions (and any partitions subsequently added to the database).

SIZE number-of-pages

The size of the buffer pool specified as the number of pages. ⁶⁹ In a partitioned database, this will be the default size for all partitions where the buffer pool exists.

except-on-nodes-clause

Specifies the partition or partitions for which the size of the buffer pool will be different than the default. If this clause is not specified, then all partitions will have the same size as specified for this buffer pool.

EXCEPT ON NODES

Keywords that indicate that specific partitions are specified. NODE is a synonym for NODES.

node-number1

Specifies a specific partition number that is included in the partitions for which the buffer pool is created.

TO node-number2

Specify a range of partition numbers. The value of node-number2 must be greater than or equal to the value of node-number1 (SQLSTATE 428A9). All partitions between and including the specified partition numbers must be included in the partitions for which the buffer pool is created (SQLSTATE 42729).

SIZE number-of-pages

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

PAGESIZE integer [K]

Defines the size of pages used for the bufferpool. The valid values for integer without the suffix K are 4 096, 8 192, 16 384 or 32 768. The valid values for integer with the suffix K are 4, 8, 16 or 32. An error occurs if the page size is not one of these values (SQLSTATE 428DE). The default is 4 096 byte (4K) pages. Any number of spaces is allowed between integer and K, including no space.

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 database extended storage configuration is turned on, pages that are being migrated out of this buffer pool, will NOT be cached in the extended storage.

Notes

• Until the next time the database is started, any table space that is created will use an already active buffer pool of the same page size. The database has to be restarted for the table space assignment to the new buffer pool to take effect.
• 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. If DB2 is unable to obtain the total memory for all buffer pools, it will attempt to start up only the default buffer pool. If this is unsuccessful, it will start up a minimal default buffer pool. In either of these cases, a warning will be returned to the user (SQLSTATE 01626) and the pages from all table spaces will use the default buffer pool.

⁶⁹

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.