DB2 Bufferpools

What is Bufferpool in DB2

In DB2, bufferpool is a part of 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.

How to create Bufferpool?

A default bufferpool is created at the time when we create the new database. It is called as "IBMDEFAULTBP". You can create a number of bufferpools according to your requirements. 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.

Bufferpool Size

The default page size of a bufferpool is 4KB.

The bufferpool size is set when we use the "CREATE DATABASE" command. If you do not specify the size of the bufferpool, it will be set to 4KB.

Once the bufferpool is created, it is not possible to modify the page size later.

List the available Bufferpools

You can list out all the available bufferpools in current database.

Syntax: