DB2 - Bufferpools
This chapter introduces you to Bufferpools in the database.
Introduction
The bufferpool is portion of a main memory space which is allocated by the database manager. The purpose of bufferpools is to cache table and index data from disk. All databases have their own bufferpools. A default bufferpool is created at the time of creation of new database. It called as “IBMDEFAULTBP”. Depending on the user requirements, it is possible to create a number of bufferpools. In the bufferpool, the database manager places the table row data as a page. This page stays in the bufferpool until the database is shutdown or until the space is written with new data. The pages in the bufferpool, which are updated with data but are not written onto the disk, are called “Dirty” pages. After the updated data pages in the bufferpool are written on the disk, the bufferpool is ready to take another data.
Relationship between tablespaces and bufferpools
Each table space is associated with a specific buffer pool in a database. One tablespace is associated with one bufferpool. The size of bufferpool and tablespace must be same. Multiple bufferpools allow you to configure the memory used by the database to increase its overall performance.
Bufferpool sizes
The size of the bufferpool page is set when you use the “CREATE DATABASE” command. If you do not specify the page size, it will take default page size, which is 4KB. Once the bufferpool is created, it is not possible to modify the page size later
Listing the available bufferpools in the current database directory
Syntax: [The syntax below shows all available bufferpools in database]
db2 select * from syscat.bufferpools
Example: [To see available bufferpools in current database]
db2 select * from syscat.bufferpools
Output:
BPNAME BUFFERPOOLID DBPGNAME NPAGES PAGESIZE ESTORE
NUMBLOCKPAGES BLOCKSIZE NGNAME
------------------------------------------------------------
IBMDEFAULTBP
1 -
-2 4096 N 0 0 -
1 record(s) selected.
Creating the bufferpool
To create a new bufferpool for database server, you need two parameters namely, “bufferpool name” and “size of page”. The following query is executed to create a new bufferpool.
Syntax: [In the syntax below,‘bp_name’ indicates bufferpool name and ‘size’ indicates size for page you need to declare for bufferpools (4K,8K,16K,32K)]
db2 create bufferpool <bp_name> pagesize <size>
Example: [To create a new bufferpool with name “bpnew” and size “8192”(8Kb).]
db2 create bufferpool bpnew pagesize 8192
Output
DB20000I The SQL command completed successfully.
Dropping the bufferpool
Before dropping the bufferpool, it is required to check if any tablespace is assigned to it.
Syntax: [To drop the bufferpool]
drop bufferpool <bp_name>
Example: [To drop ‘bpnew’ named bufferpool]
db2 drop bufferpool bpnew
Output
DB20000I The SQL command completed successfully.
