«

»

Juil 09

Imprimer ceci Article

Création de Procédure Stockée SQL via ISeries Navigator (AS400/DB2)

Introduction

Tout le monde n’a pas la chance de travailler avec Oracle ou SQL Server dès le début de sa carrière, mais quand on développe, peu importe la plateforme, peu importe le langage, quand on aime … on ne regarde pas à ça. … oui ok, souvent on a pas le choix de toute façon 😉

Cet article suppose des connaissances de base en SQL

Contexte

IBM ─► AS400 ─► ISeries Navigator ─►Script SQL

Notice

Stop !

Avant d’aller plus loin, oubliez vos a priori sur le monde d’IBM ! Ouvrez vos esprits, soyez OpenMind !

Pourquoi utiliser des procédures stockées ?

Les raisons peuvent être multiples.

Par simple choix de structure, de garder le métier sur la DB et ne pas avoir 36 règles de gestion dupliquées avec les risques que ça engendre d’en oublier une en cas de modification … Gestion centralisée : le RPG peut utiliser vos procédures stockée, mais des autres systèmes également : PHP, ASP, Excel / Calc, Crystal Report  ou autres outils de reporting … et quand vous voulez changer le corps de la procédure, pas la peine de passer dans chaque système pour changer la règle.

Cela permet en 1 appel de lancer plusieurs requêtes côté DB, de vraiment faire travailler votre base de données.

Référence

RedBooklogo redbook

http://www.redbooks.ibm.com/

Les RedBook IBM sont une source sûre, fiable, des idées d’utilisation qui vont vous permettre d’aller plus loin. Vous ne perdez jamais votre temps à chercher de la doc dans un redbook.

Évidemment, oubliez l’idée de l’imprimer : nous sommes à l’air du numérique, vous avez un PDF, l’outil de recherche est suffisant pour trouver l’information (CTRL+F), il y a des tables des matières, les sujets sont découpés et leurs documents sont bien structurés pour trouver l’information.

IBM

Le classic boulder : http://publib.boulder.ibm.com/ bien pour trouver 1 info spécifique = quand on sait ce qu’on cherche.

Pour commencer

Je vous conseille ce RedBook (une bible) : « Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries » http://www.redbooks.ibm.com/abstracts/sg246503.html?Open

L’outil

ISeries Navigator est un outil simple, assez basique mais complet pour utiliser la DB pour un développeur qui ne fait pas du RPG depuis 15 ans … ce n’est pas une grosse installation et c’est toujours mieux qu’un écran noir et vert (vous voyez de quoi je parle ?).
Ici, j’utilise la version 7 Edition 1, mais la syntaxe est identique au travers des versions.

Il existe d’autres outils, mais ils feront l’objet d’un autre article.

On plonge !

Il existe des Wizard pour créer vos procédures stockées, vos fonctions, vos séquences, vos index, …

Mais personnellement, pour comprendre ce que l’on fait, c’est toujours bien de faire au moins une fois le processus à 100% à la « mano ».

    1. Ouvrez votre script SQL (Comment faire ? Cliquez ici)
    2. Tapez les instructions suivantes :

CREATE PROCEDURE my_lib.myFirst()
BEGIN
END;

    1. Exécutez votre script : Le fait d’exécuter cet ensemble de lignes va créer la procédure dans votre DB, nécessaire pour pouvoir l’utiliser
    2. Appelez la procédure avec l’instruction CALL :

CALL my_lib.myFirst();

– « ok, mais ça fait rien ton truc ! »

– oui, mais il faut bien commencer quelque part …

Retour d’un SELECT

Améliorons notre petite procédure, utilisons la même procédure, pour ce faire, il faut la supprimer pour la recréer (étrange je sais)

DROP PROCEDURE my_lib.myFirst;

Comme bien souvent, le chef vous a demandé de sortir la liste de tous les clients qui sont dans la DB et vous a donné la librairie et le nom de la table.

Ajoutons un select sur une table :

CREATE PROCEDURE my_lib.myFirst()
DYNAMIC RESULT SETS 1
BEGIN
DECLARE C1 CURSOR WITH RETURN FOR SELECT * FROM my_lib.la_table_du_chef;
OPEN C1;
END;

