| 
SQL/400 V3R10 Principales nouveautés (mise en conformité avec la norme) Contraintes d'entité: - désignation d'une clé unique (pour les valeurs non nulles) -------------------------------- UNIQUE-------------------- |--CONSTRAINT nom-contrainte-| |-(zonea, zoneb)-| + si CONSTRAINT nom-contrainte n'est pas renseigné le système génère automatiquement un nom par défaut + utilisable associé directement à une colonne CREATE TABLE (zone1 dec(3,0) UNIQUE, zone2 char(30), ... ou fin de définition de table si l'unicité est demandée sur plusieurs colonnes: |
|
CREATE TABLE (zone1 dec(3, 0), zone2 char(30), zone3 dec(2, 0), UNIQUE(zone1, zone3)) un index est crée avec la table (il fait partie de la table) - désignation d'une clé primaire (identifiant univoque). -------------------------------- PRIMARY KEY--------------- |--CONSTRAINT nom-contrainte-| |-(zonea, ..)-| + les clés primaires doivent être définies NOT NULL + même syntaxe que l'unicité CREATE TABLE (zone1 dec(3, 0), ! CREATE TABLE (zone1 dec(3, 0), zone2 char(30) ! zone2 char(30), PRIMARY KEY, ! zone3 dec(2, 0), zone3 dec(2, 0) ! PRIMARY KEY(zone1, zone2)) ! |
|
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) par des déclarations de contraintes syntaxe générale -----------------------------------FOREIGN KEY------------------------> | | | | |-CONSTRAINT nom-contrainte-| |-(zone,...)-| >------------REFERENCES autre-table----------------------------- | | |--(zone1 [, zone2 ...])-| >--------------------------------------------------------------- | | | | |--ON DELETE action-| |-- ON UPDATE action-| |
|
Exemple : 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. Comme avec les contraintes d'unicité et de clé primaire, si la clause CONSTRAINT n'est pas employée, le système génère un nom par défaut. 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 référencer une commande existante. |
|
ON DELETE, ON UPDATE : que faire si l'on supprime une ligne du fichier parent si l'on change la clé dans le fichier parent RESTRICT et NO ACTION : ne rien faire,l'événement est interdit. (comme la commmande ADDPFCST) CASCADE : 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'é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 |
|
Il est possible d'ajouter/retirer une contrainte par l'ordre ALTER TABLE : |--ADD---> ALTER TABLE nom --- |--DROP--> |--contrainte d'unicité (même syntaxe que CREATE TABLE)---| >---ADD-- | |--contrainte référentielle(même syntaxe que CREATE TABLE-| >---DROP-----PRIMARY KEY------------------------------------------- | | |--FOREIGN KEY-| | |--UNIQUE------|-- nom de la contrainte--| |--CONSTRAINT--| |
|
en V3R60 et V3R20. Il est possible d'ajouter (ADD), retirer (DROP) ou de modifier (ALTER) une colonne, modifiant ainsi la structure même de la table : ADD ALTER TABLE nom-table-----< ALTER >-COLUMN ---------------> DROP ADD COLUMN = même syntaxe que CREATE TABLE ADD COLUMNS-nom-FOR COLUMN-nom/400---Définition-------> >----------------------------------------------------> !--NOT NULL-------------! !--clause WITH DEFAULT--! >-------------------------------------------------- ! ! !--CONSTRAINT--nom---UNIQUE----------------! !-PRIMARY KEY-----! !-REFERENCES ...--! |
|
!--CASCADE---! DROP COLUMN-----nom de zone----- !------ !--RESTRICT--! CASCADE indique que tous les logiques, toutes les vues toutes les contraintes qui s'appuient sur cette colonne sont supprimées. RESTRICT indique que la colonne n'est pas retirée si un des éléments ci-dessus s'appuie sur cette colonne. ALTER COLUMN -------------------------------------------------> !-SET ---DATA TYPE- type de zone---! ! ! !--NOT NULL---------------! >----DROP ---DEFAULT-----------------------------> ! ! !-NOT NULL--! >----(clause WITH DEFAUT)------------------. |
|
la clause WITH DEFAULT et les valeurs par defaut sont modifiées WITH DEFAULT-------------------------------------------------------. !--'constante'----! !---NULL----------! !--CURRENT_DATE---! !--CURRENT_TIME---! !CURRENT_TIMESTAMP! CURRENT_DATE, CURRENT_TIME et CURRENT_TIMESTAMP représente l'horloge au moment de l'insertion (écriture) les valeurs par défaut si vous n'indiquez rien sont : blanc pour l'alpha 0 pour le numérique CURRENT_DATE pour une variable date CURRENT_TIME pour une variable heure CURRENT_TIMESTAMP pour une variable horodatage |
|
Pour les programmes réalisant leurs E/S par SQL, cette seule action suffit puisqu'il n'y a pas de buffer : un programme contenant des ordres SQL demande des colonnes (et non un enregistrement), que le système place dans des VARIABLES ! Cela n'empêchera pas les recompilations des programmes d'autres langages et SQL/400 sera compatible avec la norme certains produits micros(comme Visual Basic) utilisent le ALTER TABLE Cela apporte aussi une nouvelle commande OS : CHGPF qui comporte un paramètre SRCFILE qui permet une REcréation ATTENTION : la recopie se fait sur les noms de zone, vous ne pouvez donc pas renommer des zones, mais les modifier, en ajouter et modifier des paramètres comme EDTCDE, COLHDG, .... |
|
Autres nouveautés SQL. L'OS/400 a maintenant une vision globale de la base de données : 1/ il possède un catalogue général dans QSYS QADBCCST QSYS (PF): Une ligne par champs de contrainte QADBFCST QSYS (PF): Une ligne par contrainte QADBFDEP QSYS (PF): Une ligne par relation entre fichiers QADBIFLD QSYS (PF): Une ligne par champs de fichier (PF,LF,Table,vue) QADBKFLD QSYS (PF): Une ligne par champs clé (LF ou Index) QADBPKG QSYS (PF): Une ligne par Package SQL QADBXRDBD QSYS (PF): Une ligne par RDB (base éloignée DRDA) QADBXREF QSYS (PF): Une ligne par fichier BD sur votre machine Plus des fichiers logiques pointants sur ces physiques. vous pouvez interroger ces fichiers par QUERY ou SQL (si vous avez les droits) |
|
2/ il n'y a plus de frontières Bibliothèque OS/400/collection SQL Tous les objets SQL (Tables, vues, indexs) peuvent êtres crées dans n'importe dans une bibliothèque OS/400 traditionnelle. (auparavant cela était réservé aux bibliothèques de type collection SQL) + en création de table, le fichier physique ne sera pas journalisé (SQL envoie un message de type *DIAG) + en création de vue il n'y a pas de limites et cela ouvre des perspectives pour une bibliothèque d'infocentre. RAPPEL sur les vues: elles permettent de mémoriser un SELECT simple (tout sauf ORDER BY et UNION) Create view V1 as SELECT * FROM fichier WHERE zone1 < 15 create view v2 (nomdezone1, montant, ...) as SELECT zone1, qte * prix FROM fichier create view V3 (nocli, total, moyenne) as SELECT nocli, sum(qte), avg(qte) FROM fichier GROUP BY nocli |
|
NOUVELLE SYNTAXE : CREATE VIEW (AS SELECT ... FROM... WHERE ...)---------------> >----------------------------------------------------------- | | | |-CASCADED-| | |-WITH ----------- CHECK OPTION--| | |-LOCAL----| | 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 d'une autre société. SQL autorise des vues s'appuyant sur des vues CREATE TABLE t1 .... CREATE VIEW v1 (AS SELECT ... FROM t1 ....) CREATE VIEW v2 (AS SELECT ... FROM v1 ....) |
|
avec CASCADED le contrôle est effectué AUSSI par rapport aux sélections de TOUTES les vues sous-jacentes (utilisées dans FROM). avec LOCAL le contrôle n'est effectué que pour les vues sous-jacentes ayant la clause WITH CHECK OPTION. soit V1 s'appuyant sur T0 V2 s'appuyant sur V1 WITH (1) CHECK OPTION V3 s'appuyant sur V2 V4 s'appuyant sur V3 WITH (2) CHECK OPTION V5 s'appuyant sur V4 lors d'une mise à jour sur V5 : ! Conditions vérifiées : ! (1) = LOCAL (2) = LOCAL ! V4,V2 (1) = CASCADED (2) = LOCAL ! V4,V2,V1 (2) = CASCADED* ! V4,V3,V2,V1 * : quelque soit la valeur de (1) |
|
Divers SELECT : - COUNT(DISTINCT(nom-zone)) indique le nombre de valeurs différentes rencontrées - 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 (disponible avec la PTF SF22302) + produit cartésien: SELECT ... FROM table1 CROSS JOIN table2 (équivalent au précédent sans clause WHERE) |
|
+ jointure conditionnelle SELECT ... FROM table1 JOIN table2 ON zone1 = zone2 (toute expression logique est acceptée après ON) les jointures sont par défaut internes (elles n'affichent que les enregistrements en correspondance) On parle de INNER JOIN (qui est indentique à JOIN seul) + 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 [comme JDFTVAL/SDD ou OPNQRYF JDFTVAL(*YES)] pour SQL/400 LEFT JOIN est identique à LEFT OUTER JOIN les colonnes de table2 sont initialisées à NULL. |
|
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 ... + traitement des enregistrements sans correspondance SELECT ... FROM table1 EXCEPTION JOIN table2 ON zone1 = zone2 ne fournit que les enregistrements de table1 n'ayant pas d'équivalence dans table2 [comme OPNQRYF JDFTVAL(*ONLYDFT)] ATTENTION : CROSS, EXCEPTION, INNER, JOIN, LEFT, OUTER deviennent des mots réservés SQL si vous installez la PTF SF22302 et que vous compilez des programmes utilisant ces fonctionnalités, vous ne pouvez plus retirer la PTF. SQL/400 a ainsi une très haute compatibilité avec la norme SQL et donc ODBC |
|
SQL/400 et Two Phases Commit : - SQL supporte la validation à deux phases (voir ce cours) et a été modifié en conséquence. + nouveau paramètre CRTSQLxxx RDBCNNMTH (RDB connexion method) admet : *DUW (dft) Distributed Unit of Work = validation à deux phases *RUW Remote Unit of Work = validation simple (comme V2R30) > Si vous indiquez *RUW (la doc parle de connexion type 1) Comme avant vous ne pouvez être connecté qu'à une seule base à la fois. Vous devez terminer la transaction avant de changer de base. > Si vous indiquez *DUW, (connexion type 2) Vous pouvez vous connecter à plusieurs bases(même syntaxe pour CONNECT) |
|
Il existe alors trois nouveaux ordres (valides uniquement en type 2) SET CONNECTION nom-du-serveur utilisation: EXEC SQL CONNECT TO as1 (manipulation des données sur as1) EXEC SQL CONNECT TO as2 (manipulation des données sur as2, as1 est toujours connecté) EXEC SQL SET CONNECTION as1 (retour sur as1) RELEASE ---nom-serveur--- |-CURRENT----| |ALL/ALL SQL-| Libère le serveur indiqué, les ressources sont encore verrouillées. (ce serveur ne fera pas partie de la prochaine transaction) DISCONNECT (même syntaxe que RELEASE) déconnecte le serveur après un COMMIT |
|
Procédures cataloguées : [STORED PROCEDURES] SQL permet maintenant de demander l'exécution d'un programme sur un serveur distant (normalement devant réaliser une action sur la base de ce serveur) Cette fonction n'est pas encore normalisée (mais largement répandue). - certains SGBD n'autorisent que des procédures SQL. - DB2/400 autorise l'appel à n'importe quel programme. + avec passage de paramètres. + ces paramètres pouvant être renseignés par le pgm distant. Syntaxe CALL nom-procédure---------------------------------------------- | | |-(param1,[param2,...])-| |
|
nom-procédure = nom du pgm à appeler ou procédure telle que définie par DECLARE PROCEDURE (voir ci-dessous) la procédure est recherchée dans la collection en cours pour qualifier : BIB/PGM avec la convention système BIB.PGM avec la convention SQL voir: STRSQL paramètre NAMING CRTSQLxxx paramètre OPTION paramx = paramètre envoyé (ou reçu) qui peut être + une constante + une variable du pgm (:var) (par défaut :var est en entrée/sortie avec SQL statique en entrée (envoyée) avec SQL dynamique) + NULL + une valeur spéciale (CURRENT DATE/TIME/TIMEZONE , SERVER) (USER) |
|
Il est possible de déclarer une procédure afin de préciser certaines options DECLARE -nom-proc-PROCEDURE------------------------------------------------> | |-IN----| | |-(param---OUT-----type de données(lg)-----| |-INOUT-| |-EXTERNAL------------------| >------------------------------LANGUAGE--langage--------------------------- |-EXTERNAL NAME-nom-externe-| |-SIMPLE CALL-----------| |-SIMPLE CALL WITH NULL-| définition des paramètres : IN le paramètre est uniquement reçu par la procédure OUT le paramètre est renvoyé par la procédure INOUT le paramètre est utilisé dans les deux sens type de donnée(lg) : idem CREATE TABLE CHAR(xx) INT(lg, dec) etc ... |
|
EXTERNAL NAME permet de définir le nom réel du pgm sur le serveur, si cette clause n'est pas renseignée c'est le nom de procédure qui est utilisé. LANGUAGE permet de définir le langage d'origine du pgm à exécuter C, CL, COBOL, COBOLLE, RPG, RPGLE, REXX, ... ceci est obligatoire avec une procédure REXX si cette clause n'est pas utilisé le server recherche l'attribut du pgm et par défaut le considère comme un pgm C. SIMPLE CALL WITH NULL signifie que le pgm doit recevoir les indicateurs indiquant les valeurs nulles (254 param maxi) SIMPLE CALL le pgm ne reçoit pas ces indicateurs (255 param maxi) l'instruction DECLARE PROCEDURE doit être utilisée pour appeler des procédures REXX, et doit être écrite AVANT l'instruction CALL. la déclaration peut-être permanente avec CREATE PROCEDURE . |
|
Particularité de SQL et donc d'ODBC : il ne reconnait pas les membres.
vous pouvez quand même outrepasser en utilisant les procédures
Exemple CALL QSYS.QCMDEXC ('OVRDBF TOTO MBR(xxx) OVRSCOPE(*JOB)',
0000000035,00000)
Quelques explications :
le premier paramètre est entre quotes
(il s'agit d'une constante alpha)
la substitution doit être valable pour l'ensemble du job
(le pgm qui passe la commande n'étant plus actif après)
la longueur doit être indiquée avec 10 entiers, 5 décimales (15dt
la marque décimale doit être celle indiquée dans les paramètres.
vous pouvez donc appeler n'importe quel programme, mais en plus passer
n'importe quelle commande !!!
|
|
Extensions diverses : - SELECT NOM, SALAIRE + COM AS TOTPAIE FROM PERSON attribue le "nom" TOTPAIE à l'expression SALAIRE + COM - CREATE TABLE (NUMERODUCLIENT for column NOCLI CHAR(6), ... Les noms longs sont acceptés [128 C pour les objets / 30 c pour les colonnes] il sont gérés comme des ALIAS pour les colonnes pour les tables le nom OS/400 est généré à partir des septs premiers caractères. valide aussi avec les vues (pour une politique INFOCENTRE), donc SQL interactif et QM modifiés pour en tenir compte - EXEC SQL autorisé avec REXX (uniquement certains ordres) |
|
- CREATE COLLECTION xxxxx ------------------------------------------------ |-IN ASP n-| |-WITH DATA DICTIONNARY-| - ASP 1 par défaut - par défaut SQL n'utilise plus les dictionnaires IDDU il utilise les fichiers de références croisées du système (QADB...) il crée des fichiers catalogues: SYSTABLES, SYSVIEW, SYSVIEWDEP, ... qui sont des vues s'appuyant sur ces fichiers systèmes : ° d'une part dans QSYS2 (qui référencent tous les fichiers de votre système) ° d'autre part dans votre collection (qui ne référencent que les fichiers de votre collection) - DESCRIBE TABLE -nom de la table --- INTO - descripteur-- - décrit une table (colonnes, définition) dans SQLDA. (cf PREPARE) |
|
SQL Package : Rappel : lors de la compilation d'un pgm SQLxxx (SQLRPG, ....) , le pré-compilateur crée pour chaque reqête SQL un plan d'accès (méthode d'accès choisie, index(s), estimation du coût CPU, etc...) Ces plans d'accès sont stockés dans un SQLPACKAGE. si vous travaillez en local ce SQLPACKAGE est lui-même intégré au programe si vous travaillez avec une base éloignée: - si la ligne est active au moment de la compilation, il y a création d'un objet *SQLPKG sur l'AS distant (celui où se trouve la base) - sinon, vous pourrez, quand la ligne sera active, passer la commande CRTSQLPKG en fournissant les coordonnées du pgm local. Ce SQL PACKAGE améliore nettement les performances sur le site distant lors de l'exécution de la requête. |
|
ODBC utilise cette technique de facon à réutiliser les plans d'accès lors de requêtes successives.(les Packages sont par défaut dans QGPL) nouvelle commande PRTSQLINF permet d'obtenir des informations sur les plans d'accès d'un SQL PACKAGE. - paramètres de la commande CRTSQLxxx :marque décimale,séparateur de date - information sur les plans d'accès (index utilisés, etc... comme en mode DEBUG) Optimiseur: CHGQRYA nouvelle commande (niveau JOB) permettant de paramétrer le QUERY GOVERNOR + QRYTIMLMT : utilisations abusives (temps maxi(en secondes) d'exécution d'une requête), si le temps estimé est supérieur, la requête n'est pas lancée. + DEGREE : autoriser le parallèlisme (machines multi-voies) |
|
V3R60 : Nous avons vu ALTER TABLE Avec en plus, de nouvelles fonctions ou de nouveaux registres : CHARACTER_LENGTH = nbr de caractères (zone à lg variable) COALESCE et IFNULL = alias de VALUE(v1, v2), renvoie le premier non null CONCAT( ) = ancienne syntaxe : nom CONCAT prenom maintenant en plus : CONCAT(nom, prenom). CURDATE() = idem au registre CURRENT DATE CURTIME() = " " CURRENT TIME NOW() = " " CURRENT TIMESTAMP DAYOFMONTH(date) = jour dans le mois (idem DAY().) DAYOFWEEK(date) = jour dans la semainde (1=dimanche) DAYOFYEAR(date) = jour (julien) dans l'année. QUARTER(date) = N° du trimestre WEEK(date) = N° de la semaine |
|
POWER(nombre, exposant) élévation à la puissance SUBSTR nouvelle écriture : SUBSTRING(zone FROM début ------------------------) !--FOR longueur--! si longueur n'est pas indiqué on va jusqu'à fin de zone LEFT(zone, lg) Extrait les n caractères de gauche. LTRIM( ), RTRIM( ) et TRIM( ) Supprimme les espaces à gauche, à droite ou aux deux extrémités. UCASE( ) ou UPPER transformation minuscule Majuscule |
|
TRANSLATE(chaîne-----------------------------------------------------> ! ! !---, remplacement----------------------------! ! ! !--, origine ------! >-------------------------------) ! ! !--PADDED WITH------! Exemple: TRANSLATE(var1 , ' ?F' , '0,$') VARCHAR(expression, Lg----------------) ! ! !--CCSID---! Transforme une expression en lg variable VARGRAPHIC( ) Idem en DBCS. |
