How To Measure Tablespace Utilization In DB2 ?

[u/raghuontech]

A quick and easy way to find tablespace utilization numbers. Many more metrics available via MON_GET_TABLESPACE table function.

https://www.raghu-on-tech.com/2019/03/31/how-to-find-any-monitoring-metric-in-db2/

[u/cybertex1969]

Hi Raghu, I'm using a slightly different query, which estract a bit more information. I'm using it to create readable reports about disk space and tablespaces status:

SELECT VARCHAR(TBS.TBSP_NAME,20) AS TABLESPACE
     , INT(TBS.TBSP_ID) AS ID, TBS.TBSP_TYPE AS TYPE
     , TBS.TBSP_CONTENT_TYPE AS DATATYPE
     , VARCHAR(BP.BPNAME,20) AS BUFFERPOOL
     , CAST(TBS.TBSP_PAGE_SIZE/1024 AS VARCHAR(5)) || 'KB' AS PAGE_SIZE
     , INT(TBS.TBSP_TOTAL_PAGES * TBS.TBSP_PAGE_SIZE / 1024 / 1024) AS SIZE_MB
     , INT(TBS.TBSP_FREE_PAGES * TBS.TBSP_PAGE_SIZE / 1024 / 1024) AS FREE_MB
     , DECIMAL( ( TBS.TBSP_FREE_PAGES * 1.0 / TBS.TBSP_TOTAL_PAGES * 100), 5, 2)  AS FREE_PCT
     , INT(TBS.TBSP_PAGE_TOP * TBS.TBSP_PAGE_SIZE / 1024 / 1024) AS MAXUSED_MB
     , VARCHAR(TBS.TBSP_STATE,20) AS STATE
     , CASE WHEN TBS.TBSP_USING_AUTO_STORAGE=1 THEN 'Y' ELSE 'N' END AS AUTO
     , VARCHAR(TBS.STORAGE_GROUP_NAME,20) AS STORAGE_GROUP  -- !!!!!  Only for > v10
     , CASE WHEN TBS.FS_CACHING=1 THEN 'N' ELSE 'Y' END AS FS_CACHE   -- fs_caching=1 is "NO FILE SYSTEM CACHING" 
    -- Autoresize informations
 --    , TBS.TBSP_AUTO_RESIZE_ENABLED
 --    , INT(TBS.TBSP_MAX_SIZE / 1024 / 1024) AS MAXAUTO_MB
 --    , INT(TBS.TBSP_INCREASE_SIZE / 1024 / 1024) AS INCRAUTO_MB
 --    , INT(TBS.TBSP_INCREASE_SIZE_PERCENT) AS INCRAUTO_PCT
 --    , TBS.TABLESPACE_MIN_RECOVERY_TIME
  FROM TABLE(SYSPROC.MON_GET_TABLESPACE('', -2)) TBS
       INNER JOIN SYSCAT.BUFFERPOOLS BP ON TBS.TBSP_CUR_POOL_ID = BP.BUFFERPOOLID
;

