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
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_TB_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_TB_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
| 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 |