The idea... discover MTIs and replace them with permanent indexes

mti_info.sql

--
-- Subject: The idea... discover MTIs and replace them with permanent indexes
-- Author: Scott Forstie  
-- Date  : June, 2022
-- Features Used : This Gist uses QSYS2.MTI_INFO, REPLACE(), QSYS2.CONDIDXA, RUNSQL CL command, and TIMESTAMPDIFF
--
-- Background:
-- The SQL Query Engine (SQE) recognizes when an index would be beneficial, 
-- and sometimes creates an index known as a Maintained Temporary Index (MTI).
-- The MTI can be removed by SQE for many reasons.
-- All MTIs are removed during an IPL of the IBM i.
--
-- The MTI_INFO UDTF returns information for MTIs that exist at the time of the query.
-- For more detail, look here:
-- https://www.ibm.com/docs/en/i/7.5?topic=services-mti-info-table-function
--
-- The idea of this Gist is to automate discovery and reaction to creation of potentially valuable indexes.
-- An ideal index strategy also includes examination of when performance based indexes are no longer 
-- high value, and removing them.
--
-- QSYS2.CONDIDXA may not be well known. CONDIDXA is the Condensed version of the Db2 for i Index Advisor.
--
-- IBM Lab Services can help educate, guide, and assist with all matters related to Db2 for i. I highly recommend them.
--
-- Step 1:
-- Confirm that you're using IBM i 7.3 or higher

-- Step 2:
-- Confirm that the Db2 for i PTF Group level is at or above the enabling level for MTI_INFO.
-- https://www.ibm.com/support/pages/node/1116627

-- Step 3:
-- Study and possibly execute the SQL queries below.
-- Beware: One of the queries will construct and execute CREATE INDEX statements! (cool)
--
stop;

--
-- What MTIs exist right now?
--
select *
  from table (
      qsys2.mti_info()
    );
stop;

--
-- What MTIs exist right now?
-- (avoid MTIs over IBM operating system objects)
--
select *
  from table (
      qsys2.mti_info()
    )
  where table_schema not like 'Q%';



--
-- Add more dimensions of insight for the MTI, via the Condensed Index Advisor (if any exists)
--
select 
 -- MTI defitional detail
 mti.TABLE_SCHEMA, mti.TABLE_NAME, KEY_DEFINITION, STATE, MTI_SIZE, 
 -- Related Index advice detail
 LAST_ADVISED, TIMES_ADVISED, ESTIMATED_CREATION_TIME, MOST_EXPENSIVE_QUERY, AVERAGE_QUERY_ESTIMATE, TABLE_SIZE, 
 MTI_USED, MTI_CREATED, LAST_MTI_USED, SYSTEM_TABLE_SCHEMA, MTI_USED_FOR_STATS, LAST_MTI_USED_FOR_STATS,
 -- Extra detail
 CREATE_TIME, LAST_BUILD_START_TIME, LAST_BUILD_END_TIME, REUSABLE, 
 SPARSE, SPARSE_DEFINITION, QRO_HASH, PLAN_IDENTIFIER, USER_NAME, QUALIFIED_JOB_NAME, JOB_NAME, JOB_USER, JOB_NUMBER,
 MTI_NAME, LIBRARY_NAME, FILE_NAME, SYSTEM_TABLE_NAME, PARTITION_NAME, KEY_COLUMNS_ADVISED, INDEX_TYPE  
  from table (
         qsys2.mti_info()
       ) mti
      left outer join qsys2.condidxa idx

         on idx.table_schema = mti.TABLE_SCHEMA and
            idx.table_name   = mti.TABLE_NAME and
            key_definition   = key_columns_advised and
            index_type       = 'RADIX'
  where mti.table_schema not like 'Q%';
