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

Published 08 August 2006, updated 12 August 2008

More options

Rate and comment

Authors: Hernando Bedoya


DB2® for i5/OS® on V5R4 provides features and tooling to define, test, and improve index strategies. With the addition of Maintained Temporary Index (MTI) detail, the user can better understand temporary indexes and their effect on system performance.

Written by Scott Forstie
SQL development leader
IBM® DB2 UDB 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:

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.

Context menu path showing schema to Index Advisor to 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.

MTI information table sorted by the MTI USED column

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.

Context menus from item row to Table to Show Indexes

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

MTI detail showing SQL name, type, schema, text, and query use

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

Column nameDescription
Table for Which Index was AdvisedThe optimizer is advising creation of a permanent index over this table. This is the long name for the table. The advice was generated because the table was queried and no existing permanent index could be used to improve the performance of the query.
SchemaSchema or library name for the table.
Short NameSystem table name on which the index is advised.
PartitionPartition detail for the index. Possible values:
  • <blank>, which means for all partitions
  • For Each Partition
  • Specific name of the partition
Keys AdvisedColumn names for the advised index. The order of the column names is important. The names should be listed in the same order on the CREATE INDEX SQL statement, unless the leading, order-independent key information indicates that the ordering can be changed.
Leading Keys Order IndependentThe keys at the beginning of the KEY_COLUMNS_ADVISED field that could be reordered and still satisfy the index being advised.
Index Type AdvisedPossible values:
Last Advised for Query UseThe timestamp representing the last time this index was advised for a query.
Times Advised for Query UseThe cumulative number of times this index has been advised. This count should stop increasing when a matching permanent index is created. The row of advice will remain in this table until the user removes it.
Estimated Index Creation TimeEstimated time required to create this index.
Reason AdvisedCoded reason why index was advised.
Possible values:
  • Row selection
  • Ordering/Grouping
  • Row selection and Ordering/Grouping
Logical Page Size Advised (KB)Recommended page size to be used on the PAGESIZE keyword of the CREATE INDEX SQL statement when creating this index.
Most Expensive Query EstimateExecution time in seconds of the longest-running query that generated this index advice.
Average of Query Estimates (seconds)Average execution time in seconds of all queries that generated this index advice.
Rows in Table when AdvisedNumber of rows in the table for which the index is being advised, for the last time this index was advised.
NLSS Table AdvisedThe sort sequence table in use by the query that generated the index advice. For more detail about sort sequences: and
NLSS Schema AdvisedThe library of the sort sequence table.
MTI USEDThe number of times that this specific MTI has been used by the optimizer. The optimizer will stop using a matching MTI when a permanent index is created.
MTI CREATEDThe number of times that this specific MTI has been created by the optimizer. MTIs do not persist across system IPLs.
MTI LAST USEDThe timestamp representing the last time this specific MTI was used by the optimizer to improve the performance of a query. The MTI Last Used field can be blank, which indicates that an MTI that exactly matches this advice has never been used by the queries that generated this index advice.

Database manager indexes advised system table

The following table describes the indexes advised system table.

Column nameSystem column
Data typeDescription
TABLE_NAMETBNAMEVARCHAR(258)Table over which an index is advised
TABLE_SCHEMADBNAMECHAR(10)Schema containing the table
SYSTEM_TABLE_NAMESYS_TNAMECHAR(10)System table name on which the index is advised
PARTITION_NAMETBMEMBERCHAR(10)Partition detail for the index
KEY_COLUMNS_ADVISEDKEYSADVVARCHAR(16000)Column names for the advised index
LEADING_COLUMN_KEYSLEADKEYSVARCHAR(16000)Leading, Order Independent keys, the keys at the
beginning of the KEY_COLUMNS_ADVISED field
that could be reordered and still satisfy the index
being advised
LAST_ADVISEDLASTADVTIMESTAMPLast time this row was updated
TIMES_ADVISEDTIMESADVBIGINTNumber of times this index has been advised
ESTIMATED_CREATION_TIMEESTTIMEINTEstimated number of seconds for index creation
REASON_ADVISEDREASONCHAR(2)Coded reason why index was advised
LOGICAL_PAGE_SIZEPAGESIZEINTRecommended page size for index
MOST_EXPENSIVE_QUERYQUERYCOSTINTExecution time in seconds of the query
AVERAGE_QUERY_ESTIMATEQUERYESTINTAverage execution time in seconds of the query
TABLE_SIZETABLE_SIZEBIGINTNumber of rows in table when the index was advised
NLSS_TABLE_NAMENLSSNAMECHAR(10)NLSS table to use for the index
MTI_USEDMTIUSEDBIGINTNumber of times an MTI that matched the advised
definition was used by the database because a
matching permanent index did not exist
MTI_CREATEDMTICREATEDINTNumber of times this specific index advice was
used by the database to create a MTI
LAST_MTI_USEDLASTMTIUSETIMESTAMPLast time an MTI was used by the database
because a matching permanent index did not exist

PTF information
The Index Advisor MTI enabling Server PTFs were included in SF99504: 540 DB2 UDB for iSeries Group Level #4.
The iSeries Navigator enabling Client PTFs will ship on September 1, 2006.

Related articles
DB2 for i5/OS Redefines On Demand for Indexing

The Optimizer Takes Its Own Advice

Taming the Business-Intelligence Monster

Special Notices

This material has not been submitted to any formal IBM test and is published AS IS. It has not been the subject of rigorous review. IBM assumes no responsibility for its accuracy or completeness. The use of this information or the implementation of any of these techniques is a client responsibility and depends upon the client's ability to evaluate and integrate them into the client's operational environment.

Follow IBM Redbooks

Follow IBM Redbooks