Posted by 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.
Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL.
Ember lives in Denver and work from home
One comment