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.

Procedure definition:

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

advice_option values:
-- 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;