> Tech > Les tables temporaires globales : une alternative aux jeux de résultats?

Les tables temporaires globales : une alternative aux jeux de résultats?

Tech - Par iTPro.fr - Publié le 24 juin 2010
email

par Kent Milligan, Mis en ligne le 3/05/2006 - Publié en Décembre 2005

Le langage procédural SQL permet de créer facilement des procédures stockées. Les structures de ce langage permettent aux développeurs SQL de coder des vérifications conditionnelles, d’effectuer un traitement itératif avec diverses structures de boucles, d’appeler d’autres procédures stockées, et de retourner efficacement des jeux de données via des jeux (ou ensembles) de résultats, le tout très simplement. (Si vous n’avez pas encore utilisé des jeux de résultats, considérez que c’est un paramètre de sortie contenant un ensemble de valeurs de données.) Les jeux (ou ensembles) de résultats sont utilisés couramment parce que (1) ils peuvent contenir des données provenant de tables multiples et (2) ils peuvent améliorer la performance en renvoyant un jeu de données sous forme de blocs au lieu d’une ligne à la fois.La figure 1 montre un exemple de procédure stockée nommée Get_ Free_Employees qui renvoie un jeu de résultats contenant les employés non encore affectés à un projet. Un paramètre d’entrée vient cantonner la recherche aux employés libres d’un département donné. L’en-tête de procédure inclut le comptage des jeux de résultats renvoyés par la procédure.
Dans la procédure elle-même, les jeux de résultats sont renvoyés parce que la clause With Return est incluse sur la définition du curseur et que le curseur est laissé ouvert après sortie de la procédure. (Voir l’encadré « PTF pour With Return to Client et With Return to Caller »). Le tableau de la figure 2 présente les données contenues dans le jeu de résultats renvoyé par cette procédure stockée.

Sur DB2 UDB for iSeries, seuls les clients utilisant les interfaces de programmation ODBC, JDBC ou CLI peuvent consommer les (ou accéder aux) jeux de résultats de procédures stockées. (Vous trouverez des exemples de coding montrant ces interfaces consommant des jeux de résultats dans l’IBM Redbook Stored Procedures, Triggers and User Defined Functions on DB2 Universal Database for iSeries.) Dans cette liste, l’absence du langage procédural SQL ne passe pas inaperçue. Si une procédure SQL appelle une autre procédure stockée qui renvoie un jeu de résultats, cette procédure SQL n’a aucun moyen d’accéder au contenu du jeu de résultats renvoyé par la procédure stockée. (Remarque : Embedded SQL est une autre interface classique présentant la même limitation. Les techniques mentionnées dans cet article s’appliquent aussi à cette interface. De plus, cette limitation n’existe que dans DB2 UDB for iSeries. Les autres produits DB2 UDB supportent des instructions SQL supplémentaires pour éliminer cette restriction.)

Les tables temporaires globales (introduites dans la V5R2) sont une fonction SQL qui permet de contourner cette limitation dans le langage procédural SQL. Vous pouvez utiliser une table globale pour contenir des données temporaires pour une connexion ou application base de données. Au lieu d’utiliser une procédure SQL pour renvoyer des données via un jeu de résultats, vous pourriez placer les mêmes données dans une table temporaire globale.
Ensuite, il suffirait que la procédure SQL invoquante connaisse le nom de la table temporaire pour pouvoir accéder aux données du jeu de résultats.
Certains programmeurs (particulièrement les anciens qui ont utilisé par le passé la bibliothèque QTEMP) se demanderont peut-être quelle est la différence entre l’utilisation des tables temporaires globales et la création de leurs propres tables temporaires dans un schéma (ou bibliothèque) spécial. Les tables temporaires globales SQL sont en fait créées dans la bibliothèque QTEMP par DB2 UDB.

