DB2 LUW: Understanding bufferpools

The below running notes will help you to understand bufferpools in DB2 LUW:

(1) Bufferpools details are stored in syscat.bufferpools

db2 => describe table syscat.bufferpools

(2) Tablespaces iniformation in syscat.tablespaces

db2 => describe table syscat.tablespaces

(3) Create a student table as below:

db2 => create table student(sid integer, sname varchar(20), sage smallint)

db2 => describe table student

db2 => create index student_idx1 on student(sid)

db2 => create index student_idx2 on student(sid, sname)

(4) The below query will display BUFFERPOOL associated with student table.

SELECT substr(t.tabschema,1,20) table_schema, substr(t.tabname,1,20) table_name, \
substr(t.owner,1,20) table_owner, substr(ts.tbspace,1,20) Tablespace_name, \
substr(b.bpname,1,20) bufferpool_name,b.PAGESIZE,b.NUMBLOCKPAGES, b.BLOCKSIZE \
from syscat.tables t, syscat.tablespaces ts, syscat.bufferpools b \
where t.tabname = ‘STUDENT’ \
and t.tbspaceid = ts.tbspaceid \

(5) The below query will display BUFFERPOOL names associated with each Index

select substr(INDSCHEMA,1,20) Index_schema, substr(INDNAME,1,20) Index_Name, substr(TABNAME,1,20) Table_name, \
substr(i.colnames,1,20) colnames, colcount, substr(ts.tbspace,1,20) Tablespace_name, ts.tbspaceid, ts.BUFFERPOOLID, \
substr(b.BPNAME,1,20) Bufferpool_name, b.NPAGES, b.PAGESIZE \
FROM syscat.indexes i, syscat.tablespaces ts, syscat.bufferpools b \
WHERE i.tabname = ‘STUDENT’ \
AND i.tbspaceid = ts.tbspaceid \

(6) Assigning a given index or table to a different bufferpool

When the database is created, a default buffer pool named IBMDEFAULTBP is created, which is shared by all table spaces. More buffer pools can be added using
the CREATE BUFFERPOOL statement.

The buffer pool size defaults to one of the following values:

-2:  Automatically managed

-1: Size specified by the BUFFPAGE database configuration parameter

<value>: by specifying the SIZE keyword in the CREATE BUFFERPOOL command.
A table is associated with a tablespace. A tablespace is associated with a bufferpool.
i.e. table <==> tablespace <==> bufferpool

Also, A tablespace can only associate with one bufferpool. However, A bufferpool can associate with multiple tablespaces.

So, If we would like to change a bufferpool associated with a table/Index, we have to basically do one of the below:
(a) Alter tablespace to have different bufferpool
create bufferpool bp4k size 3000 pagesize 4096
alter tablespace <tbspace name> bufferpool bp4k

Note: If your existing tablespace is currently associated with bufferpool size of 4K, you can’t associate this tablespace with bufferpool page size other
than 4k.

(b) Move table/index into a different tablespace:
use ADMIN_MOVE_TABLE for table move. For Index, drop and recreate index in new tablespace.
Buffer pool memory allocation happens at startup

When you use the CREATE BUFFERPOOL command to create a buffer pool or use the ALTER BUFFERPOOL statement to alter buffer pools, the total memory that is
required by all buffer pools must be available to the database manager so all of the buffer pools can be allocated when the database is started. If you
create or modify buffer pools while the database manager is online, additional memory should be available in database global memory. If you specify the
DEFERRED keyword when you create a new buffer pool or increase the size of an existing buffer pool and the required memory is unavailable, the database
manager makes the change the next time the database is activated.

If this memory is not available when a database starts, the database manager will only start with system buffer pools (one for each page size) with a minimal
size of 16 pages, and an SQL1478W (SQLSTATE01626) warning is returned. The database continues in this operational state until its configuration is changed
and the database can be fully restarted. Performance may be suboptimal. The database manager starts with minimal-sized values only to allow you to connect to
the database so that you can re-configure the buffer pool sizes or perform other critical tasks. As soon as you perform these tasks, restart the database. Do
not operate the database for an extended time in such a state.

To avoid starting the database with system buffer pools only, you can use the DB2_OVERRIDE_BPF registry variable to adjust the memory required so that it
fits into what is available.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: