This part acquaints you with Bufferpools in the database.
Introduction
The bufferpool is bit of a principle memory space which is assigned by the database administrator. The motivation behind bufferpools is to reserve table and record information from circle. All databases have their own bufferpools. A default bufferpool is made at the hour of formation of new database. It called as "IBMDEFAULTBP". Contingent upon the client necessities, it is conceivable to make various bufferpools. In the bufferpool, the database director puts the table line information as a page. This page remains in the bufferpool until the database is shutdown or until the space is composed with new information. The pages in the bufferpool, which are refreshed with information yet are not composed onto the plate, are designated "Filthy" pages. After the refreshed information pages in the bufferpool are composed on the circle, the bufferpool is prepared to take another information.
Relationship between tablespaces and bufferpools
Each table space is related with a particular cradle pool in a database. One tablespace is related with one bufferpool. The size of bufferpool and tablespace must be same. Numerous bufferpools permit you to arrange the memory utilized by the database to expand its general execution.
Bufferpool sizes
The size of the bufferpool page is set when you utilize the "Make DATABASE" order. On the off chance that you don't determine the page size, it will take default page size, which is 4KB. When the bufferpool is made, it is preposterous to expect to adjust 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 make another bufferpool for database server, you need two parameters to be specific, "bufferpool name" and "size of page". The accompanying question is executed to make another bufferpool.
Language structure: [In the sentence structure below,'bp_name' shows bufferpool name and 'size' demonstrates size for page you have to pronounce for bufferpools (4K,8K,16K,32K)]
db2 create bufferpool <bp_name> pagesize <size>
Model: [To make another bufferpool with name "bpnew" and size "8192"(8Kb).]
db2 create bufferpool bpnew pagesize 8192
Output:
DB20000I The SQL command completed successfully.
Dropping the bufferpool
Prior to dropping the bufferpool, it is required to check if any tablespace is allocated 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.