How do I know the bufferpool name associated with a table space?

by | Feb 25, 2008

You can use following query to find out the name of the bufferpool associated with a table space.

SELECT SUBSTR(BPNAME,1,18) "BUFFER POOL", 
       SUBSTR(TBSPACE,1,18) "TABLE SPACE",
       B.PAGESIZE 
FROM   SYSCAT.BUFFERPOOLS B, 
       SYSIBM.SYSTABLESPACES T 
WHERE  B.BUFFERPOOLID = T.BUFFERPOOLID 
ORDER  BY 1,2;

A typical output may look like:

BUFFER POOL        TABLE SPACE        PAGESIZE
------------------ ------------------ -----------
IBMDEFAULTBP       IBMDB2SAMPLEREL           8192
IBMDEFAULTBP       SYSCATSPACE               8192
IBMDEFAULTBP       SYSTOOLSPACE              8192
IBMDEFAULTBP       SYSTOOLSTMPSPACE          8192
IBMDEFAULTBP       TEMPSPACE1                8192
IBMDEFAULTBP       USERSPACE1                8192