ADMIN_GET_INDEX_INFO table function - returns index information
The ADMIN_GET_INDEX_INFO table function returns index information not available in the catalog views, such as compression information and the logical and physical size of the index.
Syntax
The schema is SYSPROC.
Table function parameters
- objecttype
- An input argument of type VARCHAR(1) that indicates the object
type. The value must be one of the following case-sensitive values:
- 'T', NULL, or the empty string (") to indicate a table
- 'I' for an index
- objectschema
- A case-sensitive input parameter of type VARCHAR(128) that specifies
the object schema.If objecttype is 'T', NULL, or the empty string (''), thenobjectschema indicates the table schema.
- If objectschema is specified and objectname is NULL or the empty string (''), then information is returned for all indexes on all tables in the specified schema.
- If both objectschema and objectname are specified, then information is returned for all indexes on the specified table.
If objecttype is 'I', then objectschema indicates the index schema.- If objectschema is specified and objectname is NULL or the empty string ("), then information is returned for all indexes in the specified schema.
- If both objectschema and objectname are specified, then information is returned for the specified index.
- If neither objectschema or objectname are specified, then information is returned for all indexes in all of the schemas.
If objectname is specified and objectschema is not specified, the function returns an SQL error. A parameter value is said to be unspecified when either it has a value of NULL or the empty string (").
- objectname
- A case-sensitive input parameter of type VARCHAR(128) that specifies the object name. See the description for the objectschema parameter.
Authorization
- 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.
Example
After enabling index compression for several indexes on a table, you want to determine which indexes are compressed and which indexes require a rebuild in order to be compressed. In this example, S is the schema name and T1 is the table name.
db2 SELECT iid, compress_attr, index_compressed
FROM TABLE(sysproc.admin_get_index_info('','S','T1')) AS t
The following is an example of output from this query.
IID COMPRESS_ATTR INDEX_COMPRESSED
--------- ------------- ----------------
1 Y Y
2 Y Y
3 Y N
4 N N
Additionally, you want to see other index information for all indexes in the schema S2. In this example:
- T2 = a partitioned table with two data partitions
- T3 = a nonpartitioned table
- IND_1 = a nonpartitioned index on T2
- IND_2 = a partitioned index on T2
- IND_3 = a partitioned index on T2
- IND_4 = an index on T3
- IND_5 = an index on T3
db2 SELECT tabname, indname, iid,index_partitioning, datapartitionid,
index_object_l_size, index_object_p_size, index_requires_rebuild,
large_rids FROM TABLE(sysproc.admin_get_index_info('I','S2','')) AS t
The following is an example of the output from this query.
TABNAME INDNAME IID INDEX_PARTITIONING DATAPARTITIONID
------- ------- --------- ------------------ ---------------
T2 IND_1 1 N 0
T2 IND_2 2 P 1
T2 IND_2 2 P 2
T2 IND_3 3 P 1
T2 IND_3 3 P 2
T3 IND_4 4 0
T3 IND_5 5 0
Output from this procedure (continued):
INDEX_OBJECT_L_SIZE INDEX_OBJECT_P_SIZE INDEX_REQUIRES_REBUILD LARGE_RIDS
------------------- ------------------- ---------------------- ----------
50 51 N Y
40 40 N Y
45 45 N Y
40 40 N Y
45 45 N Y
20 20 N Y
20 20 N Y
Information returned
Column Name | Data Type | Description |
---|---|---|
INDSCHEMA | VARCHAR(128) | index_schema - Index schema monitor element |
INDNAME | VARCHAR(128) | index_name - Index name monitor element |
TABSCHEMA | VARCHAR(128) | table_schema - Table schema name monitor element |
TABNAME | VARCHAR(128) | table_name - Table name monitor element |
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
IID | SMALLINT | iid - Index identifier monitor element |
DATAPARTITIONID | INTEGER | Data partition ID. |
COMPRESS_ATTR | CHAR(1) | The state of the COMPRESSION attribute on the
index.
|
INDEX_COMPRESSED | CHAR(1) | Physical index format.
|
INDEX_PARTITIONING | CHAR(1) | Identifies the partitioning characteristic of
the index.
|
INDEX_OBJECT_L_SIZE | BIGINT | Logical size of the index object. For nonpartitioned
tables, this is the amount of disk space logically allocated for all
indexes defined on the table. For a nonpartitioned index on a partitioned
table, this is the amount of disk space logically allocated for the
index. For a partitioned index on a partitioned table, this is the
amount of disk space logically allocated for all index partitions
defined on the data partition. All sizes are reported in kilobytes
(KB). The logical size is the amount of space that the table or data partition knows about. It may be less than the amount of space physically allocated to hold index data for the table or data partition (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. If the table or index is in error when this function is executed, then this value is NULL. |
INDEX_OBJECT_P_SIZE | BIGINT | Physical size of the index object. For nonpartitioned
tables, this is the amount of disk space physically allocated for
all indexes defined on the table. For a nonpartitioned index on a
partitioned table, this is the amount of disk space physically allocated
for the index. For a partitioned index on a partitioned table, this
is the amount of disk space physically allocated for all index partitions
defined on the data partition. All sizes are reported in kilobytes
(KB). The size returned takes into account full extents allocated for the indexes and includes the EMP extents for indexes created in DMS table spaces. If the table or index is in error when this function is executed, then this value is NULL. |
INDEX_REQUIRES_REBUILD | CHAR(1) | Rebuild status for the index.
|
LARGE_RIDS | CHAR(1) | Indicates whether or not the index is using
large row IDs (RIDs) (4 byte page number, 2 byte slot number).
|
RECLAIMABLE_SPACE | BIGINT | This value applies only to an index in a DMS table space. This value is an estimate of disk space, in kilobytes, that can be reclaimed from the entire index object by running the REORG INDEXES or REORG INDEX command with the RECLAIM EXTENTS option. For any index not defined in a DMS table space, the value is zero. If the table or index is in error when this function is executed, then this value is NULL. |