, , Statistiques sur les MTIs

A partir de la TR6 de la V7R4, vous avez une nouvelle vue qui vous permet de visualiser vos MTIs (Maintained Temporary Indexes) c’est des indexs que le système décide de construire temporairement pour optimiser vos requêtes.

C’est ceux la même qui sont perdus à chaque IPL …

Cette nouvelle vue s’appelle MTI_INFO elle est dans QSYS2

plus d’informations ici

https://www.ibm.com/docs/en/i/7.5?topic=services-mti-info-table-function

Rappelle :
Vous pouviez déjà avoir des informations sur les MTIs en interrogeant index advisor, par exemple dans ACS vous avez cet exemple qui vous indique les MTI utilisés depuis le dernière IPL

CONDIDXA étant la vue aggrégée de QSYS2.SYSIXADV

Avec la TR6 de la V7R4 (TR disponible à partir du 24 mai 2022)


Vous pouvez désormais compresser en standard avec l’algorithme *ZLIB dans les commandes SAV*


Bien que un peu vieux, (la première version date de 1995) c’est un algo très fiable et efficace


Pour en savoir plus sur ZLIB https://fr.wikipedia.org/wiki/Zlib

Remarque :

Jusque la on pouvait l’installer en open-source

Envoyer un message avec réponse

Il peut être intéressant d’envoyer un message avec une réponse, pour des traitements occasionnels par exemple .

Voici 2 exemples de code qui font la même chose

exemple en RPG

**free
// Message simple avec réponse
// texte 55 max
dcl-s w_reponse char(1) ;
dsply ‘Voulez-vous continuer ?’ ‘QSYSOPR’ w_reponse;
// traitement de la réponse
if w_reponse = ‘O’;
dsply ‘Oui avec plaisir !’ ‘QSYSOPR’;
else ;
dsply ‘Certainement pas !’ ‘QSYSOPR’ ;
endif ;
*inlr = *on ;

exemple en répondant O

Exemple en CLP

pgm
/* Message simple avec réponse / / */
dcl &w_reponse *char 10
SNDUSRMSG MSG(‘Voulez-vous continuer ?’) +
TOMSGQ(QSYSOPR) MSGRPY(&W_REPONSE)
if cond(&w_reponse = ‘O’) then(do)
SNDUSRMSG MSG(‘Oui avec plaisir !’) msgtype(info) TOMSGQ(QSYSOPR)

enddo

else do

SNDUSRMSG MSG(‘Certainement pas !’) msgtype(info) TOMSGQ(QSYSOPR)
enddo
endpgm

exemple en répondant O

Rappel

C’est n’est pas la meilleur manière pour traiter de l’information, mais ca peut rendre service

, , Droits Navigator for i

il faudra désormais une autorisation explicite pour utiliser Navigator for i

Jusqu’à présent les fonctions usage, QIBM_NAV_SERVICEABILITY et QIBM_NAV_ALL_FUNCTION étaient autorisées par défaut à partir de mai 2022, elles seront interdites par défaut seules les profils ayant les autorisations spéciales *secofr *allobj pourront accéder les autres devront être autorisées nommément.

Plus d’informations ici

https://www.ibm.com/support/pages/node/6485853

https://www.ibm.com/support/pages/node/6486565

https://www.ibm.com/support/pages/node/6520030#Feb2022

C’est une possibilité de contrôler les requêtes SQL sur le temps d’exécution estimée ou sur la mémoire temporaire , se base sur le plan d’accès créé par l’optimiseur pour votre requête.

C’est la commande CHGQRYA qui permet cette opération
paramètres QRYTIMLMT( ) et QRYSTGLMT( ) pour le temps et la mémoire
Exemple
CHGQRYA JOB(123456/PLB/QPADEV0001) QRYTIMLMT(45)

Vous exécutez votre requête, si elle dépasse 45 secondes un message CPA4259 qui nécessite une réponse est envoyé au travail
Si vous répondez I ca continue,

 La requête excède la limite de durée ou de mémoire définie (C I)

? I

C si vous voulez arrêter la requête

