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

One of the following authorizations is required:

• SELECT privilege on the ADMINTABINFO administrative view
• CONTROL privilege on the ADMINTABINFO administrative view
• DATAACCESS authority

In addition, one of the following privileges or authorities is also required:

• EXECUTE privilege on the ADMIN_GET_TAB_INFO_V97 table function
• DATAACCESS authority

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: 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.

tabschema

An input argument of type VARCHAR(128) that specifies a schema name.

tabname

An input argument of type VARCHAR(128) that specifies a table name, a materialized query table name or a hierarchy 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'))

• If both the tabschema and tabname are specified, information is returned for that specific table only.
• If the tabschema is specified but tabname is NULL or the empty string ("), then information is returned for all tables in the given schema.
• If the tabschema is NULL or the empty string (") and tabname is specified, then an error is returned. To retrieve information for a specific table, the table must be identified by both schema and table name.
• If both tabschema and tabname are NULL or the empty string ("), then information is returned for all tables.
• If tabschema or tabname do not exist, or tabname does not correspond to a table name (type T), a materialized query table name (type S) or a hierarchy table name (type H), an empty result set is returned.
• When the ADMIN_GET_TAB_INFO_V97 table function is retrieving data for a given table, it will acquire a shared lock on the corresponding row of SYSTABLES to ensure consistency of the data that is returned (for example, to ensure that the table is not dropped while information is being retrieved for it). The lock will only be held for as long as it takes to retrieve the size and state information for the table, not for the duration of the table function call.
• Physical size reported for tables in SMS table spaces is the same as logical size.
• When an inplace reorg is active on a table, the physical size for the data object (DATA_OBJECT_P_SIZE) will not be calculated. Only the logical size will be returned. You can tell if an inplace reorg is active on the table by looking at the INPLACE_REORG_STATUS output column.
• The logical size reported for LOB objects created before DB2® UDB Version 8 might be larger than the physical size if the objects have not yet been reorganized.