RESET_TABLE_INDEX_STATISTICS procedure

The RESET_TABLE_INDEX_STATISTICS procedure clears usage statistics for indexes defined over a table or tables and optionally deletes rows from the index advice tracking table.

Authorization: The usage statistics will only be reset when the caller has *OBJMGT and *OBJOPR authority on the table. For each index found over the table, *OBJOPR authority to the index is required. If the caller does not have the required authority to the table, the object is skipped and no warning is returned. If the caller does not have the required authority to the index, the object is skipped and an SQL warning is returned.

To delete index advice, the DELETE privilege is required on QSYS2/SYSIXADV. Index advice is only deleted when the caller has the required authority to the table and index.

Read syntax diagramSkip visual syntax diagramRESET_TABLE_INDEX_STATISTICS( SCHEMA_NAME =>  schema-name, TABLE_NAME =>  table-name ,DELETE_ADVICE => delete-advice )

The schema is QSYS2.

This procedure will reset the LAST_QUERY_USE, LAST_STATISTICS_USE, QUERY_USE_COUNT and QUERY_STATISTICS_COUNT usage statistics for all indexes over the specified tables. These values can also be reset using the Change Object Description (CHGOBJD) CL command, but the command requires an exclusive lock.

schema-name
A character string expression for the name of the schema or schemas to use. The name is case-sensitive and must not be delimited. Wildcard characters (_ and %) are allowed in the string following the rules for the SQL LIKE predicate.
table-name
A character string expression for the name of the table or tables to use. The name is case-sensitive and must not be delimited. Wildcard characters (_ and %) are allowed in the string following the rules for the SQL LIKE predicate.
delete-advice
A character string expression that indicates whether this procedure should remove rows from the index advice tracking table.
NO
Index advice for the table is not affected. This is the default.
YES
This procedure will delete rows from the index advice tracking table (QSYS2/SYSIXADV) that correspond to schema-name and table-name. To be removed, an index must exist and the user must be authorized to the index.

The procedure writes information related to every index processed into an SQL global temporary table. The LAST_USE_DATE and NUMBER_DAYS_USED columns are not affected. The following query will display the results of the last call to the procedure:

SELECT * FROM SESSION.SQL_INDEXES_RESET

The table that is created contains the following columns:

Table 1. SQL_INDEXES_RESET result table
Column Name System Column Name Data Type Description
TABLE_SCHEMA DBNAME VARCHAR(128) Schema name of table.
TABLE_NAME NAME VARCHAR(128) Name of table.
TABLE_PARTITION TABLE00001 VARCHAR(128) Name of the table partition or member.
PARTITION_TYPE PARTI00001 CHAR(1) The type of table partitioning.
PARITION_NUMBER PARTI00002 INTEGER The partition number of this partition.
NUMBER_DISTRIBUTED_PARTITIONS NUMBE00001 INTEGER If the table is a distributed table, contains the total number of partitions.
INDEX_SCHEMA INDEX00001 VARCHAR(128) Schema name of index.
INDEX_NAME INDEX_NAME VARCHAR(128) Name of index.
INDEX_MEMBER INDEX00002 VARCHAR(128) Partition or member name of index.
INDEX_TYPE INDEX_TYPE CHAR(11) Type of index.
LAST_QUERY_USE LAST_00002 TIMESTAMP The timestamp of the last time the SQL index was used in a query since the last time the usage statistics were reset.

Contains the null value if the SQL index has never been used in a query since the last time the usage statistics were reset.
LAST_STATISTICS_USE LAST_00003 TIMESTAMP The timestamp of the last time the SQL index was used by the optimizer for statistics since the last time the usage statistics were reset.

Contains the null value if the SQL index has never been used in a query since the last time the usage statistics were reset.
QUERY_USE_COUNT QUERY00001 BIGINT The number of times the SQL index was used in a query since the last time the usage statistics were reset.

Contains 0 if the SQL index has never been used in a query since the last time the usage statistics were reset.
QUERY_STATISTICS_COUNT QUERY00002 BIGINT The number of times the SQL index was used by the optimizer for statistics since the last time the usage statistics were reset.

Contains 0 if the SQL index has never been used in a query since the last time the usage statistics were reset.
SYSTEM_TABLE_SCHEMA SYS_DNAME CHAR(10) System table schema name.
SYSTEM_TABLE_NAME SYS_TNAME CHAR(10) System table name.
SYSTEM_TABLE_MEMBER SYSTE00001 CHAR(10) System member name.

Examples

  • Zero the statistics for all indexes over table TOYSTORE.SALES 
    CALL QSYS2.RESET_TABLE_INDEX_STATISTICS ('TOYSTORE', 'SALES')
  • Zero the statistics for all indexes over any table in schema TOYSTORE whose name starts with the letter S.
    CALL QSYS2.RESET_TABLE_INDEX_STATISTICS ('TOYSTORE', 'S%')