Steps

• List all tables with its size in a database (decending order)
  1. Login to the database server as instance owner
  2. Create an SQL text named tsize.sql as below:

     SELECT TABSCHEMA, TABNAME, SUM(DATA_OBJECT_P_SIZE) + SUM(COL_OBJECT_P_SIZE) +
      SUM(INDEX_OBJECT_P_SIZE) + SUM(LONG_OBJECT_P_SIZE) +
      SUM(LOB_OBJECT_P_SIZE) + SUM(XML_OBJECT_P_SIZE) AS SIZE_IN_KB
      FROM SYSIBMADM.ADMINTABINFO GROUP BY TABSCHEMA, TABNAME ORDER BY 3 DESC ;

  3. Connect to the database, and run the SQL
     The unit of the size is Kiro-byte

     db2 connect to <db_name>
     db2 -tvf tsize.sql
• List all tables in the specific tablespace. Tablespace ID = 0 in this example
  1. Login to the database server as instance owner
  2. Create an SQL text named tsize_tbs0.sql as below:
     You can change 0 (TBS=0) to any tablespace ID in the database.

     with TBSSIZE as
     (select d.TABNAME,d.TABSCHEMA,d.DATAPARTITIONID,d.TBSPACEID as tbs,DATA_OBJECT_P_SIZE + COL_OBJECT_P_SIZE as size from SYSCAT.DATAPARTITIONS d
      join SYSIBMADM.ADMINTABINFO t
      on d.TABNAME=t.TABNAME and d.TABSCHEMA=t.TABSCHEMA and d.DATAPARTITIONID=t.DATA_PARTITION_ID
      union
      select d.TABNAME,d.TABSCHEMA,d.DATAPARTITIONID,d.INDEX_TBSPACEID as tbs,INDEX_OBJECT_P_SIZE as size from SYSCAT.DATAPARTITIONS d
      join SYSIBMADM.ADMINTABINFO t
      on d.TABNAME=t.TABNAME and d.TABSCHEMA=t.TABSCHEMA and d.DATAPARTITIONID=t.DATA_PARTITION_ID
      union
      select d.TABNAME,d.TABSCHEMA,d.DATAPARTITIONID,d.LONG_TBSPACEID as tbs,LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE as size from SYSCAT.DATAPARTITIONS d
      join SYSIBMADM.ADMINTABINFO t
      on d.TABNAME=t.TABNAME and d.TABSCHEMA=t.TABSCHEMA and d.DATAPARTITIONID=t.DATA_PARTITION_ID )
      select char(TABSCHEMA,20),char(TABNAME,50),TBS,SUM(size) from TBSSIZE group by TABSCHEMA,TABNAME,TBS having TBS=0 order by SUM(size) desc ;
     

  3. Connect to the database, and tun the SQL

     db2 connect to <db_name>
     db2 -tvf tsize_tbs0.sql