Calculate the size of the tables on DB2

Hi Ralph,

That's more involved than it might first appear.  How well do you know your way around JCL?


Kent

I just want a query to calculate the space used by the tables, for example using the system views sysibm.systables or sysibm.systablespace or other...

Hi!

The catalog tables have all of the required info :)
You could query sysibm.systables like this

This is to see the size of a single table

select t.creator schema, t.name tablename, t.dbname database, t.cardf totalrecords, t.spacef totalstorage_in_KB
from sysibm.systables t
where t.name = <mytablename>
and t.creator = <myschemanema>
and t.dname = <mydatabase>

Size of a schema.
select t.creator schema,  sum (IFNULL(t.cardf, 0 )) totalrecords, sum(IFNULL(t.spacef, 0)) totalstorage_in_KB
from sysibm.systables t
group by t.creator

Size of a database.
select t.dbname schema, sum (IFNULL(t.cardf, 0 )) totalrecords, sum(IFNULL(t.spacef, 0)) totalstorage_in_KB
from sysibm.systables t
group by t.dbname

The info in the catalog depends on how often you run your runstats. Make sure you have as current statistics as possible to determine the size.

This is just to give you an idea. For partitioned tables you should join systables and systablepart on dbname and tsname and select the spacef from systablepart tables.
Regards,
    Tomas Helgi

OK, where can I check if the statistics are updated?

There is another way to calculate the size of the tables regardless of the statistics (such as the view DBA_SEGMENTS in oracle?