Toutes les instructions SQL contenues dans un programme – mais pas les CL – sont écrites dans qsys2.sysprogramstmtstat; qu’on ait le source du programme ou pas.
En plus de stocker l’instruction, on accède à d’autres informations comme le nombre d’exécutions, le nombre de variables hôtes …
DSPPGMREF référence l’utilisation de fichier par SQL de façon très succincte, on accède à bien plus de détails
en explorant qsys2.sysprogramstmtstat.

On peut visualiser directement les différentes instructions :

Si on n’est intéressé que par les tables écrites dans les instructions contenues dans un programme, il faut filtrer. On utilise la fonction table qsys2.parse_statement qui sait parser le SQL. Cette dernière, pour faire le travail, a besoin d’informations supplémentaires contenues dans
qsys2.sysprogrammstat :

  • convention de nommage
  • séparateur décimal
  • délimitateur de chaine

Cela donne la requête :

WITH program_statements(naming_mode, dec_point, string_delim, stmt_text, stmt_nbr, system_program_name, program_type) AS
(SELECT a.naming,
a.decimal_point,
a.sql_string_delimiter,
b.statement_text,
b.statement_number,
a.system_program_name,
a.program_type
FROM qsys2.sysprogramstat a
INNER JOIN qsys2.sysprogramstmtstat b
ON a.program_schema = b.program_schema
AND a.program_name = b.program_name
AND a.module_name = b.module_name
WHERE a.number_statements > 0
AND a.program_schema = ‘GCOMPARE’
AND a.program_name =’COMPARE1′ )
SELECT system_program_name,
program_type,
c.schema,
c.name,
stmt_nbr,
stmt_text
FROM program_statements,
TABLE(qsys2.parse_statement(stmt_text, naming_mode, dec_point, string_delim)) c
WHERE c.name_type = ‘TABLE’
ORDER BY stmt_nbr,
c.schema,
c.name;

On peut modifier cette dernière requête en indiquant une liste de bibliothèques pour a.program_schema.
En la planifiant régulièrement et en récupérant son resultset dans un fichier, on pourra voir rapidement les utilisations connues d’une table.

Malheureusement (ou pas) les « Execute Immediate :var » n’indiquent pas ce qu’elles contiennent …

, CONTROLER IBAN & RIB

L’International Bank Account Number, généralement nommé sous l’acronyme IBAN, est un système international de numérotationLe numéro IBAN, ou code IBAN, est affiché sur votre Relevé d’Identité Bancaire (RIB). Formalisé par une suite de chiffres et de lettres pas toujours compréhensibles pour les usagers bancaires, l’IBAN est une norme internationale ISO qui a été mise en place pour sécuriser et faciliter les échanges et transferts bancaires internationaux sur tous les continents.

Dans cet article nous allons voir comment vérifier si la clé d’un IBAN est correcte. Ensuite nous effectuerons la vérification de la clé d’un RIB. Les exemples sont basés sur un compte bancaire français.

Contrôler la clé IBAN

Structure de l’IBAN

FRKK BBBB BGGG GGCC CCCC CCCC CKK

B = code banque
G = code guichet
C = numéro de compte
K = clef

Le RIB (ou BBAN) comprend les informations suivantes :

– Le nom de la banque et de l’agence;
– Le code banque à 5 chiffres;
– Le code guichet à 5 chiffres ;
– Le numéro du compte à 11 chiffres ou lettres au maximum;
– La clé RIB, à 2 chiffres.

Le calcul de la clé IBAN est fonction de l’enchaînement entre le BBAN, le code pays (transformé en données chiffrées) et 00.
Concernant la conversion du code pays, il faut savoir que la lettre A équivaut au nombre 10, B = 11, C = 12, D = 13… jusqu’à la dernière lettre de l’alphabet Z qui vaut 35.


La formule arithmétique pour résoudre l’équation et déterminer une clé IBAN est :

Clé IBAN = 98 – ((Valeur numérique) modulo 97).

Il faut donc procéder en deux étapes :

  • Reconstituer l’IBAN sous la forme BBAN + [Code Pays ISO2] + « 00 »
  • Remplacer toutes les lettres de l’IBAN par le nombre qui leur correspond (entre 10 et 35)

Voici une procédure, écrite dans un programme de service, qui permet la vérification de la clé d’un IBAN.

dcl-proc checkIban export;
  dcl-pi *n ind;
    numIban char(27) const;
  end-pi;

  dcl-s count int(3) ;
  dcl-s index int(3) ;
  dcl-s posit int(3) ;
  dcl-s stringToCalculate varchar(50) ;
  dcl-c letters    'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ;

  stringToCalculate = %subst(numIban:5) + %subst(numIban:1:2) + '00';

  for count = 1 to %len(%trim(stringToCalculate)) ;
    index = %scan(%subst(stringToCalculate:count:1):letters) ;
    if index > 0 ;
      posit = count - 1 ;
      index += 9 ;
    
      stringToCalculate = %subst(stringToCalculate : 1 : posit)
                        + %trim(%char(index))
                        + %subst(stringToCalculate : posit + 2) ;
    endif ;
  endfor;

  return (98 - %rem(%DEC(%trim(stringToCalculate) : 50 : 0):97)
         = %DEC(%SUBST(numIban : 3 : 2) : 2 : 0)) ;

end-proc;                       

Ajouter le code suivant dans le source de liage

EXPORT     SYMBOL("CHECKIBAN") 

Utilisation du contrôle

// Déclaration
dcl-PR checkIban ind ;
 P_Iban char(27) const;
end-pr ;         

// Appel
 retour = checkIban('FR3330002005500000157841Z25');
  if retour;
    dsply 'IBAN Correct' ;
  else ;
    dsply 'IBAN Faux' ;
  endif;  

Contrôler la clé RIB

Structure du RIB (voir paragraphe précédent, c’est un composant de l’IBAN)

Le numéro de RIB permet l’identification du compte pour une utilisation Nationale. C’est le numéro à transmettre pour effectuer un virement en France.

La clé RIB est une formule mathématique, qui ne peut s’appliquer que sur des valeurs numériques. Il convient donc de remplacer les éventuelles lettres présentes dans les données du RIB avant de pouvoir en calculer la clé.

Chaque lettre est remplacée par son équivalent numérique :

A,J = 1

B,K,S = 2

C,L,T = 3

D,M,U = 4

E,N,V = 5

F,O,W = 6

G,P,X = 7

H,Q,Y = 8

I,R,Z = 9

La clé peut alors être calculée avec la formule suivante :

Clé RIB = 97 – ( ( 89 x Code banque + 15 x Code guichet + 3 x Numéro de compte ) modulo 97 )

Voici une procédure, écrite dans un programme de service, qui permet la vérification de la clé d’un RIB.

dcl-proc checkRib export;
  dcl-pi *n ind;
    numRib char(23) const ;
  end-pi;

  dcl-s count int(3) ;
  dcl-s index int(3) ;
  dcl-s posit int(3) ;
  dcl-s stringToCalculate varchar(23) ;
  dcl-c letters    'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ;

  stringToCalculate = numRib ;

  for count = 1 to %len(%trim(stringToCalculate)) ;
    index = %scan(%subst(stringToCalculate:count:1) : letters) ;
    if index > 0;
      posit = count - 1 ;
      select ;
        when index >= 10 and index <= 18 ;
          index -= 9 ;
        when index >= 19 ;
          index -= 17 ;
      endsl ;

      stringToCalculate = %subst(stringToCalculate : 1 : posit)
                        + %trim(%char(index))
                        + %subst(stringToCalculate : posit + 2) ;
    endif ;
  endfor ;

  return (97 - %rem(
                  (%DEC(%subst(stringToCalculate:1:5) : 5 : 0)*89
                  + %DEC(%subst(stringToCalculate:6:5) : 5 : 0)*15
                  + %DEC(%subst(stringToCalculate:11:11) : 11 : 0)*3)
                    :97)
          = %DEC(%SUBST(stringToCalculate : 22 : 2) : 2 : 0)) ;

