ACT_ON_INDEX_ADVICE procedure

The ACT_ON_INDEX_ADVICE procedure creates new indexes for a table based on indexes that have been advised for the table.

Authorization: See Note below.

Read syntax diagramSkip visual syntax diagramACT_ON_INDEX_ADVICE(schema-name ,table-name, times_advised,mti_used,average_estimate )

The schema is SYSTOOLS.

schema-name
A character string containing the system name of the schema containing the table.
table-name
A character string containing the system name of the table. If NULL is passed, this parameter is not used to select the target index advice.
times-advised
The number of times an index should have been advised before creating a permanent index. If NULL is passed, this parameter is not used to select the target index advice.
mti-used
The number of times a maintained temporary index (MTI) has been used because a matching permanent index did not exist. If NULL is passed, this parameter is not used to select the target index advice.
average-estimate
The average estimated number of seconds needed to execute the query that drove the index advice. If NULL is passed, this parameter is not used to select the target index advice.

For each potential index meeting the specified criteria, a CREATE INDEX statement will be run to generate the permanent index. A radix index will be named name_RADIX_INDEX_n. An EVI index will be named name_EVI_INDEX_n. The name represents the table name and n is a unique number. The row containing this advised index is removed from the QSYS2.SYSIXADV table.

Note

This procedure is provided in the SYSTOOLS schema as an example of how to process index advice using an SQL procedure. Similar to other Db2® for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar procedures, or to create a customized version within a user-specified schema.

Services provided in SYSTOOLS have authorization requirements that are determined by the interfaces used to implement the service. To understand the authority requirements, extract the SQL for the service and examine the implementation.

Examples

For schema PRODLIB, find all instances of index advice where a maintained temporary index was used more than 1000 times and create permanent SQL indexes. 

CALL SYSTOOLS.ACT_ON_INDEX_ADVICE(‘PRODLIB’,NULL,NULL,1000,NULL)