nouveautés SQL en V5R20 :
 
 - variables de type ROWID, permettant une génération automatique de clé
 
 
 - fonction SQL, retournant une table et utilisable dans la clause FROM
 
   (en fait la fonction retourne une ligne à la fois)
 
 
 - Notion de TABLE TEMPORAIRE  (DECLARE GLOBAL TEMPORARY TABLE)
 
 
 - DIVERS : UNION admis dans le vues, ORDER BY sur des colonnes non choisies
 
 
 - SAVEPOINT (ou point de reprise) permettant un retour arrière ROLLBACK,
     avec choix du point du "moment" logique de reprise.
 
 
 

 
 - clé générée automatiquement, deux syntaxes :
 
 
   > dans les deux cas on peut préciser :
 
                  --ALWAYS--------      toujours
     GENERATED ---                --->
                  -- BY DEFAULT---      uniquement si la zone est nulle
 
 
   A/ ROWID. Il s'agit de variable VARCHAR(40) contenant des valeur générées
              de manière non consécutive.
 
      Create TABLE test1 (ID ROWID GENERATED ALWAYS ,
                          LIB CHAR(40), DATCRT DATE)
 
 
   B/ AS IDENTITY , est associé à un champ numérique sans décimale,
 
      Create TABLE test1 (ID INTEGER AS IDENTITY START WITH 100,
                          LIB CHAR(40), DATCRT DATE)

 
      on peut alors préciser avec AS IDENTITY :
 
      --START WITH (valeur initiale)----------------->
 
      >-INCREMENT BY (incrément)---------------------->
 
          --MINVALUE (valeur mini) -
      >--                           ----------------->
          --NO MINVALUE ---------- -
 
         --MAXVALUE (valeur maxi) -
      >--                           ----------------->
         --NO MAXVALUE ---------- -
 
         -- CYCLE----------    ---ORDER-------
      >--                  ----               ------->
         --NO CYCLE--------    --- NO ORDER---
 
   avec CYCLE, quand MAXVALUE est atteint on recommence à MINVALUE.
   NO ORDER indique une numérotation qui peut ne pas être réalisée dans
    l'ordre  des requêtes.

 et enfin :
 
        -- CACHE x ----                  indique que DB2/400 peut garder
   >----               -------------- .   en mémoire les x dernières valeurs
        --NO CACHE----                    (plus rapide)
 
 
 AS IDENTITY est un attribut d'un champ numérique
 
 ROWID est un nouveau type de champ (à déclarer en RPG par SQLTYPE(ROWID).)
 
 Exemple :
 
 CREATE TABLE TABCDE
     (CDENO SMALLINT NOT NULL
        GENERATED ALWAYS AS IDENTITY
                           (START WITH 500
                            INCREMENT BY 1
                            CYCLE),
      LIVREA VARCHAR (36) ,
      CDEDAT DATE)
 

 
 les ordres INSERT et UPDATE sont completés comme suit :
 
    UPDATE nom-table ----------------------------------SET ...  -->
                       |                          |
                       |-OVERRIDING SYSTEM VALUE--|
                       |                          |
                       |-OVERRIDING USER VALUE----|
 
     Avec OVERRIDING SYSTEM VALUE, on peut modifier une colonne ROWID ou
           AS IDENTITY définie avec GENERATED ALWAYS.
 
     Avec OVERRIDING USER VALUE, on peut modifier une colonne définie avec
           GENERATED ALWAYS ou GENERATED BY DEFAULT.
 
     sans indication la clause SET ne peut PAS manipuler une clé générée
      avec la clause GENERATED ALWAYS, mais on peut manipuler
       une zone AS IDENTITY GENERATED BY DEFAULT.
 
     Par contre, une zone de type ROWID avec GENERATED BY DEFAULT,
        uniquement si la nouvelle valeur a elle même été génerée par DB2.
        (copie de fichier, par exemple)

 
 - création de table par référence :
 
   en V5R10 CREATE TABLE admet la clause like
 
     si vous souhaitez dupliquer aussi la clause IDENTITY, il faut indiquer
 
      CREATE TABLE CLITEMP LIKE CLIENTS INCLUDING IDENTITY 
 
   SQL admet, en plus, en V5R20 la syntaxe suivante, permettant
        une duplication partielle de la liste des zones [du format] :
 
 
      CREATE TABLE nom-fichier AS (sous-sélection)
            WITH [NO] DATA [INCLUDING IDENTITY]
 
     avec WITH DATA, la table est créé et les données dupliquées.
 
  Exemple :
 
    CREATE TABLE CLITMP AS (SELECT nomcli, adr1, adr2, dept, ville from cli)
            WITH NO DATA

 
 - tables temporaires  :
 
   DECLARE GLOBAL TEMPORARY TABLE (même syntaxe que CREATE TABLE) -->
 
                   --DELETE ROWS---             --DELETE ROWS---
    >-- ON COMMIT--                -- ON DELETE                 ---->
                   -PRESERVE ROWS--             - PRESERVE ROWS-
 
 
    >---------------------------------------
       |                |   |             |
       |--WITH REPLACE--|   |--NOT LOGGED-|
 
 
    créé une table dans QTEMP (peut être qualifiée aussi par "SESSION")
 
    WITH REPLACE permet une création avec remplacement, si la table existe
     déja dans QTEMP
 
    NOT LOGGED, indique une table non journalisée (insensible aux ROLLBACK)
 

 