stop;
--
-- Generate CREATE INDEX (SQL) statements to replace the MTIs with a permanent index
--
select 
  'runsql sql(''create index ' concat qsys2.delimit_name(mti.table_schema) concat '.' concat 
   qsys2.delimit_name(mti.table_name) concat '_MTI_' concat rtrim(substr(replace(KEY_DEFINITION, ', ', ''),1,100)) concat ' on ' concat 
   qsys2.delimit_name(mti.table_schema) concat '.' concat qsys2.delimit_name(mti.table_name) concat ' ( ' concat KEY_DEFINITION concat ' ) ' concat ''') commit(*NONE)' ,
   TIMESTAMPDIFF(4, CAST(last_build_end_time - last_build_start_time AS CHAR(22)))
              AS index_build_estimate_minutes 
  from table (
         qsys2.mti_info()
       ) mti
      left outer join qsys2.condidxa idx
         on idx.table_schema = mti.TABLE_SCHEMA and
            idx.table_name   = mti.TABLE_NAME and
            key_definition   = key_columns_advised and
            index_type       = 'RADIX'
  where mti.table_schema not like 'Q%';
stop;

--
-- Generate and execute CREATE INDEX (SQL) statements to replace the MTIs with a permanent index
-- Note: don't execute the create index if the estimate build time exceeds 1 hour
--
select 
 qsys2.qcmdexc('runsql sql(''create index ' concat qsys2.delimit_name(mti.table_schema) concat '.' concat 
   qsys2.delimit_name(mti.table_name) concat '_MTI_' concat rtrim(substr(replace(KEY_DEFINITION, ', ', ''),1,100)) concat ' on ' concat 
   qsys2.delimit_name(mti.table_schema) concat '.' concat qsys2.delimit_name(mti.table_name) concat ' ( ' concat KEY_DEFINITION concat ' ) ' concat ''') commit(*NONE)' )
  from table (
         qsys2.mti_info()
       ) mti
      left outer join qsys2.condidxa idx
         on idx.table_schema = mti.TABLE_SCHEMA and
            idx.table_name   = mti.TABLE_NAME and
            key_definition   = key_columns_advised and
            index_type       = 'RADIX'
  where mti.table_schema not like 'Q%' and
        TIMESTAMPDIFF(4, CAST(last_build_end_time - last_build_start_time AS CHAR(22))) < 60;
stop;

Should people review the findings before creating a permanent index or do you suggest creating a permanent index all the time?
I don't know when operations will IPL the system.
Is there a way I could get the system to query and report these details about the MTIs every time the system is being IPL'd?

Hello Glenn.
Indexes aren't free. They require storage and CPU (to maintain them).
Therefore, I typically advise an iterative approach where indexes are being added and removed, based upon a judgment of the value they provide.
I have met clients who decide to automate their management of the index strategy, and they seem very happy with the outcome.
Yes, you can at least capture the essential detail by establishing the MTI_INFO query as a command exit point for PWRDWNSYS. Perhaps you would wrapper the query with an INSERT statement and always have the full insight as to the MTI's that are being used by the business. Then, someone could review those details and decide when and where to create permanent indexes.
Exciting days indeed, thanks to the various techniques for probing and managing Db2 for i.
Regards, Scott

Thanks Scott!Yours truly,Glenn ***@***.***(416) 317-3144 -------- Original message --------From: Scott Forstie ***@***.***> Date: 2022-06-03 10:14 a.m. (GMT-05:00) To: forstie ***@***.***> Cc: GlennGundermann ***@***.***>, Comment ***@***.***> Subject: Re: forstie/mti_info.sql @forstie commented on this gist. Hello Glenn. Indexes aren't free. They require storage and CPU (to maintain them). Therefore, I typically advise an iterative approach where indexes are being added and removed, based upon a judgment of the value they provide. I have met clients who decide to automate their management of the index strategy, and they seem very happy with the outcome. Yes, you can at least capture the essential detail by establishing the MTI_INFO query as a command exit point for PWRDWNSYS. Perhaps you would wrapper the query with an INSERT statement and always have the full insight as to the MTI's that are being used by the business. Then, someone could review those details and decide when and where to create permanent indexes. Exciting days indeed, thanks to the various techniques for probing and managing Db2 for i. Regards, Scott —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: ***@***.***>