Accueil du site > Les articles > SQL en V5R4
Version à imprimer Enregistrer au format PDF

SQL en V5R4

Quelques nouveautés SQL en V5R4

samedi 10 juin 2006, par Serge Gomes Visites  5335

SQL en free-format dans le RPG

SQL passe au free-code (ou free-format), les programmeurs ne doivent plus reléguer des appels SQL à des sous-procédures (aux C-Spéc. colonnes) ou pire encore éclater des blocs /free au milieu de la procédure principale. Maintenant, un bloc SQL peut être placé directement dans un bloc /Free. Les seules règles sont :

— Le bloc SQL doit commencer par EXEC SQL (ces 2 mots doivent être sur la même ligne)
— Le rapport lui-même peut enjamber plusieurs lignes (sans caractère spécial)
— Le bloc SQL, tout comme le format libre normal, doit se terminer par un point-virgule.

EXEC SQL 
     Select NUMCOD
       Into :W_Num
       Where LIBART=:W_lib ;

Row Value Expressions

Un prédicat en SQL évalue si une condition est vraie, fausse ou inconnue. Jusqu’à maintenant chaque prédicat était évalué pour une valeur unique. Maintenant, plusieurs expressions peuvent être évaluées simultanément. Ces groupes de valeurs s’appellent Row Value Expressions (lignes de valeurs d’expressions ?)

Select *  From Article
 Where (NUMCOD, FAMCOD)=(’21021’,’10A’)

La liste de colonne mise entre parenthèses est la « Row Value Expressions ».

En plus des constantes, une « Row Value Expressions » peut également être comparée à des colonnes de la table courante, aux expressions de colonne, ou à un ordre SELECT.

Bien sur le nombre de colonnes doit être égal et les types de données compatibles.

Select * 
  From SysTables 
 Where (Sys_TName,Sys_DName) In (Select Sys_TName,Sys_DName
                                   From SysColumns
                                  Where Column_Name=’ITEMNO’)

Attention : Une « Row Value Expressions ».n’accepte pas de constantes.

Valeur Hexadécimales

Les valeurs hexadécimales peuvent être enchaînées dans un select, les espaces sont ignorés. Cela améliore la visibilité du code :

Select x’F3  F4  F5’ From SYSIBM/SysDummy1 
Résultat de la requête : ‘345’

ROW_NUMBER()

ROW_NUMBER() permet d’attribuer un N° d’ordre résultant d’ un critère de tri de la clause OVER . La clause ORDER BY est obligatoire si le tri de la requête (ORDER BY) est différent du critère indiqué dans la clause OVER.

SELECT ROW_NUMBER() OVER(ORDER BY FAM, PRIX) AS N_ORD, 
  FAM, CODART , PRIX
  FROM ARTICLE ORDER BY FAM, PRIX

Dans cet exemple la clause ORDER BY incluse dans la clause OVER peut être retirée de l’expression, car la clause ORDER BY de la requête est identique et produit donc le même résultat.

Résultat de la requête :

ARTICLE
N_ORD	FAM	CODART	PRIX
1	A12	52148	12,25
2	A12	12459	14,58
3	A12	10258	17,88
4	A12	18569	24,50
5	B14	25487	115,40
6	B14	25684	118,50
7	B20	85682	65,85
8	B20	12784	68,54
9	B20	15489	125,42
10	B20	35421	144,99
11	C50	25486	3,25
12	C50	21548	3,55

Un numéro d’ordre est attribué à chaque ligne en tenant compte de l’ordre de tri par famille, puis par prix.

- Partition BY

Il serait intéressant de recommencer la numérotation à chaque changement de famille, pour cela il faut utiliser la clause PARTITION BY.

SELECT ROW_NUMBER() OVER
 ( PARTITION BY FAM ORDER BY FAM, PRIX) AS N_ORD, 
  FAM, CODART , PRIX
  FROM ARTICLE ORDER BY FAM, PRIX
