DB2 10.5 for Linux, UNIX, and Windows

REORGCHK_TB_STATS procedure - Retrieve table statistics for reorganization evaluation

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

Syntax


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

The schema is SYSPROC.

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:

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:

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_TB_STATS('T','JESCOTT.EMPLOYEE')

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

Table 1. Information returned by the REORGCHK_TB_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
DATAPARTITIONNAME	VARCHAR(128)	Name of the data partition. NULL for nonpartitioned tables.
CARD	BIGINT	Cardinality (number of rows in the table).
OVERFLOW	BIGINT	Number of overflow rows.
NPAGES	BIGINT	Total number of pages on which the rows of the table exist; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table.
FPAGES	BIGINT	Total number of pages; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table.
ACTIVE_BLOCKS	BIGINT	Total number of active blocks for a multidimensional clustering (MDC) or insert time clustering (ITC) table. This field is only applicable to tables defined using the ORGANIZE BY clause. It indicates the number of blocks of the table that contains data.
TSIZE	BIGINT	Size of the table.
F1	INTEGER	F1 formula value.
F2	INTEGER	F2 formula value.
F3	INTEGER	F3 formula value.
REORG	CHAR(3)	A 3-character field, each character mapping to one of the three formulas: F1, F2, and F3; 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