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 \
and ts.BUFFERPOOLID = b.BUFFERPOOLID
(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 \
AND ts.BUFFERPOOLID = b.BUFFERPOOLID
(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
(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.