Cet article explique comment développer et déployer des procédures stockées sur un système de bases de données DB2 (AS/400 dans mes tests, mais l’OS a peu d’importance). Il s’agit d’expliquer comment rapidement mettre en place un environnement de développement et démarrer dans les procédures stockées, il ne s’agit pas d’un article de fond sur les procédures stockées ou sur DB2.
Pour les besoins de ce tutoriel, nous travaillerons sur une bibliothèque nommée TSTLIB contenant une table ACCOUNTS contenant des données de comptes bancaires, possédant ces champs : REF, DESC, INIT_VALUE et DATE sur un serveur ayant l’IP 192.168.1.1.
Introduction
Les procédures stockées sont des routines compilées au sein même du dictionnaire de la base de données. Sans rentrer dans les détails, les procédures stockées permettent d’accroitre les performances, de réduire le traffic réseau, de centraliser la logique de données permettant ainsi une meilleure maintenance du code en offrant une collection de procédures aux développeurs directement dans le système de bases de données. Ainsi, ceux-ci ne sont plus attachés à cette tâche qui est déléguée aux développeurs data/DBAs (ce qui est plus logique).
Avant de pouvoir écrire des procédures stockées, il faut disposer d’un environnement de développement minimum, celles-ci devant être écrites, puis déployées afin d’être exécutées sur le serveur. Pour ce faire, nous allons utiliser l’outil gratuit d’IBM basé sur Eclipse IBM Data Studio disponible pour Windows et GNU/Linux depuis le site DeveloperWorks d’IBM (un compte gratuit est nécessaire pour télécharger le produit).
Mise en place de l’environnement
L’installation est simple et n’a pas vraiment besoin d’etre expliquée ici. A la fin de l’installation, après le démarrage de l’application, il faut ajouter une connection à une bibliothèque DB2, pour ce faire suivre ces étapes :
- Depuis le menu File, créer un nouveau Data Development Project
- Donner un nom au projet, par exemple mysp
- Dans la fenetre Select connection, cliquer sur le bouton Add
- Dans la nouvelle sous-fenêtre :
- DB2 for iOS,
- AS/400 Toolbox for Java comme JDBC driver,
- Properties :
- Host : 192.168.1.1 (l’IP du serveur DB2)
- User name : un nom d’utilisateur valide
- Password : le mot de passe pour l’utilisateur
- Default schema : TSTLIB (le nom de la biliothèque sur laquelle on va travailler)
- Connection URL : jdbc:as400:192.168.1.1;prompt=false (devrait être pré-rempli)
- Next et Finish
L’environnement est prêt pour notre nouveau projet.
Création d’une Procédure Stockée simple
Notre nouvel environnement de développement est maintenant connecté à DB2, il est temps de travailler! Comme exemple, nous allons créer une routine toute simple qui retourne la valeur du champ INIT_VALUE d’un compte ayant la référence REF. Une procédure stockée accepte trois types de paramètres : entrée IN, sortie OUT et les deux INOUT. Dans notre exemple, la valeur de retour pour INIT_VALUE sera un paramètre OUT alors que REF sera IN.
La création d’une procédure stockée se fait depuis le panneau Data Project Explorer par un clic-droit sur Stored Procedures → New → Stored Procedure :
- Donner un nom à la procédure, par exemple FIRST_SP
- Un template peut-être utilisé, nous allons simplement choisir Custom
- Finish
L’éditeur d’IBM Data Studio contient maintenant le squelette de notre procédure. Nous n’avons pas besoin de ce qu’il y a entre BEGIN et END, le code devrait ressembler à ceci :
CREATE PROCEDURE FIRST_SP () (1)
LANGUAGE SQL (2)
P1: BEGIN (3)
END P1
La structure d’une procédure stockée est constituée de 3 parties :
- (1) Un nom de procédure
- (2) Quelques paramètres et propriétés
- (3) Le corps de la routine (où la logique SQL réside)
Les paramètres IN, OUT ou INOUT sont entre les parenthèses de CREATE PROCEDURE. Ainsi, dans notre case, nous avons besoin de la référence du compte the_ref en parametre IN de type VARCHAR d’une longueur de 6, le résultat sera une valeur init_value, donc OUT, de type DECIMAL (longueur de 14 avec une précision de 2). La déclaration de la procédure devrait ressembler à ceci :
CREATE PROCEDURE FIRST_SP (
IN ref VARCHAR(6),
OUT init_value DECIMAL(14,2)
)
LANGUAGE SQL
P1: BEGIN
END P1
La partie propriétés sera laissée telle quelle avec seulement LANGUAGE SQL, nous pouvons ainsi nous concentrer sur le corps de la routine. Pour notre exemple, il s’agit d’une simple requête SELECT :
SELECT init_value FROM TESTLIB.ACCOUNTS WHERE ref = 'reference';
Voyons ce qui est nécessaire pour transformer cette requête en routine. Une variable interne the_value servira à recevoir la valeur retournée par la requête, il nous faudra également déclarer un curseur. Un curseur est un pointeur vers un enregistrement lors d’une requête de type SELECT. Lorsqu’une requête SQL retourne un jeu de résultat (ayant une ou plusieurs lignes), le curseur se place sur le premier pour pouvoir ainsi récupérer les valeurs des champs, s’il y a plus d’une ligne, il faut faire avancer curseur pour continuer à lire les enregistrements. Le fonctionnement d’un curseur est un vaste sujet. Comme notre procédure est un SELECT, nous avons besoin d’un curseur que l’on va appeler c_init.
Un exemple concret et complet valant mieux que des discours théoriques, voici la procédure complète annotée :
CREATE PROCEDURE FIRST_SP (
IN the_ref VARCHAR(6),
OUT init_value DECIMAL(14,2)
)
LANGUAGE SQL
P1: BEGIN
-- Déclaration des variables
DECLARE the_value DECIMAL(14,2); (1)
-- Déclaration du curseur
DECLARE c_init CURSOR FOR (2)
SELECT INIT_VALUE (3)
FROM TESTLIB.ACCOUNTS
WHERE REF = the_ref;
-- Ouverture du curseur
OPEN c_init; (4)
-- Collecte de la valeur
FETCH FROM c_init INTO the_value; (5)
-- Placer la valeur dans la variable OUT
SET init_value = the_value; (6)
-- Fermeture du curseur
CLOSE c_init; (7)
END P1
- (1) Déclaration de la variable init_value de type DECIMAL(14,2)
- (2) Déclaration du curseur c_init pour la requete SQL SELECT (3)
- (4) Le curseur est ouvert
- (5) L’unique colonne INIT_VALUE de la ligne est retournée dans la variable the_value
- (6) La variable the_value est placée dans la variable init_value (parametre OUT)
- (7) Le curseur est fermé
La procédure étant écrite, elle peut maintenant etre déployée pour être exécutée.
Déploiement et exécution d’une Procédure Stockée
Depuis Data Project Explorer de IBM Data Studio, faire un clic-droit sur notre procédure FIRST_SP, puis Deploy :
Cliquer sur Finish, le processus de déploiement démarre et son déroulement peut etre surveillé dans le panneau SQL Results en bas de la fenêtre d’IBM Data Studio :
Le status nous informe que le déploiement s’est bien déroulé, il est temps de tester notre routine. Toujours depuis Data Project Explorer, faire un clic-droit sur FIRST_SP, puis Run :
Une fenêtre s’ouvre demandant de renseigner le paramètre IN requis, il s’agit de la référence d’un compte, TST001 dans cet exemple. Cliquer sur OK, la procédure est exécutée :
En observant le panneau SQL Results, nous pouvons voir que l’exécution s’est bien déroulée. Mais quid de notre paramètre OUT? C’est quand même le but de cette routine. Il suffit de regarder le résultat sous l’onglet Parameters du panneau SQL Results :
Le panneau récapitule les variables IN et OUT (et INOUT) de notre procédure et nous voyons que le résultat pour init_val est : 10.00.
Hello, chouette article ! Mon chef veut que je test « IBM Data Studio », ce sera l’occasion d’appliquer un premier exercice !
Merci! IBM Data Studio est sympa pour développer des procèdures stockées, c’est d’ailleurs la seule solution que je connaisse pour AS400/DB2.
Merci pour cet article très pratique qui m’a fait gngner en temps et en performance dans mes activités quotidiennes sur iSeries. Bien à vous, merci encore
Bonjour
Dans une procédure stockée, j’affecte une valeur à une variable « var1 » (cela fonctionne à ce niveau)
ensuite je veux faire un
INSERT INTO table1(champ1, champ2, champ3, champ4) (SELECT champ1, champ 2, champ 3, var1 FROM table2) WHERE ….. )
Avec d’autres SGBD (PostGres, MariaDB, …) cela fonctionne, le 3 premières colonnes de table1 reçoivent les valeurs des 3 colonnes de table2 et la 4éme colonne de table1 reçoit la valeur de la variable.
Mais avec DB2 cela ne fonctionne pas ! Une solution ?
Bonjour Jacques,
Alors honnêtement, cela fait des années que je ne pratique plus DB2, je ne peux donc pas vraiment vous répondre. Désolé.