Le plan d’accès

module d’I/O, l’analyste programmeur devait collecter des informations sur l’utilisation fonctionnelle de ce module d’I/O. Cela s’est fait via une session questions/réponses où l’on trouvait des qestions de ce genre :

• Quelle est l’exigence ? Réponse :
Extraire le nom patronymique de l’employé de la table Employee.
• Où cela se fera-t-il ? Réponse :
Principalement dans des environnements interactifs.
• Avec quelle fréquence ? Réponse :
Aussi souvent que nécessaire.

Muni des réponses à ces questions, l’analyste programmeur a proposé le plan suivant pour mettre en oeuvre le nouveau module d’I/O :

1. Trouver un fichier logique DDS existant indexé sur le numéro d’employé dont le format contient le nom patronymique de l’employé.
2. Si un fichier logique indexé n’existe pas, en créer un nouveau.
3. Coder et compiler.
4. Tester le programme (procéder à de multiples exécutions).

La figure 4 montre le DDS du fichier logique indexé EMPLOYEE que l’analyste programmeur a choisi d’utiliser pour ce module d’I/O. A noter que le fichier logique indexé est un fichier de substitution converti dans le cadre de la méthodologie de conversion DDS en DDL expliquée dans mon article de juillet 2005. C’est pour moi DDS LF phase 1.

La figure 5 est un fragment de code du programme source codé pour accéder au fichier logique indexé et renvoyer le nom patronymique de l’employé. Pendant la compilation du programme, le Record Format Level Identifier du fichier logique indexé EMPLOYEE est enregistré comme partie intégrante de l’objet programme. Pendant l’activation du programme, l’ID format dans le programme est comparée à l’ID format de l’objet base de données. S’ils sont différents, le programme échouera avec une erreur « level check » (vérification de niveau). Il doit alors être recompilé, ou (espérons que non) un override doit être émis pour ignorer les erreurs de vérification de niveau.

Vous pouvez visualiser le Format Level Identifier dans le programme en utilisant la commande DSPPGMREF (Display Program References). La figure 6 montre un exemple de cette action via la fenêtre iSeries Navigator Run SQL Script.
(La commande DSPPGMREF est incluse dans la liste des exemples. Cliquez simplement sur la flèche basse, faites défiler les exemples jusqu’à trouver DSPPGMREF, mettez-le en évidence puis insérez-le dans la fenêtre de script.)

Comme le DDS LF en est à la phase 1, vous pouvez modifier la table de base associée EMP_TABLE, soit en ajoutant de nouvelles colonnes, soit en modifiant les colonnes existantes, sans que l’ID du format d’enregistrement EMPLOYEE ne change. Cela signifie que tous les programmes utilisant ce fichier DDS LF n’ont pas besoin d’être recompilés.

Ils s’exécuteront sans aucune erreur de vérification de niveau.
L’analyste programmeur a testé le programme en l’appelant trois fois avec des numéros d’employés différents. Le programme s’est bien exécuté et chaque exécution a duré environ 1,8 millisecondes (selon la configuration du système iSeries de développement). Cela a paru très acceptable et le programme a été mis en production.

Le plan SQL. A l’autre bout de l’immeuble, une autre équipe de programmeurs était en train de développer de nouvelles applications et elle aussi devait extraire le nom de l’employé de la table EMPLOYEE.

Cependant ce groupe développait des modules d’I/O destinés à être utilisés comme des procédures stockées externes (c’est-à-dire un programme HLL qui est appelé via une instruction SQL CALL et qui renvoie des données sous la forme de jeux de résultats ou de paramètres).

Le programmeur de base de données SQL a aussi créé un plan d’action, semblable à celui créé par l’analyste programmeur RLA avec quelques exceptions. Le plan ressemblait à ceci :

