IBM Support

DB2 : Identify tables consuming most space in a tablespace

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 : 
 
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

Page Feedback