ARTICLE
N_ORD	FAM	CODART	PRIX
1	A12	52148	12,25
2	A12	12459	14,58
3	A12	10258	17,88
4	A12	18569	24,50
1	B14	25487	115,40
2	B14	25684	118,50
1	B20	85682	65,85
2	B20	12784	68,54
3	B20	15489	125,42
4	B20	35421	144,99
1	C50	25486	3,25
2	C50	21548	3,55

Recursive Common Table Expression (RCTE)

Avec V5R4, les CTEs sont devenus un traitement SQL encore plus puissant avec l’apparition des RCTEs capables d’effectuer un traitement récursif.

Ce type de traitement est particulièrement utile pour interroger des tables qui ont des lignes liées à d’autres lignes de la même table et donc particulièrement adapté aux tables définissants une nomenclatures, une structure hiérarchique ou tout autre type de relation. Prenons le cas d’une société IMAGIN_AIR répartis entre plusieurs établissements liés entre eux par une relation hiérarchiques de type père-fils.

La table ETS

CREATE TABLE ETS(
   ID_ETS INTEGER PRIMARY KEY,
   LIBELLE VARCHAR(15),
   ID_PERE INTEGER)

Extrait de la table ETS

ETS
ID_ETS	ID_PERE	LIBELLE
1	 	PERE
2	1	REGION NORD
3	1	REGION SUD
4	3	SECTEUR S1
5	3	SECTEUR S2
6	2	SECTEUR N1
7	2	SECTEUR N2
8	4	AGC S11
9	4	AGC S12
10	4	AGC S13
11	5	AGC S21
12	6	AGC N11
13	6	AGC N12
14	6	AGC N13
15	8	CPT 211
16	8	CPT 212

Un comptoir (CPT) dépend d’une agence, une agence (AGC) d’un secteur, un secteur d’une région et une région dépend du siège.

Si on souhaite connaître les secteurs dépendant de la région sud, une simple requête suffit, mais si on veut lister toutes les agences et tous les comptoirs sous-jacents à la région sud, il faut traverser plusieurs niveaux de la hiérarchie.

Voici l’occasion de tester les RCTE :

— A => Initialisation

WITH Reg_Sud (niv, ID_ETS, LIBELLE) AS	
 (SELECT 1, ID_ETS, LIBELLE FROM ETS
  WHERE LIBELLE = ’REGION_SUD’
UNION ALL	

— B => Naviguer récursivement
SELECT B.niv + 1, A.ID_ETS, A.LIBELLE
  FROM ETS as A, Reg_Sud B
  WHERE B.ID_ETS = A.ID_PERE )

— C => Fin de la recursion, résultat final
SELECT niv, LIBELLE FROM Reg_Sud	

Une valeur constante « 1 » est renvoyée par la requête d’initialisation A, afin de montrer le niveau hiérarchique de chaque établissement. Cette requête d’initialisation ne sera exécutée qu’une seule fois et n’entre donc pas dans le cycle récursif.

La requête B est la partie récursive de la CTE. Elle contient une référence dans la clause FROM vers la table Reg_Sud issu de la requête d’initialisation. Au 1er passage la requête B retournera les établissements dépendants de REGION_SUD car ID.PERE contiendra la référence (ID_ETS) de REGION_SUD. Ce 1er passage va donc scanner tous les établissements contenant « 3 » (la valeur ID_ETS de REGION_SUD) dans la zone ID_PERE. Le cycle récursif va continuer et ne s’arrêtera que lorsqu’il rencontrera un établissement non référencé dans la zone ID_PERE d’un autre établissement. La colonne niveau est incrémenté pour suivre la profondeur récursive par rapport à REGION_SUD.

L’opération UNION ALL associe les résultats provenant des requête A et B.

La requête C permet de lancer le traitement récursif et fait référence à la CTE Reg_Sud.

Résultat de la requête :

niv	LIBELLE
1	REGION SUD
2	SECTEUR 2
2	SECTEUR 1
3	AGC S21
3	AGC S11
3	AGC S12
3	AGC S13
4	CPT 211
4	CPT 212

