QSYS2.INDEX_ADVICE procedure

This procedure is useful to anyone who wants to analyze index advice from different machines or from different points in time.

The Db2 for IBM i index advice condenser is externalized through the QSYS2/CONDENSEDINDEXADVICE view. The view and underlying user defined table function are hard-wired to use the raw index advice stored within the QSYS2/SYSIXADV file. Some users need to have the ability to utilize the index advice condenser against a file that was saved and restored from a different machine.

A new database supplied procedure (QSYS2.INDEX_ADVICE) has been added. The procedure establishes the QTEMP/CONDENSEDINDEXADVICE view over a user supplied library and file name. Once established, the user can query QTEMP/CONDENSEDINDEXADVICE to condense the index advice against the target index advice file.

The QSYS2.INDEX_ADVICE procedure also has options to return the index advice as a result set, either in raw advice format or in condensed format.
When the job ends or disconnects, the objects in QTEMP are automatically removed. The QSYS2.INDEX_ADVICE procedure also has options to return the index advice as a result set, either in raw advice format or in condensed format.

When the procedure is called with advice_option=0, the index advice level of the target file is determined. Once established, the user can query QTEMP.CONDENSEDINDEXADVICE to condense the index advice against the target index advice file.

create procedure QSYS2.INDEX_ADVICE(
in advice_library_name char(10),
in advice_file_name char(10),
in advice_option integer)

-- if advice_option=0 then setup for targeted condensed index advice, do not return a result set
-- if advice_option=1 return condensed index advice as a result set
-- if advice_option=2 return raw index advice as a result set

-- Example usage:
call qsys2.index_advice('ADVICELIB', 'SYSIXADV', 0);

-- Count the rows of raw advice
select count(*) from QTEMP.SYSIXADV where table_schema = 'PRODLIB' ;

-- Count the rows of condensed advice
select count(*) from QTEMP.CONDENSEDINDEXADVICE where table_schema = 'PRODLIB' ;

-- Review an overview of the most frequently advised, using condensed advice
select table_name, times_advised, key_columns_advised from QTEMP.CONDENSEDINDEXADVICE where table_schema = 'PRODLIB' order by times_advised desc;