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.