Managing buffer pools
The following stored procedures manage buffer pools for Amazon RDS for Db2 databases. To run
these procedures, the master user must first connect to the rdsadmin
database.
rdsadmin.create_bufferpool
Creates a buffer pool.
Syntax
db2 "call rdsadmin.create_bufferpool( '
database_name
', 'buffer_pool_name
',buffer_pool_size
, 'immediate
', 'automatic
',page_size
,number_block_pages
,block_size
)"
Parameters
The following parameters are required:
database_name
-
The name of the database to run the command on. The data type is
varchar
. buffer_pool_name
-
The name of the buffer pool to create. The data type is
varchar
.
The following parameters are optional:
buffer_pool_size
-
The size of the buffer pool in number of pages. The data type is
integer
. The default is-1
. immediate
-
Specifies whether the command runs immediately. The data type is
char
. The default isY
. automatic
-
Specifies whether to set the buffer pool to automatic. The data type is
char
. The default isY
. page_size
-
The page size of the buffer pool. The data type is
integer
. Valid values:4096
,8192
,16384
,32768
. The default is8192
. number_block_pages
-
The number of block pages in the buffer pools. The data type is
integer
. The default is0
. block_size
-
The block size for the block pages. The data type is
integer
. Valid values:2
to256
. The default is32
.
Usage notes
For information about checking the status of creating a buffer pool, see rdsadmin.get_task_status.
Examples
The following example creates a buffer pool called BP8
for a database
called TESTDB
with default parameters, so the buffer pool uses an 8 KB
page size.
db2 "call rdsadmin.create_bufferpool( 'TESTDB', BP8')"
The following example creates a buffer pool called BP16
for a
database called TESTDB
that uses a 16 KB page size with an initial page
count of 1,000 and is set to automatic. Db2 runs the command immediately. If you use an
initial page count of -1, then Db2 will use automatic allocation of pages.
db2 "call rdsadmin.create_bufferpool( 'TESTDB', 'BP16', 1000, 'Y', 'Y', 16384)"
The following example creates a buffer pool called BP16
for a
database called TESTDB
. This buffer pool has a 16 KB page size with an
initial page count of 10,000. Db2 runs the command immediately using 500 block pages
with a block size of 512.
db2 "call rdsadmin.create_bufferpool( 'TESTDB', 'BP16', 10000, 'Y', 'Y', 16384, 500, 512)"
rdsadmin.alter_bufferpool
Alters a buffer pool.
Syntax
db2 "call rdsadmin.alter_bufferpool( '
database_name
', 'buffer_pool_name
',buffer_pool_size
, 'immediate
', 'automatic
',change_number_blocks
,number_block_pages
,block_size
)"
Parameters
The following parameters are required:
database_name
-
The name of the database to run the command on. The data type is
varchar
. buffer_pool_name
-
The name of the buffer pool to alter. The data type is
varchar
. buffer_pool_size
-
The size of the buffer pool in number of pages. The data type is
integer
.
The following parameters are optional:
immediate
-
Specifies whether the command runs immediately. The data type is
char
. The default isY
. automatic
-
Specifies whether to set the buffer pool to automatic. The data type is
char
. The default isN
. change_number_blocks
-
Specifies whether there is a change to the number of block pages in the buffer pool. The data type is
char
. The default isN
. number_block_pages
-
The number of block pages in the buffer pools. The data type is
integer
. The default is0
. block_size
-
The block size for the block pages. The data type is
integer
. Valid values:2
to256
. The default is32
.
Usage notes
For information about checking the status of altering a buffer pool, see rdsadmin.get_task_status.
Examples
The following example alters a buffer pool called BP16
for a database
called TESTDB
to non-automatic, and changes the size to 10,000 pages.
Db2 runs this command immediately.
db2 "call rdsadmin.alter_bufferpool( 'TESTDB', 'BP16', 10000, 'Y', 'N')"
rdsadmin.drop_bufferpool
Drops a buffer pool.
Syntax
db2 "call rdsadmin.drop_bufferpool( '
database_name
', 'buffer_pool_name
'"
Parameters
The following parameters are required:
database_name
-
The name of the database that the buffer pool belongs to. The data type is
varchar
. buffer_pool_name
-
The name of the buffer pool to drop. The data type is
varchar
.
Usage notes
For information about checking the status of dropping a buffer pool, see rdsadmin.get_task_status.
Examples
The following example drops a buffer pool called BP16
for a database
called TESTDB
.
db2 "call rdsadmin.drop_bufferpool( 'TESTDB', 'BP16')"