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

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.

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

Table 1. Information returned by ADMIN_GET_INDEX_INFO
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. Y = Index compression is enabled N = Index compression is not enabled
INDEX_COMPRESSED	CHAR(1)	Physical index format. Y = Index is in compressed format N = Index is in uncompressed format If the physical index format does not match the compression attribute, an index reorganization is needed to convert the index to the defined format. If the table or index is in error when this function is executed, then this value is NULL.
INDEX_PARTITIONING	CHAR(1)	Identifies the partitioning characteristic of the index. N = Nonpartitioned index P = Partitioned index Blank = Index is not on a partitioned table
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. Y if the index defined on the table or data partition requires a rebuild N otherwise If the table is in error when this function is executed, then this value is NULL.
LARGE_RIDS	CHAR(1)	Indicates whether or not the index is using large row IDs (RIDs) (4 byte page number, 2 byte slot number). Y indicates that the index is using large RIDs N indicates that the index is not using large RIDs P (pending) indicates that the table that the index is defined on supports large RIDs (that is, the table is in a large table space), but the index for the table or data partition has not been reorganized or rebuilt yet. Therefore, the table is still using 4 byte RIDs, and action must be taken to convert the table or index to large RIDs. If the table is in error where this function is executed, then this value is NULL.
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.