Found this: -
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 T1SELECT 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'))
