The REORGCHK_IX_STATS procedure returns a result set containing index statistics that indicate whether or not there is a need for reorganization.
If scope has a value of 'T', use this argument to specify one of the following values:
If scope has a value of 'S', use this argument to specify a schema name.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.
CALL SYSPROC.REORGCHK_IX_STATS('T','JESCOTT.EMPLOYEE')
The procedure uses the SYSTOOLSTMPSPACE table space. If SYSTOOLSTMPSPACE does not already exist, the procedure will create this table space.
Column name | Data type | Description |
---|---|---|
TABLE_SCHEMA | VARCHAR(128) | table_schema - Table schema name monitor element |
TABLE_NAME | VARCHAR(128) | table_name - Table name monitor element |
INDEX_SCHEMA | VARCHAR(128) | index_schema - Index schema monitor element |
INDEX_NAME | VARCHAR(128) | index_name - Index name monitor element |
DATAPARTITIONNAME | VARCHAR(128) | Name of the data partition. NULL for nonpartitioned tables. |
INDCARD | BIGINT | Number of index entries in the index. This can be different than table cardinality for some indexes. For example, the index cardinality on XML columns might be greater than the table cardinality. |
NLEAF | BIGINT | nleaf - Number of leaf pages monitor element |
NUM_EMPTY_LEAFS | BIGINT | Number of pseudo-empty index leaf pages. |
NLEVELS | INTEGER | nlevels - Number of index levels monitor element |
NUMRIDS_DELETED | BIGINT | Number of pseudo-deleted RIDs. |
FULLKEYCARD | BIGINT | Number of unique index entries that are not marked deleted. |
LEAF_RECSIZE | BIGINT | Record size of the index entry on a leaf page. This is the average size of the index entry excluding any overhead and is calculated from the average column length of all columns participating in the index. |
NONLEAF_RECSIZE | BIGINT | Record size of the index entry on a non-leaf page. This is the average size of the index entry excluding any overhead and is calculated from the average column length of all columns participating in the index except any INCLUDE columns. |
LEAF_PAGE_OVERHEAD | BIGINT | Reserved space on the index leaf page for internal use. |
NONLEAF_PAGE_OVERHEAD | BIGINT | Reserved space on the index non-leaf page for internal use |
PCT_PAGES_SAVED | SMALLINT | Percent of pages saved using Index Compression. A non-zero number indicates the index is compressed. |
F4 | INTEGER | F4 formula value. |
F5 | INTEGER | F5 formula value. |
F6 | INTEGER | F6 formula value. |
F7 | INTEGER | F7 formula value. |
F8 | INTEGER | F8 formula value. |
REORG | CHAR(5) | A 5-character field, each character mapping to one of the five formulas: F4, F5, F6, F7, and F8; a dash means that the formula value is in the recommended range; an asterisk means that the formula value is out of the recommended range, indicating a need for reorganization. |