Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save dancarlosgabriel/a73e785185bcb93aaa339d9979ec42f9 to your computer and use it in GitHub Desktop.
Save dancarlosgabriel/a73e785185bcb93aaa339d9979ec42f9 to your computer and use it in GitHub Desktop.
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".
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment