db2: list tables in specific tablespace

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 ;