Personal tools
You are here: Home DB2 How To's How to Calculate the size of one Table or one Schema
Navigation
Log in


Forgot your password?
 
Document Actions

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.

Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls