Hi,

Is there an easy way to get the size of an individual db2 table? I want to remove old records from tables that are taking up too much space but think a count query will simply take too long to run on certain tables.. thanks!!

You can get the number of rows (card column) or number of pages (npages columns) from syscat.tables. The numbers are as of last runstats execution.

- show table size:
db2 “select substr(t.tabschema,1,10)||’.’||substr(t.tabname,1, 20) as table ,char(date(t.stats_time)) as statsdate ,char(time(t.stats_time)) as statstime ,T.CARD as rows_per_tbl, decimal(float(t.npages)/(1024/(ts.pagesize/1024)),9,2) as used_mb ,decimal(float(t.fpages)/(1024/(ts.pagesize/1024)),9,2) as allocated_mb from SYSCAT.TABLES T ,SYSCAT.TABLESPACES TS where t.tbspace=ts.tbspace and T.tabname=’table name here‘ and T.TYPE=’T’”

- show all tables sizes in DB:
db2 “select substr(t.tabschema,1,10)||’.’||substr(t.tabname,1, 20) as table ,char(date(t.stats_time)) as statsdate ,char(time(t.stats_time)) as statstime ,T.CARD as rows_per_tbl, decimal(float(t.npages)/(1024/(ts.pagesize/1024)),9,2) as used_mb ,decimal(float(t.fpages)/(1024/(ts.pagesize/1024)),9,2) as allocated_mb from SYSCAT.TABLES T, SYSCAT.TABLESPACES TS where t.tbspace=ts.tbspace and T.TYPE=’T’ order by table”

thank you Markham!

db2top,
uppercase "T",
arrow right until you'll see the header "Table-Size"

You do not see the # rows but the size in Kb/Mb/Gb.

I tried Markham's command and for some reason, I get back CARD, USED_MB and ALLOCATED_MB as -1, 0.00, 0.00 for all the tables. Any idea why this would come back as the result? Thanks!

See post #2 in this thread.

Duplicate removed

Hmm, for some reason if I try "select * from syscat.tables" I still get the -1, 0.00, 0.00 combination.. is there some setting option I can change to make this command return the correct values?

Do runstats. If you still get those values then it isn't a table but a view.

Andy