Getting information about MTI using SQL

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 that 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 MTI_INFO Table function.

The Table function has two optional parameters:

1. Table schema
2. Table name

If no parameters are passed then all the MTI present in your partition are returned in your results.

If the Table schema is passed as the only parameter, then it is assumed that you want the results for every Table in that Schema/library.

if you want to know all of the MTI for a file that exists in multiple libraries you can use '*ALL' for the Table schema name.

If I wanted to list all the MTI in the partition I am using I can use the following SQL statement.

SELECT * FROM TABLE(QSYS2.MTI_INFO()) ORDER BY TABLE_SCHEMA,TABLE_NAME

I have sorted the results by Schema and Table name so I can group the MTI for each Table together.

I do recommend you run this statement in your partition so that you can see the information that is returned in the results.

If I wanted to count the number of MTI I have I could use the following statement:

SELECT COUNT(*) FROM TABLE(QSYS2.MTI_INFO())

Which returns to me:

00001 ------ 127

Here I am only concerned with the MTI built over one of my Tables:

SELECT * FROM TABLE(QSYS2.MTI_INFO(TABLE_SCHEMA => 'PRODLIB', TABLE_NAME => 'ACCMAST'))

You can see in this example I have used the parameter names. Again, I am not going to show the results as there are so many columns, and I think it is useful for you to see them yourself.

in this example I am only interested in a few of the columns, which I thought looked the most interesting and helpful.

01 SELECT PLAN_IDENTIFIER,TABLE_SCHEMA,TABLE_NAME,REFERENCE_COUNT, 02 KEYS,KEY_DEFINITION,DATE(CREATE_TIME) AS CREATE_DATE 03 FROM TABLE(QSYS2.MTI_INFO('PRODLIB','ACCMAST')) 04 ORDER BY REFERENCE_COUNT DESC

In this statement I did not use the parameters names with the parameter values, I just used the parameter values.

The columns I want in my results are:

• PLAN_IDENTIFER:  The number of the plan cache that created this MTI
• TABLE_SCHEMA:  Table schema
• TABLE_NAME:  Table name
• REFERENCE_COUNT:  Current number of references to this MTI
• KEYS:  Number of key columns
• KEY_DEFINITION:  Key columns
• CREATE_TIME:  When the MTI was created. I have used the Date scalar function to convert the timestamp into a date.

My results for the statement are:

PLAN_ TABLE_ TABLE_ REFERENCE KEY_ IDENTIFIER SCHEMA NAME _COUNT KEYS DEFINITION CREATE_DATE ---------- ------- ------ --------- ---- ------------------- ----------- 10423 PRODLIB ACCMAST 40 2 ACCTNBR, ACCTNAME YYYY-MM-DD 10417 PRODLIB ACCMAST 21 2 CISKEY, ACCTNBR YYYY-MM-DD 10352 PRODLIB ACCMAST 21 2 DATE1 DESC, ACCTNBR YYYY-MM-DD

Notice that on the third row of the results that the DATE1 column is sorted in descending order.

Where else can I find this information? Fortunately I can see it in the Index Advisor View, SYSIXADV, too.

This is the SQL statement I created to retrieve the same type of information from SYSIXADV:

01 SELECT TIMES_ADVISED,LAST_ADVISED,TABLE_SCHEMA,TABLE_NAME, 02 KEY_COLUMNS_ADVISED,LAST_MTI_USED 03 FROM QSYS2.SYSIXADV A 04 WHERE LAST_MTI_USED IS NOT NULL 05 AND TABLE_SCHEMA = 'PRODLIB' 06 AND TABLE_NAME = 'ACCMAST' 07 ORDER BY TIMES_ADVISED DESC,LAST_MTI_USED DESC

The results look like:

TIMES_ TABLE_ TABLE_ KEY_COLUMNS ADVISED LAST_ADVISED SCHEMA NAME _ADVISED ------- -------------------------- ------- ------- ------------------ 1173 YYYY-MM-DD 19:24:29.800146 PRODLIB ACCMAST ACCTNBR, ACCTNAME 391 YYYY-MM-DD 19:24:29.800074 PRODLIB ACCMAST CISKEY, ACCTNBR 391 YYYY-MM-DD 19:23:58.534475 PRODLIB ACCMAST DATE1, ACCTNBR LAST_MTI_USED -------------------------- YYYY-MM-DD 19:23:20.472161 YYYY-MM-DD 19:23:19.404742 YYYY-MM-DD 19:22:55.593550

The times advised is a good column to have as it is the total number of times advised. The MTI_INFO only counts the number of times the MTI has been used since it was created, which is after the last IPL.

One thing I did notice is the key columns for the third result is different from the MTI. Here DATE1 is not sorted in descending order.

The LAST_MTI_USED matches the create time from MRI_INFO.

I have to say that MTI_INFO is another great tool to use when looking which suggested Indexes to build.

 

You can learn more about the MTI_INFO SQL Table function from the IBM website here.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.