Database and SQL performance tuning is a persistent focus, which many times leads into the indexing strategy. In this gist, I show how some of the existing tools can be tied together to achieve an automated "DBE in a box".

Reactive index strategy.sql

-- Subject: DBE in a box
-- Author: Scott Forstie  
-- Date  : August, 2023
-- Features Used : This Gist uses SQL PL,  qsys2.SYSTEM_STATUS_INFO_basic, systools.lprintf, qsys2.sysixadv, qsys2.sysindexes, QSYS2.SYSINDEXSTAT, SYSTOOLS.ACT_ON_INDEX_ADVICE, QSYS2.RESET_TABLE_INDEX_STATISTICS and SYSTOOLS.REMOVE_INDEXES
--
-- Note: 
-- 1) Indexes are not free, and care should be taken when deciding whether an index is worth creating
-- 2) Indexes are not free, and care should be taken to understand whether adequate system resources are available before creating additional indexes
-- 3) Indexes are not free, and care should be taken to establish a well trained Database Engineer (DBE) responsible for your IBM i partitions
--
-- This Gist shows how a portion of the job of the DBE could be automated.
-- 
-- Disclaimer: 
-- This example is being provided by IBM to allow IBM i users to understand how index advice 
-- could be consumed to improve an index strategy.  The creation of indexes can be time consuming 
-- and having seldom used indexes may result in a performance degradation.  As with any index 
-- strategy, it is recommended that you carefully consider the performance characteristics of 
-- your application prior to creating new indexes and that you evaluate the index usage 
-- statistics. 
-- 
-- While efforts were made to verify the completeness and accuracy of this sample procedure, 
-- this sample is provided 'as is' without any warranty whatsoever and to the maximum extent permitted, 
-- IBM disclaims all implied warranties. 
-- 
stop;
CREATE or replace PROCEDURE Coolstuff.DBE_in_a_box ( 
	IN minimum_system_asp_percent_available decimal(5,2) default 70.0,
    in p_mti_use_count bigint default 1000, 
    in p_index_age varchar(100) default ' 7 days ', 
    out size_of_indexes_gig decimal(20,2) ) 
 
	NOT DETERMINISTIC 
	MODIFIES SQL DATA   
	SET OPTION   
	COMMIT = *NONE ,  
	DYNUSRPRF = *USER ,  
	USRPRF = *USER   
	BEGIN
declare v_system_asp_percent_available decimal(5,2);

select system_asp_used into v_system_asp_percent_available
  from QSYS2.SYSTEM_STATUS_INFO_basic X;

if (v_system_asp_percent_available >  minimum_system_asp_percent_available) then 
  call systools.lprintf('Coolstuff.DBE_in_a_box() did not run. The system_asp_used was:' concat v_system_asp_percent_available concat ' and the minimum_system_asp_percent_available was:' concat minimum_system_asp_percent_available);
  return;
end if;

--
-- Create an index if the index advice shows that a corresponding MTI has been created and used at least p_mti_use_count times.
--
CALL SYSTOOLS.ACT_ON_INDEX_ADVICE(P_MTI_USED => p_mti_use_count, P_TIMES_ADVISED => null, P_AVERAGE_QUERY_ESTIMATE => null, P_FILE => null, P_LIBRARY => null);
 
--
-- Delete index advice that has been acted upon
--
delete from qsys2.sysixadv where MTI_USED >= p_mti_use_count;

--
-- Remove indexes created by SYSTOOLS.ACT_ON_INDEX_ADVICE, if the index has NOT been used at least p_mti_use_count times in the last week
--
CALL SYSTOOLS.REMOVE_INDEXES (
  P_LIBRARY    => null,
  P_TIMES_USED => p_mti_use_count,
  P_INDEX_AGE  => p_index_age
);

--
-- Reset the query use statistics for indexes created by SYSTOOLS.ACT_ON_INDEX_ADVICE
--
begin
  declare at_end integer default 0;
  declare not_found condition for '02000';
  declare v_index_schema varchar(128) for sbcs data;
  declare v_index_name   varchar(128) for sbcs data;
  declare act_on_indexes cursor for 
    select INDEX_SCHEMA, INDEX_NAME
      from QSYS2.SYSINDEXSTAT
      where OWNING_INDEX_TYPE = 'INDEX' and 
        (INDEX_NAME like '%_EVI_INDEX_%' or
          INDEX_NAME like '%_RADIX_INDEX_%'); 

  declare continue handler for not_found set at_end = 1;
  open act_on_indexes;
  fetch from act_on_indexes into v_index_schema, v_index_name;
  while (at_end = 0) do
    call QSYS2.RESET_TABLE_INDEX_STATISTICS(v_index_schema, v_index_name, DELETE_ADVICE => 'YES');
    fetch from act_on_indexes into v_index_schema, v_index_name;
  end while;
    
  close act_on_indexes;
end;

--
-- Total storage dedicated to DBE in a box created indexes
--
select decimal(decimal(sum( INDEX_SIZE ),34,2) / decimal(1024 * 1024 * 1024,34,2),20,2) into size_of_indexes_gig 
      from QSYS2.SYSINDEXSTAT
      where OWNING_INDEX_TYPE = 'INDEX' and 
        (INDEX_NAME like '%_EVI_INDEX_%' or
          INDEX_NAME like '%_RADIX_INDEX_%'); 

end;
stop;

--
-- create and drop indexes found by the DBE in a box
--
call Coolstuff.DBE_in_a_box(size_of_indexes_gig => ?);
stop;

--
-- See the 'DBE in a box' created indexes from today
--
select * from qsys2.sysindexes where (INDEX_NAME like '%_EVI_INDEX_%' or
          INDEX_NAME like '%_RADIX_INDEX_%') and date(CREATE_TIMESTAMP) = current date;