DB2 10.5 for Linux, UNIX, and Windows

ADMIN_GET_TAB_INFO_V97 table function - Retrieve size and state information for tables

The ADMIN_GET_TAB_INFO_V97 table function provides methods to retrieve table size and state information that is not currently available in the catalog views.

Note: This table function has been deprecated and replaced by the ADMINTABINFO administrative view and ADMIN_GET_TAB_INFO table function - retrieve table size and state information.

Refer to the Table 1 table for a complete list of information that can be returned.

Syntax


>>-ADMIN_GET_TAB_INFO_V97--(--tabschema--,--tabname--)---------><

The schema is SYSPROC.

Table function parameters

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.

Authorization

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

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Examples

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'))

Usage notes

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.

REDISTRIBUTING_PENDING

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

Information returned

Table 1. Information returned by ADMINTABINFO administrative view and the ADMIN_GET_TAB_INFO_V97
Column name	Data type	Description
TABSCHEMA	VARCHAR(128)	table_schema - Table schema name monitor element
TABNAME	VARCHAR(128)	table_name - Table name monitor element
TABTYPE	CHAR(1)	Table type: 'H' = hierarchy table 'S' = materialized query table 'T' = table
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element
DATA_PARTITION_ID	INTEGER	data_partition_id - Data partition identifier monitor element
AVAILABLE	CHAR(1)	State of the table: 'N' = the table is unavailable. If the table is unavailable, all other output columns relating to the size and state will be NULL. 'Y' = the table is available. Note: Rollforward through an unrecoverable load will put a table into the unavailable state.
DATA_OBJECT_L_SIZE	BIGINT	Data object logical size. Amount of disk space logically allocated for the table, reported in kilobytes. The logical size is the amount of space that the table knows about. It might be less than the amount of space physically allocated for the table (for example, in the case of a logical table truncation). For multi-dimensional clustering (MDC) tables, this size includes the logical size of the block map object. The size returned takes into account full extents that are logically allocated for the table and, for objects created in DMS table spaces, an estimate of the Extent Map Page (EMP) extents. This size represents the logical size of the base table only. Space consumed by LOB data, Long Data, Indexes and XML objects are reported by other columns.
DATA_OBJECT_P_SIZE	BIGINT	Data object physical size. Amount of disk space physically allocated for the table, reported in kilobytes. For MDC tables, this size includes the size of the block map object. The size returned takes into account full extents allocated for the table and includes the EMP extents for objects created in DMS table spaces. This size represents the physical size of the base table only. Space consumed by LOB data, Long Data, Indexes and XML objects are reported by other columns.
INDEX_OBJECT_L_SIZE	BIGINT	Index object logical size. Amount of disk space logically allocated for the indexes defined on the table, reported in kilobytes. The logical size is the amount of space that the table knows about. It might be less than the amount of space physically allocated to hold index data for the table (for example, in the case of a logical table truncation). The size returned takes into account full extents that are logically allocated for the indexes and, for indexes created in DMS table spaces, an estimate of the EMP extents. For partitioned indexes on partitioned tables, this is the logical size of the index object containing index partitions for the data partition identified by DATA_PARTITION_ID. This value does not take into account nonpartitioned indexes on partitioned tables. For information about both partitioned and nonpartitioned indexes, you can use the ADMIN_GET_INDEX_INFO function.
INDEX_OBJECT_P_SIZE	BIGINT	Index object physical size. Amount of disk space physically allocated for the indexes defined on the table, reported in kilobytes. The size returned takes into account full extents allocated for the indexes and includes the EMP extents for indexes created in DMS table spaces. For partitioned indexes on partitioned tables, this is the physical size of the index object containing index partitions for the data partition identified by DATA_PARTITION_ID. This value does not take into account nonpartitioned indexes on partitioned tables. For information about both partitioned and nonpartitioned indexes, you can use the ADMIN_GET_INDEX_INFO function.
LONG_OBJECT_L_SIZE	BIGINT	Long object logical size. Amount of disk space logically allocated for long field data in a table, reported in kilobytes. The logical size is the amount of space that the table knows about. It might be less than the amount of space physically allocated to hold long field data for the table (for example, in the case of a logical table truncation). The size returned takes into account full extents that are logically allocated for long field data and, for long field data created in DMS table spaces, an estimate of the EMP extents.
LONG_OBJECT_P_SIZE	BIGINT	Long object physical size. Amount of disk space physically allocated for long field data in a table, reported in kilobytes. The size returned takes into account full extents allocated for long field data and includes the EMP extents for long field data created in DMS table spaces.
LOB_OBJECT_L_SIZE	BIGINT	LOB object logical size. Amount of disk space logically allocated for LOB data in a table, reported in kilobytes. The logical size is the amount of space that the table knows about. It might be less than the amount of space physically allocated to hold LOB data for the table (for example, in the case of a logical table truncation). The size includes space logically allocated for the LOB allocation object. The size returned takes into account full extents that are logically allocated for LOB data and, for LOB data created in DMS table spaces, an estimate of the EMP extents.
LOB_OBJECT_P_SIZE	BIGINT	LOB object physical size. Amount of disk space physically allocated for LOB data in a table, reported in kilobytes. The size includes space allocated for the LOB allocation object. The size returned takes into account full extents allocated for LOB data and includes the EMP extents for LOB data created in DMS table spaces.
XML_OBJECT_L_SIZE	BIGINT	XML object logical size. Amount of disk space logically allocated for XML data in a table, reported in kilobytes. The logical size is the amount of space that the table knows about. It might be less than the amount of space physically allocated to hold XML data for the table (for example, in the case of a logical table truncation). The size returned takes into account full extents that are logically allocated for XML data and, for XML data created in DMS table spaces, an estimate of the EMP extents.
XML_OBJECT_P_SIZE	BIGINT	XML object physical size. Amount of disk space physically allocated for XML data in a table, reported in kilobytes. The size returned takes into account full extents allocated for XML data and includes the EMP extents for XML data created in DMS table spaces.
INDEX_TYPE	SMALLINT	Indicates the type of indexes currently in use for the table. Returns: 1 if type-1 indexes are being used. 2 if type-2 indexes are being used.
REORG_PENDING	CHAR(1)	A value of 'Y' indicates that a reorg recommended alter has been applied to the table and a classic (offline) reorg is required. Otherwise 'N' is returned.
INPLACE_REORG_STATUS	VARCHAR(10)	Current status of an inplace table reorganization on the table. The status value can be one of the following values: ABORTED (in a PAUSED state, but unable to RESUME; STOP is required) EXECUTING NULL (if no inplace reorg has been performed on the table) PAUSED
LOAD_STATUS	VARCHAR(12)	Current status of a load operation against the table. The status value can be one of the following values: IN_PROGRESS NULL (if there is no load in progress for the table and the table is not in load pending state) PENDING
READ_ACCESS_ONLY	CHAR(1)	'Y' if the table is in Read Access Only state, 'N' otherwise. A value of 'N' should not be interpreted as meaning that the table is fully accessible. If a load is in progress or pending, a value of 'Y' means the table data is available for read access, and a value of 'N' means the table is inaccessible. Similarly, if the table status is set integrity pending (refer to SYSCAT.TABLES STATUS column), then a value of 'N' means the table is inaccessible.
NO_LOAD_RESTART	CHAR(1)	A value of 'Y' indicates the table is in a partially loaded state that will not allow a load restart. A value of 'N' is returned otherwise.
NUM_REORG_REC_ALTERS	SMALLINT	Number of reorg recommend alter operations (for example, alter operations after which a reorganization is required) that have been performed against this table since the last reorganization.
INDEXES_REQUIRE_REBUILD	CHAR(1)	For nonpartitioned tables, 'Y' if any of the indexes defined on the table require a rebuild, and 'N' otherwise. For partitioned tables, 'Y' if any index partitions for the data partition identified by DATA_PARTITION_ID require a rebuild, and 'N' otherwise.
LARGE_RIDS	CHAR(1)	Indicates whether or not the table is using large row IDs (RIDs) (4 byte page number, 2 byte slot number). A value of 'Y' indicates that the table is using large RIDs and 'N' indicates that it is not using large RIDs. A value of 'P' (pending) will be returned if the table supports large RIDs (that is, the table is in a large table space), but at least one of the indexes for the table has not been reorganized or rebuilt yet, so the table is still using 4 byte RIDs (which means that action must be taken to convert the table or indexes).
LARGE_SLOTS	CHAR(1)	Indicates whether or not the table is using large slots (which allows more than 255 rows per page). A value of 'Y' indicates that the table is using large slots and 'N' indicates that it is not using large slots. A value of 'P' (pending) will be returned if the table supports large slots (that is, the table is in a large table space), but there has been no offline table reorganization or table truncation operation performed on the table yet, so it is still using a maximum of 255 rows per page.
DICTIONARY_SIZE	BIGINT	Size of the table dictionary, in bytes, used for row compression if a row compression dictionary exists for the table. If a historical dictionary exists, this value is the sum of the current and historical dictionary sizes.
BLOCKS_PENDING_CLEANUP	BIGINT	blocks_pending_cleanup - Pending cleanup rolled-out blocks monitor element
STATSTYPE	CHAR(1)	'F' = System fabricated statistics without table or index scan. These statistics are stored in memory and are different from what is stored in the system catalogs. This is a temporary state and eventually full statistics will be gathered by DB2® and stored in the system catalogs. 'A'= System asynchronously gathered statistics. Statistics have been automatically collected by DB2 by a background process and stored in the system catalogs. 'S' = System synchronously gathered statistics. Statistics have been automatically collected by DB2 during SQL statement compilation. These statistics are stored in memory and are different from what is stored in the system catalogs. This is a temporary state and eventually DB2 will store the statistics in the system catalogs. 'U' = User gathered statistics. Statistics gathering was initiated by the user through a utility such as RUNSTATS, CREATE INDEX, LOAD, REDISTRIBUTE or by manually updating system catalog statistics. NULL = unknown type
XML_RECORD_TYPE	SMALLINT	Indicates the type of XML record currently in use for the table. 1 if the type-1 (single node) XML record format is being used. 2 if the type-2 (multi-node) XML record format is being used. Null if the table has no XML columns.
RECLAIMABLE_SPACE	BIGINT	For an MDC table in a DMS table space, this value indicates the amount of disk space that can be reclaimed by running the REORG command with the RECLAIM option. Disk space is reported in kilobytes. For any other table, the value is zero.
XML_DICTIONARY_SIZE	BIGINT	Size of the XML dictionary, in bytes, used for data compression if a data compression dictionary exists for the XML storage object. If the table does not contain any XML columns or if a compression dictionary has not been created, the value is 0.