Calculating footprint of DB2 database tables

Footprint is always important. Here I show you some examples of queries to calculate it for DB2 tables and indexes. In particular there are some interesting administrative table functions:  ADMIN_GET_TAB_INFO to get information about table size and ADMIN_GET_INDEX_INFO to get information about index size.

FOOTPRINT OF TABLES:
•  Data physical disk space for a specific table on a schema (MYSCHEMA), the value of data_object_p_size is the physical size of the data, the value is reported in KB:

db2 "SELECT data_object_p_size AS Table_Data_Physical_Size FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO('MYSCHEMA','MYTABLE'))"

•  Data physical disk space for each table (passing NULL to the second parameter of the table function) and ordered by the descending size:

SELECT tabname, data_object_p_size  AS Table_Data_Physical_Size  FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO('MYSCHEMA',NULL)) ORDER BY Table_Data_Physical_Size DESC

• Total physical disk space for all tables for data, indexes, long, lob and xml the value reported in GB:

SELECT SUM(((data_object_p_size + index_object_p_size + long_object_p_size + lob_object_p_size + xml_object_p_size)/1024)/1024) AS total_p_size FROM TABLE( SYSPROC.ADMIN_GET_TAB_INFO( 'MYSCHEMA', NULL ))

• Total physical for a specific table for data, indexes, long, log and xml, the value is reported in MB:

SELECT SUM(((data_object_p_size + index_object_p_size + long_object_p_size + lob_object_p_size + xml_object_p_size)/1024)) AS total_p_size FROM TABLE( SYSPROC.ADMIN_GET_TAB_INFO( 'MYSCHEMA', 'MYTABLE'))

FOOTPRINT OF INDEXES:
•  Physical disk space of the all indexes (MB)  for all tables or a specific table:

SELECT tabname, indname, index_object_p_size/1024 from table(SYSPROC.ADMIN_GET_INDEX_INFO('', 'MYSCHEMA',NULL)) AS t GROUP BY tabname, indname, index_object_p_size

To get the footprint for a specific table it needs to specify a string value instead of third parameter NULL on the table function. For example pass a table name like 'MYTABLE'.

Visit here the DB2 documentation to check the details of the table functions.