La requête dépasse la limite de durée ou de mémoire définie.
? C
SQL query exceeds specified limit or threshold.

Vous pouvez estimer le temps d’exécution d’une requête

Mettre le temps maxi à zéro
CHGQRYA QRYTIMLMT(0)
Lancer la requêtes
Retrouvez le message CPA4259 et répondez C
Si vous faites sur le message vous avez le temps d’exécution estimée
ID message . . . . . . : CPA4259
Date d’envoi . . . . . : 26/04/22 Heure d’envoi . . . . : 14:23:33

Message . . . . : La requête excède la limite de durée ou de mémoire définie
(C I)

Cause . . . . . : La requête de base de données qui allait démarrer a une
durée d’exécution estimée à 12, ce qui excède la limite indiquée 0, ou son
utilisation de mémoire temporaire de 1 dépasse la limite spécifiée

  1. Les limites de durée de requête et de mémoire temporaire sont
    définies via la commande CL CHGQRYA.

remettre la valeur par défaut
CHGQRYA QRYTIMLMT(*SYSVAL)

remarque

Attention si vous avez de requêtes sous forme de curseur dans un sqlrpgle par exemple

Le traitement ne s’arrête pas , mais votre open cursor va recevoir un SQLCODE -666, si vous avez des traitements dépendants ca peut être gênant. il est donc conseillé de rajouter le contrôle derrière cette opération

exemple

if sqlcode = -666 ;
dsply ‘Estimation requête excessive’ ;
*inlr = *on ;
return;
Endif;

Pour mettre une réponse automatique C


Utiliser la table de réponse de travaux

par exemple
ADDRPYLE SEQNBR(56) MSGID(CPA4259) CMPDTA(PLB 51) RPY(C)
puis indiquer que le travail est en réponse auto
CHGJOB INQMSGRPY(*SYSRPYL)

Vous pouvez également indiquer quel fichier qaqqini vous voulez utiliser

paramètre CHGQRYA QRYOPTLIB(PLB)

Vous devez indiquer la bibliothèque ou ce trouve votre fichier d’options

plus d’informations ici

https://www.ibm.com/docs/fr/i/7.5?topic=supervisor-query-example-exit-programs


https://www.ibm.com/docs/en/i/7.4?topic=governor-how-use

, , Transformation LF en index

Pourquoi transformer des DDS en SQL?

Une des raisons est que les index peuvent être beaucoup plus performants que les LF quand le moteur SQL les utilise.

Rappel
Pour transformer un PF en table

Vous devez extraire le source en utilisant l’API QSQGNDDL
Le plus simple est de passer par ACS
En faisant génération instruction SQL

Vous obtenez le source SQL qu’il aurait fallu pour générer cette table.


Attention tout n’est pas traduit (exemple un PF sans clé unique)

Pour transformer un LF en index sur nom par exemple

Si vous utilisez ACS, votre index est considéré comme une vue et si vous faites l’opération de génération SQL vous obtenez un source qui ne va pas vous servir à grand chose.
Remarque, par contre si vous regardez par Visual Explain vous voyez bien que le moteur utilise le PF comme un index.

Si vous utilisez la procédure de QSYS2.GENERATE_SQL, même problème.

.

Si vous lui indiquez index, il ne trouve pas l’objet SQL

CALL QSYS2.GENERATE_SQL(‘AALF’, ‘GDATA’, ‘INDEX’,
‘QSQLSRC’, ‘GDATA’, ‘AALF’,
CREATE_OR_REPLACE_OPTION => ‘1’,
CONSTRAINT_OPTION => ‘2’);

La solution est donc passer directement par l’API système QSQGNDDL.

Pour vous aider, on a fait une commande RTVSQLSRC que vous pouvez trouver ici

https://github.com/Plberthoin/PLB/tree/master/GOUTILS

et là vous pouvez forcer le type INDEX

RTVSQLSRC FILE(GDATA/AALF) SRCFILE(GDATA/QSQLSRC) TYPSQL(INDEX)

et là vous obtenez le source qui va bien

Même remarque que pour les PF (tout ne se traduit pas exemple LF avec sélection)

