Skip to content

Instantly share code, notes, and snippets.

@forstie
Last active Jun 3, 2022
Embed
What would you like to do?
The idea... discover MTIs and replace them with permanent indexes
--
-- 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;
@GlennGundermann
Copy link

GlennGundermann commented Jun 3, 2022

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?

@forstie
Copy link
Author

forstie commented Jun 3, 2022

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

@GlennGundermann
Copy link

GlennGundermann commented Jun 3, 2022

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment