DB2 UDB - Getting information about table size, state etc.

Found this: -

ADMINTABINFO administrative view and ADMIN_GET_TAB_INFO_V97 table function - retrieve table size and state information

whilst looking for a mechanism to determine the size, in KB, of DB2 database tables.

The ADMINTABINFO administrative view returns size and state information for tables, materialized query tables (MQT) and hierarchy tables only. These table types are reported as T for table, S for materialized query tables and H for hierarchy tables in the SYSCAT.TABLES catalog view. The information is returned at both the data partition level and the database partition level for a table.

The schema is SYSIBMADM.

Example 1: Retrieve size and state information for all tables

SELECT * FROM SYSIBMADM.ADMINTABINFO

Example 2: Determine the amount of physical space used by a large number of sparsely populated tables.

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 GROUP BY TABSCHEMA, TABNAME

Example 3: Identify tables that are eligible to use large RIDs, but are not currently enabled to use large RIDs.

SELECT TABSCHEMA, TABNAME FROM SYSIBMADM.ADMINTABINFO 

   WHERE LARGE_RIDS = 'P'

Example 4: Identify which tables are using type-1 indexes and require a reorganization to convert to type-2 indexes.

SELECT TABSCHEMA, TABNAME FROM SYSIBMADM.ADMINTABINFO 

   WHERE INDEX_TYPE = 1

Example 5: Identify which tables have XML data in type-1 format and require an online table move to convert to type-2 format.

SELECT TABSCHEMA, TABNAME FROM SYSIBMADM.ADMINTABINFO

   WHERE XML_RECORD_TYPE=1

Example 4 (sic): Check the current type of statistics information collected for table T1

SELECT SUBSTR(TABSCHEMA, 1, 10) AS TBSCHEMA, SUBSTR(TABNAME, 1, 10) 

   AS TBNAME, STATSTYPE FROM SYSIBMADM.ADMINTABINFO WHERE TABNAME = 'T1';

TBSCHEMA   TBNAME     STATSTYPE

---------- ---------- ---------

DB2USER1   T1         U        

  1 record(s) selected.

plus: -

ADMIN_GET_TAB_INFO_V97 table function

The ADMIN_GET_TAB_INFO_V97 table function returns the same information as the ADMINTABINFO administrative view, but allows you to specify a schema and table name.

Example 1: Retrieve size and state information for the table DBUSER1.EMPLOYEE.

SELECT * FROM TABLE (SYSPROC.ADMIN_GET_TAB_INFO_V97('DBUSER1', 'EMPLOYEE')) 

   AS T

Example 2: Suppose there exists a non-partitioned table (DBUSER1.EMPLOYEE), with all associated objects (for example, indexes and LOBs) stored in a single table space. Calculate how much physical space the table is using in the table space:

SELECT (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 TABLE( SYSPROC.ADMIN_GET_TAB_INFO_V97( 'DBUSER1', 'EMPLOYEE' )) AS T

Calculate how much space would be required if the table were moved to another table space, where the new table space has the same page size and extent size as the original table space:

SELECT (data_object_l_size + index_object_l_size + long_object_l_size + 

   lob_object_l_size + xml_object_l_size) as total_l_size 

   FROM TABLE( SYSPROC.ADMIN_GET_TAB_INFO_V97( 'DBUSER1', 'EMPLOYEE' )) AS T

Example 3: Determine the total size for the compression dictionaries for the table DBUSER1.EMPLOYEE.

SELECT SUBSTR(TABSCHEMA,1,10) AS TBSCHEMA, SUBSTR(TABNAME,1,10) AS TBNAME, 

   DICTIONARY_SIZE + XML_DICTIONARY_SIZE AS TOTAL_DICTIONARY_SIZE

   FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO_V97('DBUSER1','EMPLOYEE'))

Example 4: Determine the amount of space reclaimable from a multidimensional clustering table SAMPLE.STAFF:

SELECT RECLAIMABLE_SPACE 

   FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO_V97('SAMPLE','STAFF'))