Une fois que vous avez le source il vous suffit alors de rejouer le script SQL.

Remarque :

Vous ne pouvez pas faire un create or replace , puisque SQL continue à voir le LF comme une vue.
Vous devez donc le supprimer avant le recréer.

Compléments apportés par Birgitta merci à elle

Il y a 2 options en GENERATE_SQL et le ACS wizard avec lesquelles on peut transformer LFs en index.

https://www.ibm.com/docs/en/i/7.4?topic=services-generate-sql-procedure


Out of the GENERATE_SQL documentation:
INDEX_INSTEAD_OF_VIEW – option:
The index instead of view option specifies whether a CREATE INDEX or CREATE VIEW statement will be generated for a DDS-created keyed logical file. The valid values are:

0 – A CREATE VIEW statement will be generated.
1 – A CREATE INDEX statement will be generated that matches the index for a DDS-created keyed logical file.

ADDITONAL_INDEX_OPTION:
The additional index option specifies whether additional CREATE INDEX statements will be generated for DDS-created keyed physical or logical files. The valid values are:

0 – Additional CREATE INDEX statements will not be generated.
1 – An additional CREATE INDEX statement will be generated that matches the index for a DDS-created keyed physical file. If the physical file has a PRIMARY KEY constraint, a CREATE INDEX statement is not generated.
An additional CREATE INDEX statement will be generated that matches the index for a DDS-created keyed logical file. If a value of ‘1’ is specified for the index instead of view option, an additional CREATE INDEX statement is not generated. Additional CREATE INDEX statements will also be generated that match the join indexes of a DDS-created join logical file.

, Les jobs database QDBSVR*

Sur votre machine il y a un certain nombre de jobs systèmes qui tournent pour gérer votre base de données, voici les principaux et ce à quoi ils servent :

QDBSRVXR


Gère les références du catalogue principalement QADBXREF sauf des zones qui sont dans QADBIFLD

QDBSRVXR2


C’est lui qui gère les références de zones dans QADBIFLD

QDBSRV01


C’est le répartiteur de taches de maintenance DB, il aiguille vers les autres jobs de maintenance

QDBSRV02, QDBSRV03


C’est la maintenance des chemins d’accès sur les fichiers systèmes

QDBSRV04, QDBSRV05


Ces Jobs font la maintenance des chemins d’accès sur les fichiers base de données utilisateur

Vous en aurez un par processeurs supplémentaires

QDBSRV06-QDBSRV07 processeur suppélentaire 1
QDBSRV08-QDBSRV09 processeur suppélentaire 2
Etc …

Vous pouvez les voir par exemple en utilisant la commande WRKACTJOB et en demandant les travaux systèmes.

WRKACTJOB JOB(*SYS)

Mais vous pouvez également les suivre par SQL grâce à la fonction table ACTIVE_JOB_INFO

SELECT x.JOB_NAME_SHORT , x.JOB_STATUS, x.TEMPORARY_STORAGE, x.CPU_TIME, x.TOTAL_DISK_IO_COUNT, x.ELAPSED_CPU_TIME, x.ELAPSED_PAGE_FAULT_COUNT
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
JOB_NAME_FILTER => ‘QDBSRV*’)) x
ORDER BY x.JOB_NAME_SHORT

Remarque

il est difficile de les régler , une mauvaise performance de ces jobs traduit souvent un manque de puissance machine

, Suppression des conseils d’index dans la table SYSIXADV

La table QSYS2.SYSIXADV implémente les conseils de création d’index. Ceci à chaque fois qu’une requête exécutée nécessiterait la création d’un index pour être plus performante. Il est utile de consulter cette table régulièrement afin de créer les index conseillés parmi les plus consommateurs et/ou les plus sollicités.

