Maintained Temporary Indexes, MTI, are SQL Indexes that have been created automatically by the Db2 optimizer, without any outside intervention. The reason an MTI is created is there is no suitable existing Index to meet the requirements of a SQL query. MTI is just like any other SQL Index in my IBM i systems. MTI are temporary, as is suggested by their name, and are deleted when the partition is IPL-ed. A MTI might not be recreated the first time the SQL query is executed, it may take several times before the Db2 optimizer decides it is advantageous to create it. Therefore, to optimize your systems it is a good idea to review the Index Advisor on a regular basis, to determine if there are Indexes should be created to alleviate the need for a MTI.
This begs the question: How can I see what MTI currently exist on my partition?
Fortunately there is a way using the