1. Coder et compiler le programme HLL.
2. Examiner le plan d’accès SQL.
3. Créer un index si conseillé par DB2.
4. Tester le programme (multiples exécutions, en examinant le plan d’accès après chaque exécution).
Observons que dans le plan ci-dessus, le programmeur de base de données SQL ne se soucie plus de trouver un index existant (fichier logique indexé). Cela incombe à l’optimiseur DB2. Toutefois, le programmeur de base de données DB2 a besoin d’examiner les choix effectués par l’optimiseur et de réagir aux éventuelles suggestions de DB2 (étapes 2 et 3).

La figure 7 est un fragment de code d’un programme RPG utilisant SQL imbriqué. Les programmes compilés en utilisant SQL imbriqué ne contiennent pas d’ID de format d’enregistrement. Pendant l’exécution, le plan associé à l’instruction SQL imbriqué est validé par rapport à la base de données. Si le plan est encore valide (même dans le cas où la base de données aurait été altérée), le programme s’exécutera. Autrement dit, il n’y a pas d’erreur de vérification de niveau.

Un DSPPGMREF d’un objet programme qui contient SQL imbriqué montrera l’objet base de données référencé dans l’instruction SQL. Toutefois, il n’y a pas d’ID format associée, comme le montre la figure 8.
Quand un programme contenant SQL imbriqué s t a t i q u e ( p a r opposition à SQL imbriqué dynamique) est compilé, un plan incomplet est généré et stocké avec l’objet programme. Le plan ne sera complètement optimisé qu’à la prochaine exécution du programme. Il y a une raison à cela : l’environnement dans lequel un programme est compilé n’est pas forcément le même que celui dans lequel il sera exécuté (par exemple, interactif dans un cas et batch dans l’autre).

Vous pouvez utiliser la fonction SQL iSeries Navigator Explain ou la commande i5/OS PRTSQLINF (Print SQL Information) pour afficher le plan d’accès SQL généré pour un programme contenant des instructions SQL imbriquées. La figure 9 montre le plan d’accès SQL généré pour le programme SQL RPG immédiatement après la compilation.

Le panneau Explain contient les paramètres du compilateur utilisé pendant la phase de précompilation du programme RPG. Plusieurs de ces paramètres affectent directement la performance et devraient être inclus dans l’examen du coding. Ce sont ALWCPYDTA, CLOSQLCSR et ALWBLK. Je reviendrai plus en détail sur l’option CLOSQLCSR dans un moment.

Chaque instruction SQL présente dans le programme est ensuite présentée avec son plan d’accès associé. Dans la figure 9, il n’existe pas de plan pour l’instruction SQL. Le plan ne sera généré qu’à la première exécution du programme RPG. Le tableau de la figure 10 compare les plans après la première exécution du programme RPG.

Le côté droit du tableau contient maintenant l’information de plan optimisée pour l’instruction SQL imbriquée dans le programme RPG. L’information sur le plan est stockée sous la forme de messages de texte qui expliquent l’environnement dans lequel le programme a été exécuté (par exemple, DB2 SMP est actif, le fichier QAQQINI est utilisé) et les méthodes d’accès aux données utilisées pour produire le jeu de résultats.

Explain optimisé nous montre que DB2 a examiné tous les index appropriés pour le fichier physique EMP_TABLE (message SQL4006), a choisi un index avec un nom système de EMP_T00001 (message SQL4008) et utilisera cet index pour effectuer le positionnement des lignes clés (aussi appelé sonde d’index) pour accéder à la base de données.

Le programmeur de base de données a testé le programme SQL RPG en l‘exécutant trois fois et il a constaté que la troisième exécution était plus rapide que les deux premières. Le programmeur SQL a appelé son copain RPG et l’a invité à comparer les résultats de leurs tests respectifs.

On voit la comparaison dans le graphique de la figure 11. En essence, le programme RPG RLA s’est régulièrement exécuté en moins de 2 ms. La première exécution du programme RPG SQL a duré près de dix fois plus que le RPG RLA. En revanche, la troisième exécution du programme RPG SQL a été inférieure à la moitié du temps du programme RPG RLA (0,8 ms contre 1,8 ms). Les deux programmes utilisaient l’indicateur Last Record (*INLR). Que se passe-t-il ? La réponse est fournie par l’ODP réutilisable.