Quelques notions SQL sur l'AS400

Ce document suppose une connaissance de base SQL et introduit des compléments non directement accessibles par l'aide en ligne.

Conserver les zéros non significatifs

Le code parle de lui même. Merci à Laurent Capitaine pour cette approche. 
   //Evident, même si la doc IBM n'est pas claire sur le sujet 
   codpos = %editc(cdposn:'X');

   // Positionnement de deux chiffres cardrés à droite
   n2 = 9;
   evalr a10 = %editc(n2:'X'); // a10 vaut "        09"
Vérification et transformation dans l'autre sens: 
   // Check trouve le premier caractère qui n'est PAS dans la chaine de recherche 
   if %check('0123456789':codePostal) <> 0;
      erreur = CODE_POSTAL_INVALIDE;
      return;
   endif;  

   codePostalNum = %int(codePostal);

Requête en mode bloc

      * Sortie vers ds multidimensionnée.
     D ptr             S                   like(T_Ptr)
     D items           ds                  likeds(item)
     D                                     dim(MaxItems)

     C/EXEC SQL
     C+ FETCH curseur1 FOR :maxItems ROWS INTO :items
     C/END-EXEC SQL
     C
      * Boucle sur lignes lues pour renseigner des informations complémentaires.
      /free
            nbrItems = SQLER3;
            for i = 1 to nbrItems;
                 remplitLigne(items(i));
            endfor;
      /end-free

Restreindre une requête

select * from fichier  fetch first 1 row only

Accès par rang

select * from table where RRN(table) = :n

Conversions de types

ATTENTION: à ce jour, en 2006, les cast automatique fonctionne mal sous db2 400: le premier accès peut aboutir, et les suivants échouer.

Les conversions de type sont intéressantes pour effectuer des jointures ou des reformatage. 

select cast(codpos as decimal (8, 3)) from codpost
where codpos > '09500'                             

Fonction CAST
   9.800,000
  10.000,000
  10.001,000
  10.002,000
Il est possible de spécifier le format et le caractère décimal, à condition que tout corresponde er que le nombre ne soit pas tronqué à gauche. 
select decimal('-1234.56', 8, 3, '.')) from ....
La troncature ne permet que de diminuer la précision du nombre, pas de le tronquer à gauche. 
select trunc(1234,5678, -1) from ....     --> 1230,0000

Jointure sur fichiers

Le mot clé join permet d'écrire des requêtes avec les mêmes fonction que "3=Enregistrements non concordants avec fichier primaire" de QRY, avec pas mal de variantes, et à joindre des fichiers avec une sémantique et une efficacité meilleures qu'avec SELECT. Les join peuvent être imbriqués.

INNER JOIN – Ce qui correspond entre deux fichiers

Les enregistrements sans correspondance ne sont pas sélectionnés. INNER JOIN et JOIN sont équivalents. 

select p.nom , p.codpos, v.ville     
from personnes p JOIN villes v
on v.codpos = p.codpos
Nom de la personne, code postal et ville.
Si le code postal est invalide, la personne n'est pas sélectionnée.

OUTER JOIN – Avec ou sans correspondance

Outer join permet de sélectionner avec jointure en incluant les cas de non correspondance sur l'un des "côtés" de la jointure, LEFT pour le fichier "à gauche" du join, RIGHT pour le fichier "à droite" du join. LEFT JOIN et LEFT OUTER JOIN sont equivalents.

Les champs non valorisés par la requête ont la valeur NULL, qui peut être substituée par la fonction IFNULL, pour ne transmettre à un programme des valeurs nulles par exemple. 

select p.nom , p.codpos, v.ville     
from personnes p LEFT OUTER JOIN villes v 
on v.codpos = p.codpos
Si le code postal est invalide, la personne est sélectionnée
mais la ville a pour valeur NULL. 
select p.nom , p.codpos,
IFNULL(v.ville, concat(p.codpos,' ???'))     
from personnes p LEFT OUTER JOIN villes v 
on v.codpos = p.codpos
Si le code postal est invalide, la personne est sélectionnée
et la ville a pour valeur codpos ???.

EXCEPTION JOIN – Ce qui ne correspond pas entre les deux fichiers

EXCEPTION JOIN permet de gérer les non concordances. Des choix sont à faire entre ce qui relève de la clause JOIN et ce qui doit être positionné dans la clause WHERE. 

select p.nom , p.codpos,
IFNULL(v.ville, concat(p.codpos,' ???'))     
from personnes p LEFT EXCEPTION JOIN villes v 
on v.codpos = p.codpos
order by p.codpos
Sélection des personnes avec code postal invalide,
triées par code postal. 
select v.codpos, v.ville
from personnes p RIGHT EXCEPTION JOIN villes v 
on v.codpos = p.codpos
where v.codpos like '64%' order by v.codpos
Sélection des communes sans personnes rattachées, sur un département.
RIGHT est utile pour reprendre les critères
d'une requête LEFT sans avoir à la reformuler entièrement.

Exemples plus complexes

Transcodifications ciblées 
update PERMANENT p set code1 =
  ( select  nouvCode1 from ACHANGER c 
    where c.ancien1Code1 = p.code1),
  code2 =   
  ( select  nouvCode2 from ACHANGER c 
    where c.ancien1Code1 = p.code1)
where exists ( select * from ACHANGER
  where c.categ = p.categ and 
        c.ancien1Code1 = p.code1)

Niveaux d'isolation en SQL intéractif

Définis par SET TRANSACTION puis F4. 

               Définition d'une instruction SET TRANSACTION              
                                                                         
indiquez vos choix, puis appuyez sur ENTREE.                               
                                                                         
ISOLATION LEVEL . . . . . . . .   5          1=NO COMMIT (NC, NONE)      
                                             2=READ UNCOMMITTED (UR, CHG)
                                             3=READ COMMITTED (CS)       
                                             4=REPEATABLE READ (RS, ALL) 
                                             5=SERIALIZABLE (RR)

ce qui se traduit par SET TRANSACTION ISOLATION LEVEL ...
... NO COMMITcorrespond à COMMIT *NONE
... READ UNCOMMITTED, READ WRITEcorrespond à STRCMTCTL *CHG
... READ COMMITTEDcorrespond à STRCMTCTL *CS
... REPEATABLE READcorrespond à STRCMTCTL *ALL
... SERIALIZABLEcorrespond à STRCMTCTL *RR

ATTENTION:une transaction démarrée de cette manière se termine dès qu'un COMMIT ou ROLLBACK est effectué. Une manière plus permanente de fixer le contrôle de validation est par F13=Modification des attributs de session.

A cette occasion, l'option Réaffichage des données . . . *ALWAYS définie par défaut ralentit fortement les paginations arrières alors qu'elle est rarement utile.

ATTENTION: Si un contrôle de validation a été défini sur la session SQL (cette information est conservée d'une fois sur l'autre) il FAUT faire un COMMIT ou ROLLBACK avant de sortir de SQL car:

En pratique, il existe deux types de verrous pour validations.

REMARQUE: Le niveau SERIALIZABLE introduit un verrouillage exclusif de niveau table dès que quoi que ce soit est modifié et peut être même accédé. L'usage de ce niveau d'isolation ne semble utilisable que si deux conditions sont réunies: table mise à jour par une seule transaction à la fois, avec des durées de transaction de mise à jour extrêmement courtes: pendant toute la durée d'une phase de validation, la table complète est verrouillée en lecture.

Principes de base (à confirmer)