How to Calculate the size of one Table or one Schema
calculating the size of one table and 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>"
DB29.1 or later:
db2 "SELECT tabname,TABSCHEMA, SUM(DATA_OBJECT_P_SIZE)+
SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+
SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROM
SYSIBMADM.ADMINTABINFO where TABSCHEMA=<tabschema_name> group by tabname,tabschema"
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>' "
DB29.1 or later:
db2 "SELECT TABSCHEMA, TABNAME, SUM(DATA_OBJECT_P_SIZE)+
SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+
SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROM
SYSIBMADM.ADMINTABINFO where tabschema='<tabschema_name>' and tabname='<table_name>' group by tabschema,tabname"
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"
DB29.1 or later:
db2 "SELECT TABSCHEMA, SUM(DATA_OBJECT_P_SIZE)+
SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+ SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROM SYSIBMADM.ADMINTABINFO GROUP BY TABSCHEMA"
Do you know the diference between FPAGES AND NPAGES ???
NPAGES - Total number of pages on which the rows of the table exist
FPAGES - Total number of pages
So, FPAGES >= NPAGES, when you run the utilities reorg and runstats, the reorg will try to do FPAGES=NPAGES and the runstats will update the statistics on catalog.