Wednesday, August 17, 2022

Getting information about MTI using SQL

MTI information via 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.

4 comments:

  1. Thank you for the information, Simon.

    ReplyDelete
  2. That's great info!
    Ringer

    ReplyDelete
  3. There is security wrapped around this function. My attempt on PUB400:
    Message ID: CPF503E
    The error code is 1.
    1 -- The external program or service program returned SQLSTATE 42502. The text message returned from the program is: *JOBCTL SPECIAL AUTHORITY OR QIBM_DB_SQLADM FUNCTION USAGE IS REQUIRED.

    Ringer

    ReplyDelete
  4. While many MTIs can be mapped to a row in the Index Advisor view, SYSIXADV, not every MTI has a corresponding Advised Index entry. That's another reason that the MTI_INFO() service is a powerful new addition to Db2 for i

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.