Malgré l’utilisation de la bibliothèque QTEMP, SQL demande toujours que le qualificateur, explicite ou implicite, pour le nom de table temporaire soit SESSION. DB2 UDB ne crée pas une bibliothèque ou un schéma appelé SESSION : il utilise simplement le qualificateur SESSION comme une sorte d’alias pour la bibliothèque QTEMP. A l’instar des objets dans QTEMP, DB2 UDB supprime automatiquement la table temporaire à la fin de la connexion.

Les tables temporaires globales : une alternative aux jeux de résultats?

Après avoir vu les similitudes entre l’utilisation des tables temporaires globales et QTEMP, intéressons-nous aux particularités de l’instruction Declare Global Temporary Table.
La clause With Replace sur l’instruction Declare Global procure un avantage unique en permettant à un programmeur de régler facilement le cas où il y a de multiples exécutions de la même procédure dans une connexion (ou job). Si vous utilisez l’approche QTEMP, il vous faudra peut-être écrire quelques lignes de code pour vérifier si un objet DB2 temporaire de même nom a déjà été créé dans la connexion, et réagir en conséquence.

En présence de la clause With Replace, DB2 UDB regarde si la table temporaire globale existe déjà dans la connexion. Si elle a déjà été créée pour cette connexion, toutes les lignes existantes sont supprimées. D’autres différences portent sur les clauses On Commit et On Rollback, qui permettent de garder les données dans la table temporaire synchronisées avec les transactions de la base de données active.

Quand une transaction de base de données est engagée, on peut demander à DB2 UDB de supprimer automatiquement toutes les lignes existantes (c’est-à-dire, utiliser l’action Delete Rows) pour que la table temporaire soit prête pour la transaction suivante.
L’action Preserve Rows sur cette clause vous permet de garder les données existantes à portée de main dans le cas où elles serviraient dans la prochaine transaction. Les mêmes actions peuvent être effectuées par DB2 si une transaction de base de données avorte ou est ramenée en arrière. Les clauses On Commit et On Rollback ne s’appliquent que si un contrôle de commitment ou un niveau d’isolation autre que *NONE est utilisé.

La figure 3 montre un exemple de cette technique de table temporaire globale pour des jeux de résultats, en utilisant la même procédure Get_Free_Employees exposée précédemment. La première différence est que la clause du jeu de résultats est absente de l’en-tête de procédure. La plus grosse différence est que les déclarations du curseur et l’instruction Open ont été remplacées par l’instruction Declare Global Temporary Table. DB2 UDB crée la table temporaire dans Session (c’est-à-dire la bibliothèque QTEMP) en utilisant les valeurs sur la liste Select (par exemple, firstname) pour déterminer (1) combien de colonnes il faut créer et (2) leur type de données.

A l’instar de l’instruction Create Table, l’instruction Declare Global permet de définir individuellement les définitions de colonnes ou de les dupliquer à partir d’une autre table ou vue avec la clause Like. Avec la clause With Data, DB2 UDB exécute la requête définie et place ses résultats dans la table temporaire globale.

Il existe plusieurs versions de cette instruction parce que l’instruction Declare Global Temporary Table ne peut pas contenir des références aux variables d’hôte.

Par conséquent, il fallait coder en dur les valeurs des différents départements dans différentes versions de la même instruction. L’instruction Declare Global Temporary Table peut être préparée dynamiquement, de telle sorte qu’une autre option construirait une version de l’instruction Declare dynamiquement puis utiliserait des instructions SQL Prepare et Execute. Le code de la figure 4 démontre cette approche dynamique, afin que vous puissiez comparer les styles de coding et voir ce qui convient le mieux à vos applications.

Téléchargez cette ressource

Guide inmac wstore pour l’équipement IT de l’entreprise

Guide inmac wstore pour l’équipement IT de l’entreprise

Découvrez toutes nos actualités à travers des interviews, avis d'experts et témoignages clients et ainsi, retrouvez les dernières tendances et solutions IT autour de nos 4 univers produits : Poste de travail, Affichage et collaboration, Impression et capture et Infrastructure.

Tech - Par iTPro.fr - Publié le 24 juin 2010