News
Abstract
SYSTOOLS index advice procedures
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Functional Enhancements > SYSTOOLS index advice procedures
SYSTOOLS.ACT_ON_INDEX_ADVICE procedure
- Provided as an example of how to automate the creation of permanent indexes based upon the index advice and user supplied criteria.
- Either use it “as is” or customize it to fit your business logic and requirements.
CREATE PROCEDURE SYSTOOLS.ACT_ON_INDEX_ADVICE (
IN P_LIBRARY CHAR(10) ,
IN P_FILE CHAR(10) ,
IN P_TIMES_ADVISED BIGINT ,
IN P_MTI_USED BIGINT ,
IN P_AVERAGE_QUERY_ESTIMATE INTEGER )
--
-- Immediately act on the index advice where the condensed times advised
-- is greater than 4,
-- The MTI used value and the average query estimate are not factored in
CALL SYSTOOLS.ACT_ON_INDEX_ADVICE('ADVICELIB', 'EMPLOYEE', 4, 0, 0);
SYSTOOLS. HARVEST_INDEX_ADVICE procedure|
- Similar to the other procedure, except this one places the CREATE INDEX statements in a target source physical file.
- Either use it “as is” or customize it to fit your business logic and requirements.
CREATE PROCEDURE SYSTOOLS.HARVEST_INDEX_ADVICE (
IN P_LIBRARY CHAR(10) ,
IN P_FILE CHAR(10) ,
IN P_TIMES_ADVISED BIGINT ,
IN P_MTI_USED BIGINT ,
IN P_AVERAGE_QUERY_ESTIMATE INTEGER ,
IN T_LIBRARY CHAR(10) ,
IN T_FILE CHAR(10) )
CL: DLTF FILE(QGPL/INDEXSRC);
CL: CRTSRCPF FILE(QGPL/INDEXSRC) RCDLEN(10000);
CALL SYSTOOLS.HARVEST_INDEX_ADVICE('CORPDB23', 'SALES', 100, 0, 0, 'QGPL', 'INDEXSRC');
Refer to the IBM i Knowledge Center for details: Db2 for i performance services
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
14 January 2020
UID
ibm11167790