The ADMIN_GET_TAB_INFO_V95 table function provides methods to retrieve table size and state information that is not currently available in the catalog views.
Refer to the Table 1 table for a complete list of information that can be returned.
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_V95('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_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
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.
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 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. This value is only reported for non-partitioned tables. For partitioned tables, this value will be 0. |
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. This value is only reported for non-partitioned tables. For partitioned tables this value will be 0. |
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:
|
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) | 'Y' if any of the indexes defined on the table 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 dictionary, in bytes, used for row compression if a row compression dictionary exists for the table. |
BLOCKS_PENDING_CLEANUP | BIGINT | blocks_pending_cleanup - Pending cleanup rolled-out blocks monitor element |
STATSTYPE | CHAR(1) |
|