| 
L'ordre SQL SELECT complet (FULL SELECT) admet maintenant les opérateurs INTERSECT et EXCEPT en plus de l'opérateur UNION ou UNION DISTINCT INTERSECT (ou INTERSECT DISTINCT, qui est identique) affiche les enregistrements qui se trouvent dans l'une ET l'autre des requêtes. EXCEPT (ou EXCEPT DISTINCT, qui est identique) affiche les enregistrements qui se trouvent dans l'une ET PAS dans l'autre des requêtes. SELECT matricule, nom from personnel WHERE departement = 'INFO' EXCEPT SELECT matricule, nom from absence WHERE year(dateabs) = year(now()) and code = 'M' Donne la liste des lignes qui sont dans la première requête uniquement. ( les personnes du département INFO qui n'ont pas été absent cette année pour cause de maladie) INTERSECT donne la liste des lignes qui sont dans les deux requêtes |
|
récapitulatif, soit deux fichiers f1 et f2 : ...... ...... | : f1 : : f2 : | UNION UNION DISTINCT INTERSECT f1 EXCEPT f2 : A : : A : | A A A D : A : : B : | A B B E : B : : B : | A C C : C : : C : | B D : C : : F : | B E : D : : F : | B F f2 EXCEPT f1 : E : :....: | C F : E : | C :..... | C | D | E | E | F | F | | |
|
Jointure, suite : ----------------- pour faire une jointure, nous pouvons écrire : SELECT * from vins v join producteurs p USING( pr_code ) et pour une jointure avec des sélections imbriquées; select P.pr_nom, V.compteur from producteurs P, LATERAL (select count(*) AS compteur from vins where pr_code = P.pr_code) V .............................................................. : donnant la liste détaillée des producteurs avec pour : : chacun le nombre de vins produits. : :............................................................: LATERAL fonctionne comme une jointure, mais en autorisant dans la requête l'utilisation de colonne(s), venant de niveau supérieur (P.pr_code) |
|
cependant, pour l'instant il semblerait que la jointure soit plus rapide. par exemple, cet ordre donnant le nombre de cours et d'exemples par module ........................................................................... : select m.AF4MDL, texte, nbcours, nbexemples from AF400.af4MDLP1 m , : :LATERAL : : (select count(*) as nbcours : : from AF400.AF4MBRP1 WHERE AF4mdl=M.af4mdl) as C , : :LATERAL : : (select count(*) as nbexemples : : from AF400.af4lnkp1 WHERE af4mdl=m.af4mdl) as E : :.........................................................................: met 1,5 fois plus de temps (en V5R30), que celui-ci : ............................................................................ : select m.AF4MDL, mdltxt, nbcours, nbexemples from AF400.af4MDLP1 m : :JOIN : : (select AF4MDL, count(*) as nbcours : : from AF400.AF4MBRP1 group by AF4mdl) as C on m.af4mdl = c.af4mdl : :LEFT JOINT : : (select af4mdl, count(*) as nbexemples : : from AF400.af4lnkp1 group by af4mdl) as E on C.af4mdl= e.af4mdl : :..........................................................................: |
|
nouvelles fonctions MULTIPLY_ALT() alternative à l'opérateur X, travaille avec une plus grande précision, à utiliser avec des résultats intermédiaires de plus de 63 chiffres ! REPEAT(c , nb) repète le caractère 'c', nb fois REPLACE(zone, org, new) remplace org par new dans zone RIGHT(zone , l) extrait l caractère de zone à partir de la droite (complément à LEFT() et à SUBSTR() .) DATABASE() retourne le nom du serveur comme CURRENT SERVER DAYNAME(Date) retourne le nom du jour (en Français) de Date MONTHNAME(Date) retourne le nom du mois (en Français) de Date EXTRACT(DAY from zonedate) extrait la partie JOUR de zone date, on peut demander DAY, MONTH, YEAR d'une date HOUR, MINUTE SECOND d'une heure |
|
TIMESTAMP_ISO() convertit en TIMESTAMP une date (l'heure est à 00:00:00) ou une heure(la date est à aujourd'hui) INSERT(Z , deb, nb, chaine) insert 'chaine' à partir de 'deb' en remplacant 'nb' caractères (0 = insertion pure). sur une zone char(1) contenant '*' Insert(zone1 , 1 , 0 , 'XX') --> 'XX*' --on insert devant Insert(zone1 , 1 , 1 , 'XX') --> 'XX' --on remplace Insert(zone1 , 2 , 0 , 'XX') --> '*XX' --on insert derrière OCTET_LENGTH indique le nombre d'octets occupés par une colonne BIT_LENGTH() indique le nombre de bits occupés par une colonne BINARY() et VARBINARY(), liées aux nouveaux types de données. BINARY et VARBINARY sont des types proches du BLOB, les données étant notées sans notion de CCSID, ces fonctions assurent une conversion vers ce nouveau type. |
|
ENCRYPT_RC2(data, pwd , hint) Encrypte les données founies en premier argument en utilisant le deuxième comme clé, selon l'algorithme RC2. le mot de passe peut être fixé aussi par SET ENCRYPTION PÄSSWORD, avant. l'asctuce (facultative) est un "pense-bète" mémorisé avec la donnée. GET_HINT() retourne l'astuce (le pense-bète) permettant de se souvenir de la clé (qui est obligatoire avec les fonctions ci-dessous) DECRYPT_BIT() décrypte une donnée cryptée et retourne du VARCHAR /BIT DATA DECRYPT_BINARY() décrypte une donnée cryptée et retourne du BINARY DECRYPT_CHAR() décrypte une donnée cryptée et retourne du VARCHAR simple DECRYPT_DB() décrypte une donnée cryptée et retourne du Double Byte (UTF-8) |
|
Tables matérialisées: Cela permet de définir une table en lui associant une requete initiale -la table peut etre réactualisée avec le resultat de la requete à tout moment. - dans une prochaine version, cette table sera utilisée par l'optimiseur syntaxe : CREATE SUMMARY TABLE -(nom)---AS (Select SQL complet [ORDER BY admis])-> !--DATA INITIALLY IMMEDIATE---! >---! !--REFRESH DEFERRED--> !--DATA INITIALLY DEFERRED ---! !---ENABLED QUERY OPTIMIZATION--! >--- MAINTAINED BY USER--! !---. !---DISABLE QUERY OPTIMIZATION--! |
|
DATA INITIALLY IMMEDIATE indique que la requete est réalisée une première fois à la création de la table DATA INITIALLY DEFERRED indique une table matérialisée uniquement avec REFRESH TABLE REFRESH DEFERRED admet l'utilisaiton le l'ordre REFRESH TABLE MAINTAINED BY USER est obligatoire ENABLED QUERY OPTIMIZATION permettera, dans l'avenir une utilisation de cette table par l'optimiseur de requete (val. par défaut) Une table matérialisée doit etre journalisée, donc placée dans une bibliothèque avec un journal QSQJRN. |
|
l'ordre ALTER TABLE permet de revenir sur une table matérialisée ALTER TABLE xxx DROP MATERIALIZED QUERY, rend la table ordinnaire ALTER TABLE xxx ALTER MATERIALIZED QUERY, modifie les caractèristiques ALTER TABLE xxx ADD MATERIALIZED QUERY, fait d'une table simple, une table matérialisée. Exemple : --------- CREATE SUMMARY TABLE BDVIN9/VINS_PAR_PRODUCTEUR AS ( SELECT VINS.VIN_NOM , VINS.VIN_CEPAGE1 , PRODUCTEURS.PR_NOM , PRODUCTEURS.PR_COMMUNE , PRODUCTEURS.PR_PROPRIO FROM BDVIN9/VINS VINS RIGHT OUTER JOIN BDVIN9/PRODUCTEURS ON PRODUCTEURS.PR_CODE = VINS.PR_CODE ) DATA INITIALLY IMMEDIATE REFRESH DEFERRED MAINTAINED BY USER ENABLE QUERY OPTIMIZATION |
|
la commande DSPFD montre des attributs spécifiques : Attributs fichier base de données Fichier à description externe . . . . : Oui Type de fichier SQL . . . . . . . . . : TABLE DE REQUETE MATERIALISEE .../... Gérée par . . . . . . . . . . . . . . . : Utilisateur Mode de régénération . . . . . . . . . . : Différé Optimisation des requêtes activée . . . . : Oui Niveau validation de REFRESH TABLE . . . : *NONE Fichiers auxquels accède la TABLE DE REQUETE MATERIALISEE Fichier Bibliothèque VINS BDVIN9 PRODU00001 BDVIN9 Instruction CREATE TABLE DE LA TABLE DE REQUETE MATERIALISEE SQL : CREATE SUMMARY TABLE VINS_PAR_PRODUCTEUR AS ( SELECT VINS.VIN_NOM , VINS.VIN_CEPAGE1 , PRODUCTEURS.PR_NOM , PRODUCTEURS.PR_COMMUNE , PRO DUCTEURS.PR_PROPRIO FROM BDVIN9.VINS VINS RIGHT OUTER JOIN BDVIN9.PR ODU00001 PRODUCTEURS ON PRODUCTEURS.PR_CODE = VINS.PR_CODE ) DATA IN ITIALLY IMMEDIATE REFRESH DEFERRED MAINTAINED BY USER ENABLE QUERY O PTIMIZATION |
|
La table peut etre manipulée, des enregistrements insérés ou détruits, mais quand vous passez l'ordre : ............................................................................ : Entrée d'instructions SQL : : : : Saisissez l'instruction SQL, puis appuyez sur ENTREE. : : > refresh table bdvin9/vins_par_producteur : : Instruction REFRESH TABLE terminée. : : ===> : :..........................................................................: la table reprend son contenu initial. il existe de nouvelles colonnes dans SYSTABLES pour les tables matérialisées En cours de version V5R30 (DataBase group niveau 4) et avec les PTF suivantes : SI17164, SI17609, SI17610, SI17611, SI17637 et MF34848, I5/OS est capable d'utiliser une MQT (Materialized Query Table) dans un but d'optimisation |
|
Attention, seul SQE sait utiliser les tables matérialisées, il ne faut donc pas que votre requete utilise des options traitées uniquement par CQE (comme LIKE, l'utilisaiton des BLOB, etc ...) il faut une requete - portant sur un seule table ou avec INNER JOIN - sans UNION - sans User Defined Function (UDF) ni UDT - sans sous sélection - sans certaines fonctions du langage comme SOUNDEX, DIFFERENCE, DECRYPT_xx, INSERT, REPEAT, REPLACE, DAYNAME, MONTHNAME. L'utilisation des tables matérialisée est une option, ce n'est pas le standart, il faut le demander en placant dans QAQQINI : MATERIALIZED_QUERY_TABLE_USAGE = *ALL MATERIALIZED_QUERY_TABLE_REFRESH_AGE = *ANY (ou une durée sous la forme d'un timestamp AAAAMMJJHHMMSS) il faut d'autre part que la TABLE a été crée avec ENABLED QUERY OPTIMIZATION et que le paramètre ALWCPYDTA soit à *OPTIMIZE (par défaut sous ODBC/JDBC) |
|
Le rafraichissement de la table matérialisée est à votre charge Si vous demandez dans votre requete, une colonne ne se trouvant pas dans MQT, elle ne peut pas etre utilisée. une seule table MQT par requete C'est a vous de faire le rapport en entre les avantages : - gain de temps lors de la requete, optimisation transparante. et les inconvénients - temps de rafraichisement des données (la MQT n'est PAS maintenue par le système) - affichage de données obsolètes, suivant le dernier REFRESH. (sauf à utiliser MATERIALIZED_QUERY_TABLE_REFRESH_AGE) Si la table materialisée est utilisée, c'est elle qui sera montrée par Visual Explain et vous verrez son nom dans la log dans le message CPI4329 (si vous etes en debug) |
|
SEQUENCE il s'agit d'un nouveau type d'objet pour SQL (en fait une Data Area) permettant de mémoriser à l'extérieur d'une table, une suite numérique. CREATE SEQUENCE nom AS type-numérique (sans décimale) -----> --------------------------------------------------------------> ! ! ! ! ! ! !-START WITH s-! !- INCREMENT BY i-! !-MINVALUE mini-! !- CYCLE --! !--CACHE--! !-ORDER---! ---------------------! !--- !--! !--. ! ! !-NOCYCLE--! !-NOCACHE-! !-NOORDER-! !-MAX VALUE maxi-! STARTWITH, MINVALUE/MAXVALUE indiquent les valeurs de départ,mini et maxi. avec CYCLE, quand on arrive à "maxi" on boucle, sinon on génère une erreur avec CACHE le système garde en mémoire les dernière valeurs (conseillé) |
|
l'ordre ALTER SEQUENCE permet de modifier une séquence existante (même paramètres que CREATE SEQUENCE) Une séquence (en plus de la Data Area) est enregistrée dans SYSSEQOBJECTS. Utilisation Dans un ordre SELECT, INSERT, UPDATE, SET (programmation), utiliser : NEXT VALUE FOR nom-séquence PREVIOUS VALUE FOR nom-séquence ex: INSERT into t1 VALUES(NEXT VALUE FOR sequence1, 'libellé', ...) NEXT VALUE génère une nouvelle valeur (pas de retour arrière possible, même en cas d'erreur ou avec ROLLBACK) si vous placez plusieurs fois NEXT VALUE dans un même ordre SQL, une seule valeur est générée par ligne manipulée. (si vous renseigner deux zones par ligne) |
|
PREVIOUS VALUE retrouve la dernière valeur générée, sans modifier la séquence. .......................................................................... : Attention en convention d'appellation *SYS, il ne faut pas qualifier : : (c'est une restriction de CETTE version), sinon vous recevez SQL0204. : : : : la qualification ne fonctionne qu'en convention d'appellation *SQL : : : : voir l'APAR SE17443, qui décrit ce problème : :........................................................................: Programmation : il est possible d'utiliser une DS avec Insert et Update INSERT INTO fichier VALUES(:dsfichier) UPDATE fichier set ROW = :dsfichier where macle = :macle et, l'utilisation de DS qualifiée (RPG4) est admise dans les ordres SQL Par exemple : Select count(*) into maDS.maZone from vins |
|
Dernier point concernant les procédures cataloguées : 1/ une procédure cataloguée externe peut faire référence à une procédure (au sens ILE) d'un programme de service 2/ si la procédure retourne un jeu d'enregistrements (RESULT SET) (curseur ouvert ou DS à occurences), ils peuvent etre multiples L'intéret d'une procédure cataloguée retournant un curseur peut être : - l'adoption de droits -le retour de "RESULT SETS" multiples (curseur et/ou ARRAY) il faut alors indiqué lors du CREATE PROCEDURE DYNAMIC RESULT SETS x si > à 1 les RESULT SET sont retrouvé dans l'ordre ou ils sont générés |
|
3/ pour un curseur, il faut indiquer lors de la déclaration --CLIENT-- ----WITH RETURN TO -------- ! --CALLER-- ! --- WITH HOLD---- >- ! !--- --- !--WITHOUT RETURN--------------------! --WITHOUT HOLD--- WITH HOLD, le curseur est maintenu lors d'un commit, sinon il disparait WITH RETURN TO CLIENT, le curseur ouvert est retourné à l'application client, sinon au pgm appellant (call d'appels en cascade) si le curseur est SCROLL (dynamique), seuls les lignes non lues par FETCH, sont retournées. ATTENTION, avec les PTF SI15978, SI15979 ET SI15424 LA VALEUR PAR DÉFAUT EST RETURN TO CALLER |
|
l'ordre SET RESULT SETS possède lui aussi la clause --CLIENT-- FOR RETURN TO -- --- . --CALLER-- qui indique si le curseur ou le pseudo-curseur (ARRAY) doit etre retourné à l'application cliente ou au programme appellant. la différence est importante quand on appelle un CL, qui lui meme appelle un RPG (par exemple) retournant une DS à occurence. la valeur par défaut est RETURN TO CALLER, ce qui fait qu'un programme qui fonctionnait en V5R2 ne fonctionne plus en V5R3 sauf à etre recompilé avec : C/EXEC SQL C+ SET RESULT SETS ARRAY :DS1 FOR :X ROWS FOR RETURN TO CLIENT C/END-EXEC La doc indique WITH RETURN (comme sur un curseur) c'est bien FOR RETURN |
|
Trigger INSTEAD OF Les PTF SI17399 et SI17434 ainsi que le group database (SF99503) niveau 4 ammènent a DB2/400 une fonctionnalité bien interessante: Les trigegrs INSTEAD OF, sur des vues non modifiables, chargés de répercuter la mise à jour, la vue devenant modifiable. Vues non modifiables - jointure - contenant une fonction (substr , données encryptées, ...) Imaginons une vue montrant les 10 premiers caracteres d' un texte de 50 CREATE VIEW vue1 AS ( SELECT cours, SUBSTR(texte , 1 , 10) as TXT10 from fichiercours) la vue n'est pas modifiable à cause de la fonction SUBSTR |
|
Le trigger suivant la rend modifiable : CREATE TRIGGER trigger1 INSTEAD OF UPDATE ON vue1 REFERENCING old row as O new row as N FOR EACH ROW MODE DB2ROW BEGIN UPDATE fichiercours set cours = N.cours, texte = N.TXT10 concat SUBSTR(texte, 11 , 40) where cours = O.cours; END Si tant est que cela ait du sens de ne modifier que les 10 premiers caractères du texte sans toucher au 40 autres... Nous pourrions imaginer la meme chose pour une vue avec jointure sur deux tables avec un trigger répercutant la mise à jour dans chacune des tables. Les options suivantes ne sont pas admises sur le trigger : - OF colonne (pour un trigger UPDATE) - FOR EACH STATEMENT - clause WHEN |
