| 
La version 5.20 de l'OS/400 proposait de réaliser une requête sur le résultat d'une requête par la clause WITH. par exemple, sur une base vinicole : SELECT pr_code, count(*) from vins group by pr_code donne le nombre de vins produit par producteur mais impossible d'obtenir la moyenne générale avg(count(*)) étant refusé ! WITH temp AS ( select pr_code, count(*) nbr from vins group by pr_code ) select avg(nbr) from temp donne la moyenne générale (en une seule passe) nous pouvons alors demander la liste des producteurs > à la moyenne, par WITH temp AS ( select pr_code, count(*) nbr from vins group by pr_code ) select * from temp where nbr > (select avg(nbr) from temp) |
|
la clause WITH n'acceptait qu'un select simple (sans UNION et ORDER BY) ce n'est plus le cas en V5R40 par exemple : WITH temp as ( select pr_code, count(*) as nbr from bdvin1/vins group by pr_code union select pr_code , 0 as nbr from bdvin1/producteurs p where not exists (select * from bdvin1/vins where pr_code = p.pr_code) ) SELECT avg(nbr) from temp fait chuter la moyenne puisque cela inclut les producteurs n'ayant pas de vin enregistré dans la base. |
|
Si vous avez placé un order by dans la CTE (Common TABLE Expression), soit la table intermédiaire, matérialisée par la clause WITH, vous pouvez demander à ce que ce critère de tri soit respecté dans la requête finale par ORDER BY ORDER OF (le nom temporaire) par exemple : WITH temp AS ( select pr_code, count(*) nbr from vins group by pr_code order by nbr) select * from temp JOIN producteurs using (PR_CODE) where nbr > (select avg(nbr) from temp) ORDER BY ORDER OF temp, PR_NOM classe le résultat par nbr (comme temp) PUIS par nom. Vous remarquerez aussi que la clause ORDER BY accepte maintenant les ALIAS de colonne Avant il fallait répeter count(*) ou bien indiquer ORDER BY 2 |
|
Deux fonctions très utiles pour les statistiques et le décisionnel (OLAP): ROW_NUMBER() numérote les lignes sur un critère de tri with temp as ( select pr_code, count(*) as nbr from bdvin1/vins group by pr_code ) select row_number() over (order by nbr desc) , nbr , pr_code from temp Affiche : ROW_NUMBER NBR PR_CODE 1 26 2.432 2 26 2.455 3 22 2.486 4 20 2.547 5 20 6.390 6 19 2.461 7 19 4.321 8 19 6.343 |
|
RANK() attribue un rang non consécutif (en gérant les ex-aequo) DENSE_RANK() attribue un rang consécutif with temp as ( select pr_code, count(*) as nbr from bdvin1/vins group by pr_code ) select rank() over (order by nbr desc) , nbr , pr_code from temp ! pour infos : ! Affiche : RANK NBR PR_CODE ! DENSE_RANK 1 26 2.432 ! 1 1 26 2.455 ! 1 3 22 2.486 ! 2 4 20 2.547 ! 3 4 20 6.390 ! 3 6 19 2.461 ! 4 6 19 4.321 ! 4 6 19 6.343 ! 4 |
|
Dans tous les cas, le critère de tri à l'affichage (ORDER BY) peut être différent du critère indiqué dans OVER. WITH temp as ( select pr_code, count(*) as nbr from bdvin1/vins group by pr_code ) SELECT rank() over (order by nbr desc) , nbr, pr_code from temp ORDER BY PR_CODE Affiche RANK NBR PR_CODE 5.065 1 1 2.582 3 2 3.506 2 3 5.065 1 4 5.065 1 5 5.065 1 6 rappel: le même rang est attribué à toutes les lignes ayant la même valeur. |
|
Requêtes récursives -------------------- Soit le fichier suivant : FILS PERE LIBELLE 01 00 LIB N°1 02 01 LIB N°2 03 02 LIB N°3 04 00 LIB N°4 05 00 LIB N°5 06 03 LIB N°6 07 00 LIB N°7 08 00 LIB N°8 09 00 LIB N°9 10 00 LIB N°10 11 09 LIB N°11 12 07 LIB N°12 13 01 LIB N°13 14 12 LIB N°14 15 14 LIB N°15 |
|
Nous voulons afficher la ligne 'LIB N°1' et toutes les lignes dépendantes: Il faut, dans une clause WITH, afficher la ligne concernée, puis placer en dessous avec UNION ALL, le résultat de la jointure entre elle même et le fichier des liens. WITH temp (NIVEAU, FILS, PERE, LIBELLE) as (select 1 , fils, pere, libelle from TBliens where libelle = 'LIB N°1' UNION ALL select P.niveau+1 , F.fils, P.fils , f.libelle from temp P join TBliens F on F.pere=P.fils ) SELECT * FROM TEMP Affiche : NIVEAU FILS PERE LIBELLE 1 01 00 LIB N°1 2 02 01 LIB N°2 2 13 01 LIB N°13 3 03 02 LIB N°3 4 06 03 LIB N°6 |
|
WITH temp (NIVEAU, FILS, PERE, LIBELLE) as ( select 1 , fils, pere, libelle from TBliens where pere = '00' UNION ALL select P.niveau+1 , F.fils, P.fils , f.libelle from temp P join TBliens F on F.pere=P.fils ) SELECT * FROM TEMP , affiche l'arborescence complète : NIVEAU FILS PERE LIBELLE 1 01 00 LIB N°1 1 04 00 LIB N°4 1 05 00 LIB N°5 1 07 00 LIB N°7 1 08 00 LIB N°8 1 09 00 LIB N°9 1 10 00 LIB N°10 2 02 01 LIB N°2 2 13 01 LIB N°13 2 12 07 LIB N°12 2 11 09 LIB N°11 3 03 02 LIB N°3 3 14 12 LIB N°14 4 06 03 LIB N°6 4 15 14 LIB N°15 |
|
Vous remarquerez que SQL traite le liens dans l'ordre ou ils apparaissent, c'est a dire dans l'ordre des niveaux Si vous souhaitez faire apparaitre toute la profondeur d'une branche (tous les fils de '01') avant de passer à la branche suivante, ajoutez : SEARCH DEPTH FIRST BY (un nom de colonne) SET nouvelle-colonne Indiquez 1/ un nom de colonne dont il faut analyser TOUTE la profondeur 2/ un nom de colonne virtuelle à créér, ce flag interne sert à SQL pour savoir s'il a déja traité une ligne et DOIT être le critère de tri (ORDER BY) sur la requête finale WITH temp (NIVEAU, FILS, PERE, LIBELLE) as (select 1 , fils, pere, libelle from TBliens where pere = '00' UNION ALL select P.niveau+1 , F.fils, P.fils , f.libelle from temp P join TBliens F on F.pere=P.fils ) SEARCH DEPTH FIRST BY fils SET tri Select * From Temp ORDER BY tri |
|
NIVEAU FILS PERE LIBELLE 1 01 00 LIB N°1 2 02 01 LIB N°2 3 03 02 LIB N°3 4 06 03 LIB N°6 2 13 01 LIB N°13 1 04 00 LIB N°4 1 05 00 LIB N°5 1 07 00 LIB N°7 2 12 07 LIB N°12 3 14 12 LIB N°14 4 15 14 LIB N°15 1 08 00 LIB N°8 1 09 00 LIB N°9 2 11 09 LIB N°11 1 10 00 LIB N°10 Enfin, la clause CYCLE , évite les boucles infinies (si les données le permettaient) |
|
Imaginons notre fichier comme contenant des groupes de travail
'01' est le responsable d'un groupe de travail, auquel participe
indirectement '06', qui est lui même le responsable d'un groupe
ou se trouve '00' (le grand chef) ==> AIE, AIE, on boucle.
INSERT INTO TBLIENS values('00', '06', 'on boucle')
--> sur une session 5250, il faut interrompre par appel système/2 !
pour eviter cela
CYCLE (nom de colonne) SET vartemp = valeur1 DEFAULT valeur0
quand SQL va se rendre compte qu'il boucle (une ligne déja vue)
il va attribuer à vartemp (nouvelle variable interne) la valeur valeur1
(dans les autres cas, elle contient valeur0)
la ligne va quand même être affichée, mais la boucle se termine
|
|
Exemple avec : WITH temp (NIVEAU, FILS, PERE, LIBELLE) as ( toujours le même select ... ) SEARCH DEPTH first by fils SET tri CYCLE FILS SET flag to '1' DEFAULT '0' Select FLAG, niveau , fils, pere, libelle FROM temp ORDER BY tri FLAG NIVEAU FILS PERE LIBELLE 0 1 01 00 LIB N°1 0 2 02 01 LIB N°2 0 3 03 02 LIB N°3 0 4 06 03 LIB N°6 0 5 00 06 on boucle 1 6 01 00 LIB N°1 <== déja vu ! 0 6 04 00 LIB N°4 0 6 05 00 LIB N°5 0 6 07 00 LIB N°7 A vous donc de rajouter la CLAUSE CYCLE si vous craignez que les données puissent engendrer des boucles sans fin. |
|
ATTENTION, ces nouvelles fonctions : + fonctions OLAP (rank(), row_number(), etc..) + requêtes recursives + ORDER OF dans ORDER BY + les sous sélections contenant des SELECTS complets (avec UNION ou ORDER BY) + ainsi que les opérateurs EXCEPT et INTERSECT (V5R30) ne sont implémentées que par SQE (le nouveau moteur, disponible en V5R20) les cas suivants impliquent une utilisation de CQE (l'ancien moteur) : -> l'utilisation de TRANSLATE, UPPER/UCASE, LOWER/LCASE -> un tri basé sur STRSEQ(*LANGIDUNQ) ou STRSEQ(*LANGIDSHR) -> l'utilisation de fonctions TABLE (UDTF) -> l'utilisation de logiques dans la clause FROM -> l'utilisation de tables ayant des logiques avec select/omit (sauf à utiliser IGNORE_DERIVED_INDEX dans QAQQINI) SQE prend à sa charge en V5R40 l'utilisation de LIKE (pas en V5R30) |
|
par exemple, la requête suivante : ---------------------------------- WITH temp (NIVEAU, FILS, PERE, LIBELLE) as (select 1 , fils, pere, libelle from TBliens where lower(libelle) = 'lib n°1' UNION ALL select P.niveau+1 , F.fils, P.fils , f.libelle from temp P join TBliens F on F.pere=P.fils ) Select * From Temp génère le code erreur SQL0255 : Fonction incompatible avec la requête. prévoyant 9 codes raison. le notre est le 5 -- Code 5 -- Une expression récursive de table commune n'est pas compatible avec cette requête. |
|
Autre nouveautés :
-nouvelles fonctions
LAST_DAY(date) retourne la date correspondant au dernier jour du mois
par exemple LAST_DAY('2006-04-21') = 2006-04-30
ADD_MONTHS(date, nbr) ajoute un nombre de mois à la date
Attention, si la date est au dernier jour du mois, la date
calculée est aussi au dernier jour du mois
par exemple DATE('2006-04-30) + 1 months = 2006-05-30
ADD_MONTHS('2006-04-30' , 1) = 2006-05-31
GENERATE_UNIQUE() genère une valeur unique de type CHAR(13)
(basée sur le timestamp en cours)
la fonction TIMESTAMP() peut-être utilisée pour
convertir en clair la valeur générée.
|
|
NEXT_DAY(date , 'JOUR')
retourne le timestamp de la prochaine date ayant le
jour demandé (sur 3 c ou 10c.) à partir de "date".
valeurs admises :
'MON' ou 'LUN' ou 'LUNDI' pour le prochain Lundi
'TUE' ou 'MAR' ou 'MARDI', prochain Mardi
'WED' ou 'MER' ou 'MERCREDI', prochain Mercredi
'THU' ou 'JEU' ou 'JEUDI', prochain Jeudi
'FRI' ou 'VEN' ou 'VENDREDI', prochain Vendredi
'SAT' ou 'SAM' ou 'SAMEDI', prochain Samedi
'SUN' ou 'DIM' ou 'DIMANCHE', prochain Dimanche
Exemple :
NEXT_DAY('2006-12-31' , 'DIM') => ' 2007-01-07-00.00.00.000000'
|
|
ENCRYPT_TDES(data , pwd, astuce) comme ENCRYPT_RC2 mais en utilisant le cryptage "Triple DES" la valeur est retournée par DECRYPT_BIT, DECRYPT_BINARY, DECRYPT_DB et DECRYPT_CHAR, comme ENCRYPT_RC2 RAISE_ERROR(sqlstate, 'diagnostic') permet de signaler une erreur dans un trigger ou une procédure calatoguée. SQLSTATE ne DOIT PAS commencer par '00', '01' et '02' qui sont réservés à SQL. VARCHAR_FORMAT(timestamp 'format') Transforme un timestamp en chaine avec choix du format le seul (!) format admis est 'YYYY-MM-DD HH24:MI:SS' la fonction TO_CHAR() est un alias et rend le même service. |
|
-la fonction TIMESTAMP admet 'YYYY-MM-DD HH:MM:SS.000000' et
'YYYY-MM-DD HH:MM:SS' comme format (avant 'YYYY-MM-DD.HH.MM.SS.000000')
-les valeurs peuvent être comparées en ligne dans une clause WHERE
exemple :
select * from vins where (cepage1, cepage2) = ('Syrah' , 'Grenache')
ce qui va permettre d'écrire (sans la clause EXISTS)
select * from clients where (societe, client) NOT IN
(select societe, client from commande)
Avant la V5R40, il était préféréable d'utiliser :
--------------
select * from clients c where NOT EXISTS (select * from commande where
societe = c.societe and client = c.client), qui reste vrai!
-L'ordre CREATE INDEX admet un nouveau paramètre PAGESIZE( en Ko)
(correspond au nouveau paramètre PAGESIZE() de CRTLF)
|
|
-L'ordre CREATE TABLE admet un nouveau paramètre RCDFMT, permettant d'indiquer un nom de format : create table client (nocli integer, nom char(35) ) RCDFMT clientf1 -Lors d'une lecture avec choix du niveau d'isolation RS ou RR RS = Read Serialisable => Les lignes lues et manipulées sont verouillées jusqu'au COMMIT (seules les lignes sans verrouillage sont lues) RR = Repetable read => Les lignes lues et manipulées sont verouillées jusqu'au COMMIT les tables sont allouées (insertion impossible) jusqu'au COMMIT. vous pouvez ajouter maintenant USE AND KEEP EXCLUSIVE LOCKS le verrouillage de chaque ligne passe de "lecture" à "mise à jour" |
|
- LABEL ON INDEX est nouveau et change le texte de l'objet. - limites : un nom de colonne passe de 30 à 128c. le nombre de tables dans une requete passe de 256 à 1000 ! (cela reste 256 dans une vue) la taille d'une requête passe de 65 Ko à 2 Go. - meilleurs temps de réponse des routines SQL PSM (il faut les recréer) - nouveautés Procédures cataloguées : ALTER PROCEDURE permet de changer le paramétrage d'une procédure cataloguée sans la détruire. SET CURRENT DEBUG MODE = ALLOW (on peut faire du Debug) DISALLOW (on ne peut pas, mais on peut changer ce paramètre, par ALTER PROCEDURE) DISABLED (on ne pourra jamais faire de Debug) SET SESSION AUTHORISATION pour changer le profil actif du thread en cours. SET CURRENT DEGREE = idem à CHGQREYA ou PARALLEL_DEGRE dans QAQQINI |
|
Le fichier QAQQINI admet un nouveau paramètre STORAGE_LIMIT qui vient compléter QUERY_TIME_LIMIT. ces deux paramètres sont aussi présents sur la commande CHGQRYA QRYTIMLMT permet de fixer un temps de traitement maxi QRYSTGLMT permet de fixer une taille temporaire à ne pas dépasser un nouveau point d'exit QIBM_QQQ_QUERY_GOVR vient compléter le tableau. si vous associez un pgm à ce point d'exit, il est appellé à chaque fois qu'une limite est dépassée. Votre programme recevera des informations système (format QRYG0100) contenant (entre autre) - le temps prévu et le temps maxi autorisé - la taille mémoire nécessaire et la taille maxi - la requête elle même... et devra indiquer si la requête doit être annulée ou non |
|
AU cas ou en tel programme n'existe pas ou s'il ne se prononce pas, vous recevez CPA4259. Complément d'informations sur message ID message . . . . . . : CPA4259 Date d'envoi . . . . . : 18/04/06 Heure d'envoi . . . . : 17:45:0 Message . . . . : La requête excède la limite de durée ou de mémoire défini (C I) Cause . . . . . : La requête de base de données qui allait démarrer a un durée d'exécution estimée à 1, ce qui excède la limite indiquée 21473525 ou son utilisation de mémoire temporaire de 1 dépasse la limite spécifiée Les limites de durée de requête et de mémoire temporaire sont définies via la commande CL CHGQRYA. Que faire . . . : Tapez C pour annuler l'exécution de la requête ou I pour poursuivre. Réponses possibles : C -- Annuler l'exécution de la requête. I -- Ignorer la limite et exécuter la requête. |
|
En demandant la suite du message, vous verrez : Complément d'informations sur message ID message . . . . . . : CPA4259 Date d'envoi . . . . . : 18/04/06 Heure d'envoi . . . . : 17:45:0 Technical description . . . . . . . . : The following information explain the type of access plan used by the query. This information is similar to that which can be printed using the PRTSQLINF CL command. PCLINUX QSECOFR 169716 Dernière sauvegarde du plan d'accès le 18/04/06 à 17:45:00. Substitution attributs de requête par options de requête du fichier QAQQINI de QUSRSYS. ODP réutilisable de tri utilisé. Réalisation de la requête pour position de jointure 1, table 1. Index VINS utilisé pour la table 1. Accès par index seulement utilisé sur la table 1. Réalisation de la requête pour position de jointure 2, table 1. Index VINS utilisé pour la table 1. Accès par index seulement utilisé sur la table 1. Tous les index ont été examinés pour la table 2. |