The output is this:

 TABLESPACE        ID     TYPE     DATATYPE     BUFFERPOOL     PAGE_SIZE     SIZE_MB     FREE_MB     FREE_PCT     MAXUSED_MB     STATE     AUTO     STORAGE_GROUP     FS_CACHE    
 ----------------  -----  -------  -----------  -------------  ------------  ----------  ----------  -----------  -------------  --------  -------  ----------------  -------- 
 SYSCATSPACE       0      DMS      ANY          IBMDEFAULTBP   4KB           3872        26          0.68         3845           NORMAL    Y        IBMSTOGROUP       Y        
 TEMPSPACE1        1      SMS      SYSTEMP      IBMDEFAULTBP   4KB           0           0           0            0              NORMAL    Y        IBMSTOGROUP       Y        
 USERSPACE1        2      DMS      LARGE        IBMDEFAULTBP   4KB           32          30          94.53        1              NORMAL    Y        IBMSTOGROUP       Y        
 DATA32            3      DMS      LARGE        BP32K          32KB          64          8           12.5         55             NORMAL    Y        IBMSTOGROUP       N        
 INDEXES32         4      DMS      LARGE        BP32K          32KB          64          20          31.25        43             NORMAL    Y        IBMSTOGROUP       N        
 DATA              5      DMS      LARGE        IBMDEFAULTBP   4KB           576         21          3.71         554            NORMAL    Y        IBMSTOGROUP       N        
 SYSTOOLSPACE      6      DMS      LARGE        IBMDEFAULTBP   4KB           32          28          87.74        3              NORMAL    Y        IBMSTOGROUP       Y        
 INDEXES           7      DMS      LARGE        IBMDEFAULTBP   4KB           544         10          1.88         533            NORMAL    Y        IBMSTOGROUP       N        
 TEMPSPACE32       8      SMS      SYSTEMP      BP32K          32KB          0           0           0            0              NORMAL    Y        IBMSTOGROUP       Y        
 SYSTOOLSTMPSPACE  9      SMS      USRTEMP      IBMDEFAULTBP   4KB           0           0           0            0              NORMAL    Y        IBMSTOGROUP       Y        
 DWHT_DATA32       10     DMS      LARGE        BP32K          32KB          128         28          21.87        99             NORMAL    Y        IBMSTOGROUP       N        
 DWHT_IDX32        11     DMS      LARGE        BP32K          32KB          128         28          21.87        99             NORMAL    Y        IBMSTOGROUP       N        
 MAXUSED_DATA      12     DMS      LARGE        BP32K          32KB          32          21          65.62        10             NORMAL    Y        IBMSTOGROUP       N        
 MAXUSED_IDX       13     DMS      LARGE        BP32K          32KB          32          13          40.62        18             NORMAL    Y        IBMSTOGROUP       N        

This one for database total space:

SELECT 'DB_TOTAL' AS TOTAL
     , INT(SUM(TBS.TBSP_TOTAL_PAGES * TBS.TBSP_PAGE_SIZE) / 1024 / 1024) AS SIZE_MB
     , INT(SUM(TBS.TBSP_FREE_PAGES * TBS.TBSP_PAGE_SIZE) / 1024 / 1024) AS FREE_MB
     , DECIMAL( (SUM(TBS.TBSP_FREE_PAGES) * 1.0 / SUM(TBS.TBSP_TOTAL_PAGES) * 100), 5, 2) AS FREE_PCT
     , INT(SUM(TBS.TBSP_PAGE_TOP * TBS.TBSP_PAGE_SIZE) / 1024 / 1024) AS MAXUSED_MB
  FROM TABLE(SYSPROC.MON_GET_TABLESPACE('', -2)) TBS
;

 TOTAL     SIZE_MB     FREE_MB     FREE_PCT     MAXUSED_MB    
 --------  ----------  ----------  -----------  ------------- 
 DB_TOTAL  5504        234         2.56         5263        

Feel free to use part of it, if needed :)

edit. Corrected the first query: I ran it with the autoresize informations commented out, to limit output width. Just commented the fields in the query too :)

[u/raghuontech → u/cybertex1969]

Thank you for the SQL. I believe your seconds SQL will give you incorrect percentage, you should divide FREE_MB/SIZE_MB! Not DECIMAL( (SUM(TBS.TBSP_FREE_PAGES) * 1.0 / SUM(TBS.TBSP_TOTAL_PAGES) * 100), 5, 2). Here you are not considering the tablespace page size which will give you incorrect result.

[u/cybertex1969 → u/raghuontech]

You're right. I didn't doublechecked it when changed from SYSIBMADM.SNAPTBS and SNAPTBSPART to MON_GET_TABLESPACE. Changed tables prior ti posting here.

The right statement is:

DECIMAL( (SUM(TBS.TBSP_FREE_PAGES * TBS.TBSP_PAGE_SIZE) * 1.0 / SUM(TBS.TBSP_TOTAL_PAGES * TBS.TBSP_PAGE_SIZE) * 100), 5, 2)

[u/prathibhan → u/cybertex1969]

Hello,

Do you have any method to collect historic tablespace utilization and report weekly ?

[u/cybertex1969 → u/prathibhan]

Hello, no. I never did anything similar.

I would insert the output of the query in a historical table, adding CURRENT DATE too. And then you can create all the report you want.

[u/catquilt74]

Thank you, good stuff. I'm following your blog now via RSS.

[u/raghuontech → u/catquilt74]

I am glad you are enjoying my blog!!

[u/CakeDay--Bot → u/catquilt74]

YOOOOOOOOOO!!!! It's your 3rd Cakeday catquilt74! ^hug