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
.-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
- If the buffer pool is created using the DEFERRED option, any table
space created in this buffer pool will use a small system buffer pool
of the same page size, until next database activation. The database
has to be restarted for the buffer pool to become active and for table
space assignments to the new buffer pool to take effect. The default
option is IMMEDIATE.
- 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 the database is unable to obtain memory
for the regular buffer pools, it will attempt to start with small
system buffer pools, one for each page size (4K, 8K, 16K and 32K).
In this situation, a warning will be returned to the user (SQLSTATE
01626), and the pages from all table spaces will use the system buffer
pools.
- 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.
- NODEGROUP can be specified in place of DATABASE PARTITION GROUP
- DBPARTITIONNUM or NODE can be specified in place of
MEMBER, except when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON
- DBPARTITIONNUMS or NODES can be specified in place of
MEMBERS, except when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON
DB2 10.5 for Linux, UNIX, and Windows