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;