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.
- Certifications and Badges
Lead Database Administrator
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science.
Ember lives in Denver and work from home
One comment