| 
V6R10 , cette version apporte de nombreuses nouveautés à SQL. - DECFLOAT nouveau format numérique 16 ou 32 Chiffres (BigDecimal en java) à la norme IEEE 754R permettant le stockage de : valeurs décimales très grandes: 16: -9.999999999999999x10 p.384 à 9.999999999999999x10 p.384 32: -9.999999999999999999999999999999999x10 puissance 6144 à 9.999999999999999999999999999999999x10 puissance 6144 le zéro signé + et - la valeur infinie (INFINITY) positive ou négative la valeur "quiet NaN" (not a number=NAN) résultat d'un calcul invalide ne provoquant pas d'erreur la valeur "signal NaN"(SNAN) résultat d'un calcul invalide provoquant une erreur |
|
- NCHAR, NVARCHAR, NCLOB ou NATIONAL CHAR|VARCHAR|CLOB nouveaux types de donnée en UNICODE (CCSID=1200) - les zones FOR BIT DATA (CCSID=65535) sont compatibles avec le type BINARY - l'attribut : IMPLICITLY HIDDEN , lors du CREATE TABLE (NOT HIDDEN est le défaut) les zones cachées n'apparaissent pas en retour d'un SELECT * FROM... mais uniquement si vous les demandez dans la liste du SELECT - les timestamp générés automatiquement (le type TIMESTAMP est facultatif) FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP attribut ajouté à une zone TIMESTAMP NOT NULL, indique que cette colonne est modifiée avec le timestamp en cours à chaque INSERT/UPDATE Il ne peut y avoir qu'une seule colonne de ce type par table. |
|
Quand vous créez une table par copie d'une autre: CREATE TABLE T1 like (select ..) Vous pouvez préciser : EXCLUDING -- >--IMPLICITLY HIDDEN COLUMN ATTRIBUTES INCLUDING -- et EXCLUDING -- >--ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES INCLUDING -- Vous pouvez aussi indiquer sur CREATE TABLE VOLATILE indiquant à l'optimiseur que le nombre de lignes peut varier très rapidement (utilisation d'un index souhaitable) ou NOT VOLATILE |
|
et NOT LOGGED INITIALLY indiquant que la table n'est pas journalisée automatiquement sinon, la table est journalisé automatiquement dès la création. La journalisation automatique pouvant être définie par : 1/ la présence d'un journal QSQJRN 2/ la présence d'une data area QDFTJRN indiquant le nom du journal 3/ le fait d'avoir utilisé la commande STRJRNLIB sur la bibliothèque (ce dernier point est nouveau en V6R10) CREATE INDEX La création d'index subit de nombreux changements, les rendant proches des fichiers logiques (LF) |
|
A/ On admet les expressions en tant que clé
CREATE INDEX i1 on table T1 ( UPPER(NOM) as NOMMAJ )
la zone NOMMAJ est la clé de cet index.
toute requête utilisant WHERE UPPER(NOM) like '...%', utilisera
implicitement l'index en question.
l'expression ne peut pas contenir :
- des sous requêtes
- des fonctions agrégées (COUNT, AVG, SUM, etc ...)
- des fonctions NOT DETERMINISTIC (dont le résultat varie), comme
ATAN2,DECRYPT_DB,ENCRYPT_TDES,RAND, CURDATE, DIFFERENCE, REPEAT
GENERATE_UNIQUE, CURTIME, DLURLCOMPLETE, GETHINT, REPLACE, INSERT
DLURLxxx , DLURLPATHONLY, IDENTITY_VAL_LOCAL ROUND_TIMESTAMP, SOUNDEX
DAYNAME, MONTHNAME, MONTH_BETWEEN, TIMESTAMP_FORMAT, TIMESTAMPDIFF
- des UDF sauf celles liées à un nouveau type de données (UDT)
- la manipulation de SEQUENCE
- des variables , des marqueurs ("?")
|
|
B/ On admet la clause WHERE sur les index : CREATE INDEX i1 on table T1 (NOM) WHERE NOM NOT LIKE 'VOL%' la clause WHERE subit les mêmes règles que les expressions C/ Vous pouvez préciser un format par la clause RCDFMT (rappel: c'est possible sur les tables depuis la V5R40) suivi de la phrase suivante : ADD ALL COLUMNS : toutes les colonnes du PF appartiennent au format ADD KEYS ONLY : seules les zones clés appartiennent au format ADD col1, col2 : ces zones font suite aux zones clés dans le format ATTENTION : la valeur par défaut est ADD KEYS ONLY et le nom de format par défaut est le nom de l'INDEX (avant l'index avait le même format que la table) |
|
Exemple récapitulatif : CREATE INDEX logi1 on HTTPLOG ( SUBSTR(virtualhost, 1 , 10) VHOST2 ) Where virtualhost IS NOT NULL RCDFMT httpfmt2 ADD host, logtime -- en plus de VHOST2 L'ordre ALTER FUNCTION est nouveau et permet de modifier les attributs d'une fonction (UDF) l'ordre LABEL ON admet en plus, à cette version : LABEL ON CONSTRAINT* FUNCTION PROCEDURE TRIGGER TYPE* tous ces types d'objets sont aussi admis par COMMENT ON. (ceux avec * sont nouveaux en V6R10) |
|
SELECT un select utilisant la clause USING pour faire sa jointure : - retourne les zones de jointure, puis les autres dans l'ordre des tables - les zones de jointure ne doivent PAS être qualifiées une nouvelle option de jointure est disponible: FULL OUTER JOIN affichant l'équivalent du SELECT suivant (soit toutes les combinaisons) SELECT * FROM T1 LEFT OUTER JOIN T2 on ... UNION SELECT * FROM T1 RIGHT EXCEPTION JOIN T2 on ... la clause SKIP LOCKED DATA est nouvelle et permet d'ignorer les lignes verrouillées. cette clause concerne toutes les tables lues cette clause ne concerne que COMMIT(*ALL ou *RS) ==> ignorée avec COMMIT(*NONE|*CHG|*RR) |
|
VALUES(val1, val2, ...) peut être utilisé à la place de SELECT
rendant possible : SELECT z1, z2, z3 FROM FICHIER WHERE ...
UNION VALUES(val1 , val2, val3)
VALUES peut être aussi utilisé simplement pour tester une fonction :
Sur la ligne de commande SQL, pour voir le résultat
Dans un TRIGGER pour voir si la fonction produit une erreur(interceptable)
On peut passer un ordre SELECT sur le résultat d'un INSERT permettant
ainsi, de retrouver facilement la valeur d'une zone IDENTITY ou d'un
TIMESTAMP, par exemple.
Exemple avec T1 possédant Z1 AS IDENTITY et Z3 de type TIMESTAMP
SELECT Z1, Z3 FROM FINAL TABLE
(INSERT INTO T1 (z2, z3) VALUES('test', now() )), affiche:
Z1 Z3
3 2008-08-07-09.04.17.455674
|
|
SI vous insérez plusieurs lignes, la clause ORDER BY admet maintenant
INPUT SEQUENCE, demandant à ce que les lignes soit triées dans
l'ordre ou elles ont été insérées
Exemple
SELECT Z1, Z3 FROM FINAL TABLE
(insert into qtemp.toto (z2, z3)
values ('test2' , now() ) ,
('test3' , now() )
)
ORDER BY INPUT SEQUENCE
Z1 Z3
4 2008-08-07-09.57.46.375829
5 2008-08-07-09.57.46.375829
la clause GROUP BY évolue beaucoup pour implémenter des fonctions OLAP
|
|
Soit un fichier CAVE et un select GROUP BY suivant : SELECT year(entreele) , cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY year(entreele) , cav_format affichant YEAR CAV_FORMAT SUM 2.006 CAISSE de 6 20,82 2.008 Bouteille 5,35 2.007 Bouteille 278,15 2.008 CAISSE de 6 15,76 2.006 Bouteille 278,78 2.007 CAISSE DE 6 28,96 Le montant des vins par année et format de stockage (un niveau de rupture) Nous allons ajouter les nouvelles clauses : GROUPING SETS ROLLUP CUBE |
|
SELECT year(entreele) , cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY GROUPING SETS (year(entreele) , cav_format) affiche le total par année, PUIS le total par format YEAR CAV_FORMAT SUM 2.008 - 21,11 2.006 - 299,60 2.007 - 307,11 - CAISSE DE 6 28,96 - CAISSE de 6 36,58 - Bouteille 562,28 Cette clause admet la syntaxe suivante: SELECT year(entreele), MONTH(entreele), cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY GROUPING SETS ( (year(entreele) , cav_format) , (year(entreele) , month(entreele)) ) |
|
YEAR MONTH CAV_FORMAT SUM 2.006 - CAISSE de 6 20,82 2.008 - Bouteille 5,35 2.007 - Bouteille 278,15 2.008 - CAISSE de 6 15,76 2.006 - Bouteille 278,78 2.007 - CAISSE DE 6 28,96 2.007 8 - 15,59 2.006 7 - 14,40 2.008 3 - 15,76 2.007 3 - 20,82 2.007 9 - 155,48 2.007 2 - 12,19 2.007 1 - 28,96 2.006 11 - 35,00 2.006 4 - 20,82 2.007 6 - 74,07 2.006 9 - 229,38 2.008 5 - 5,35 |
|
SELECT year(entreele) , cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY ROLLUP (year(entreele) , cav_format) affiche le total par année/format, puis par année, puis le total général YEAR CAV_FORMAT SUM 2.006 Bouteille 278,78 2.006 CAISSE de 6 20,82 2.006 - 299,60 2.007 Bouteille 278,15 2.007 CAISSE DE 6 28,96 2.007 - 307,11 2.008 Bouteille 5,35 2.008 CAISSE de 6 15,76 2.008 - 21,11 - - 627,82 Vous pourriez compléter le select par un ORDER BY |
|
SELECT year(entreele) , cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY CUBE (year(entreele) , cav_format) affiche tous les totaux de toutes les combinaisons (un cube, donc !) YEAR CAV_FORMAT SUM 2.006 Bouteille 278,78 2.006 CAISSE de 6 20,82 2.006 - 299,60 2.007 Bouteille 278,15 2.007 CAISSE DE 6 28,96 2.007 - 307,11 2.008 Bouteille 5,35 2.008 CAISSE de 6 15,76 2.008 - 21,11 - - 627,82 - CAISSE DE 6 28,96 - CAISSE de 6 36,58 - Bouteille 562,28 |
|
Enfin, vous pouvez utiliser la syntaxe suivante : SELECT year(entreele) , month(entreele), cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY GROUPING SETS( ROLLUP(year(cav_entreele) , cav_format) , ROLLUP(year(cav_entreele), month(cav_entreele)) ) affiche des totaux par année/format, contenant des sous totaux par année et un total général(ROLLUP), puis la même chose pour le couple année/mois YEAR MONTH CAV_FORMAT SUM 2.006 - Bouteille 278,78 2.006 - CAISSE de 6 20,82 2.006 - - 299,60 2.007 - Bouteille 278,15 2.007 - CAISSE DE 6 28,96 2.007 - - 307,11 2.008 - Bouteille 5,35 2.008 - CAISSE de 6 15,76 2.008 - - 21,11 - - - 627,82 |
|
2.006 4 - 20,82 2.006 7 - 14,40 2.006 9 - 229,38 2.006 11 - 35,00 2.006 - - 299,60 2.007 1 - 28,96 2.007 2 - 12,19 2.007 3 - 20,82 2.007 6 - 74,07 2.007 8 - 15,59 2.007 9 - 155,48 2.007 - - 307,11 2.008 3 - 15,76 2.008 5 - 5,35 2.008 - - 21,11 - - - 627,82 Liste des nouvelles fonctions GROUPING(col) indique si la ligne en cours est une ligne de regroupement concernant cette colonne (information utile en programmation) |
|
Exemple SELECT year(entreele) , cav_format, sum(prxactuel) , GROUPING(cav_format) FROM ma_cave join vins using(vin_code) GROUP BY CUBE (year(entreele) , cav_format) YEAR CAV_FORMAT SUM GROUPING 2.006 Bouteille 278,78 0 2.006 CAISSE de 6 20,82 0 2.006 - 299,60 1 2.007 Bouteille 278,15 0 2.007 CAISSE DE 6 28,96 0 2.007 - 307,11 1 2.008 Bouteille 5,35 0 2.008 CAISSE de 6 15,76 0 2.008 - 21,11 1 - - 627,82 1 - CAISSE DE 6 28,96 0 - CAISSE de 6 36,58 0 - Bouteille 562,28 0 |
|
Autres fonctions toutes les fonctions manipulant des DECFLOAT utilisent SET CURRENT DECFLOAT ROUNDING MODE pour la gestion des arrondis - ROUND_CEILING : arrondi à +1 si positif - ROUND_DOWN : arrondi inférieur (troncature) - ROUND_FLOOR : arrondi à -1 si négatif - ROUND_HALF_DOWN : arrondi comptable (si >0,5) - ROUND_HALF_EVEN : arrondi au chiffre pair supérieur - ROUND_HALF_UP : arrondi comptable (si >=0,5) - ROUND_DOWN : arrondi supérieur (+1) DECFLOAT() convertit une données au nouveau format DECFLOAT COMPARE_DECFLOAT() compare deux DECFLOAT (gestion des valeurs indéfinies) QUANTIZE(D, E) produit un DECFLOAT avec D comme partie décimale et E comme exposant (puissance de 10) NORMALIZE_DECFLOAT affiche un DECFLOAT à sa forme la plus simple ex : NORMALIZE_DECFLOAT(DECFLOAT(-120)) = -1.2E+2 |
|
TOTALORDER(a1 , a2) retourne -1 si a1<a2, 0 si a1=a2 , 1 si a1>a2
suivant le critère de tri qui suit:
-NAN<-SNAN<-INFINITY<-0.10<-0.100<-0<0<0.100<0.10<INFINITY<SNAN<NAN
DECFLOAT_SORTKEY() convertit un DECFLOAT en zone numérique pouvant
être un critère de tri (ORDER BY)
RID() retourne le N° de rang au format integer [ RRN = DEC(15,0) ]
ASCII() retourne la valeur ASCII d'un caractère [ ASCII('E') = 69 ]
CHR() retourne le caractère dont on fournit la valeur ASCII
MONTH_BETWEEN(d1, d2) retourne le nombre de mois (avec décimales sur 31j)
qui sépare les dates d1 et d2.
VALUES(months_between('25/09/08' , '31/08/08'))=> 0,806451612903225
VALUES(months_between('30/09/08' , '31/08/08'))=> 1,000000000000000
|
|
TIMESTAMP_FORMAT('chaine' , 'format')
produit un Timestamp à partir de "chaîne" qui est un timestamp au
format caractère, suivant le format indiqué en deuxième argument.
Ex : TIMESTAMP_FORMAT('99/02/05' , 'RR/MM/DD')=1999-02-05-00.00.00.000000
Dans format vous pouvez utiliser les séparateurs suivants :
- . / , ' : ; et (espace)
et les code suivants (les zéros de gauche seront facultatifs)
DD les jours MM les mois YY l'années sur 2
YYYY l'année sur 4 RR l'année ajustée (00 à 49>2000, 50 à 99>1900)
HH24 l'heure (24h) SS les secondes NNNNNN les micro-secondes
Chaque code est facultatif, SQL assumant l'année en cours pour YYYY
le mois en cours pour MM, 01 pour DD, 0 pour le reste.
|
|
ROUND_TIMESTAMP(A,B) et TRUNC_TIMESTAMP(A,B) fournissent un nouveau timestamp de A, arrondi ou tronqué au format indiqué par B. voici les formats et la valeur retour à partir de 1999-06-28-12.48.37.543210 +------------+------+--------------------------+--------------------------+ + Code + ? + ROUND_TIMESTAMP + TRUNC_TIMESTAMP + +------------+------+--------------------------+--------------------------+ | CC ou SCC |siecle|2000-01-01.00.00.00.000000|1900-01-01.00.00.00.000000| | | | | | | YYYY/SYYYY | | | | | YEAR/SYEAR |Année |1999-01-01.00.00.00.000000|1999-01-01.00.00.00.000000| |YYY ouYY ouY| | (arrondi au 1 juillet) | | | | | | | | Q |trim. |1999-07-01.00.00.00.000000|1999-04-01.00.00..00.00000| | | | (arrondi au 16 du mois | | | | | du mi-trimestre) | | | | | | | | MONTH/MON |mois |1999-07-01.00.00.00;000000|1999-06-01.00.00.00;000000| | MM ou RM | | (arrondi au 16 du mois) | | +-------------------------------------------------------------------------+ |
|
0 retour à partir du Lundi 1999-06-28-12.48.37.54321 (01/01 = Vendredi) 0 +------------+------+--------------------------+--------------------------+ + Code + ? + ROUND_TIMESTAMP + TRUNC_TIMESTAMP + +------------+------+--------------------------+--------------------------+ | WW |semain|1999-07-02.00.00.00.000000|1999-06-25.00.00.00.000000| | | | (nb de 7j + arrondi +- 4j| | | | | depuis le 1er janvier) | | | | | | | | W |semain|1999-06-29.00.00.00.000000|1999-06-22.00.00.00.000000| | | | (nb de 7j + arrondi +- 4j| | | | | depuis le 1er du mois) | | | | | | | | DDD ou DD | jour |1999-06-29.00.00.00.000000|1999-06-28.00.00..00.00000| | ou J | | (arrondi à 12 heures) | | | | | | | | | | | | | DAY ou DY | jour |1999-06-27.00.00.00.000000|1999-06-27.00.00.00;000000| | ou D | | (arrondi au dimanche) | | +-------------------------------------------------------------------------+ |
|
0 retour à partir du Lundi 1999-06-28-12.48.37.54321 0 +------------+------+--------------------------+--------------------------+ + Code + ? + ROUND_TIMESTAMP + TRUNC_TIMESTAMP + +------------+------+--------------------------+--------------------------+ | HH ou HH12 | heure|1999-06-28.13.00.00.000000|1999-06-28.12.00.00.000000| | ou HH24 | | (arrondi à 30 Mn) | | | | | | | | MI |minute|1999-06-28.12.49.00.000000|1999-06-28.12.48.00.000000| | | | (arrondi à 30 S) | | | | | | | | SS |second|1999-06-28.12.48.37.000000|1999-06-28.12.48.38.000000| | | | (arrondi à 0,5 seconde) | | | | | | | +-------------------------------------------------------------------------+ Les codes peuvent être saisis en minuscules. ENCRYPT_AES() encrypte suivant le format AES (en plus de RC2 et TDES) |
|
Les registres suivants sont nouveaux : ROW CHANGE TIMESTAMP FOR nom : retourne le timestamp de la dernière modification, uniquement pour les tables possédant une zone "FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP", uniquement Exemple : Select * from clients where ROW CHANGE TIMESTAMP FOR clients = current date - 1 day ROW CHANGE TOKEN FOR nom : retourne un "token" des modifications indiquant si une ligne a été modififée, ou 0 si elle n'a pas été modifiée. le problème est que si la table ne contient pas un ROW CHANGE TIMESTAMP le token est un "compteur interne" des modfications pouvant être partagé par plusieurs lignes, donc assez peu utilisable dans ce cas. |
|
Les registres suivants sont nouveaux : CURRENT DEBUG MODE : mode debug lors de la création de la routine CURRENT DECFLOAT ROUNDING MODE : ( voir le type DECFLOAT ) ainsi que les registres "clients" suivants : CURRENT CLIENT_ACCTNG : chaîne de connexion CURRENT CLIENT_USERID : profil utilisateur client CURRENT CLIENT_APPLNAME : nom de l'application cliente CURRENT CLIENT_PROGRAMID : nom du programme de connexion CURRENT CLIENT_WRKSTNNAME: nom du poste client Ils sont à renseigner par l'application lors de la connexion (sauf le PROGRAMMID, qui le sera souvent en automatique) |
|
OleDB :
Set connect = New ADODB.Connection
connect.Open
"Provider=IBMDASQL; Data Source=MONAS; Application Name=pgm;"
Java :
Class.forName("com.ibm.as400.access.AS400JDBCDriver");
Connection connect=Drivermanager.getConnection("jdbc:as400//MONAS/BDVIN;"
,"profil","motdepasse");
connect.setClientInfo("ApplicationName" , "pgm");
connect.setClientInfo("ClientUser" , "moi");
Cli (php) :
Utilisez SQLSetConnectAttr
|
|
Autres : appellez la procédure cataloguée WLM_SET_CLIENT_INFO, qui fonctionne dans tous les cas, avec les paramètres suivants : 1/ CLIENT_USERID VARCHAR(255) 2/ CLIENT_WRKSTNNAME VARCHAR(255) 1/ CLIENT_APPLNAME VARCHAR(255) 1/ CLIENT_ACCTSTR VARCHAR(255) 1/ CLIENT_PROGRAMMID VARCHAR(255) vous devez la retrouver dans SYSPROCS et la liste des paramètres dans SYSPARMS Programmation : Les noms de Curseur et d'instructions (PREPARE) passent à 128 c. |
|
Lors de l'appel à une procédure cataloguée vous pouvez transmettre maintenant des variables avec la variable indicateur associée C/EXEC SQL + CALL PROC1 (:P1:P1ind , :P2:P2ind) C/EXEC SQL P1ind et P2ind étant des variable binaires (en RPG "5i 0") contenant 0 si la variable est non nulle, -1 si elle l'est. La procédure cataloguée doit recevoir (par ex. par "*ENTRY PLIST" en RPG) 1/ P1 2/ P2 3/ un tableau (ici à 2 postes) de variables binaires les variables indicateurs peuvent être modifiées par la procédure qui indique ainsi si elle retourne (-1) ou non (0) la valeur nulle. |
|
Une Sous Sélection (SELECT imbriqué) admet maintenant :
ORDER BY et FETCH FIRST x ROWS ONLY , permettant de retrouver
plus facilement le premier ou le dernier d'un groupe
On pouvait le faire avant avec " where xxx = (SELECT MAX(xxx) from ...)
Il est possible de placer les sources SQL dans des fichiers stream,
à la compilation (CRTSQLRPGI), et au lancement d'un script (RUNSQLSTM)
avec le nouveau paramètre SRCSTMF()
pour les scripts vous pouvez aussi, si vous continuez à travailler
avec des membres source, en préciser la largeur
Exemples :
RUNSQLSTM SRCSTMF('/home/cm/monscript.txt')
RUNSQLSTM SRCFILE(QTXTSRC) SRCMBR(MONSCRIPT) MARGINS(120)
|
|
les schémas (bibliothèques) SYSTOOLS et SYSIBMADM sont nouveaux, vides (??) et réservés à IBM ! les fichiers suivants du catalogues SQL sont nouveaux : statistiques sur l'activité des tables SYSCOLUMNSTAT SYSPACKAGESTAT SYSINDEXSTAT SYSPROGRAMSTAT SYSMQTSTAT SYSTABLEINDEXSTAT SYSTABLESTAT notion de partition, c'est à dire de membre SYSPARTITIONSTAT SYSPARTITIONINDEXES SYSPARTITIONMQT SYSPARTITIONINDEXSTAT et enfin, ce dernier avec une ligne par bibliothèque SYSSCHEMAS |
