Thursday 18 April 2013

How to Calculate the size of one Table or one Schema?



Perform the select below to know the size all tables in a specific schema:

 db2 "select substr(a.tabname,1,30), (a.fpages*PAGESIZE/1024) as size_k, a.card from syscat.tables a, syscat.tablespaces b where a.TBSPACEID=b.TBSPACEID and a.tabschema='<schema_name>'" 

Perform the select below to know the size one table:

 db2 "select substr(a.tabname,1,30), (a.fpages*PAGESIZE/1024) as size_k,
a.card from syscat.tables a, syscat.tablespaces b where
a.TBSPACEID=b.TBSPACEID and a.tabname='<tab_name>'  and tabschema='<schema_name>' "
 
Perform the select below to know the size of all schema:
 
db2 "select sum(a.fpages*PAGESIZE/1024) as size_k_of_schemaName  from syscat.tables a, \
syscat.tablespaces b where a.TBSPACEID=b.TBSPACEID and a.tabschema='<schema_name>' 
group by a.tabschema"

1 comment:

  1. db2 "SELECT SUBSTR(TabSchema,1,15), SUBSTR(TabName,1,15), TabType, (Data_Object_P_Size + Index_Object_P_Size + Long_Object_P_Size + Lob_Object_P_Size + Xml_Object_P_Size) as Total_P_Size FROM SysIbmAdm.AdminTabInfo ORDER BY Total_P_Size"

    ReplyDelete