Exemples :
 
 DECLARE GLOBAL TEMPORARY TABLE CLIENTS
         (nocli integer, raisoc char(50),
           adr1 varchar(50) , ville varCHAR(50) )
  WITH REPLACE
 
 
 DECLARE GLOBAL TEMPORARY TABLE CLITEMP AS
         (SELECT nocli , raisoc, adr1 , ville
            from clients) WITH DATA
 
Dans tous les cas de figure, la table n'apparait pas dans les catalogues SQL
                                et est détruite en fin de session.
 
- SAVEPOINT
 
 Cette notion permet de matérialiser des étapes dans une transaction
  offrant la possibilité de revenir à une étape précise et non au début
  de la transaction en cas de ROLLBACK.
 

 
 un point de reprise est posé par l'instruction SAVEPOINT (un SEUL AS/400)
 
      UPDATE client ...                       ;
 
    SAVEPOINT MAJ ON ROLLBACK RETAIN CURSORS  ;
 
      DELETE FROM CLIENTS ...  ;
      DELETE FROm COMMANDES    ;
 
    SAVEPOINT DLT ON ROLLBACK RETAIN CURSORS   ;
 
      INSERT INTO ...          ;
 
    IF xxx ROLLBACK TO SAVEPOINT MAJ
     ELSE RELEASE SAVEPOINT ;
 
 ON ROLLBACK RETAIN CURSORS, permet de garder le(les) curseur(s) ouverts
 ON ROLLBACK RETAIN LOCKS , permet de garder les verrouillages/ligne
 
 RELEASE SAVEPOINT, libère (détruit) le point de reprise
 

 
Fonction retournant une table.
 
Vous pouvez écrire une fonction (PL/SQL ou RPG) retournant une table.
 
 il s'agit en fait d'un programme retournant des données non Base de données
 (une ligne à la fois), permettant d'utiliser avec SQL des informations
 externes (fichiers IFS, fichier en interne, XML, ...)
 
 la fonction est appellée une fois pour initialiser le dialogue
 puis une fois par ligne à retourner, et enfin une ultime fois à la fin.
 
 CREATE FUNCTION nom-fonction ( [param1 [,param2 ..]] )
   RETURNS TABLE (zone1 char(50), zone2 dec(7, 2) ....)
 
 la fonction est utilisée ensuite directement dans la clause FROM, par :
 
 SELECT zone1 from TABLE( nom-fonction(paramètre) )
 
 
    une jointure est possible
 

 
