DB2 Bufferpool

The database manager allocates an area of space in the main memory known as Bufferpools. The bufferpools aim to index data from the disk and cache table. Every database has its bufferpool. At the time of database creation, a new bufferpool is created by default. It is known as "IBMDEFAULTBP". We can create many bufferpools as per user needs. In addition, we can DROP BUFFERPOOL, ALTER BUFFERPOOL, CREATE BUFFERPOOL and CREATE BUFFERPOOL. In the DB2 pureScale environment, every member has its local bufferpool. The cluster caching facility maintains group buffer pools known as GBP.

When the data is from a certain row of the table, the database manager first locates the page that has the data into the buffer pool. Pages in the bufferpool stay there as long as the database is on and another page does not occupy the space. In the buffer pool, the pages that are updated with the data and are not written on the disk are known as "dirty" pages. Once the updated data pages in the bufferpool are rewritten on the disk, the buffer pool is considered ready to intake the new data.

Let's see how tablespaces and bufferpools are related:

Every tabelspace has an associated table space in a database. One bufferpool is for one tablespace. The size of the tablespace and bufferpool should be the same.

Bufferpool sizes:

When we execute the CREATE DATABASE command, the size of the buffer pool page is set. If we don't mention the page size, the default page size is considered, which is 4KB. When we

create the buffer pool once we cannot change the page size.

Let's see how we can see the available buffer pools:

By the use of the following statement, we can fetch the available buffer pools in the present database:

Syntax:

db2 "select * from syscat.bufferpools"    

Let's execute it:

Before that, we need to start DB2 and connect it with the database.

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from syscat.bufferpools

Output:

Create a Bufferpool

We can create a bufferpool with the common CREATE BUFFERPOOL. We have to pass two parameters that are bufferpool name and page size.

Syntax:

db2 create bufferpool <bufferpool_name> pagesize <pg_size>  

Here,' bufferpool_name' is the name we wish to give to bufferpool, and pg_size depicts the size of the page.

Let's see an example:

We will create a bufferpool named "bufferpool_new" where pagesize is 2192.

C:\Program Files\IBM\SQLLIB\BIN>db2 create bufferpool bufferpool_new pagesize 8192 

Following statement will be displayed:

DB20000I  The SQL command completed successfully.

Output:

Verify Bufferpools

A bufferpool is created innately when the database is created. And others we have created using a statement. We can verify it as follows.

Example:

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from syscat.bufferpools

Resizing the buffer pool innately:

To resize the bufferpool innately, we can use the following command. Before that, we should have STMM turned on at the database configuration level.

Syntax:

db2 alter bufferpool <buffer_pool_name> size AUTOMATIC

 Lets see an example:

We will resize the newly created bfferpool- bufferpool_new:

C:\Program Files\IBM\SQLLIB\BIN>db2 alter bufferpool bufferpool_new size AUTOMATIC

Following statement will be returned:

DB20000I  The SQL command completed successfully.

Output:

Alter the size of bufferpool to the fixed size:

Syntax:

db2 ALTER BUFFERPOOL <buffer_pool_name> SIZE <size_val> Lets see an 

Example:

We will resize the newly created bfferpool- bufferpool_new:

C:\Program Files\IBM\SQLLIB\BIN>db2 alter bufferpool bufferpool_new size 18000

Following statement will be returned:

DB20000I  The SQL command completed successfully.

Output:

Drop Bufferpool

We can also drop the bufferpool by using the drop bufferpool command. Before we drop the bufferpool we should check if the tablespace is allocated to the bufferpool.

Syntax:

drop bufferpool <bufferpool_name>

 Example:

We will drop the bufferpool named bufferpool_new.

C:\Program Files\IBM\SQLLIB\BIN>db2 drop bufferpool bufferpool_new

Following statement will be displayed:

DB20000I  The SQL command completed successfully.

Output:

To Verify, we can use the following statement:

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from syscat.bufferpools

Output:

We can see other bufferpools, and the bufferpool_new is dropped.