HARVEST_INDEX_ADVICE procedure

The HARVEST_INDEX_ADVICE procedure generates one or more CREATE INDEX statements in source file members for a specified table based on indexes that have been advised for the table.

The schema is SYSTOOLS.

schema-name: A character string containing the system name of the schema containing the table.
table-name: A character string containing the system name of the table.
times-advised: The number of times an index should have been advised before creating a permanent index. Pass a value of 1 to not limit index creation by the number of times advised.
mti-used: The number of times a maintained temporary index (MTI) has been used because a matching permanent index did not exist. Pass a value of 0 to not limit index creation by MTI use.
average-estimate: The average estimated number of seconds needed to execute the query that drove the index advice. Pass a value of 0 to not limit index creation by the average query estimate.
output-library: A character string value containing the name of the library for the output source file.
output-file: A character string value containing the name of the output source file. The file must exist and must be a source physical file.

For each potential index meeting the specified criteria, a CREATE INDEX statement to create the permanent index will be generated in a member in the source file provided to this procedure. A radix index will be named name_RADIX_INDEX_n. An EVI index will be named name_EVI_INDEX_n. The name represents the table name and n is a unique number. The row containing this advised index is removed from the QSYS2.SYSIXADV table.

Example

Harvest create index statements for file TOYSTORE/SALES into source physical file QGPL/INDEXSRC. Then use RUNSQLSTM to create the indexes.

BEGIN
 DECLARE NOT_FOUND CONDITION FOR '02000';
 DECLARE ERROR_COUNT INTEGER DEFAULT 0;
 DECLARE AT_END INT DEFAULT 0;
 DECLARE V_INDEX_COUNT INTEGER DEFAULT 0;
 DECLARE V_PARTITION_NAME VARCHAR(10);
 DECLARE INDEX_SOURCE_CURSOR CURSOR FOR 
   SELECT TABLE_PARTITION FROM QSYS2.SYSPARTITIONSTAT 
      WHERE TABLE_SCHEMA = 'QGPL' AND TABLE_NAME = 'INDEXSRC';
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ERROR_COUNT = ERROR_COUNT + 1;

 CALL QSYS2.QCMDEXC('DLTF FILE(QGPL/INDEXSRC)');
 CALL QSYS2.QCMDEXC('CRTSRCPF FILE(QGPL/INDEXSRC)  RCDLEN(10000)');  
 -- Create the source file with a big record length, 
 -- to allow the create index statement to fit on one line
 CALL SYSTOOLS.HARVEST_INDEX_ADVICE('TOYSTORE', 'SALES', 100, 50, 5, 'QGPL', 'INDEXSRC');
  BEGIN
    DECLARE V_RUNSQLSTM_TEXT VARCHAR(500);
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET AT_END = 1;
    DECLARE CONTINUE HANDLER FOR NOT_FOUND    SET AT_END = 1;
    OPEN INDEX_SOURCE_CURSOR;
    FETCH FROM INDEX_SOURCE_CURSOR INTO V_PARTITION_NAME;
    WHILE ( AT_END = 0 ) DO 
      -- Now that QGPL/INDEXSRC has been populated with members named HARVnnnn, 
      -- the RUNSQLSTM command can be used to execute the CREATE INDEX statement.
      -- By using ERRLVL = 30, we'll ignore any failures.
      SET V_RUNSQLSTM_TEXT = 'RUNSQLSTM SRCFILE(QGPL/INDEXSRC) SRCMBR(' 
                 CONCAT RTRIM(V_PARTITION_NAME) 
                 CONCAT ') COMMIT(*NONE) NAMING(*SQL) ERRLVL(30) MARGINS(10000)';
      CALL QSYS2.QCMDEXC(V_RUNSQLSTM_TEXT);   
      FETCH FROM INDEX_SOURCE_CURSOR INTO V_PARTITION_NAME;
    END WHILE;
    CLOSE INDEX_SOURCE_CURSOR;
  END;
END;