Maintained Temporary Indexes (MTIs)... everyone has them, but they're similar to building a house on sand... your foundation for performance is not rock solid. Use this Gist to gain some insight into this topic!

Largest MTIs in use today.sql

--
-- Gist: Largest MTIs in use today
--
-- Use SQL to understand where Maintained Temporary Indexes (MTIs) are being used, and more...
--

--
--
-- Find the tables with the 10 largest MTIs
--
select table_schema, table_name, system_table_schema, system_table_name, maintained_temporary_index_size
  from qsys2.systablestat
  where maintained_temporary_index_size > 0
  order by maintained_temporary_index_size desc
  limit 10;
stop;

--
-- Find the tables with the 10 largest MTIs, and discover how many MTIs exist
--
select s.table_schema, s.table_name, maintained_temporary_index_size, index_name, s.system_table_schema, s.system_table_name
  from qsys2.systablestat s, lateral (
         select *
           from qsys2.syspartitionindexes
           where table_schema = s.table_schema and table_name = s.table_name and index_type = 'TEMPORARY'
       )
  where maintained_temporary_index_size > 0
  order by maintained_temporary_index_size desc
  limit 10;
stop;

--
-- Find the tables with the 10 largest MTIs, and see the index advice detail
--
-- Note: There isn't a direct correlation between the MTIs that exist 'right now' and the MTIs that appear in the index advice AND have been used since the last IPL
--       So, if you see 1 MTI from the previous query, you could see more than 1 MTI advices from this query
-- 
with last_ipl (ipl_time) as (
    select job_entered_system_time
      from table (
          qsys2.job_info(job_status_filter => '*ACTIVE', job_user_filter => 'QSYS')
        ) x
      where job_name = '000000/QSYS/SCPF'
  )
  select s.table_schema, s.table_name, maintained_temporary_index_size, adv.key_columns_advised, adv.mti_created,
         adv.mti_used, adv.last_mti_used, adv.mti_used_for_stats, adv.last_mti_used_for_stats, s.system_table_schema,
         s.system_table_name
    from qsys2.systablestat s,
         lateral (
           select *
             from last_ipl, qsys2.condidxa c
             where c.table_schema = s.table_schema and c.table_name = s.table_name and (c.last_mti_used > ipl_time or
                     c.last_mti_used_for_stats > ipl_time)
         ) adv
    where maintained_temporary_index_size > 0
    order by maintained_temporary_index_size desc
    limit 10;
stop;