DB2 : Procédures Stockées

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
  • Sélectionner la connexion nouvellement créée, Next
  • Dans Default Application Process Settings, choisir le bon schéma : TSTLIB
  • 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 ProceduresNewStored Procedure :

  • Donner un nom à la procédure, par exemple FIRST_SP
  • Un template peut-être utilisé, nous allons simplement choisir Custom
  • Finish


Nouvelle Procédure Stockée

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 :

Déploiement

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 :

SQL Results status

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 :

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 :

Running

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 :

Run succeeded

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.

5 thoughts on “DB2 : Procédures Stockées”

  1. Hello, chouette article ! Mon chef veut que je test « IBM Data Studio », ce sera l’occasion d’appliquer un premier exercice !

    1. 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.

  2. 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

  3. 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 ?

    1. 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é.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *