Technical Blog Post
Abstract
DB2 : Identify tables consuming most space in a tablespace
Body
It is easily possible to identify the amount of space consumed by tables in a given tablespace. This is required when size of tables increases to reach the size limit or makes tablespaces have unequal data distribution which might eventually lead to bad performance mainly for backups.
Lets consider an example and take a look at the top 3 space consuming tables in tablespace 3 :
$ db2 "SELECT substr(a.TABSCHEMA,1,12) sc, substr(a.TABNAME,1,12) tb, SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZ
E+LOB_OBJECT_P_SIZE+XML_OBJECT_P_SIZE) sz, SUM(DATA_OBJECT_P_SIZE), SUM(INDEX_OBJECT_P_SIZE), SUM(LONG_OBJECT_P_SIZE), SUM(LOB_OBJECT_P_SIZE), SUM(XML_OBJECT_P_SIZE) FROM SYSIBMADM.ADMINTABINFO a, SYSCAT.tables t where t.tabname=a.tabname and t.tabschema=a.tabschema and t.tbspaceid=3 GROUP BY a.TABSCHEMA, a.TABNAME order by sz desc"
SC TB SZ 4 5 6 7 8
------------ ------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
DB2ADMIN TABLE1 332181504 3072 2048 0 332176384 0
DB2ADMIN TABLE2 1861632 1140736 720896 0 0 0
DB2ADMIN TABLE3 1436672 944128 488448 0 4096 0
Naming columns correctly :
SCHEMA TABLE TOTAL SIZE DATA_OBJECT_P_SIZE INDEX_OBJECT_P_SIZE LONG_OBJECT_P_SIZE LOB_OBJECT_P_SIZE XML_OBJECT_P_SIZE
------------ ----------------- --------------------- -------------------------------- ----------------------------------- ------------------------------------ ------------------------------- ---------------------------
DB2ADMIN TABLE1 332181504 3072 2048 0 332176384 0
DB2ADMIN TABLE2 1861632 1140736 720896 0 0 0
DB2ADMIN TABLE3 1436672 944128 488448 0 4096 0
Please refer below link for column description :
Reference link : https://www.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0052897.html
So from this output we know that TABLE1 is the highest consumer here which is consuming about 332 GB at this time. And so on for other tables.
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
UID
ibm13285903