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

Posted by


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.


This SQL only works on DB2 9.7 and up. It relies on the MON_GET interfaces, which were introduced in 9.7.


WITH sum_ts(bufferpoolid, tot_pages) AS (
                , sum(TBSP_TOTAL_PAGES)
        FROM table(mon_get_tablespace('',-2)) AS mgt
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

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

Sample Output

------------------ ----------- --------------------
IBMDEFAULTBP              4096              5644983
TEMPSYSBP                16384                    2
USERBP16                 16384                24624
BP32K                    32768                 1028

  4 record(s) selected.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.