end-proc;  

Ajouter le code suivant dans le source de liage

EXPORT     SYMBOL("CHECKRIB") 

Utilisation du contrôle

// Déclaration
dcl-PR checkRib ind ;
 P_Rib char(23) const ;
end-pr ;               

// Appel
retour = checkRib('3000200550044AB57O41Z68') ;
  if retour;
    dsply 'RIB Correct'  ;
  else ;
    dsply 'RIB Faux' ;
  endif; 
, Exemples SQL personnalisés sur ACS

Lorsque l’on travaille sur une belle requête SQL, nous avons tendance à la garder et la sauvegarder en local sur notre poste (parfois dans l’IFS). Pour la partager à un collègue quoi de mieux qu’un bon vieux mail ?

Ou alors, on peut exploiter les Exemples personnalisés d’ACS pour mutualiser nos découvertes !

Exemples SQL sur ACS

Via l’Exécuteur de scripts SQL d’ACS, une multitude d’exemples est fournie.
Pour y accéder trois possibilités :

  • Edition > Exemples > Insertion à partir d’exemples…
  • Ctrl + I
  • Via la petite icône SQL avec les deux flèches ci-dessous

On y retrouve tout un catalogue d’exemples relativement bien fourni :

Il suffit de rechercher les mots clés qui nous intéressent puis de cliquer sur Insertion, et enfin de remplacer les données variables de la requête.

Ajouter ses exemples personnalisés

Création d’un répertoire dans l’IFS

La première étape consiste à créer un répertoire commun dans l’IFS, le plus simple est de le créer dans /home/ qui est généralement déjà configuré comme partagé (donc visible pour Windows).
Par la suite nous utiliserons le chemin suivant : /home/exemples_sql/.
C’est ici que nous travaillerons pour créer nos exemples personnalisés.

Création d’un exemple

Il suffit de créer un nouveau source SQL, par exemple via ACS :

-- category: Exemples perso
-- description: Recherche d'un fichier dans l'ifs
SELECT *
  FROM TABLE (
      qsys2.ifs_object_statistics(start_path_name => '/')
  )
 WHERE path_name LIKE '%fichier.txt';

Le commentaire category permet de trier et regrouper vos exemples par usages.
Le commentaire description correspond au texte indiqué dans la liste des exemples.

Une fois l’exemple terminé il suffit de sauvegarder le script dans le répertoire de l’IFS choisi : Fichier > Sauvegarde sous… > Fichier STREAM IFS.

Il est bien entendu toujours possible de modifier ou supprimer des exemples à partir de ce répertoire.

Intégration du répertoire d’exemples à ACS

Dans un onglet d’ACS, ouvrir le menu des exemples : Edition > Exemples > Insertion à partir d’exemples…
Puis cliquer sur Préférences…

Cliquer ensuite sur Ajout

Indiquer ici le chemin vers le répertoire de l’IBM i qui contient les exemples SQL : \\<Nom de l’IBM i>\home\exemples_sql

Les exemples sont maintenant dans la liste avec les autres.
Ils sont triés par catégorie (que l’on retrouve juste en dessous de la mire de recherche).
Ils sont listés en dessus par description et un aperçu est disponible à droite.

Pour retrouver des exemples deux possibilités :

  • Choisir la catégorie à afficher en cliquant sur la catégorie actuelle (ici Exemples perso)
  • Utiliser la mire de recherche, qui affichera les exemples correspondant aux mots clés, toutes catégories confondues

Pour plus de détails

Exemples SQL de Birgitta Hauser : https://gist.github.com/BirgittaHauser
Exemples SQL de Scott Forstie : https://gist.github.com/forstie