DB2 10.5 for Linux, UNIX, and Windows

CREATE BUFFERPOOL statement

The CREATE BUFFERPOOL statement defines a buffer pool at the current server. Buffer pools are defined on members which can access data partitions.

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

Read syntax diagramSkip visual syntax diagram
                                       .-IMMEDIATE-.   
>>-CREATE BUFFERPOOL--bufferpool-name--+-----------+------------>
                                       '-DEFERRED--'   

   .-ALL DBPARTITIONNUMS-----------------------------------.   
>--+-------------------------------------------------------+---->
   |                           .-,-----------------------. |   
   |                           V                         | |   
   '-DATABASE PARTITION GROUP----db-partition-group-name-+-'   

   .-SIZE--1000--AUTOMATIC----------------.      
>--+--------------------------------------+--●------------------>
   +-SIZE--number-of-pages----------------+      
   |       .-1000------------.            |      
   '-SIZE--+-----------------+--AUTOMATIC-'      
           '-number-of-pages-'                   

>--+-------------------+--●------------------------------------->
   '-| except-clause |-'      

   .-NUMBLOCKPAGES 0------------------------------------------------.   
>--+----------------------------------------------------------------+-->
   '-NUMBLOCKPAGES--number-of-pages--+----------------------------+-'   
                                     '-BLOCKSIZE--number-of-pages-'     

>--●--+--------------------------+--●--------------------------><
      '-PAGESIZE--integer--+---+-'      
                           '-K-'        

except-clause

|--EXCEPT ON--+-MEMBER--+--------------------------------------->
              '-MEMBERS-'   

      .-,-------------------------------------------------------------.      
      V                                                               |      
>--(----member-number1--+--------------------+--SIZE--number-of-pages-+--)--|
                        '-TO--member-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 the catalog (SQLSTATE 42710). The bufferpool-name must not begin with the characters 'SYS' (SQLSTATE 42939).
IMMEDIATE or DEFERRED
Indicates whether or not the buffer pool will be created immediately.
IMMEDIATE
The buffer pool will be created immediately. If there is not enough reserved space in the database shared memory to allocate the new buffer pool (SQLSTATE 01657) the statement is executed as DEFERRED.
DEFERRED
The buffer pool will be created when the database is deactivated (all applications need to be disconnected from the database). Reserved memory space is not needed; required memory will be allocated from the system.
ALL DBPARTITIONNUMS or DATABASE PARTITION GROUP
Identifies the members on which the buffer pool is to be defined. The default is ALL DBPARTITIONNUMS.
ALL DBPARTITIONNUMS
This buffer pool will be created on all members which can access all data partitions in the database.
DATABASE PARTITION GROUP db-partition-group-name, ...
Identifies the database partition group or groups to which the buffer pool definition applies. The buffer pool will be created only on members in the specified database partition groups. Each database partition group must exist in the database (SQLSTATE 42704).
SIZE
Specifies the size of the buffer pool. This size will be the default size for all members on which the buffer pool exists. The default is 1000 pages.
number-of-pages
The number of pages for the new buffer pool. The minimum number of pages is 2 and the maximum is architecture-dependent (SQLSTATE 42615).
AUTOMATIC
Enables self tuning for this buffer pool. The database manager adjusts the size of the buffer pool in response to workload requirements. The implicit or explicit number of pages that are specified is used as the initial size of the buffer pool. 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 the bp_cur_buffsz monitor element.
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 (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).

EXCEPT ON MEMBER or EXCEPT ON MEMBERS
Specifies the member or members for which the size of the buffer pool will be different than the default specified for the database partition group to which the member has access. If this clause is not specified, all members that can access the data partitions in the specified database partition group will have the same size as specified for this buffer pool.
member-number1
Specifies a member number for a member that has access to a data partition for which the buffer pool is created (SQLSTATE 42729).
TO member-number2
Specifies a range of member numbers. The value of member-number2 must be greater than or equal to the value of member-number1 (SQLSTATE 428A9). Each member identified by the member number range inclusive must have access to the data partition for which the buffer pool is created (SQLSTATE 428A9).
SIZE number-of-pages
The size of the buffer pool specified as the number of pages. The minimum number of pages is 2 and the maximum is architecture-dependent (SQLSTATE 42615).
PAGESIZE integer [K]
Defines the size of pages used for the buffer pool. The valid values for integer without the suffix K are 4096, 8192, 16 384, or 32 768. The valid values for integer with the suffix K are 4, 8, 16, or 32. Any number of spaces is allowed between integer and K, including no space. If the page size is not one of these values, an error is returned (SQLSTATE 428DE).

The default value is provided by the pagesize database configuration parameter, which is set when the database is created.

Notes