«

»

Juil 17

Imprimer ceci Article

Séquence SQL

Les Séquences

C’est quoi ?

Ayant connu mysql avant de connaitre l’ISeries, j’ai été étonnée de ne pas avoir d’auto-incrément en DB2. Mais dans le monde AS400, l’approche est différente : c’est remplacé par un genre de DATA AREA.

Pas la peine d’aller sur votre session 52/50 et de faire une create data area (quelque chose comme ça : CRTDTAARA)… il est possible de générer l’équivalent en SQL.

A quoi ça sert ?

A générer des clés uniques, incrémentée (ou décrémentée) automatiquement, gérée par le système.

Exemple :

  • Créer un id unique pour une table utilisateur
  • Créer un numéro de commande
  • Créer une trace dans une table de log

L’avantage par rapport à un auto-incrément d’une colonne est qu’il est possible de récupérer la valeur, ou la valeur suivante sans être obligé de faire un insert dans une table, voir le dernier point : « Comment l’utiliser ? »

Comment créer une séquence ?

En SQL

CREATE SEQUENCE myseq
AS INTEGER
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
CACHE 10
ORDER

Plus intéressant si on gère la maxvalue, c’est à dire que l’on indique le comporte une fois la valeur maximum atteinte :
CREATE SEQUENCE my_lib.my_seq
AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999
CYCLE CACHE 50 ORDER ;

Une fois le 9999 atteint, il repart à 1 🙂 La maxvalue doit être en accord avec votre rythme de nettoyage de votre table (oui ça se gère la fin de vie de l’information …)

 

>>-CREATE SEQUENCE--sequence-name------------------------------->

   .---------------------------------------------.
   V                                        (1)  |
>----+------------------------------------+------+-------------><
     |     .-INTEGER---.                  |
     +-AS--+-data-type-+------------------+
     +-START WITH--numeric-constant-------+
     |               .-1----------------. |
     +-INCREMENT BY--+-numeric-constant-+-+
     | .-NO MINVALUE----------------.     |
     +-+-MINVALUE--numeric-constant-+-----+
     | .-NO MAXVALUE----------------.     |
     +-+-MAXVALUE--numeric-constant-+-----+
     | .-NO CYCLE-.                       |
     +-+-CYCLE----+-----------------------+
     | .-CACHE--20---------------.        |
     +-+-NO CACHE----------------+--------+
     | '-CACHE--integer-constant-'        |
     | .-NO ORDER-.                       |
     '-+-ORDER----+-----------------------'
data-type:

|--+-built-in-type------+---------------------------------------|
   '-distinct-type-name-'

built-in-type:

|--+-+---SMALLINT---+---------------------------+---------------|
   | +-+-INTEGER-+--+                           |
   | | '-INT-----'  |                           |
   | '---BIGINT-----'                           |
   |                  .-(5,0)-----------------. |
   '-+-+-DECIMAL-+-+--+-----------------------+-'
     | '-DEC-----' |  |             .-,0-.    |
     '-NUMERIC-----'  '-(--integer--+----+--)-'

Avec le Wizard dans l’ISeries Navigateur

Placez vous dans la librairie voulu, développez via le [+]

iseries sequence

Placez vous sur [séquence], clic droit « Nouveau » :

iseries context menu sequence

Indiquez les valeurs désirées :

[ok] et c’est fini 🙂

Comment l’utiliser ?

  • VALUES (NEXTVAL FOR my_lib.my_seq)
  • VALUES NEXT VALUE FOR my_lib.my_seq INTO :varInProc;
  • SELECT NEXTVAL FOR my_lib.my_seq FROM staff WHERE id=10;
  • INSERT INTO my_lib.myTable VALUES (NEXTVAL FOR my_lib.my_seq, …)

Exemple dans une application en C# :
StringBuilder query = new StringBuilder();
Database db = DatabaseFactory.CreateDatabase("AS400");
query.Append(@"values(NEXTVAL FOR my_lib.my_seq)");
dbCommand = db.GetSqlStringCommand(query.ToString());
return int.Parse(db.ExecuteScalar(dbCommand).ToString());

 

Commentaires et suggestions ?

maKyo

Lien Permanent pour cet article : http://ma-kyo.com/sequence-sql/