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.
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.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
SELECT * FROM SYSIBMADM.ADMINTABINFO
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
SELECT TABSCHEMA, TABNAME FROM SYSIBMADM.ADMINTABINFO
WHERE LARGE_RIDS = 'P'
SELECT TABSCHEMA, TABNAME FROM SYSIBMADM.ADMINTABINFO
WHERE XML_RECORD_TYPE=1
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.
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.
EXECUTE privilege on the ADMIN_GET_TAB_INFO table function.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
SELECT * FROM TABLE (SYSPROC.ADMIN_GET_TAB_INFO('DBUSER1', 'EMPLOYEE'))
AS T
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
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'))
SELECT RECLAIMABLE_SPACE
FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO('SAMPLE','STAFF'))
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:
|
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:
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. Amount of disk space physically allocated for the table, reported in kilobytes. For MDC and ITC 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 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:
|
LOAD_STATUS | VARCHAR(12) | Current status of a load operation against the
table. The status value can be one of the following values:
|
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) |
|
XML_RECORD_TYPE | SMALLINT | Indicates the type of XML record currently in
use for the table.
|
RECLAIMABLE_SPACE | BIGINT | For an MDC or ITC table in a DMS table space, this value indicates the amount of disk space that can be reclaimed by running the REORG TABLE command with the RECLAIM EXTENTS 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. |
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. |