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

One of the following authorities is required to execute the routine:

• EXECUTE privilege on the routine
• DATAACCESS authority
• DBADM authority
• SQLADM authority

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

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

Example 3: Check the current type of statistics information collected for table T1

db2 => 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.

• 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_V95 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.

REDISTRIBUTING_PENDING

1. no redistribute has been run for the given table N
2. redistribute started to run on the database partition group but not on the table N
3. redistribute failed in the phase before moving data N
4. redistribute failed in the phase of moving data Y
5. redistribute completely successfully and committed for the table. N