- Toutes les fonctions acceptent un nouveau paramètre :
 
     FENCED , la fonction s'exécute dans un thread à part
 
              (plus sûr, plus gourmand en ressources)
 
     NOT FENCED , le contraire.
 
 
- HOLD LOCATOR nom-variable.
 
  pour les variables contenant un pointeur sur un champ BLOB (ou CLOB),
   le pointeur reste valide après la transaction (COMMIT).
 
- UNION admis dans le CREATE VIEW
 
 
    CREATE VIEW v1 AS SELECT * FROM FACTURES
                       UNION
                      SELECT * FROM ARCHIVES
 

 
- les expressions utilisées dans ORDER BY, n'ont plus à être présentes dans
   la liste des colonnes du SELECT.
 
 
- notion de CURRENT SCHEMA
 
   contient la base de données à utiliser pour les ordres non qualifiés
 
   à l'origine : USER (l'utilisateur en cours) en dénomination *SQL
                 *LIBL en système de dénomination *SYS (système)
 
   modifiable maintenant par SET SCHEMA
 
 
   SET ----------------SCHEMA-----------------------------------------
        |          |             |           |
        |-CURRENT--|             |--un-nom---|
                                 |           |
                                 |--DEFAULT--|
                                 |           |
                                 |--USER-----|

 
 Et enfin les limites V5R10 quant aux sous-requêtes SQL disparaissent
 
 soit un fichier des producteurs (de vin)
 et un fichier des vins (contenant le code producteur)
 
 nous pouvons demander la requête suivante :
 
 SELECT P.pr_code, pr_nom, appel_code, count(*)
 FROM bdvin/producteurs P join bdvin/vins V
  on P.pr_code = V.pr_code
 GROUP BY P.pr_code, pr_nom, appel_code
 
 ensuite mémorisons cet ordre dans une vue
 
 CREATE VIEW BDVIN/stat as
  SELECT P.pr_code, pr_nom, appel_code, count(*) AS NBRVINS
  FROM bdvin/producteurs P join bdvin/vins V
   on P.pr_code = V.pr_code
  GROUP BY P.pr_code, pr_nom, appel_code
 
 

 Nous pouvons maintenant demander a voir sur chaque ligne le nombre de vins
  du producteur et le nombre de vins de l'appellation(tous producteurs)
 
  SELECT pr_nom, nbrvins,
  (select sum(nbrvins)
    from stat where appel_code = S.APPEL_CODE) as total_appel
  from stat S
 
 ce qui nous affiche :
  ..............................................................
  : PR_NOM                           NBRVINS     TOTAL_APPEL   :
  : Château Marquis de Terme               1              54   :
  : Château Maucaillou                     3               9   :
  : Château Mayne Lalande                  2              23   :
  : Château Monbrison                      1              54   :
  : Château Moulin La Pitié                1              11   :
  :............................................................:
 
 Nous pouvons même maintenant aller jusqu'à demander un % sur chaque ligne
  avec la requête suivante :
 
 

SELECT pr_nom, nbrvins,
(select sum(nbrvins)
  from stat where appel_code = S.appel_code) as total_appel   ,
(select dec(s.nbrvins, 7 , 3) / dec(sum(nbrvins), 7 , 3) * 100
  from stat where appel_code = S.appel_code) as pourcentage
from stat S
 
ce qui nous affiche :
...........................................................................
: PR_NOM                      NBRVINS     TOTAL_APPEL       POURCENTAGE   :
: Château Marquis de T              1              54           1,851     :
: Château Maucaillou                3               9          33,333     :
: Château Mayne Laland              2              23           8,695     :
:.........................................................................:
permettant, pour terminer, un classement par % (des 10 premiers par ex.)
 
 SELECT pr_nom, nbrvins,
 (select sum(nbrvins)
   from stat where appel_code = S.appel_code) as total_appel   ,
 (select dec(s.nbrvins, 7 , 3) / dec(sum(nbrvins), 7 , 3) * 100
   from stat where appel_code = S.appel_code) as pourcentage
 from stat S ORDER BY 4 DESC FETCH FIRST 10 ROWS ONLY