Il existe la procédure RESET_TABLE_INDEX_STATISTICS (https://www.ibm.com/docs/en/i/7.4?topic=services-reset-table-index-statistics-procedure) dont le premier rôle est de remettre à zéro les statistiques sur ces index. Une option permet la suppression des suggestions à condition que l’index ait été créé entretemps.

Pour réaliser la suppression de ces suggestions, on peut alors se créer une procédure SQL pour réaliser simplement ce ménage de façon régulière une fois que les conseils ont été suivis :
CREATE OR REPLACE PROCEDURE GREFER.DLTADVIDX (
IN LIB CHARACTER(10),
IN FIC CHARACTER(10),
IN JOURS INTEGER)

LANGUAGE SQL
MODIFIES SQL DATA
PROGRAM TYPE MAIN
CONCURRENT ACCESS RESOLUTION DEFAULT
DYNAMIC RESULT SETS 0
OLD SAVEPOINT LEVEL
COMMIT ON RETURN NO
SET OPTION DBGVIEW = *SOURCE
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE RETCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’;
DECLARE RETSTATE CHAR(5) DEFAULT ‘00000’;
DECLARE COMPTE INTEGER;
DECLARE RFIC CHAR(10);
DECLARE OTYPE CHAR(10);
DECLARE MSG CHAR(256);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, NOT FOUND SET RETCODE = SQLCODE;
SET RETSTATE = SQLSTATE;
SELECT X.OBJATTRIBUTE INTO OTYPE FROM TABLE (QSYS2.OBJECT_STATISTICS(‘QSYS’, ‘LIB’, LIB) ) AS X ;
IF RETCODE = 0 THEN If UPPER(FIC) = ‘*ALL’ Then
Set RFIC = ‘ ‘;
End If;
If LOCATE(‘*’, FIC) > 1 Then Set RFIC = SUBSTR(FIC, 1, LOCATE(‘*’, FIC) -1);
End If;
If RFIC is not null then
SELECT COUNT(*) INTO COMPTE FROM QSYS2.SYSIXADV WHERE TABLE_SCHEMA=UPPER(LIB) AND TABLE_NAME LIKE UPPER(trim(RFIC))||’%’ AND LAST_ADVISED<(NOW()-JOURS DAYS); Else SELECT COUNT(*) INTO COMPTE FROM QSYS2.SYSIXADV WHERE TABLE_SCHEMA=UPPER(LIB) AND TABLE_NAME = UPPER(FIC) AND LAST_ADVISED < (NOW() - JOURS DAYS); End If; IF RETCODE <> 0 THEN
Set MSG = ‘Anomalie de traitement’;
Return;
END IF;
CASE COMPTE
WHEN 0 THEN
Set MSG = ‘Rien a supprimer’;
Return;
WHEN 1 THEN
Set MSG = TRIM(CHAR(COMPTE)) CONCAT ‘ information supprimee’;
ELSE
SET MSG = TRIM(CHAR(COMPTE)) CONCAT ‘ informations supprimees’;
END CASE;
If RFIC is not null then
DELETE FROM QSYS2.SYSIXADV WHERE TABLE_SCHEMA = UPPER(LIB)
AND TABLE_NAME LIKE UPPER(trim(RFIC))||’%’
AND LAST_ADVISED<(NOW()-JOURS DAYS); Else DELETE FROM QSYS2.SYSIXADV WHERE TABLE_SCHEMA = UPPER(LIB) AND TABLE_NAME = UPPER(FIC) AND LAST_ADVISED<(NOW()-JOURS DAYS); End If; IF RETCODE <> 0 THEN
Set MSG = ‘Anomalie de traitement’;
END IF;
Call systools.lprintf(msg);
ELSE
Set MSG = ‘Bibliothèque inexistante’;
Call systools.lprintf(msg);
END IF;
END;

LABEL ON ROUTINE GREFER.DLTADVIDX(CHAR(), CHAR(), INT)
IS ‘INIT STATS INDEX. (DELETE STATS)’;
COMMENT ON PARAMETER ROUTINE GREFER.DLTADVIDX (CHAR(), CHAR(), INT)
(LIB IS ‘BIBLIOTHÈQUE DU FICHIER’, FIC IS ‘INDEX’, JOURS
IS ‘NOMBRE DE JOURS DERNIÈRE INFORMATION’);

Cette fonction admet trois paramètres, Bibliothèque, Fichier et Nombre de jour de conservation de la suggestion.

Exemples d’utilisation :

Call Grefer.DLTADVIDX(‘MYLIB’, ‘*ALL’, 1); supprimera tous les conseils de plus d’un jour pour tous les fichiers de la bibliothèque MYLIB

Call Grefer.DLTADVIDX(‘MYLIB’, ‘MYF*’, 7); supprimera tous les conseils de plus de 7 jours pour tous les fichiers de la bibliothèque MYLIB dont le nom commence par MYF

Call Grefer.DLTADVIDX(‘MYLIB’, ‘MYFILE’, 28); supprimera tous les conseils de plus de 28 jours pour le fichier MYFILE de la bibliothèque MYLIB

Cela permet de nettoyer ce cumul de conseils sur certaines bibliothèques, fichiers avant d’évaluer les réels besoins de la base de données en se basant sur les ESTIMATED_CREATION_TIME les plus gourmandes et/ou les TIMES_ADVISED les plus nombreuses.

, , Suggestion d’Index agrégés

Vous connaissez index advisor, c’est une table que le système met à jour à chaque suggestion d’index, elle se nomme SYSIXADV et elle est dans QSYS2.

Vous pouvez l’interroger par SQL en faisant un simple select et en appliquant un filtre par rapport à une date de dernière utilisation et soit un nombre de fois recommandés ou un temps de reconstruction.

Exemple :

Depuis 1 mois et plus de 1000 fois

select * from qsys2.SYSIXADV where

LAST_ADVISED > current date – 1 month and times_advised > 1000

Depuis 1 jour et temps de reconstruction > 100 pour la bibliothèque GREFER

select * from qsys2.SYSIXADV where

TABLE_schema = ‘GREFER’ and LAST_ADVISED > current date – 1 days and ESTIMATED_CREATION_TIME > 100

Index agrégé

Il existe une vue sur cette table pour les index agrégés (clés composées) son nom est CONDENSEDINDEXADVICE et son nom court CONDIDXA.

Elle est souvent utilisée dans les outils comme gestion des schémas ou Visual Explain par exemple.

Mais vous pouvez l’utiliser directement dans vos requêtes comme SYSIXADV

Exemple :

Les index suggérés sur la bibliothèque GREFER

select * from qsys2.condidxa where TABLE_schema = ‘GREFER’

Ça vous évitera de créer des index dont les zones sont utilisées dans les autres.

Exemple

Pour la table LSTOBJ

Sur la table SYSIXADV :

Sur la vue CONDIDXA :

dans cet exemple à base d’un dspobjd on voit bien qu’un seul index est suffisant alors qu’ Advisor en suggère 3 de base.

rappel

l’index est un des principaux facteurs de performances de votre base de données , il faut surveiller ce que dit index advisor

, Réorganiser un très gros fichier

Voici la problématique qui nous est arrivée :

Suite à un gros coup de ménage sur une petite machine saturée avec un énorme fichier de 12 millions d’enregistrement plus 30 millions d’enregistrements supprimés et 4 index dessus

Le réflexe naturel, a été le bon vieux RGZPFM qui devait nous réorganiser tout ça … eh bien non. On n’a jamais réussi à le lancer jusqu’au bout.

Voici comment nous avons fait pour nous en sortir. Nous avons utilisé une des possibilités du CPYF qui élimine les enregistrements supprimés.

En gros voici les opérations à réaliser


1) Copie vers un fichier de sauvegarde de votre fichier
2) Suppression des index pour éviter de saturer le système en reconstruisant tous les index en même temps
3) Mise à blanc du fichier initial
4) Recopie des données depuis votre sauvegarde
5) Remise des index initiaux

Vous pouvez retrouver un exemple de source qui fait ça ici https://github.com/Plberthoin/PLB/tree/master/GTOOLS

Remarque :


Dans cet outil l’astuce utilisée pour la suppression des index ne marche que sur les LF et pas sur les index (le plus simple dans ce cas la est d’extraire le source , droper l’index et rejouer votre script SQL)
On doit pouvoir également faire une copie par insert into … select
Il existe certainement d’autres alternatives …