Utiliser les plans d’accès de tables temporaires

Le cache de plan SQE (SQE Query Engine) résout un important problème de gestion des plans d’accès, en utilisant la version résolue des instructions SQL lors de la construction des entrées cache de plan. Cette approche a donné de bons résultats pour DB2 – sauf dans le cas de requêtes référençant des tables temporaires. Vous créez des tables temporaires soit en utilisant l’instruction Declare Global Temporary Table, soit en spécifiant QTEMP comme le schéma (bibliothèque) sur l’instruction Create Table. La difficulté avec une table temporaire est que la version résolue de toute instruction SQL qui pointe vers elle doit être partagée par différentes instances physiques de la table temporaire.

Pour mieux comprendre cela, prenons un exemple simple. Une application démarre une connexion de base de données, et la première opération est de créer une table temporaire à utiliser comme table de travail pendant la connexion, en utilisant l’instruction SQL suivante :

CREATE TABLE qtemp.temptab1 (workcol1 INT, workcol2 INT)

L’application appelle ensuite divers programmes qui peuplent la table temporaire avec des données. Une fois la table temporaire peuplée, la requête suivante est périodiquement appliquée à la table temporaire :

SELECT workcol1, AVG(workcol2) FROM qtemp. temptab1 GROUP BY workcol1 ORDER BY workcol1

Ce texte d’instruction exact est utilisé quand l’optimiseur construit l’entrée cache de plan pour cette requête.
Le problème réside dans le fait que chaque connexion ou job base de données a son propre schéma QTEMP unique. Quand un autre utilisateur appelle la même application, l’optimiseur doit reconstruire tous les plans dans le cache qui font référence à la table temporaire temptab1, parce que les instructions SQL utilisent une instance physique différente de la table temporaire. Les plans d’accès que l’optimiseur de requêtes construit nécessitent des liens directs avec les objets table physiques que l’instruction SQL traitera.

Par conséquent, si une application utilise intensivement une table temporaire, et si vous êtes soucieux de performances, vous devez changer l’application de manière à utiliser une table temporaire partagée au travers de connexions. Au lieu de voir l’application créer les tables temporaires dans QTEMP, toutes les tables temporaires seront créées dans un schéma permanent – probablement pendant l’installation de l’application. Pour partager une table temporaire sur des connexions ou jobs, vous devez ajouter une autre colonne à chaque table temporaire afin qu’une connexion puisse facilement identifier les lignes qui appartiennent à cette connexion. Par exemple, la définition de table temptab1 serait changée en

CREATE TABLE tmpschema.temptab1
(ConnID INTEGER, workcol1 INTEGER, workcol2 INTEGER) VOLATILE

A noter qu’une colonne entier (integer) simple (ConnID) est utilisée comme clé pour identifier les lignes qui appartiennent à une connexion ou job spécifique. Une colonne caractère peut stocker le nom de job qualifié i5/OS dans une colonne, mais cela obligerait l’application à ajouter du code chargé d’extraire le nom de job qualifié i5/OS. Un objet séquence DB2 offre une méthode plus simple pour que l’application génère une valeur entière unique que vous pourrez utiliser pour identifier les données associées à une connexion. Vous créeriez probablement la séquence en même temps que toutes les tables temporaires – voici la définition d’objet pour cet exemple :

CREATE SEQUENCE ConnectionID START WITH 1 INCREMENT BY 1 CYCLE CACHE 10

Désormais, au lieu que l’application crée une table temporaire après avoir établi une connexion base de données, l’application extrait une valeur générée à partir de la séquence puis utilise cette valeur pour marquer toutes les lignes qu’elle stocke dans des tables temporaires. Une application pourrait facilement utiliser l’instruction Select suivante pour obtenir la prochaine valeur à partir de l’objet sequence :

SELECT NEXT VALUE FOR ConnectionID FROM sysibm.sysdummy1

Une fois la valeur extraite, l’application est stockée dans une variable (par exemple CIDVar) utilisée par tous les programmes qui peuplent et extraient des valeurs de la table temporaire. Voici un exemple d’insertion et d’extraction de données en utilisant cette variable :

INSERT TO tmpschema.temptab1 VALUES(:CIDVar, 10, 1000)
SELECT workcol1, AVG (workcol2) FROM tmpschema.temptab1
WHERE ConnID = :CIDVar GROUP BY workcol1 ORDER BY workcol1;

Cette façon de faire réduit les reconstructions de plans d’accès parce qu’elle permet à chaque instance d’application de faire référence à la même instance physique de la table « temporaire ». Le mot-clé VOLATILE (nouveau en V5R4) a été utilisé dans la nouvelle définition de table pour temptab1. Ce mot-clé réduit le nombre de reconstructions de plans d’accès en indiquant à l’optimiseur de requêtes que le contenu de la table est volatil ; la volatilité de la table indique que le nombre de ligne varie constamment. Lors de la construction d’un plan qui implique des tables temporaires, l’optimiseur de requêtes génère un plan d’accès qui délivre une performance homogène au lieu d’un plan performant pour un certain nombre de lignes. Dans cet exemple, les tables seront vides quand l’application ne sera pas exécutée et auront beaucoup de lignes s’il y a des centaines d’utilisateurs de l’application. Par conséquent, le fait de marquer la table comme volatile devrait aider l’optimiseur à construire des plans performants. La création d’un index sur la colonne ConnID dans temptab1 permettra aussi à l’optimiseur de requêtes de générer plus facilement un bon plan d’accès.

Enfin, l’application devrait être modifiée pour supprimer les lignes qu’elle a générées dans les tables temporaires simulées avant de terminer la connexion à la base de données. Vous pouvez accomplir ce changement avec DELETE FROM tmpschema.temptab1 WHERE ConnID = CIDVar