IBM Support

SYSTOOLS index advice procedures

News


Abstract

SYSTOOLS index advice procedures

Content

You are in: IBM i Technology Updates > Db2 for i - Technology UpdatesDb2 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":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
14 January 2020

UID

ibm11167790