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

The ADMINTABINFO administrative view and the ADMIN_GET_TAB_INFO table function provide methods to retrieve table size and state information that is not currently available in the catalog views.

Depending on if you are using the administrative view or the table function, refer to one of the following sections:

ADMINTABINFO administrative view
ADMIN_GET_TAB_INFO table function

ADMINTABINFO administrative view

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.

In a Db2® pureScale® environment, values reported for a table are identical on all members since all members operate on a single physical partition of data. This is unlike in a partitioned database environment, where each member operates on a different physical partition of data, and reports different values. Because the values on all members are always the same, the ADMINTABINFO view and the ADMIN_GET_TAB_INFO table functions return only a single row for each table when run on a Db2 pureScale instance.

The schema is SYSIBMADM.

Refer to the Information returned by ADMINTABINFO administrative view and ADMIN_GET_TAB_INFO table for a complete list of information that can be returned.

Authorization

One of the following authorizations is required:

SELECT privilege on the ADMINTABINFO administrative view
CONTROL privilege on the ADMINTABINFO administrative view
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

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

Examples

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

ADMIN_GET_TAB_INFO table function

The ADMIN_GET_TAB_INFO table function returns the same information as the ADMINTABINFO administrative view, but allows you to specify a schema and table name.

Refer to the Information returned by ADMINTABINFO administrative view and ADMIN_GET_TAB_INFO table for a complete list of information that can be returned.

Authorization

EXECUTE privilege on the ADMIN_GET_TAB_INFO table function.

Default PUBLIC privilege

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

Syntax

The schema is SYSPROC.

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

Information returned

Table 1. Information Returned by ADMINTABINFO administrative view and ADMIN_GET_TAB_INFO
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 multidimensional clustering (MDC) and insert time clustering (ITC) 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, reported in kilobytes. For row-organized tables, the data object physical size is the amount of disk space that is physically allocated for the table. For MDC and ITC tables, this size includes the size of the block map object. The size that is returned takes into account full extents that are allocated for the table and includes the EMP extents for objects that you create in DMS table spaces. This size represents the physical size of the base table only. The space that is consumed by LOB data, long data, indexes, and XML objects is reported in other columns. For column-organized tables, the data object physical size is the amount of disk space that is physically allocated for the table metadata only, which is relatively small. The user data for column-organized tables is stored in the column-organized data object, and its physical size is reported by COL_OBJECT_P_SIZE.
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 the 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 2 as type-2 indexes are 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)	Indicator of whether the table is using large row IDs (RID, a 4-byte page number and a 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) is returned if both the following criteria are met: The table supports large RIDs, that is, the table is in a large table space. 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. You must convert the table or indexes. This column returns the null value for column-organized tables because the concept of RIDs does not apply.
LARGE_SLOTS	CHAR(1)	Indicator of whether 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) is returned if the following criteria are both met: The table supports large slots, that is, the table is in a large table space. No offline table reorganization or table truncation operation has been performed on the table yet, so it is still using a maximum of 255 rows per page. This column returns the null value for column-organized tables because the concept of slots does not apply.
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 the database system and stored in the system catalogs. 'A'= System asynchronously gathered statistics. Statistics have been automatically collected by the database system by a background process and stored in the system catalogs. 'S' = System synchronously gathered statistics. Statistics have been automatically collected by the database system 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 the database system stores 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	Indicates the amount of disk space that can be reclaimed by running the REORG TABLE command with the RECLAIM EXTENTS option for an MDC or ITC table in a DMS table space. For a column-organized table, this value indicates the amount of free space that was found the last time that the RUNSTATS command was run. 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.
AMT_STATUS	VARCHAR(12)	Current status of ADMIN_MOVE_TABLE stored procedure call against the table. Returns the value 'IN_PROGRESS' or the null value if there is no move in progress for the table.
SPARSE_BLOCKS	BIGINT	For an insert time clustering (ITC) table, this field represents the number of sparsely used blocks in the table. This is the set of blocks that will be consolidated if the REORG TABLE command is invoked with the RECLAIM EXTENTS option.
STATS_ROWS_MODIFIED	BIGINT	stats_rows_modified - Rows modified since last RUNSTATS
RTS_ROWS_MODIFIED	BIGINT	rts_rows_modified - Rows modified since last real time statistics
STATS_DBPARTITION	CHAR(3)	stats_dbpartition - Automatics statistics collection indicator
COL_OBJECT_L_SIZE	BIGINT	col_object_l_size - Column-organized data object logical size
COL_OBJECT_P_SIZE	BIGINT	col_object_p_size - Column-organized data object physical size
STATSPROFTYPE Attention: This column is available in Db2 Version 11.5 Mod Pack 1 and later versions.	CHAR(1)	'A' = System asynchronously generated statistics profile. A statistics profile was automatically generated by the database system by a background process and stored in the system catalogs. 'U' = User generated statistics profile. A statistics profile was generated by the user using the RUNSTATS utility. NULL = A statistics profile is not generated.

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

Examples

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 + col_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 + col_object_l_size) as total_l_size 
   FROM TABLE( SYSPROC.ADMIN_GET_TAB_INFO( '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('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('SAMPLE','STAFF'))