Exécutez le script
Exécutez le CALL : CALL my_lib.myFirst();

Paramètre IN

ok ok, Allons encore un peu plus loin …
Disons que je veux introduire un paramètre à ma procédure : je veux le détail d’un client en particulier.

DROP PROCEDURE my_lib.myFirst;
CREATE PROCEDURE my_lib.myFirst(IN num INT)
DYNAMIC RESULT SETS 1
BEGIN
DECLARE C1 CURSOR WITH RETURN FOR
SELECT * FROM my_lib.la_table_du_chef where num_cli = num;
open C1;
END;

Exécutez le script
Exécutez le CALL (avec un numéro de client qui existe) : CALL my_lib.myFisrt(123);

– « et si client existe pas ? »

– « essaies, tu verras bien » 😉

Et oui un curseur vide, vient ensuite la gestion des erreurs dans un autre article …

Paramètre OUT

– « Je ne veux pas de select d’une table, je veux juste le résultat d’un count »

Utilisons un paramètre de sortie :
(à noter que le résultat du count peut aussi se faire par le retour du select via Curseur = s’affiche sous le format « grille »)


DROP PROCEDURE my_lib.myFirst;
CREATE PROCEDURE my_lib.myFirst( OUT nbr INT )
BEGIN
SELECT count(*) INTO nbr FROM my_lib.la_table_du_chef;
END;

Exécutez le script
Exécutez le CALL, pour lui indiquer que vous attendez un paramètre de sortie :
CALL my_lib.myFirst(?);

Paramètre INOUT

Pourquoi s’en priver ? la suite logique …

Il peut être utilisé pour effectuer une gestion d’erreur, récupérer un code, un libellé d’erreur.

Generate SQL – Génération d’instructions SQL

Dans les exemples utilisés, les instructions les plus basiques ont été utilisées pour effectuer le CREATE, vous verrez que lorsque vous effectuez un « Generate SQL » sur la procédure stockée que vous venez de créer, un tas d’instructions sont venues se rajouter en plus. C’est normal et toutes les options ont une explication, je vous invite à lire le manuel RedBook pour rentrer en profondeur.

En survol :

 

 Options Explication
NOT DETERMINISTIC |  DETERMINISTIC Très important !! DETERMINISTIC  => même paramètre en entrée = même résultat en sortie, il utilise son cache pour retourner l’information. Ne vous étonnez pas d’avoir le même résultat même si vous changer 10 fois le corps de votre procédure ou fonction ! Vous pouvez perdre beaucoup de temps à chercher pourquoi ça ne fonctionne pas … c’est juste qu’il passe par son cache
READS SQL DATA Indique ce que réalise votre procédure, est-ce que son rôle est de lire des données ? faire des mises à jour ? Le DB manager va vérifier que le contenu correspond à la déclaration.
CALLED ON NULL INPUT Implique que vous devez gérer le comportement en cas input à NULL, je préfère le contraire : RETURNS NULL ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD Indique si le manager doit locker les record ou pas, fonctionne de paire avec le level de « commitment control »
ALWCPYDTA = *OPTIMIZE Comportement pris lorsque le système décidera s’il doit copier des données de manière temporaire, vous pouvez lui dire que vous ne voulez pas qu’il copie des données temporaire, un message d’erreur sera retourné (ALWCPYDTA = *NO)
COMMIT = *NONE Isolation level : indique le comportement à adopter au niveau de l’isolation des données en cas d’accès concurrent lors de lecture et modification. https://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzmst02.htm

à lire sur les commit et rollback :

http://www-01.ibm.com/support/knowledgecenter/SSEPEK_9.0.0/com.ibm.db2z9.doc.apsg/src/tpc/db2z_commitrollbacksp.dita?lang=fr

DECRESULT = (31, 31, 00) Indique la précision à utiliser encas d’opération mathématique. (max-precision, max-scale, min-divide-scale)
DFTRDBCOL = *NONE Spécifie le shéma à utiliser pour les tables,vues, … qui ne sont pas qualifiée. Mais pas la peine de faire des bonds, ce n’est que pour le SQL static et n’est pas permis au sein d’une fonction, procédure ou trigger.
DYNDFTCOL = *NO Faut il utiliser le shéma de DFTRDBCOL?, utilisé pour les sql dynamique. Pas permis dans les fonctions, procédures, ou les trigger.
DYNUSRPRF = *USER Indique le User Profile qui sera utilisé pour les SQL dynamique
SRTSEQ = *HEX De quelle manière les comparaisons de chaînes sont effectuées.