La sortie fait bien apparaître les établissements dépendants de REGION SUD. On remarque que les agences ne sont pas forcément listées derrière le secteur qui leur est rattaché. SQL traite les instructions récursives en traitant le liens dans l’ordre ou ils apparaissent, c’est a dire dans l’ordre des niveaux.

- SEARCH DEPTH Une analyse, « en profondeur », permet de ramener tous les niveaux du 1er secteur dépendant de REGION SUD avant de passer à un autre secteur. Pour forcer SQL à traiter les instructions récursives en « profondeur », il faut utiliser la clause SEARCH.

WITH Reg_Sud (niv, ID_ETS, LIBELLE) AS
(SELECT 1, ID_ETS, LIBELLE FROM ETS 
  WHERE LIBELLE = ’REGION_SUD’
 UNION ALL
 SELECT B.niv + 1, A.ID_ETS, A.LIBELLE
  FROM ETS as A, Reg_Sud B
  WHERE B.ID_ETS = A.ID_PERE )
 SEARCH DEPTH FIRST BY ID_ETS SET seqno
SELECT niv, LIBELLE FROM Reg_Sud ORDER BY seqno

Résultat de la requête :

niv	LIBELLE
1	REGION SUD
2	SECTEUR 2
3	AGC S21
4	CPT 211
4	CPT 212
2	SECTEUR 1
3	AGC S11
3	AGC S12
3	AGC S13

Cette sortie reflète mieux les relations de l’organisation, il est plus facile de visualiser chaque branche de la hiérarchie.

— La clause SEARCH doit utilser la même colonne que celle spécifiée dans l’ordre de jointure de la requête B (B.ID_ETS = A.ID_PERE).
— Le mot clé SET est associé à un nom de colonne symbolique (dans notre exemple seqno). Cette colonne est indispensable à SQL pour suivre l’ordre dans lequel il a scanné les enregistrements. Le nom de cette colonne n’a aucune importance, mais vous devez spécifier le même nom dans la clause ORDER BY de la requête C.

Attention les RCTEs utilisant la clause SEARCH DEPTH sont moins performantes !

- CYCLE

Parfois vos données contiendront des relations susceptibles de créer une boucle sans fin. Imaginons notre fichier comme contenant des trajets proposés par une agence de voyages. On souhaite connaître toutes les destinations possibles à partir de Paris.

Extrait Table TRAJET :

DEP	ARR
Paris	Stockholm 
Paris	Rome
Paris	Londres
New York	Paris
Londres	Rotterdam
Londres	New York

On voit que l’on peut enchaîner Paris-Londres, Londre-New York, New York-Paris. Avec une RCTE on peut entre dans un cycle sans fin, puisqu’on peut enchaîner à nouveau Paris-Londres La clause CYCLE permet d’éviter cela

CYCLE (nom de colonne) SET var = val1 DEFAULT val0

Quand SQL rencontre une ligne qu’il a déjà traitée, il va attribuer à var la valeur Val1, la ligne sera affichée et la boucle se termine.

WITH TMP (DEP, ARR, itinerary) AS
  (SELECT A.DEP, A.ARR FROM TRAJET A
   WHERE A.DEP =’Paris’
 UNION ALL
 SELECT T.DEP, B.ARR FROM TMP T,TRAJET B
 WHERE T.ARR =B.DEP )
CYCLE ARR SET Flag TO ’1 ’ DEFAULT ’0 ’
SELECT DEP, ARR, Flag FROM TMP
DEP	ARR	Flag
Paris	Londres	0
Paris	Rome	0
Paris	Stockholm 	0
Rome	Genève	0
Londres	Rotterdam	0
Londres	New York	0
New York	Paris	0
Paris	Londres	1

GENERATE_UNIQUE()

La fonction GENERATE_UNIQUE génère une valeur unique de type CHAR(13) Cette valeur est calculée par rapport au timestamp en cours et le numéro de série de système d’iSeries, le résultat ne sera jamais nul.

