| 
DB2/400 intégrités et comparaison avec la norme SQL-2 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 contrainte doit être nommée afin que le gestionnaire renvoie 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) DEFAUT valeur ou mot-réservé UNIQUE toute valeur non nulle doit être unique. non - CHECK(expression) expression devant être vraie accepté | avant | CHECK(nom de zone op valeur) V4R20 - !-------------> 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) CHECK(nom-de-zone IN (liste de valeurs)) CHECK(nom-de-zone BETWEEN(val1 AND val2)) 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 puristes 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 existant 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 T2 dont les valeurs doivent exister comme valeurs de la clé candidate dans T1 (T1 et T2 peuvent être la même table). table référencée: table T1 qui contient la clé candidate. table qui référence: table T2 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 évènement action évènement : 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'événement est interdit. CASCADE : en cas de suppression, répercute la suppression sur la table qui référence. (en cas de suppression d'un client, suppression de toutes ses commandes) SET NULL : l'événement 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 NO ACTION ON DELETE CASCADE répercussion des suppressions, modif non autorisée. |
|
remarque: NO ACTION est la valeur par défaut. certains SGBD utilisent la clause RESTRICT. sur AS/400 RESTRICT rend le contrôle immédiat (fichiers non journalisés) NO ACTION diffère le contrôle à la fin de la transaction(COMMIT) Prenons l'exemple 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 empêcher 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. |
|
Mise en oeuvre de tout cela sur AS/400 via les commandes DB2 (SQL offre les même possibilités) Définition d'une contrainte d'unicité ADDPFCST FILE(client) TYPE(*UNQCST) CST(client_unique) <- nom de la contrainte KEY(nocli) Définition d'une clé primaire ADDPFCST FILE(client) TYPE(*PRIKEY) CST(num_client) <- nom de la contrainte KEY(nocli) Une clé primaire est une forme particulière de contrainte d'unicité. (valeur nulle non autorisée) Il peut y avoir plusieurs contraintes d'unicité (portant sur des zones différentes), il ne peut y avoir qu'une seule clé primaire. |
|
Il y a dans les deux cas création d'un chemin d'accès (index), sur le membre du fichier. ATTENTION : Toutes les contraintes de DB2/400 (clé primaire et intégrité) ne peuvent s'appliquer que sur des fichiers MONO-MEMBRE ! En effet, si vous indiquez que toute commande doit appartenir à un client connu, il est hors de question de rentrer dans une logique de type : "le client doit exister dans le membre xxxx" IL EST VRAIMENT IMPORTANT D'ABANDONNER L'UTILISATION DES MEMBRES DANS LA BASE DE DONNEES SUR AS/400. (hormis ,peut-être pour les fichiers de travail) DB2/400, SQL et ODBC ne savent pas réellement utiliser cette notion. |
|
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 contrôle lors du COMMIT (journalisation obligatoire) et après l'appel du trigger *RESTRICT contrôle immédiat (journalisation facultative) DLTRULE : les mêmes + *CASCADE , *SETNULL , *SETDFT ATTENTION: les séquences de tri doivent être identiques (rigoureusement la même table, le même CCSID) Les attributs des clés candidates et parentes doivent être identiques même définition (type, lg, nombre de décimales) même CCSID , même REFSHIFT !!! |
|
Si l'une des deux règles (UPDRULE ou DLTRULE) est différente de *RESTRICT : - vos fichiers DOIVENT être journalisés. s'ils ne le sont pas : - l'intégrité sera établie (ADDPFCST fonctionne). - MAIS toutes vos demandes d'ouverture de fichier seront refusées (erreur d' E/S). - le contrôle de validation doit être actif. s'il ne l'est pas, le système démarre un contrôle de validation "QDBCMTDFN" pour voir les contrôles de validation : (il y en a un par groupe d'activation du job) WRKCMTDFN (nouvelle commande V3R10) |
|
il est difficile de définir une contrainte d'intégrité référentielle entre deux fichiers journalisés par des journaux différents. (cas de deux collections SQL) la doc indique que c'est impossible si vous indiquez une règle à une autre valeur que *RESTRICT. en fait DB2 vous laisse faire, mais la suppression s'avère physiquement impossible avec une règle de suppresion *CASCADE par exemple. (erreur d'E/S) on constate aussi que si vous travaillez en transaction (STRCMTCTL) il faut avoir validé l'insertion de l'enregistrement père avant de pouvoir insérer le fils. bref de nonmbreuses limites qui rendent cette option (deux journaux) assez difficile à implémenter. |
|
- Check Contraints ou contraintes de domaine : il s'agit d'établir un contrôle sur une zone (doit être > à , <> de ...) devant être TOUJOURS vérifié. les contrôles demandés aujourd'hui (COMP, RANGE, VALUES) ne sont vérifiés que dans un monde interactif (gestionnaire écran 5250) les nouvelles contraintes seront vérifiées dans toutes les conditions en cas d'erreur ==> message CPF502F ............................................................ : ADDPFCST FILE(PERSONP1) TYPE(*CHKCST) CST(SALAIRE_CST) : : CHKCST(Salair > 6663,67 and prime < salair) : :..........................................................: |
|
Une gestion des contraintes est possible : Une contrainte peut être momentanément "suspendue" Il y a 4 états possibles: - Etablie/active <-- OK, contrôle en cours - Etablie/desactivée <-- suspendue - Définie/activée <-- pas de membre - Définie/désactivée <-- idem, suspendue une commande permet de gérer l'état : WRKPFCST Mais une contrainte établie peut s'avérer fausse Si une contrainte est fausse |
|
Pour une contrainte d'unicité : elle est réalisable immédiatement ou il y a échec. Pour une contrainte d'intégrité : - soit suite à une restauration - soit suite à une désactivation , puis modifs dans le fichier Elle est notée "Check Pending" (vérifications en instance) Il faut utiliser la commande WRKPFCST ou directement CHGPFCST RMVPFCST DSPCPCST (display check pending constraints) A l'IPL, la commande EDTCPCST. |
|
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, 6=DSPCPCST "Verif : Oui" signifie que le système à détecté au moins un incohérence (visualisable par l'option 6) |
|
Pour une contrainte établie/active avec verif/instance = "oui" le système refusera systématiquement TOUS LES ORDRES OPEN sur ce fichier Vous devez : 1/ Désactiver la contrainte. (attention WRKPFCST ne réaffiche pas de lui-même, utilisez F5) 2/ Eliminer les incohérences L'option 6, reste valide pour visualiser les enregistrements. (DFU ou toute autre solution à votre choix) 3/ Réactiver la contrainte. Vérifiez (après F5) que la colonne verif/instance devient "non". |
|
La commande DLTF est complétée: DLTF .... RMVCST( *RESTRICT = échec s'il existe des contraintes *REMOVE = suppression des contraintes avec le PF *KEEP = les contraintes sont conservées/définies) Les contraintes sont sauvegardées avec les fichiers physiques, sont affichées par la commande DSPDBR Le systèmes vérifie la concordance lors de la restauration. La commande CRTDUPOBJ duplique les contraintes, pas la commande CPYF ... CRTFILE(*YES) Il existe donc un lien très FORT entre PF et contraintes ! la contrainte fait partie intégrante de l'objet PF, visible par DSPFD. |
|
............................................................................ : ATTENTION : : : : : Si les fichiers systèmes de références croisées (QADB...., etc): : : : qui référencent (entre autre) les contraintes entre fichiers : : : : ne sont plus à jour : : : : : : : - le système vous le signale par un message envoyé à QSYSOPR, : : (CPF32A2 envoyé par QDBSRVXR) : : : : - Vous ne pouvez plus définir ou supprimer de contraintes : : : : POUR REMETTRE LES REFERENCES CROISEES A JOUR VOUS DEVEZ : : EFFECTUER UN RCLSTG !! [RCLSTG SELECT(*DBXREF) en V4.] : : : : : :..........................................................................: |
