pause-café
rendez-vous technique
Pause-Café est une réunion technique
destinée aux informaticiens sur plateforme IBM i.
destinée aux informaticiens sur plateforme IBM i.
Elle a lieu 3 à 4 fois par an : en Bretagne et sur internet.
Pause-café # 5
Janvier 1995
La NORME SQL 92
Principales différences avec SQL400 V2R20
CREATE TABLE
type de variable TIME WITH TIME ZONE
TIMESTAMP WITH TIME ZONE
(contiennent le décalage avec l'UTC)
= méridien de greenwitch)
ALTER TABLE nom-table
ADD zone définition (ajout d'une colonne)
MODIFY zone nouvelle-définition (modification d'une colonne)
DROP zone (retrait d'une colonne)
DROP VIEW nom-vue (RESTRICT / CASCADE)
RESTRICT comme actuellement,suppression impossible
si d'autres vues s'appuient sur celle-ci.
CASCADE suppression de cette vue et de toutes les vues
qui l'utilisent.
CREATE VIEW (AS SELECT ...WHERE ...)
WITH CHECK OPTION
la clause WHERE doit être vérifiée aussi pour
les mises à jour et les insertions.
Si vous faites une restriction sur le code société = 01
l'utilisateur ne peut pas insérer(par exemple) des
lignes sur une autre société.
SELECT : - CASE (détermine l'apparition d'une colone fonction d'un test) SELECT nom, prénom, datenais, CASE WHEN situation = 'M' THEN 'marié' WHEN situation = 'C' THEN 'célibataire' ... ELSE 'inconnu' FROM personnel ... remplacement d'un code par son libellé en clair. - COUNT(DISTINCT(nom-zone)) indique le nombre de valeurs différentes rencontrées (SQL400 ne connait que COUNT(*) qui compte le nombre de lignes, mais devrait être disponible en V3R10)
- jonction la jonction (norme 89) est définie de la manière suivante: SELECT .... FROM table1, table2 WHERE table1.zone1 = table2.zone1 la jonction norme 92 peut être définie avec la clause JOIN, de la manière suivante: + produit cartésien: SELECT ... FROM table1 CROSS JOIN table2 (équivalent au précédent sans clause WHERE) + jointure naturelle: SELECT ... FROM table1 NATURAL JOIN table2 (SQL utilise les colonnes ayant même nom dans les deux tables) pour n'utiliser qu'une partie d'entre elles, ajouter: USING(nom-zone)
+ jointure conditionnelle
SELECT ... FROM table1 JOIN table2 ON zone1 = zone2
(toute expression logique est acceptée après ON)
+ auto-jointure
SELECT ... FROM table1 a JOIN table1 b ON a.zone1 = b.zone1
Les jointures sont par défaut internes
(elles n'affichent que les enregistrements en correspondance)
On parle de INNER JOIN (certains SGBD acceptent cette clause)
+ jointure externe (OUTER JOIN)
on parle de LEFT OUTER JOIN quand on désire tous les
enregistrements du fichier1 (celui à gauche du mot JOIN)
qu'ils soient ou non en correspondance avec le fichier2
on parle de RIGHT OUTER JOIN quand on désire tous les enregistrements du fichier2 (celui à droite du mot JOIN) qu'ils soient ou non en correspondance avec le fichier1 et l'on parle de FULL OUTER JOIN quand on désire tous les enregistrements des deux fichiers. exemples : liste des clients, avec, pour ceux d'entre eux ayant passé des commandes, la liste des commandes. SELECT codcli, nomcli, numcde, datcde, datliv FROM clients c LEFT OUTER JOIN command d ON c.numcli = d.numcli WHERE ... - les opérateurs ensemblistes (nous connaissont déja UNION) SELECT .... INTERSECT SELECT .... (renvoit uniquement les lignes communes aux deux requêtes) SELECT .... EXCEPT SELECT ... (renvoit uniquement les lignes de la première requête ne se trouvant pas dans la deuxième).
Nouveautés DB2/400 (V3R10)
CREATE TABLE
(NUMERODUCLIENT for column NOCLI CHAR(6), ...
Les noms longs sont acceptés (il sont gérés comme des ALIAS)
[128 C pour les objets / 30 c pour les colonnes]
SQL interactif et QM modifiés pour en tenir compte
SELECT COUNT(DISTINCT DEPT)
renvoit le nombre de valeurs rencontrées (et non le nombre de ligne)
SELECT NOM, SALAIRE + COM AS TOTPAIE FROM PERSON
attribut le "nom" TOTPAIE à l'expression SALAIRE + COM
Extensions diverses:
CREATE COLLECTION MABIB IN ASP 2
EXEC SQL autorisé avec REXX !!
Optimiseur:
nouvelle commande PRTSQLINF permet d'obtenir des informations
sur un plan d'accés. [SQL PACKAGE]
CHGQRYA
nouvelle commande (niveau JOB)
permettant de paramètrer le "QUERY GOVERNOR"'
+ utilisations abusives
+ autoriser le parallelisme ????
La dernière normalisation de SQL (on parle de SQL-2),
définit entre autre les contraintes d'intégrité de la base de données.
Une containte doit être nommée afin que le gestionnaire renvoi un nom
de contrainte significatif dans le message lors d'une violation.
Cette nomination se fait par: <contrainte> CONSTRAINT nom.
Contraintes de domaine:
NOT NULL (variable obligatoire)
create table article (CODART CHAR(6) NOT NULL , ...)
DEFAUT valeur ou mot-réservé
create table article (CODART CHAR(6) NOT NULL,
DATCRT DATE DEFAUT CURRENT-DATE )
UNIQUE toute valeur doit être unique.
Pour les clés candidates, mais il semble qu'un
index fasse aussi bien l'affaire.
CHECK(expression) expression devant être vraie
CHECK(nom de zone op valeur)
!
!-> opérateur logique (<,>,=,...)
create table article (CODART CHAR(6) NOT NULL,
QTESTOCK DEC(11, 2),
CHECK(qtestock >= 0)
CONSTRAINT qtestock_negative)
CHECK(nom-de-zone op nom-de-zone)
create table command (NUMCDE DEC(6, 0) NOT NULL,
QTECDE DEC(11, 2), QTELIV DEC(11, 2),
CHECK(qteliv <= qtecde)
CONSTRAINT qteliv_invalide)
CHECK(nom-de-zone IN (liste de valeurs))
create table personnel (MATRICUL DEC(6, 0),
SITUATION CHAR(1) /* marié,veuf,...*/,
CHECK(situation in ('M' 'V' 'C' 'D'))
CONSTRAINT situation_invalide)
CHECK(nom-de-zone BETWEEN(val1 AND val2))
la clause CHECK admet des sous-sélections
(approche référentielle)
Soit la table commande contenant un prix à la commande qui
ne peut pas être remisé de plus de 20% par rapport au tarif
create table command (NUMCDE DEC(6, 0) NOT NULL,
ARTCDE CHAR(6) , QTECDE DEC(11, 2),
PRICDE DEC(9, 2) NOT NULL,
CHECK(pricde >= 0,8 * (SELECT pritarif
FROM article
WHERE codart = artcde))
CONSTRAINT remise_invalide)
la clause CHECK admet des conditions complexes
(reliées par OR ou bien AND)
Soit la table commande contenant un prix à la commande qui
DOIT être remisé d'au moins 10% si la quantité est > à 100
create table command .....
CHECK(qteliv < 100 OR
pricde <= 0,9 * (SELECT pritarif
FROM article
WHERE codart = artcde))
CONSTRAINT remise_mini
Contraintes d'entité:
désignation d'une clé primaire (identifiant univoque).
Create table (zone1 PRIMARY KEY , ...)
ou
create table (zone1, zone2, zone3, ...
PRIMARY KEY(zone1, zone3) )
les clés primaires doivent être définies NOT NULL
il était possible d'arriver au même résultat en SQL-89
(c'est toujours possible) en créant un index UNIQUE avec cette
clé.
cela offre probablement plus de souplesse.
Les puriste considérent que cette contrainte étant sortie de
la table, l'intégrité est moins forte.
(suppression de l'index par exemple)
Contraintes d'intégrité référentielle:
Il s'agit de concrétiser le lien exitant entre deux tables.
(ce qui était réalisé jusqu'ici par programme)
soit par des déclarations de contraintes (plus performant)
soit par des procédures associées à une action = trigger
(plus souple, plus "gourmand").
Définitions
clé candidate: attribut ou groupe d'attributs dans une table T1
susceptible d'identifier un enregistrement.Une clé primaire
est toujours une clé candidate.
clé étrangère: attribut ou groupe d'attributs dans une table T1
dont les valeurs doivent exister comme valeurs de la clé
candidate dans T2. (T1 et T2 peuvent être la même table)
table référencée: table T2 qui contient la clé candidate.
table qui référence: table T1 qui contient la clé étrangère.
Déclaration d'une contrainte d'intégrité référentielle:
(dans la table qui référence)
champs REFERENCES table-référencée(clé candidate)
(clé candidate) est facultatif et inutile lors qu'il s'agit
de la clé primaire de la table référencée.
DU point de vue de la table qui référence:
create table command (NUMCDE DEC(6, 0) NOT NULL,
ARTCDE CHAR(6) REFERENCES article,
NUMCLI DEC(6, 0) REFERENCES clients)
ici le code article doit exister dans la table article
le n° de client doit exister dans la table clients.
ou bien (si la référence porte sur plusieurs colonnes)
FOREIGN KEY(zone1, zone2, ..) REFERENCES table
create table livraison (NUMLIV DEC(6, 0) NOT NULL,
NUMCDE DEC(6, 0) NOT NULL,
NOLIGN DEC(3, 0) NOT NULL,
FOREIGN KEY(numcde, nolign)
REFERENCES lcommand)
ici une livraison doit faire référence à une ligne de
commande existante.
DU point de vue de la table référencée:
il s'agit maintenant de définir ce que doit faire le gestionnaire
en cas de manipulation de la table référencée.
Pour le lien commande<-->client.
que faire si l'on supprime un client
si l'on change le code d'un client
... etc ...
tout cela ce déclare sur la table qui référence: (ici command)
NUMCLI DEC(6, 0) REFERENCES clients ON évenement action
évenement : une action réalisée sur la table référencée
(ici clients)
ON UPDATE = clé modifiée
ON DELETE = ligne supprimée
action: que doit faire le SGBD sur la table qui référence
(ici command)
NO ACTION : ne rien faire, l'évenement est interdit.
CASCADE : en cas de mise à jour,répercute la mise
à jour sur la table qui référence.
(en cas de modification d'une code client
toutes ses commandes possèdent son
nouveau code)
en cas de suppression, suppression des
lignes associées dans la table qui
référence.
(en cas de suppression d'un client,
suppression de toutes ses commandes)
SET NULL : l'évenement est autorisé et la clé
étrangère de la table qui référence est
mise à NULL (NULL doit être autorisé)
SET DEFAULT : idem SET NULL avec la valeur/dft
Exemple avec notre table command: NUMCLI DEC(6, 0) REFERENCES clients ON UPDATE CASCADE ON DELETE NO ACTION répercussion des modifs,suppression non autorisée. remarque NO ACTION équivaut à ne rien préciser (c'est le defaut) certains SGBD utilisent la clause RESTRICT à la place. Une contrainte d'intégrité peut être sortie de la notion de table (non rattachée à une table) avec la notion d'ASSERTION. CREATE ASSERTION nom-assertion CHECK(NOT EXIST (SELECT * from command WHERE pricde >= 0,8 * (SELECT pritarif FROM article WHERE codart = artcde)) Ce que nous avions déja définit, à la création de la table.
Et enfin toute contrainte d'intégrité est par défaut immédiate
(c'est à dire vérifiée à chaque instruction SQL)
Elle peut être différée c'est à dire reportée à la fin de la transaction
(ordre COMMIT)
Prenons l'exmple suivant:
1/ Un client ne peut exister que s'il possède au moins une commande.
2/ Une commande ne peut être enregistrée que pour un client connu.
Ces deux règles peuvent être dans certains cas (nouveau client)
contradictoires et peuvent empecher la création d'une commande
pour un nouveau client.
En déclarant ces deux contraintes différées, il suffit de demander
l'ajout du client et de sa première commande dans la même transaction.
En fin de transaction (COMMIT), les deux règles s'avéreront vraies.
Syntaxe: chaque contrainte peut être complétée par
(NOT) DEFERRABLE
-- INITIALLY DEFERRED / IMMEDIATE
!
! une contrainte pouvant être déclarée différable
! lors de sa création (non différable par défaut).
!
-> il s'agit de la valeur initiale qui peut être modifiée en début
de transaction pendant l'application par:
SET CONSTRAINTS (<-liste de contraintes-> / ALL)
DEFERRED / IMMEDIATE
Mise en oeuvre de tout cela sur AS/400 via DB2/400 :
Définition d'une clé primaire
ADDPFCST FILE(client)
TYPE(*PRIKEY)
CST(num_client) <- nom de la contrainte
KEY(nocli)
Intégrité référentielle
ADDPFCST FILE(command)
TYPE(*REFCST)
CST(clicommand)
KEY(numcli) <- zone du fichier commande
PRNFILE(client) <- fichier parent
PRNKEY(nocli) <- clé candidate/fichier client
UPDRULE(*NOACTION)
DLTRULE(*CASCADE)
UPDRULE : *NOACTION , *RESTRICT (= *NOACTION mais avant l'appel du trigger)
DLTRULE : les mêmes + *CASCADE , *SETNULL , *SETDFT
Ces contraintes peuvents être définies via SQL : CREATE TABLE modifié (conforme à la norme, sur ce point) ou par ALTER TABLE (uniquement pour ajouter une contrainte) Une contrainte peut être momentanement "suspendue" ou peut s'avérer fausse (restauration par exemple) Il y a 4 états possibles: - Etablie/active <-- OK - Etablie/desactivée <-- suspendue - Définie/activée <-- pas de membre - Définie/désactivée <-- en erreur Deux commandes permettent de gérer l'état : WRKPFCST et EDTPFCST appellé directement à l'IPL en cas de problème.
Si une contrainte est fausse
Pour une contrainte d'unicité :
elle est réalisable immédiatement ou il y a echec.
Pour une contrainte d'intégrité :
- soit suite à une restauration
- soit suite à une désactivation , puis modifs dans le fichier
Elle est notées "Check Pending"
Il faut utiliser la commande WRKPFCTS
ou directement CHGPFCST
RMVPFCST
WRKPFCST
##########################################################################
# Gestion des contraintes de fichier physique #
# #
# 2=Modifier 4=Enlever 6=Afficher enreg en instance de vérif. #
# #
# Opt Contrainte Fichier Biblio Type Etat verif/instance #
# ARTICK1 ARTICP1 MABIB *PRIKEY #
# ARTICC1 ARTICP1 MABIB *REFCST ETA/DES Oui #
# ARTICC2 ARTICP1 MABIB *REFCST ETA/ACT Non #
# .... #
# ==> #
# #
##########################################################################
2=CHGPFCST, 4=RMVPFCST
"Verif : Oui" signifie que le système à détecté au moins un incohérence
(visualisable par l'option 6)
La commande DLTF est complétée:
DLTF ....
RMVCST( *RESTRICT = echec s'il existe des contraintes
*REMOVE = suppression des contraintes avec le PF
*KEEP = les contraintes sont conservées ??? )
Les contraintes sont sauvegardées avec les fichiers physiques
Le systèmes vérifie la concordance lors de la restauration
La commande CRTDUPOBJ duplique aussi les contraintes.
Il existe donc un lien très FORT entre PF et contraintes !
Pour compléter la notion d'intégrité référentielle,
Il existe la notion de déclencheur ou "TRIGGER".
(non encore normalisé, cela sera l'objet d"une norme SQL-3)
un trigger est une procédure enregistrée par le gestionnaire et
associée à un évenement sur la base.
Syntaxe: CREATE TRIGGER nom-trigger
évement ON nom-table
[WHEN(condition à vérifier)]
instructions SQL
[FOR EACH ROW/STATEMENT]
évenements possibles: BEFORE UPDATE BEFORE INSERT BEFORE DELETE AFTER UPDATE AFTER INSERT AFTER DELETE en ce qui concerne la mise à jour , certains SGBD prévoient la notion de colonne. (en cas de modification de CETTE COLONNE). sous la forme : AFTER UPDATE(colonne) ON table. WHEN(conditions) facultatif, même syntaxe que la clause WHERE instruction SQL, toute instruction (sauf SELECT) c.a.d UPDATE, DELETE, INSERT autorisés: sous-sélections (SELECT imbriqués) instructions multiples FOR EACH ROW: trigger éxecuté pour chaque enregistrement (màj multiple,...) FOR STATEMENT: trigger éxecuté une fois en fin d'instruction SQL.
Quelques exemples: soit la table lcommand contenant QTECDE (quantité commandée) QTELIV (quantité déja livrée) il s'agit d'assurer la cohérence de la zone QTELIV fonction des actions portant sur la table livraison. CREATE TRIGGER liv-add AFTER INSERT ON livraison UPDATE lcommand SET QTELIV = (SELECT SUM(qtelivl) FROM livraison WHERE numcdel = numcde AND nolignl = nolign) CREATE TRIGGER liv-dlt AFTER DELETE ON livraison .... même syntaxe .... CREATE TRIGGER liv-upd AFTER UPDATE(qtelivl) ON livraison .... même syntaxe ....
Quelques exemples: soit la table command contenant NUMCDE (N° de commande) il s'agit d'incrémenter le N° de commande automatiquement (dernier n° connu + 1) CREATE TRIGGER numcde_auto AFTER INSERT ON command WHEN(numcde = 0) UPDATE command SET numcde = (SELECT max(numcde) FROM command) + 1 Il suffit de ne pas renseigner le N° de commande lors de l'ajout (valeur 0 par défaut), pour que le trigger lui assigne un N°. Historisation automatique de la suppression d'un client CREATE TRIGGER histo-cli BEFORE DELETE ON clients INSERT into clilog (numcli, quand, qui) VALUES(NUMCLI, CURRENT_TIMESTAMP, USER)
Les triggers permettent aussi de gérer l'intégrité référentielle,
particulièrement les suppressions en cascade.
CREATE TRIGGER cli-sup
BEFORE DELETE ON clients
(DELETE FROM command WHERE CDECLI = NUMCLI)
FOR EACH ROW
(DELETE FROM lcommand WHERE LNUMCDE = NUMCDE)
FOR EACH ROW
INSERT into clilog (numcli, quand, qui)
VALUES(NUMCLI, CURRENT_TIMESTAMP, USER)
FOR EACH ROW
suppression de toutes les commandes de ce client (entètes et lignes)
et historisation.
Certains SGBD autorisent la déclaration de variables internes aux trigger,
et l'utilisation de boucles et de tests.
il s'agit alors de véritables programmes (ORACLE V7 par exemple)
(Voir DB2/400)
Exemple:
Réappro automatique:
CREATE TRIGGER reappro
AFTER UPDATE(qtestock) ON article
FOR EACH ROW
WHEN(qtestock < qtemini)
DECLARE x INTEGER
BEGIN
SELECT COUNT(*) INTO x
FROM cdefournis
WHERE cdfart = codart
IF (x = 0) THEN
INSERT INTO cdefourmis
VALUES(artfour, codart, qtereappro, CURRENT_DATE)
ENDIF;
END;
Exemple:
Ce qui aurait pu s'écrire
CREATE TRIGGER reappro
AFTER UPDATE(qtestock) ON article
FOR EACH ROW
WHEN(qtestock < qtemini AND NOT EXIST
(SELECT * FROM cdefournis
WHERE cdfart = codart) )
INSERT INTO cdefourmis
VALUES(artfour, codart, qtereappro, CURRENT_DATE)
Mise en oeuvre sur AS/400 avec DB2/400 ADDPFTRG FILE(mabib/command) TRGTIME(*AFTER) <- *AFTER/*BEFORE TRGEVENT(*INSERT) <- *INSERT/*DELETE/*UPDATE PGM(mabib/monprog) RPLTRG(*YES/*NO) TRGUPDCND(*ALWAYS/*CHANGE) *ALWAYS = appel même en cas de Màj à l'identique *CHANGE = uniquement en cas de Màj réelle. Le pgm est écrit dans le langage de votre choix !!! Les performances relèvent du CALL Les triggers recoivent deux paramètres peuvent envoyer un message de type *ESCAPE pour interdir la transaction. En environnement ILE compiler le pgm ACTGRP(*CALLER) [même groupe d'activation)
Paramètres recus: 1/ une structure complexe (voir ci dessous) 2/ 2 octets binaires : lg de la structure .......................................................................... : de : A : Type : signification : : 1 : 10 : CHAR : nom du fichier : : 11 : 20 : CHAR : nom de la bibliothèque : : 21 : 30 : CHAR : nom du membre : : 31 : 31 : CHAR : 1=Insert , 2=Delete , 3=Update : : 32 : 32 : CHAR : 1=After , 2=Before : : 33 : 33 : CHAR : niveau de verrouillage (de 0=*NONE à 3=*ALL) : : 49 : 52 : BIN : (position - 1) de l'image avant : : 53 : 56 : BIN : lg de l'image avant : : 65 : 68 : BIN : (position - 1) de l'image après : : 69 : 72 : BIN : lg de l'image après : : ?? : ?? : CHAR : Image avant : : ?? : ?? : CHAR : Image après : :........................................................................:
Et ENFIN les procédures cataloguées permettent l'appel de programmes avec
passage de paramètres, sous une forme standardisée, et éventuellement
sur un système distant.
Très intéressant dans une architecture client/serveur, l'OS/400 accepterait
des procédures cataloguées écrites dans n'importe quel langage:
(y compris REXX)
EXEC SQL
CALL PGM1 (:nocli, :datecde) <- peut être RPG, COBOL, ....
END-EXEC.
PGM1 peut tout à fait ne PAS contenir d'ordre SQL.
Cette technique devrait être reconnue y compris par le driver ODBC
Pour terminer petite comparaison DB2/400 - ORACLE (source NEWS 3X)
-------------------------------------------------------------------
+ A part SQLCODE = 0 et 100, les codes retour ne sont pas compatibles
+ DB2 reconnait les curseurs dynamiques (FETCH PRIOR), ORACLE non.
+ ORACLE demande autant de variables résultat lors d'un FETCH, qu'il y a
de colonnes extraites, DB2 accepte les structures (DS ou niveau 01)
+ ORACLE vous laisse la possibilité de "conseiller" le query governor.
+ ORACLE reconnait les jointures externes, DB2 uniquement via les logiques
+ ORACLE reconnait NOT NULL , pas NOT NULL WITH DEFAULT (CREATE TABLE)
+ DB2 reconnait les variables ISO : DATE, TIME, TIMESTAMP , pas ORACLE
+ ORACLE est capable de stocker des "Binary Large Objects", pas DB2
+ ORACLE reconnait l'ordre SQL CREATE TRIGGER, DB2 la cde ADDPFTRG
+ ORACLE n'admet que les triggers écrits avec le langage PL/SQL
(le langage de programmation d'oracle)
avec DB2 , les triggers sont écrits dans le langage de votre choix.
+ ORACLE connait l'ordre ALTER TABLE pour retirer, ajouter ou modifier
une colonne, DB2 non.
Sur l'ensemble, on peut considerer que DB2/400 n'a pas à rougir de la
comparaison, sachant qu'il s'agit d'une base de donnée intégrée.
Pour terminer ORACLE annonce le support de DB2/400 comme serveur de
données dans un environnement client/serveur via les outils CDE.2
particulièrement les produits ORACLE suivants:
Oracle*Forms, Oracle*Reports,
Oracle*Graphics et Oracle*Browser