Le résultat de cette fonction peut être employé pour attribuer des valeurs uniques dans une table. Chaque valeur successive sera plus grande que la valeur précédente, fournissant un ordre, basé sur le moment où la fonction a été exécutée qui peut être employé dans une table.

Contrairement à CURRENT TIMESTAMP, GENERATE_UNIQUE produit une valeur unique pour chaque ligne d’un rapport multiple d’insertion.

Exemple :
— Créer une table qui inclut une colonne unique pour chaque rangée.
— Peupler cette colonne en utilisant la fonction GENERATE_UNIQUE()

Exemple :

CREATE TABLE EMP_UPDATE
    (UNIQUE_ID VARCHAR(13) FOR BIT DATA,
     EMPNO CHAR(6),
     TEXT VARCHAR(1000))

INSERT INTO EMP_UPDATE VALUES (GENERATE_UNIQUE(), ’000020’, ’Update entry 1’) INSERT INTO EMP_UPDATE VALUES (GENERATE_UNIQUE(), ’000050’, ’Update entry 2’)

Notez que la colonne d’UNIQUE_ID est définie comme VARCHAR(13) FOR BIT DATA.

Cette table aura un identifiant unique pour chaque enregistrement à condition, bien sur, que la colonne UNIQUE_ID soit toujours insérée en utilisant la fonction GENERATE_UNIQUE().

Ceci peut être fait en attachant un trigger à la table.

 CREATE TRIGGER  EMP_UPDATE_UNIQUE
    NO CASCADE BEFORE INSERT ON EMP_UPDATE
    REFERENCING NEW AS NEW_UPD
    FOR EACH ROW MODE DB2SQL
SET NEW_UPD.UNIQUE_ID = GENERATE_UNIQUE()

La valeur d’horodateur qui fait partie du résultat de cette fonction peut être déterminée en utilisant la fonction TIMESTAMP() avec le résultat de GENERATE_UNIQUE comme argument.

SELECT TIMESTAMP(UNIQUE_ID), EMPNO, TEXT FROM EMP_UPDATE

LABEL ON INDEX

Cette fonctionnalité va permettre de s’affranchir de la commande CHGOBJD pour affecter un nom à un index. Exemple :

Label On Index PRODL2 Is ’Liste des produits (DATE TYP NUM)’

CREATE TABLE (RCDFMT)

CREATE TABLE admet un nouveau paramètre RCDFMT, permettant d’indiquer un nom de format :

Exemple :

create table client
   (nocli  integer, nom char(35))RCDFMT clientf

Gestion du verrouillage (USE AND KEEP EXCLUSIVE LOCKS) :

Lors d’une lecture avec SQL vous pouvez régler le verrouillage, avec choix du niveau d’isolation RS ou RR
-  RS = Read Serialisable Les lignes , non verrouillées ; lues et manipulées sont verrouillées jusqu’au prochain COMMIT.
-  RR = Repetable read Les lignes lues et manipulées sont verrouillées jusqu’au prochain COMMIT. Les tables sont allouées jusqu’au commit (insertion impossible).

Exemple :

/* Read data with intent to update */
Declare CompleteOrders Cursor For
 Select *
   From Fic
  Where Actif =’N’
   With RS USE AND KEEP EXCLUSIVE LOCKS

L’utilisation de USE AND KEEP EXCLUSIVE LOCKS passe le verrouillage de chaque ligne de « Lecture » à « Mise à jour ».

Cette clause assurera qu’après que les données aient été lues aucun des enregistrements ne peut être mise à jour à partir d’un autre travail jusqu’à ce que les verrous soient libérés.

SET OPTION

- Avant la V5R4, l’instruction SQL SET OPTION pouvait être placée n’importe où dans le code source.
- A partir de la V5R4, elle doit précéder toutes les autres instructions SQL dans vos codes sources.Sinon, vous obtiendrez une erreur de compilation.

P.-S.

Bien sur il y a encore d’autres nouveautés, RDV sur le site d’IBM...