SYSTOOLS.HARVEST_INDEX_ADVICE

À quoi ça sert

L'optimiseur Db2 for i note dans les conseils d'index les index qui auraient accéléré des requêtes. Cette procédure en extrait les ordres CREATE INDEX pour le fichier P_FILE de la bibliothèque P_LIBRARY et les place dans le fichier source T_FILE de la bibliothèque T_LIBRARY.

Les seuils définissent les index retenus : P_TIMES_ADVISED (nombre minimum de recommandations), P_MTI_USED (utilisations d'un index temporaire géré par le système) et P_AVERAGE_QUERY_ESTIMATE (estimation moyenne de la requête). On exécute ensuite le fichier avec RUNSQLSTM pour créer les index, comme le suggère l'exemple IBM.

La procédure ne crée rien elle-même : elle prépare un script que l'on relit avant de l'appliquer.

Résultat réel sur un IBM i 7.5

Procédure non exécutée sur notre IBM i de test : elle agit sur la configuration du système ou sur d'autres travaux.

L'exemple d'IBM

Extraire les ordres de création d'index pour le fichier TOYSTORE/SALES, selon des seuils de recommandation, d'utilisation et d'estimation, puis les exécuter avec RUNSQLSTM.

-- Description: Harvest create index statements for file TOYSTORE/SALES 
-- Further, use RUNSQLSTM to create the indexes. 
-- The criteria for index creation is: 
-- 1) The index has been advised at least 100 times AND 
-- 2) A matching MTI has been used at least 50 times AND 
-- 3) The average query estimate is greater than 5 seconds 
 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('QSYS/DLTF FILE(QGPL/INDEXSRC)'); 
 CALL QSYS2.QCMDEXC('QSYS/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 will ignore any failures. 
      SET V_RUNSQLSTM_TEXT = 'QSYS/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;

Paramètres