Index Advisor Surfaces Maintained Temporary Indexes Activity on DB2 for i5/OS

Maintained Temporary Indexes (MTIs) are created and used by the SQE optimizer in V5R4, under certain situations where a matching permanent index does not exist.  The MTI existence and usage information has been externalized to the customer by way of the iSeries Navigator Index Advisor and Show Indexes facilities.

Viewing the MTI information in the context of index advice can help explain performance fluctuations. In one common scenario, performance is shown to be degraded after an IPL, but improves over time as queries are executed. MTIs do not persist across an IPL. By accessing the Show Indexes detail for the table, you can confirm that MTIs exist. Further, the index advisor information details how often a specific MTI has been created and used and how recently it was used.

When paired with other index advice environmental information such as Times Advised and Average Query Estimate, a better index strategy can be determined. If an MTI becomes a crucial part of an index strategy, it might be the perfect time to change the permanent indexes to avoid using the MTI altogether. This can be accomplished by simply launching the Create Index dialog from the index advisor. The optimizer will recognize the existence of a matching permanent index and discard the MTI. This change will be reflected in the index advisor table via the MTI Last Used timestamp.

After making any change to an index strategy, the usage information within the permanent indexes can be reset. Resetting the usage statistics makes it easier to evaluate the value of the current set of permanent indexes. Because an implied maintenance cost is associated with each index, having as few permanent indexes as possible is preferable.

Use this command to reset Index statistics:
CHGOBJD OBJ(schema/index) OBJTYPE(*FILE) USECOUNT(*RESET))

Additionally, after an index strategy is changed, it might be useful to clear out the existing index advice for that schema or table. There are many ways to clear the advice, both within iSeries Navigator and from Run SQL Scripts. After clearing the advice and index statistics, the index strategy is ready to be evaluated. These techniques work in a performance analysis environment, on a development system or on production machines.

The IBM eServer iSeries database has an on demand index advisor solution. The information is there, just waiting for someone to extract the value by making better choices to achieve optimal performance.

Visual tour
From the iSeries Navigator Schemas folder view, right-click the schema of choice to launch Index Advisor.




The MTI information appears on the far right side. The table can be resorted by selecting any column header. The table shown here is sorted by MTI USED.




Right-click a row of index advice to launch Show Indexes (as shown) for easy comparison between existing permanent indexes and MTIs over this table to the indexes being advised.




MTI detail under Show Indexes is summary information. Refer to the Index Advice table for specifics about MTIs.




The QSYS2/SYSIXADV columns are described in the following table.

The following table describes the indexes advised system table.