DB2 10.5 for Linux, UNIX, and Windows

REORGCHK_IX_STATS procedure - Retrieve index statistics for reorganization evaluation

The REORGCHK_IX_STATS procedure returns a result set containing index statistics that indicate whether or not there is a need for reorganization.

Syntax


>>-REORGCHK_IX_STATS--(--scope--,--criteria--)-----------------><

The schema is SYSPROC.

Procedure parameters

scope

An input argument of type CHAR(1) that specifies the scope of the tables that are to be evaluated, using one of the following values:

'T': Table
'S': Schema

criteria

An input argument of type VARCHAR(259).

If scope has a value of 'T', use this argument to specify one of the following values:

A fully qualified table name
The value ALL for all tables
The value USER for all user-defined tables
The value SYSTEM for system-defined tables

If scope has a value of 'S', use this argument to specify a schema name.

Authorization

SELECT privilege on catalog tables.
EXECUTE privilege on the REORGCHK_IX_STATS procedure.

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.

Example

CALL SYSPROC.REORGCHK_IX_STATS('T','JESCOTT.EMPLOYEE')

Usage note

The procedure uses the SYSTOOLSTMPSPACE table space. If SYSTOOLSTMPSPACE does not already exist, the procedure will create this table space.

Information returned

Table 1. Information returned by the REORGCHK_IX_STATS procedure
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.