The CREATE BUFFERPOOL statement defines a new buffer
pool to be used by the database manager.
In a partitioned
database, a default buffer pool definition is specified for each database
partition, with the capability to override the size on specific database
partitions. Also, in a partitioned database, the buffer pool is defined
on all database partitions unless database partition groups are specified.
If database partition groups are specified, the buffer pool will only
be created on database partitions that are in those database partition
groups.
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-on-db-partitions-clause |-'
.-NUMBLOCKPAGES 0------------------------------------------------.
>--+----------------------------------------------------------------+-->
'-NUMBLOCKPAGES--number-of-pages--+----------------------------+-'
'-BLOCKSIZE--number-of-pages-'
>--●--+--------------------------+--●--------------------------><
'-PAGESIZE--integer--+---+-'
'-K-'
except-on-db-partitions-clause
|--EXCEPT ON--+-DBPARTITIONNUM--+------------------------------->
'-DBPARTITIONNUMS-'
.-,-------------------------------------------------------------------------.
V |
>--(----db-partition-number1--+--------------------------+--SIZE--number-of-pages-+--)--|
'-TO--db-partition-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; DB2® will
allocate the required memory from the system.
- ALL DBPARTITIONNUMS
- This buffer pool will be created on all database 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. If this parameter is specified, the
buffer pool will only be created on database partitions in these database
partition groups. Each database partition group must currently exist
in the database (SQLSTATE 42704). If the DATABASE PARTITION GROUP
clause is not specified, this buffer pool will be created on all database
partitions (and on any database partitions that are subsequently added
to the database).
- SIZE
- Specifies the size of the buffer pool. For a partitioned database,
this will be the default size for all database partitions on which
the buffer pool exists. The default is 1000 pages.
- number-of-pages
- The number of pages for the new buffer pool.
- 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 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 determined by the self-tuning memory manager (STMM). Note that
STMM enforces a minimum size for automatic buffer pools. 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).
- 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.
- 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.
- except-on-db-partitions-clause
- Specifies the database partition or partitions for which the size
of the buffer pool will be different than the default. If this clause
is not specified, all database partitions will have the same size
as specified for this buffer pool.
- EXCEPT ON DBPARTITIONNUMS
- Keywords that indicate that specific database partitions are specified.
DBPARTITIONNUM is a synonym for DBPARTITIONNUMS.
- db-partition-number1
- Specifies a database partition number that is included in the
database partitions for which the buffer pool is created.
- TO db-partition-number2
- Specify a range of database partition numbers. The value of db-partition-number2 must
be greater than or equal to the value of db-partition-number1 (SQLSTATE
428A9). All database partitions between and including the specified
database partition numbers must be included in the database 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 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 DB2 is
unable to obtain memory for the regular buffer pools, it will attempt
to start up 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
are supported for compatibility with previous versions of DB2 and with other database products.
These alternatives are non-standard and should not be used.
- NODE can be specified in place of DBPARTITIONNUM
- NODES can be specified in place of DBPARTITIONNUMS
- NODEGROUP can be specified in place of DATABASE PARTITION GROUP