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 via email

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