il est possible d’ajouter des options via l’instruction SET OPTION, regardez la doc ibm pour plus d’infos.

Squelette du CREATE PROCEDURE

 http://publib.boulder.ibm.com/iseries/v5r2/ic2928/index.htm?info/db2/rbafzmstcrtpsf.htm

>>-CREATE PROCEDURE--procedure-name----------------------------->

>--+-----------------------------------------+------------------>
   '-(--+-------------------------------+--)-'
        | .-,-------------------------. |
        | V                           | |
        '-----parameter-declaration---+-'

>--LANGUAGE--SQL--option-list--+----------------------+--SQL-routine-body-><
                               '-SET OPTION-statement-'
parameter-declaration:

   .-IN----.
|--+-------+--parameter-name--data-type-------------------------|
   +-OUT---+
   '-INOUT-'

data-type:

|--+-built-in-type------+---------------------------------------|
   '-distinct-type-name-'
option-list:

   .-NOT DETERMINISTIC-. (1)   .-MODIFIES SQL DATA-.
|--+-------------------+-------+-------------------+------------>
   '-DETERMINISTIC-----'       +-CONTAINS SQL------+
                               '-READS SQL DATA----'

   .-CALLED ON NULL INPUT-.  .-DYNAMIC RESULT SETS--0-------.
>--+----------------------+--+------------------------------+--->
                             '-DYNAMIC RESULT SETS--integer-'

                                .-FENCED-----.
>--+-------------------------+--+------------+------------------>
   '-SPECIFIC--specific-name-'  '-NOT FENCED-'

   .-OLD SAVEPOINT LEVEL-.  .-COMMIT ON RETURN NO--.
>--+---------------------+--+----------------------+------------|
   '-NEW SAVEPOINT LEVEL-'  '-COMMIT ON RETURN YES-'
SQL-routine-body:

|--+-SQL-control-statement-----------------------+--------------|
   +-ALTER-statement-----------------------------+
   +-COMMENT-statement---------------------------+
   +-COMMIT-statement----------------------------+
   +-CONNECT-statement---------------------------+
   +-CREATE ALIAS-statement----------------------+
   +-CREATE DISTINCT TYPE-statement--------------+
   +-CREATE FUNCTION (External Scalar)-statement-+
   +-CREATE FUNCTION (External Table)-statement--+
   +-CREATE FUNCTION (Sourced)-statement---------+
   +-CREATE INDEX-statement----------------------+
   +-CREATE PROCEDURE (External)-statement-------+
   +-CREATE SEQUENCE-statement-------------------+
   +-CREATE SCHEMA-statement---------------------+
   +-CREATE TABLE-statement----------------------+
   +-CREATE VIEW-statement-----------------------+
   +-DECLARE GLOBAL TEMPORARY TABLE-statement----+
   +-DELETE-statement----------------------------+
   +-DISCONNECT-statement------------------------+
   +-DROP-statement------------------------------+
   +-EXECUTE IMMEDIATE-statement-----------------+
   +-GRANT-statement-----------------------------+
   +-INSERT-statement----------------------------+
   +-LABEL-statement-----------------------------+
   +-LOCK TABLE-statement------------------------+
   +-REFRESH TABLE-statement---------------------+
   +-RELEASE-statement---------------------------+
   +-RELEASE SAVEPOINT-statement-----------------+
   +-RENAME-statement----------------------------+
   +-REVOKE-statement----------------------------+
   +-ROLLBACK-statement--------------------------+
   +-SAVEPOINT-statement-------------------------+
   +-SELECT INTO-statement-----------------------+
   +-SET CONNECTION-statement--------------------+
   +-SET ENCRYPTION PASSWORD-statement-----------+
   +-SET PATH-statement--------------------------+
   +-SET SCHEMA-statement------------------------+
   +-SET RESULT SETS-statement-------------------+
   +-SET TRANSACTION-statement-------------------+
   +-UPDATE-statement----------------------------+
   '-VALUES INTO-statement-----------------------'
