How to list the tablespaces and their % usage

How to list the tablespaces and their % usage

To check the tablespaces and their percentage usage you can run the following select command, and connect to the database first.

db2 connect to <db_name>

Then run the the select bellow and alter dbname with your database name.  The SNAPSHOT_TBS_CFG function returns configuration information from a table space snapshot, so the snapshot monitoring should be enabled for you can run this select. Tablespace_Type is 1 for SMS and 0 for DMS tablespaces. For SMS tablespaces the percentage usage will be always 100%, so this select makes sense only for DMS tablespaces.

db2 select "TABLESPACE_ID as id, SUBSTR(TABLESPACE_NAME, 1, 20) as name, \
INT(PAGE_SIZE) as PAGE_SIZE, INT(TOTAL_PAGES) as TOTAL_PAGES, \
INT(USED_PAGES) as USED_PAGES, \
INT(DECIMAL(TOTAL_PAGES,10,2)-DECIMAL(USED_PAGES,10,2)) as FREE_PAGES,\
'      ' CONCAT (SUBSTR(CHAR(DECIMAL(USED_PAGES, 10, 2)/ \
DECIMAL(TOTAL_PAGES,10,2)*100),9,5)) CONCAT '%' as PERCENT_USED, \
INT(NUM_CONTAINERS) as N_CONTAINERS \
from table (snapshot_tbs_cfg('<db_name>', 0)) as t \
where TABLESPACE_TYPE=0 order by PERCENT_USED desc"

Output example:

NAME       PAGE_SIZE  TOTAL_PAGES USED_PAGES FREE_PAGES PERCENT_USED N_CONTAINERS
---------- ----------- ---------- ---------- ---------- ------------ ------------
TS_INDEXES        4096     111542      80160      31382       71.86%            3
TS_EVT_REP       16384      19000       7104      11896       37.38%            2
TS_SLOTS         16384      64800      21920      42880       33.82%            3
TS_REC_LOG       16384      12800       2816       9984       22.00%            4
TS_LONG          32768       1280        110       1170       08.59%            3
TS_REST           4096       4500        288       4212       06.40%            3

Specifying a tablespace

db2 select "SUBSTR(TABLESPACE_NAME, 1, 20) as name, \
INT(PAGE_SIZE) as PAGE_SIZE, INT(TOTAL_PAGES) as TOTAL_PAGES, \
INT(USED_PAGES) as USED_PAGES, \
INT(DECIMAL(TOTAL_PAGES,10,2)-DECIMAL(USED_PAGES,10,2)) as FREE_PAGES,\
'      ' CONCAT (SUBSTR(CHAR(DECIMAL(USED_PAGES, 10, 2)/ \
DECIMAL(TOTAL_PAGES,10,2)*100),9,5)) CONCAT '%' as PERCENT_USED, \
INT(NUM_CONTAINERS) as N_CONTAINERS \
from table (snapshot_tbs_cfg('<db_name>', 0)) as t \
where TABLESPACE_TYPE=0 and TABLESPACE_NAME='TS_INDEXES'" 

Where TS_INDEXES is the tablespace name.



Output Example:

NAME       PAGE_SIZE  TOTAL_PAGES USED_PAGES FREE_PAGES PERCENT_USED N_CONTAINERS
---------- ----------- ---------- ---------- ---------- ------------ ------------
TS_INDEXES        4096     111542      80160      31382       71.86%            3

  1 record(s) selected.