Accueil du site > Les articles > DB2 SQL, Performances, Optimisations, personnalisations
Version à imprimer Enregistrer au format PDF

DB2 SQL, Performances, Optimisations, personnalisations

le fichier QAQQINI pour gérer les performances SQL

jeudi 2 août 2007, par David Malle Visites  6579

Metez tout en oeuvre pour optimiser vos traitements SQL. Avec le fichier QAQQINI par exemple.


SQL et performances

J’aime bien SQL, c’est vrai. C’est un langage facile à lire (lorsque les requêtes ne sont pas trops alambiquées) et surtout, pour schématiser, il permet souvent d’effectuer dans un seul programme une requêtes sur des données provenant de plusieurs fichiers, avec des traitements spécifiques (cumuls, concaténation, ...), des tris , tout en apportant un supplément de fonctionnalités alors qu’avec une méthode de lecture native, il fallait des fichiers temporaires, des lignes de codes des enchaînements de programmes, bref ...

Un argument récurrent contre l’usage de SQl est le côté performances. Bon, je suis d’accord sur la mise à jour par exemple, il y a des chôses à éviter :

Attention :

- 300 000 update à l’unité dans un batch, vous ne pouvez pas, c’est trop gourmand (update client set libelle = ’toto’ where nocli = :w_nocli, 300 000 fois est à proscrire). Alors qu’un update de masse, c’est performant ( update client set libelle = ’toto’ sur l’ensemble des client, allez y les yeux fermés).
- Ouvrir et fermer un curseur de manière répétée dans un programme, c’est très très gourmand.

Dire que SQL c’est trop lent, on n’en fait pas chez nous, est extrèmement réducteur : il convient plutôt dans ces cas là de se pencher sur l’optimisation de son environnement d’exécution SQL.

Le SQL Statique est plus performant que le dynamique. C’est un rapport entre les fonctionnalités et les performances qu’il faut voir au cas par cas.

A noter aussi que les évolutions côté accès fichiers sont faites par IBM du côté de SQL.

Pour l’optimisation SQL, le fichier QAQQINI !

Le fichier QAQQINI

Le système est livré avec un QAQQINI dans la bibliothèque QSYS. Considérez ce fichier comme un exemple à partir duquel vous pouvez dériver votre ou vos proproes fichiers QAQQINI (il n’est pas interdit d’en avoir plusieurs adapté à différents cas de figure , mais un seul est utilisé lors de l’exécution).

Créer un fichier QAQQINI

Utilisez la commande CRTDUPOBJ pour dupliquer le fichier dans votre bibliothèque. Le nom QAQQINI doit rester le même.

CRTDUPOBJ OBJ(QAQQINI)
         FROMLIB(QSYS)
         OBJTYPE(*FILE)
         TOLIB(LIBQAQQINI)
         DATA(*YES)

En faisant cela, vous aurez des messages dans la log car des triggers (heureusement qu’il y en plusieurs car un trigger on a pas confiance) sont associés au fichier QAQQINI. Il ne s’agit pas d’une erreur provoquée par la commande, mais de messages d’informations.

Ne modifiez pas le QAQQINI de QSYS, travaillez toujours sur des copies.

Mettre à jour le fichier QAQQINI

Une fois votre copie du fichier QAQQINI faite, vous pouvez mettre à jour son contenu via SQL avec les ordres INSERT, DELETE ou UPDATE.

UPDATE LIBQAQQINI/QAQQINI SET QQVAL='*SYSVAL'
WHERE QQPARM='QUERY_TIME_LIMIT'

DELETE FROM LIBQAQQINI/QAQQINI
WHERE QQPARM='QUERY_TIME_LIMIT'

INSERT INTO LIBQAQQINI/QAQQINI
VALUES('QUERY_TIME_LIMIT','*NOMAX','Nouvelle valeur maj par David')

Utiliser le nouveau QAQQINI

Via la commande CHGQRYA, vous pouvez indiquer la bibliothèque LIBQAQQINI (celle de votre nouveau QAQQINI) en regard de l’option QRYOPTLIB. Cela permet d’indiquer où un travail donné ira chercher le QAQQINI. Bien sur, il fautr lancer vos traitements SQL sous ce même travail.

Une autre solution est de simplement mettre en tête de liste la bibliothèque LIBQAQQINI.

Les options du QAQQINI

Il en existe maintenant beaucoup, dont certaines ne sont valables qu’à partir de certaine versions de l’OS. Il faut donc fureter dans les documentation, faire des essais avec vos environnements.

Juste deux exemples pour montrer que quelquefois, il faut se poser des questions :
- CACHE_RESULTS (V5R3 minimum) permet de réutiliser les données stockées en cache pour d’autre requêtes.
- IGNORE_DERIVED_INDEX qui permet de forcer SQL à utiliser SQE et non CTE, lorsque les chemins d’accès rencontrés ne sont pas compatibles (fichier logiques avec select ou omit par exemple), en les ignorant (à tester au cas par cas selon les traitements).

SQE / CQE

CQE (ancien moteur) est toujours utilisé dans les cas suivants :

- l’utilisation de TRANSLATE, UPPER/UCASE, LOWER/LCASE
- tri basé sur STRSEQ(*LANGIDUNQ) ou STRSEQ(*LANGIDSHR)
- l’utilisation de fonctions TABLE (UDTF)
- l’utilisation de logiques dans la clause FROM
- l’utilisation de tables ayant des logiques avec select/omit (sauf si ajout de IGNORE_DERIVED_INDEX dans QAQQINI)

SQE :

- A partir de V5R4, prend en charge l’utilisation de LIKE (pas en V5R30).
- A partir de la V5R4, le système liste les index qui lui paraissent nécessaires dans QSYS2/SYSIXADV, passez vos traitements, puis allez y jeter un coup d’oeil.

Un moniteur de base de données vous permettra de vérifier quels traitements sont pris en charge par SQE ou CTE

Démarrer le moniteur sur un travail

STRDBMON OUTFILE(BIB/FICMON)
        JOB(JOBNUM/USRPRF/JOBNAME)
        TYPE(*DETAIL)          
        INCSYSSQL(*YES)        

Arrêter le moniteur

 
ENDDBMON JOB(JOBNUM/USRPRF/JOBNAME)        

La requête ci-dessous permet d’avoir le nombre de traitement pris en charges par le SQE ou le CTE

SELECT QQC16 AS "N = CQE / Y = SQE",
       COUNT(*)FROM BIB/FICMON
 WHERE QQRID = 3014 GROUP BY QQC16

Pour aller dans le détail, consulter le fichier pour chaque traitement, afin de voir ce qu’il se passe.

Attention

- Lorsque le système aiguille vers le CQE (ancien moteur utilisé par Query ou OPNQRYF), il n’y aura pas de suggestions en ce qui concerne ces requêtes car : le cache des plans d’accès n’est fait que par SQE, le nouveau moteur SQL.

Liens utiles

- SQE, le nouveau moteur SQL
- Quelques options de QAQQINI V5R4
- SQL en V5R4
- Documentation du moniteur de base de données
- Tuning du SQE DB2 i5