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

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

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