ADMIN_GET_TAB_INFO table function - Retrieve size and state information for tables

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('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( '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( 'DBUSER1', 'EMPLOYEE' )) AS T

• If both the tabschema and tabname are specified, information is returned for that specific table only.
• If the tabschema is specified but tabname is empty (") or NULL, information is returned for all tables in the given schema.
• If the tabschema is empty (") or NULL and tabname is specified, 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 empty (") or NULL, 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 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.