Purpose
This is a bit of a rare use case. The main use I see for it is if a database server has an excess of memory and you want to size your bufferpools so that the entire database fits in-memory. That’s not a common situation. In order to properly size the bufferpools for this edge case, I need to know how many pages my tablespaces have by bufferpool, not by tablespace.
Version
This SQL only works on DB2 9.7 and up. It relies on the MON_GET interfaces, which were introduced in 9.7.
Statement
WITH sum_ts(bufferpoolid, tot_pages) AS ( SELECT TBSP_CUR_POOL_ID , sum(TBSP_TOTAL_PAGES) FROM table(mon_get_tablespace('',-2)) AS mgt GROUP BY TBSP_CUR_POOL_ID ) SELECT substr(sb.bpname,1,18) AS bpname , pagesize , sum_ts.tot_pages FROM syscat.bufferpools sb INNER JOIN sum_ts ON sb.bufferpoolid = sum_ts.bufferpoolid WITH UR;
Note that if you don’t care to report the page size of each bufferpool, this SQL can be done more easily as:
SELECT substr(sb.bpname,1,18) AS bpname , sum(tbsp_total_pages) AS tot_pages FROM syscat.bufferpools sb JOIN table(mon_get_tablespace('',-2)) mgt ON sb.bufferpoolid=mgt.TBSP_CUR_POOL_ID GROUP BY bpname WITH UR;
Sample Output
BPNAME PAGESIZE TOT_PAGES ------------------ ----------- -------------------- IBMDEFAULTBP 4096 5644983 TEMPSYSBP 16384 2 USERBP16 16384 24624 BP32K 32768 1028 4 record(s) selected.
[…] DB2 Administrative SQL Cookbook: Listing the Number of Pages in Tablespaces, by Bufferpool […]