DB2 Administrative SQL Cookbook: Listing the Number of Pages in Tablespaces, by Bufferpool

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.

• Author Details
• Certifications and Badges
• Twitter

Lead Database Administrator

Ember Crooks

https://datageek.blog/

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

My Tweets