| 
Nouveautés liées à DB2 en 7.1 - Support des champs de type tableau, dans les procédures SQL uniquement. ou en tant que paramètre CREATE TYPE tva AS dec(4.2) ARRAY[4] - Nouvelle fonction d'agrégation produisant des tableaux SELECT ARRAY_AGG(taux) into :tva from factures (GROUP BY possible) - nouvelles fonctions de manipulation des tableaux en SQL PSM CARDINALITY(nom-tableau) -> retourne le nombre d'éléments MAX_CARDINALITY(nom-tableau) -> retourne le nombre maximum d'éléments TRIM_ARRAY(nom-tableau, num) -> assigne au tableau "num" occurences sous la forme: SET tbtva = TRIM_ARRAY(tbtva, 2) -- 2 occurences |
|
- UNNEST désérialise un tableau en suite de valeur à n colonnes Cette fonction est utilisable dans un SELECT. Exemple : CREATE TYPE intArray AS INTEGER ARRAY[100] CREATE TYPE stringArray AS VARCHAR(10) ARRAY[100] CREATE PROCEDURE crtperso() BEGIN DECLARE matricules intArray; DECLARE noms stringArray; SET matricules = ARRAY[1 , 2 , 3]; SET noms = ARRAY['Denise', 'Michel', 'Sue']; INSERT INTO personp1(id, name) (SELECT t.mat, t.nom FROM UNNEST(matricules, noms) AS t(mat, nom)); |
|
- support des noms qualifiés en 3 parties en dénomination SQL select * from as400.bdvin1.vins --> table ou fichier physique ! ! ! !-Bibliothèque ! !--> nom DRDA de Remote Database (voir WRKRDBDIRE) > cette possibilité n'est offert qu'en appellation SQL (cf NAMING) > il faut que le connexion soit implicite (ADDSVRAUTE ou EIM) - avec le niveau 15 de SF99701 vous pouvez utiliser indiferement un "/" ou un "." (donc une qualification "remote") en convention d'appellation système - avec le niveau 18 un peut insérer dans une table locale le résultat d'un Select remote insert into tablelocale ( select * from nomdebase.nomdebib.nomdetable where xxxx) |
|
- choix de l'emplacement de la nouvelle colonne lors d'un ajout de colonne ALTER TABLE ... BEFORE nom-de-colonne ex : ALTER TABLE clients ADD COLUMN sms CHAR(1) BEFORE mobile - Nouvelles fonctions BITAND BITANDNOT BITOR BITXOR BITNOT (identiques aux fonctions intégrées de même nom en RPG) - nouvelle instruction MERGE |
|
MERGE INTO cible C USING (SELECT zone1 , zone2 FROM source) S ON (C.zonecle = S.zonecle) WHEN MATCHED THEN UPDATE SET zone2 = S.zone2 WHEN NOT MATCHED THEN INSERT (zone1, zone2) VALUES(s.zone1, s.zone2) les clauses WHEN peuvent être complétées par des tests de variables Exemple : |
|
la table des PAYS contient deux champs pays_code integer, pays char(20) créons AUTREPAYS CREATE TABLE BDVIN1/AUTRESPAYS (PAYS_CODE INTEGER , PAYS CHAR (20) ) MERGE into bdvin1/autrespays ap USING( select pays_code, pays from bdvin1/pays) p on (ap.pays_code = p.pays_code) when matched then update set (pays_code, pays, flag) = (p.pays_code, p.pays, 0) when not matched then insert (pays_code, pays, flag) values(p.pays_code, p.pays, 1) cet ordre SQL insert les enregistrements qui existent dans PAYS et pas dans AUTRESPAYS (sur le code pays) et met à jour ceux qui existent déjà. mais on peut faire encore plus complexe : |
|
MERGE into bdvin1/autrespays ap USING( select pays_code, pays from bdvin1/pays) p on (ap.pays_code = p.pays_code) when matched AND p.pays_code = 0 then DELETE when matched AND p.pays_code <=9 then UPDATE set (pays_code, pays, flag) = (p.pays_code, p.pays, 2) when matched AND p.pays_code > 9 then UPDATE set (pays_code, pays, flag) = (p.pays_code, p.pays, 3) when not matched then INSERT (pays_code, pays, flag) values(p.pays_code, p.pays, 1) ELSE IGNORE le table cible (AUTRESPAYS dans notre exemple) peut être une vue. |
|
on peut derrière THEN signaler une erreur SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'le pays ne peut pas être modifié' l'instruction peut être completée par : ATOMIC si une opération delete, update ou insert signale une erreur la totalité des opérations est annulée (ROLLBACK) NOT ATOMIC on ne revient pas sur les lignes impactées STOP ON SQL EXCEPTION l'instruction MERGE s'arrête à la première erreur CONTINUE ON SQL EXCEPTION l'instruction MERGE se poursuit malgrès les erreurs rencontrées. |
|
Support du type XML et des fonctions suivantes (voir http://www.volubis.fr/af4dir/courshtm/XML/SQLXML.htm ) XMLDOCUMENT production d'un flux XML à partir d'une chaine de caractère XMLPARSE production après vérification, d'un flux XML XMLVALIDATE validation d'un flux XML à l'aide d'un schéma XSD XMLTRANSFORM transforme un flux XML à l'aide de XSLT XMTEXT production d'un texte compatible XML XMLELEMENT production d'un élément XML XMLATTRIBUTES production d'un attribut XML XMLNAMESPACES production d'un balise d'espace de nommage XMLPLI production d'une balise processing instruction XMLCOMMENT production d'un commentaire XML XMLCONCAT production d'un flux XML à partir de deux XMLFOREST production d'une suite d'élements XML à partir des colonnes d'une table XMLROW production d'une ligne XML à partir des colonnes d'une table fonctions d'agrégation (récapitulatives) XMLAGG production d'une série d'éléments XML XMLGROUP production d'un flux XML valide. |
|
Fonctions de lecture des fichiers de l'IFS: GET_BLOB_FROM_FILE(chemin , option) retourne un BLOB LOCATOR, sans conversion du CCSID GET_CLOB_FROM_FILE(chemin , option) retourne un CLOB LOCATOR dans le CCSID du job GET_DBCLOB_FROM_FILE(chemin , option) retourne un DBCLOB LOCATOR dans le CCSID DBCS par défaut GET_XML_FILE(chemin) retourne un BLOB LOCATOR en UTF-8, si ce dernier ne possède pas de déclaration XML la fonction l'ajoute. la zone option peut contenir : 0 : les espaces de droite sont conservés 1 : les espaces de droite sont ignorés Vous devez être sous commitment control pour utiliser ces fonctions |
|
EXEMPLES :
--------
pour insérer du XML dans une table ayant une zone de ce type :
INSERT INTO POSAMPLE/CUSTOMER
VALUES( 4 , GET_XML_FILE('/temp/client04.xml') )
pour lire le contenu d'un fichier IFS sous SQL
select * from (values
cast(GET_CLOB_FROM_FILE('/temp/client04.xml') as char(2000))
) as temp
En programmation :
____________________________________________________________________________
Dvariable S A len(20000) varying
/free
EXEC SQL
values cast(GET_CLOB_FROM_FILE('/annotate.log') as varchar(20000) )
into :variable;
/end-free
|
|
Variable globale on peut maintenant créer des variables globales elles sont stockées en fait dans des programmes de service (*SRVPGM) accessibles par toute personne ayant les droits sur l'objet. le contenu est propre à la session . Exemple: CREATE VARIABLE profil CHAR(10) DEFAULT 'QSECOFR' la variable PROFIL sera créé pour tous les travaux du système et contiendra QSECOFR. VALUES profil , permet de l'afficher. VALUES 'CM' INTO PROFIL (ou bien SET), change son contenu pour le job la variable est initialisée en début de job, et seul le job peut la modifier. On dit que la "portée" est limité à la session. |
|
une variable peut être utilisée dans un trigger et dans une vue : CREATE VIEW admin as (select * from mesuser where nom = PROFIL) select * from admin --> montre QSECOFR set PROFIL = 'CM' select * from admin --> montre CM une variable peut être initialisée avec une autre variable CREATE VARIABLE unprofil CHAR(10) DEFAULT PROFIL le contenu initial de la variable (DEFAULT) peut être une valeur retournée par un SELECT CREATE VARIABLE nbrdeproducteurs INTEGER DEFAULT (SELECT COUNT(*) FROM PRODUCTEURS) Créer une variable = créer un programme de service qui peut être sauvegardé et restauré.Elle est enregistrée dans SYSVARIABLES et SYSVARIABLEDEP. |
|
Nouveauté suivante : on peut saisir des expressions en tant que paramètres CALL PROC01 (SUBSTR(VARIABLE, 1, 10)) CALL PROC02 (autrevariable/2) ET, on peut récupérer le résultat (result sets) retourné par une procédure avec le scénario suivant(si le procédure ne retourne qu'un seul result set) CALL PROC03 si SQLCODE = +446 // il y a un jeu de résultat retourné ASSOCIATE LOCATORS (:RS1) WITH PROCEDURE PROC03 // :RS1 doit être déclaré SQLTYPE(RESULT_SET_LOCATOR) ALLOCATE C1 CURSOR FOR RESULT SET :RS1 |
|
Exemple : D nom_du_jour s 10 D date_du_jour s 10 D RS1 S SQLTYPE(RESULT_SET_LOCATOR) /free exec sql CALL FREE03; if SQLCODE = +466; exec sql ASSOCIATE LOCATORS (:RS1) WITH PROCEDURE FREE03; exec sql ALLOCATE C1 CURSOR FOR RESULT SET :RS1; exec sql fetch c1 into :nom_du_jour , :date_du_jour; dow sqlcode = 0; // traitement des variables lues... exec sql fetch c1 into :nom_du_jour , :date_du_jour; ENDDO; exec sql close C1; ENDIF; *inlr = *on; /end-free |
|
La version 6 avait apporté à l'ordre SELECT l'option SKIP LOCKED DATA cela permettait lors d'une lecture avec verrouillage d'enregistrement, c.a.d COMMIT à CS(*CS) ou RS(*ALL), d'ignorer les lignes verrouillées Cette option a été étendue aux instructions UPDATE et DELETE vous avez, en plus, le choix entre trois comportements : WAIT FOR OUTCOME Attendre que les lignes soient libérées (COMMIT par exemple) cela n'a pas d'effet sur les niveaux de COMMIT inférieurs à CS USE CURRENTLY COMMITTED Utiliser les valeurs déjà validées cela ne peut s'appliquer qu'aux COMMIT niveau CS (sans KEEP LOCKS) SKIP LOCKED DATA les lignes verrouillées sont ignorées. Comme cette clause est maintenant valide avec UPDATE/DELETE, elle peut être utilisée avec tous les niveaux de COMMIT, sauf RR (repeatable read) |
|
Vous pouvez en plus préciser cette option : 1/ en fin des ordres, SELECT, UPDATE, DELETE, PREPARE 2/ sur le nouveau paramètre CONACC des commandes de compilation (CRTSQLRPGI par ex.) et RUNSQLSTM (pas STRSQL) 3/ sur l'ordre CREATE PROCEDURE|CREATE FUNCTION (ou ALTER) 4/ avec SET OPTION CONACC=*CURCMT | *WAIT | *DFT dans la procédure SQL 5/ dans QAQQINI avec l'option SQL_CONCURRENT_ACCESS_RESOLUTION les ordres CREATE ALIAS, CREATE FUNCTION, CREATE PROCEDURE, CREATE VIEW CREATE SEQUENCE, CREATE VIEW possédent une clause CREATE OR REPLACE pas les ordres CREATE TABLE, CREATE INDEX (dommage !) |
|
la procédure CANCEL_SQL de QSYS2 permet d'annuler une requête
CALL QSYS2.CANCEL_SQL('123456/QUSER/QZDASOINIT');
la PTF SI363919 implémente aussi cette procédure en V6R10
Performances
------------
Global Cache Statistic (GCS)
le système stocke des statistiques par colonne de fichier depuis la V5.2
et elles peuvent être visualisées par System i Navigator.
il garde maintenant en mémoire des statistiques globales sur les dernières
requêtes effectuées, particulièrement le nombre de lignes pour jointure.
ces statistiques ne peuvent, pour l'instant, pas être visualisées.
|
|
Elles sont utilisées particulièrement par Adaptive Query Processing (AQP) nouvelle technologie permettant au moteur (SQE uniquement) de changer de plan d'accès dynamiquement. si aucune ligne n'est retournée sous 2 secondes, alors AQP compare le nombre actuel de lignes(GCS) avec le nombre estimé et peux refaire le plan et relancer la requête avec le nouveau plan. en fonction des statistiques d'utilisation des fichiers (globales ->GCS) et deux nouvelles zones RANDOM_READS et SEQUENTIAL_READS dans les fichiers statistiques SYSPARTITIONSTAT et SYSINDEXSTAT de QSYS2, les fichiers les plus fréquement utilisés seront placés automatiquement sur disque SSD (si vous en possédez) ce paramètre peut être forcé lors de la création : les ordres CREATE TABLE, CREATE INDEX, DECLARE TEMPORARY TABLE possèdent une nouvel attribut UNIT |
|
UNIT SSD, demandant à placer, de préférence, cet objet
sur un disque SSD
UNIT ANY (le standard) n'indique pas de préférence
pour connaitre la préférence -> SELECT MEDIA_PREFERENCE FROM
TABLE(QSYS2.PARTITION_STATISTICS('BIBLIO', 'TABLE') ) AS temp
retourne 0 (pas de préférence) ou 255 (préférence pour SSD)
pour connaitre la localisation actuelle -> SELECT UNIT_TYPE FROM
TABLE(QSYS2.PARTITION_DISKS('BIBLIO' , 'TABLE) AS temp
retourne 0 (disque "normal") ou 1 (disque SSD)
Un fichier pouvait déjà être chargé "manuellement" en mémoire par la
commande SETOBJACC.
vous pouvez maintenant le déclarer comme devant l'être automatiquement
à l'aide du paramètre KEEPINMEM(*YES) des commandes CHGPF / CHGLF
le pool mémoire dans lequel garder ce fichier est précisé par l'option
de QAQQINI : MEMORY_POOL_PREFERENCE
|
|
- Les index avec sélection d'enregistrement(avec une clause WHERE) sont maintenant pleinement utilisés par SQE - possibilité d'inclure des fonctions agrégées (SUM,AVG,COUNT,VAR,STDDEV) dans un index EVI. CREATE ENCODED VECTOR INDEX EVI01 ON COMMANDES (DATCMD, FAM) INCLUDE (SUM(QTE) , COUNT(*) ) Avec le groupe PTF SF99701 level 18, les requêtes utilisant GROUPING SET, ROLLUP ou CUBE, bénéficient aussi de ce type d'index. Nouvelles options de QAQQINI ---------------------------- MEMORY_POOL_PREFERENCE (voir ci dessus) |
|
ALLOW_ADAPTIVE_QUERY_PROCESSING Autorise ou non l'activation de AQP ALLOW_ARRAY_VALUE_CHANGES Indique si la modification d'un tableau est "visible" par une requête pendant son execution. DETERMINISTIC_UDF_SCOPE Si une requête est déclarée déterministe (produisant toujours le même résultat) cette déclaration peut être valable par requête ou globalement pour le système. FIELDPROC_ENCODED_COMPARISON Option de comparaison pour les zones avec FLIEDPROC SQL_CONCURRENT_ACCESS_RESOLUTION (voir plus haut dans ce cour) |
|
SQL_XML_DATA_CCSID CCSID des champs XML (voir ce cours) TEXT_SEARCH_DEFAULT_TIMEZONE décalage horaire à appliquer pour les recherche XML de champs date pour une liste complète des options de QAQQINI, voyez http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzajq/qryopt.htm rappel: vous pouvez modifier une option en appellant la procédure cataloguée OVERRIDE_QAQQINI, depuis la V6R1. -- créé un fichier QAQQINI dans QTEMP Call override_qaqqini('1' , ' ', ' ') -- modifie la valeur pour le job Call override_qaqqini('2' , 'SQL_PSEUDO_CLOSE' , '05') -- détruit le fichier de QTEMP, retour à la normale Call override_qaqqini('3' , ' ', ' ') |
|
En cas d'erreur SQL imprévue (souvent erreurs internes) vous recevez SQL0901 (SQLCODE = -0901) Dans le cadre de certains serveurs d'applications ou en cas de non génération de JOBLOG (ou de ménage), il peut être difficile de retrouver ces codes erreur. Aussi SQL génère une ligne dans la table QSQ901S de QRECOVERY juste avant d'envoyer le message SQL0901 ce fichier contient : SERVERNAME : le nom du serveur FAILTIME : timestamp de l'erreur FAILRSN : raison del'erreur (à transmettre à IBM) CURUSER : utilisateur en cours JOBNAME : travail qualifié MSGS : message système APPLIB : bibliothèque de l'application APPNAME : application APPTYPE : *PGM,*SRVPGM,*SQLPKG,*PROCESS,DYNAMIC Pour ne pas générer ces traces: ADDENVVAR QIBM_NO_901_LOGGING. |
