Help required in querying dbc tables

Database

Help required in querying dbc tables

Hello,

I am trying to get the table space size occupied per table in a database. The below query gives me for a table. But I need space occupied by a table for a single or few partitions within the table. Thanks in advance

SELECT DATABASENAME, TABLENAME, SUM(CURRENTPERM) /(1024* 1024 * 1024) (decimal(15,6))

FROM DBC.TABLESIZE a, dbc.tables b

WHERE a.DATABASENAME ='xxx'

AND a.TABLENAME = 'xxx'

and a.databasename = b.databasename

and a.tablename = b.tablename

and

GROUP BY 1,2

2 REPLIES
Enthusiast

Re: Help required in querying dbc tables

 I don't think, you can get this info through any of the DBC tables. I am not sure if any of the databse utilities have any such command, but I'll check.

There is one way through which at least you can have some rough estimate:

1) Calculate the size of the table

2) Calculate the number of rows in a table

3) Now derive the partition size based on number of rows in each partition divided by total number of rows in a table multiply by the table size

With the help of some complex sql, sub-query and using derive tables etc, you can get the output in desired format.

Re: Help required in querying dbc tables

Thanks pawan. I was able to do like that which gives me rough estimates. I am still wondering if any dbc.tables would be helpful as it is partitioned.