built-in-type:

|--+-+---SMALLINT---+-----------------------------------------------------------------+--|
   | +-+-INTEGER-+--+                                                                 |
   | | '-INT-----'  |                                                                 |
   | '---BIGINT-----'                                                                 |
   |                  .-(5,0)------------------------.                                |
   +-+-+-DECIMAL-+-+--+------------------------------+--------------------------------+
   | | '-DEC-----' |  |             .-,0--------.    |                                |
   | '-NUMERIC-----'  '-(--integer--+-----------+--)-'                                |
   |                                '-, integer-'                                     |
   |          .-(--53--)------.                                                       |
   +-+-FLOAT--+---------------+-+-----------------------------------------------------+
   | |        '-(--integer--)-' |                                                     |
   | +-REAL---------------------+                                                     |
   | |         .-PRECISION-.    |                                                     |
   | '-DOUBLE--+-----------+----'                                                     |
   |                    .-(--1--)-------.                                             |
   +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+------------+-+
   | | | '-CHAR------'  '-(--integer--)-'          |  +-FOR BIT DATA---+            | |
   | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-'  +-FOR SBCS DATA--+            | |
   | |   | '-CHAR------'          |                   +-FOR MIXED DATA-+            | |
   | |   '-VARCHAR----------------'                   '-ccsid-clause---'            | |
   | |                                 .-(--1M--)-------------.                     | |
   | '---+-CLOB-------------------+----+----------------------+--+----------------+-' |
   |     +-CHAR LARGE OBJECT------+    '-(--integer--+---+--)-'  +-FOR SBCS DATA--+   |
   |     '-CHARACTER LARGE OBJECT-'                  +-K-+       +-FOR MIXED DATA-+   |
   |                                                 +-M-+       '-ccsid-clause---'   |
   |                                                 '-G-'                            |
   |                .-(--1--)-------.                                                 |
   +-+---GRAPHIC----+---------------+-------+--+--------------+-----------------------+
   | |              '-(--integer--)-'       |  '-ccsid-clause-'                       |
   | +-+-GRAPHIC VARYING-+--(--integer--)---+                                         |
   | | '-VARGRAPHIC------'                  |                                         |
   | |             .-(--1M--)-------------. |                                         |
   | '---DBCLOB----+----------------------+-'                                         |
   |               '-(--integer--+---+--)-'                                           |
   |                             +-K-+                                                |
   |                             +-M-+                                                |
   |                             '-G-'                                                |
   |             .-(--1--)-------.                                                    |
   +-+-+-BINARY--+---------------+---------+-----------------+------------------------+
   | | |         '-(--integer--)-'         |                 |                        |
   | | '-+-BINARY VARYING-+--(--integer--)-'                 |                        |
   | |   '-VARBINARY------'                                  |                        |
   | |                              .-(--1M--)-------------. |                        |
   | '---+-BLOB----------------+----+----------------------+-'                        |
   |     '-BINARY LARGE OBJECT-'    '-(--integer--+---+--)-'                          |
   |                                              +-K-+                               |
   |                                              +-M-+                               |
   |                                              '-G-'                               |
   +-+-DATE-------------------+-------------------------------------------------------+
   | |       .-(--0--)-.      |                                                       |
   | +-TIME--+---------+------+                                                       |
   | |            .-(--6--)-. |                                                       |
   | '-TIMESTAMP--+---------+-'                                                       |
   |             .-(--200--)-----.                                                    |
   +---DATALINK--+---------------+--+--------------+----------------------------------+
   |             '-(--integer--)-'  '-ccsid-clause-'                                  |
   '---ROWID--------------------------------------------------------------------------'

Et les performances ?

Il existe un tas de recommandation IBM pour les performances  des procédures stockées, dans un prochain article … 🙂

maKyo

Lien Permanent pour cet article : http://ma-kyo.com/creation-de-procedure-stockee-sql-via-iseries-navigator-as400-db2/

1 ping

  1. Procédure Stockée SQL sur un RPG » maKyo

    […] Les paramètres se gèrent de la même manière qu’en pure SQL : IN, OUT, INOUT voir l’article sur les procédures stockées sql. […]

Les commentaires sont désactivés.