how to check table is using how much space in DB2

Question

Is there a way in DB2 to identify that a table is consuming how much of the total space allocated to the underlying table space."

Thanks

Answer 1

DB2 queries to check the table size

select 
   a.CARD*(sum(b.AVGCOLLEN)+10)/1024/1024 as Tablesize_in_MB 
from 
   syscat.tables as a, syscat.columns as b 
where 
   a.TABNAME = b.TABNAME and b.TABNAME = 'TABLE_NAME' group by a.CARD

data size

select 
  char(date(t.stats_time))||' '||char(time(t.stats_time)) as statstime 
  ,substr(t.tabschema,1,8)||'.'||substr(t.tabname,1,24) as tabname 
  ,card as rows_per_table 
  ,decimal(float(t.npages)/ ( 1024 / (b.pagesize/1024)),9,2) as used_mb 
  ,decimal(float(t.fpages)/ ( 1024 / (b.pagesize/1024)),9,2) as allocated_mb 
from 
  syscat.tables t , syscat.tablespaces b 
where t.tbspace=b.tbspace 
order by 5 desc with ur

index size

select 
  rtrim(substr(i.tabschema,1,8))||'.'||rtrim(substr( i.tabname, 1,24)) as tabname 
 ,decimal(sum(i.nleaf)/( 1024 / (b.pagesize/1024)),12,2) as indx_used_per_table_mb 
from 
   syscat.indexes i, syscat.tables t , syscat.tablespaces b 
where 
   i.tabschema is not null and i.tabname=t.tabname 
   and i.tabschema=t.tabschema and t.tbspace=b.tbspace 
group by 
   i.tabname,i.tabschema, b.pagesize order by 2 desc with ur

Answer 2

Your question creates a false dichotomy because a tablespace may be created without allocating a limited amount of space to it. The limitation may rather be the drive or share that the tablespace is on. But if you know the space allocated to a tablespace or only need the percent of a tablespace's current size that a table is using, then yes, there is a way to know.

SELECT * FROM SYScat.tables where tabname='mytable';

will tell you how many pages a table is using.

Then at the command line: LiST TABLESPACES SHOW DETAIL will tell you how many total pages are in the tablespace and the size of a page in bytes.

Select * from sysibmadm.tbsp_utilization where tbsp_name='MyTblSpace' 

will give you the maximum size of the tablespace if it has one.