, , Utiliser l’instruction merge en SQL

Vous avez une instruction SQL , MERGE qui est assez PUISSANTE pour fusionner des fichiers
Voici un exemple complet :


Il intègre les éléments suivants
-La création
-La mise à jour
-La suppression


On a 2 tables Products et Mouvements

Les règles choisies sont les suivantes :

-Si le produit existe on ajoute la quantité
-S’ il est nouveau, on le créé
-Si nom du produit est SUPPRESSION, on supprime

et dans tous les cas on met à jour la date de modification

-- voici les scriptes pour tester 
-- Création de la table des produits
CREATE TABLE GDATA/PRODUCTS (
    PRODUCT_NUMBER  DECIMAL(10, 0)      NOT NULL ,
    GENERATED ALWAYS AS IDENTITY ensures unique, auto-incrementing numbers.
    PRODUCT_NAME    VARCHAR(100)        NOT NULL,
    QUANTITY        DECIMAL(10, 0)      NOT NULL DEFAULT 0,
    LAST_UPDATE_TS  TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (PRODUCT_NUMBER)
    );
-- alimentation du fichier
INSERT INTO GDATA/PRODUCTS VALUES(1, 'CLOU', 50, current timestamp) ;
INSERT INTO GDATA/PRODUCTS VALUES(2, 'VISSE', 20, current timestamp) ;
INSERT INTO GDATA/PRODUCTS VALUES(3, 'ECROU', 25, current timestamp) ;
INSERT INTO GDATA/PRODUCTS VALUES(4, 'RONDELLE', 120, current timestamp) ;
-- Création de la table des mouvements
CREATE TABLE GDATA/MOUVEMENTS (
    PRODUCT_NUMBER  DECIMAL(10, 0)      NOT NULL ,
    PRODUCT_NAME    VARCHAR(100)        NOT NULL,
    QUANTITY        DECIMAL(10, 0)      NOT NULL DEFAULT 0,
    LAST_UPDATE_TS  TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (PRODUCT_NUMBER)
    );
-- alimentation de la tables des mouvements
INSERT INTO GDATA/MOUVEMENTS VALUES(1, 'CLOU', 30, current timestamp) ; -- changement de quantité
INSERT INTO GDATA/MOUVEMENTS VALUES(3, 'SUPPRESSION', 0, current timestamp) ; -- suppression
INSERT INTO GDATA/MOUVEMENTS VALUES(5, 'RESSORT', 100, current timestamp); -- Nouveau
-- Fusion des 2 tables 
MERGE INTO GDATA/PRODUCTS AS T  -- T est l'alias de la table CIBLE (PRODUCTS)
USING GDATA/MOUVEMENTS AS S      -- S est l'alias de la table SOURCE (MOUVEMENT)
ON (T.PRODUCT_NAME = S.PRODUCT_NAME) -- La jointure se fait sur le nom du produit
-- 1. Gérer la suppression si le produit correspond ET que la source indique 'SUPPRESSION'
WHEN MATCHED AND S.PRODUCT_NAME = 'SUPPRESSION' THEN
    DELETE
-- 2. Gérer la mise à jour si le produit correspond ET que la source N'indique PAS 'SUPPRESSION'
WHEN MATCHED AND S.PRODUCT_NAME <> 'SUPPRESSION' THEN
    UPDATE SET T.QUANTITY = T.QUANTITY + S.QUANTITY,
               T.LAST_UPDATE_TS = CURRENT_TIMESTAMP
-- 3. Gérer l'insertion si le produit NE correspond PAS ET que la source N'indique PAS 'SUPPRESSION'
WHEN NOT MATCHED AND S.PRODUCT_NAME <> 'SUPPRESSION' THEN
    INSERT (PRODUCT_NUMBER, PRODUCT_NAME, QUANTITY, LAST_UPDATE_TS)
    VALUES (S.PRODUCT_NUMBER, S.PRODUCT_NAME, S.QUANTITY, CURRENT_TIMESTAMP);

Remarque :

Vous pouvez également utiliser la commande CPYF avec le paramètre MBROPT(*UPDADD) mais plus compliqué de gérer les suppressions.

Bien sur , bien tester tous les cas

, Visual Explain : affichage des index considérés

Access Client Solutions 1.1.9.8, disponible depuis avril 2025, amène son lot d’évolutions. Une m’a particulièrement intéressée : l’affichage des index considérés.

Visual Explain, les index ?

Visual Explain permet d’afficher le plan d’exécution de la requête SQL : l’ensemble des étapes nécessaires à l’obtention du résultat, de la façon la plus optimisée possible.

Pour déterminer la façon la plus optimisée, le moteur SQL va réécrire la requête, considérer les index/LF existants, exploiter les statistiques de chaque table, index ou clé sous-jacents aux tables utilisées dans la requête.

Dans le plan affiché, pour une première analyse macroscopique, on cherche en général les éléments suivants :

  • Scan de table : on préfère utiliser des accès par index plutôt que parcourir l’ensemble de la table. Il s’agit d’analyser pourquoi aucun index ne satisfait les conditions de la requête
  • Les index utilisés : même si l’on se félicite de l’utilisation d’index, il est souvent possible de faire mieux
  • Les index recommandés : justement pour faire mieux !

Une information est disponible mais difficilement exploitable : l’optimiseur explique pour chaque index trouvé pourquoi il a été utilisé, ou pourquoi il ne l’a pas été.

Un exemple

Nous avons une table dans laquelle nous consolidons certains événements logués par nos serveurs web (access_log générés par Apache). Aujourd’hui cette table HTTPLOG contient environ 240 millions d’entrées, et dispose bien évidemment d’un certains nombres d’index existants :

Prenons une requête basique :

L’affichage de Visual Explain nous montre :

En sélectionnant l’étape « Test de table », le volet de droite indique :

Ce sont la liste des index que l’optimiseur a regardé et le chiffre correspond au code qui indique pourquoi il n’a pas été utilisé. Il est possible d’aller chercher le détail des codes dans les messages (il faut activer les messages de débogage).

Cette information est accessible pour chaque étape du plan, dans le cas de jointure ou de sous-requête.

Index considérés

Prenons une autre requête SQL permettant d’analyser toutes les requêtes HTTP authentifiées et dont le retour provoque un warning ou une erreur (autre que code HTTP 200 OK).

Visual Explain nous donne :

Nous retrouvons bien entendu nos informations sur les index :

Mais il est maintenant possible de demander ces informations pour l’ensemble de la requête :

Et d’obtenir des libellés plus parlants :

Cela vous donne plus d’informations quant à l’usage des index. Pour compléter, l’index advisor nous donne :

A noter que l’optimiseur ne propose pas d’index dérivé par exemple, ou difficilement les index EVI … Gardez donc un œil critique sur ces informations, mais leur compréhension est nécessaire.

, , Gestion du SQLCODE dans un SQL embarqué

En SQL embarquée la gestion des erreurs est différente par rapport à un RPGLE classique.


Essentiellement sur 2 points


1) Ca ne plante pas
Vous pouvez donc avoir des erreurs silencieuses
Il est très important de traiter les SQLCODE
Même si vous pensez ne pas en avoir besoin
Les 3 lignes suivantes peuvent être ajoutées sans risque !

EXEC SQL ... ;
IF SQLCODE <> 0;
DSPLY ('nompgm' + ' : ' + %CHAR(SQLCODE) ;
ENDIF;


Permettra d’avoir un message dans la log qui peut vous aider en cas de probléme.

2) On n’a le résultat de la dernière instruction dans le SQLCODE.

Le risque est donc sur une boucle, il est possible que le SQLCODE soit parasité par une autre requête SQL dans une autre fonction par exemple

dou sqlcode <> 0                    ;
  exec sql ... ;                                         
  if sqlcode <> 0                   ;
    leave                           ;
  endif                             ;
enddo                               ;

Pour éliminer ce probléme vous devez sauvegarder votre SQLCODE dans une variable de travail

Votre boucle deviendra

// Déclaration de la variable de travail ! 
DCL-S  W_SQLCODE like SQLCODE; 
//
dou w_sqlcode <> 0                    ;
  exec sql ... ;                                         
  if sqlcode <> 0                   ;
    leave                           ;
  endif                             ;
  W_SQLCODE = SQLCODE                 ;
enddo  

Ainsi vous êtes sur de tester le bon SQLCODE

Remarque :
Pour ceux qui utilisent le sqlstate, la remarque est la même.
Vous pouvez être plus précis que <> 0 ;

Rappel :
en dessus de 0 c’est des avertissements
en dessous c’est des erreurs
par exemple if sqlcode < 0 ; traitera les erreurs uniquement

, , , Clé, contrainte et index : quelles différences ?

Dans une base de données bien définie, nos enregistrements sont identifiés par des clés (ie unique). Il existe toutefois différentes façon de matérialiser ces clés en SQL.

Première bonne résolution : on ne parlera pas ici des DDS (PF/LF) !

Quelques rappels

je n’insiste pas, mais une base de donnée relationnelle, DB2 for i dans notre cas, fonctionne à la perfection, à condition de pouvoir identifier nos enregistrements par des clés.

Cf https://fr.wikipedia.org/wiki/Forme_normale_(bases_de_donn%C3%A9es_relationnelles)

Une normalisation raisonnable pour une application de gestion est la forme normale de Boyce-Codd (dérivée de la 3ème FN).

Clés

Vous pouvez implémenter vos clés de différentes façons, voici une synthèse :

TypeSupport valeur nulle ?Support doublon ?Commentaire
Contrainte de clé primaireTableNonNonValeur nulle non admise, même si la colonne clé le supporte
Contrainte d’unicitéTableOuinon : valeurs non nulles
oui : valeurs nulles
Gère des clés uniques uniquement si non nulles
Index uniqueIndexOuiNonGère des clés uniques.
La valeur NULL est supportée pour 1 unique occurrence
Index unique where not nullIndexOuisnon : valeurs non nulles
oui : valeurs nulles
Gère des clés uniques uniquement si non nulles

Attention donc à la définition de UNIQUE : à priori ce qui n’est pas NULL est UNIQUE.

Concrètement ?

Prenons un cas de test simpliste pour montrer la mécanique : un fichier article avec une clé et un libellé

Clé primaire

La colonne CODE admet des valeurs nulles, mais est fait l’objet de la contrainte de clé primaire.

A la création de la contrainte de clé primaire, le système créé automatiquement une contrainte de type CHECK pour interdire l’utilisation de valeur nulle dans cette colonne :

Avec :

La clé primaire joue son rôle avec des valeurs non nulles :

Et des valeurs nulles :

On retrouve ici le nom de la contrainte générée automatiquement !

Avec une contrainte de clé unique ?

Le comportement est identique sur une clé non nulle.

Mais avec une clé nulle (ou dont une partie est nulle si elle composée) :

On peut ajouter un index unique pour gérer le problème. Dans ce cas, une et une seule valeur nulle sera acceptée :

Mais dans ce cas pourquoi ne pas utiliser une clé primaire ??

Clé étrangère, jointure

Ajoutons un fichier des commandes, ici une simplification extrême : 1 commande = 1 article.

On ajoute une contrainte de clé étrangère qui matérialise la relation entre les tables commande et article. Pour cette contrainte commande_FK, il doit exister une contrainte de clé primaire ou de clé unique sur la colonne CODE dans la table article.

La contrainte se déclenche si l’article référencé n’existe pas :

Cas identique mais en s’appuyant sur la table article_unique qui dispose d’une clé unique et non primaire :

Dans ce cas les valeurs nulles sont supportées, en multiples occurrences (sauf à ajouter encore une fois un index unique au niveau de la commande).

Récapitulons ici nos données pour comprendre les jointures :

Démarrons par ARTICLE & COMMANDE :

La table ARTICLE ne peut pas avoir de clé nulle, donc pas d’ambiguïté ici

Avec right join ou full outer join nous accèderons au lignes de commande pour lesquelles CODE = null.

C’est le comportement attendu.

Voyons avec ARTICLE_UNIQUE et COMMANDE :

Ici on pourrait s’attendre à obtenir également les lignes 11 et 12 de la table COMMANDE : le CODE est nulle pour celles-ci, mais il existe une ligne d’ARTICLE pour laquelle le code est null. Il devrait donc y avoir égalité.

En réalité les jointures ne fonctionnent qu’avec des valeurs non nulles

De même que la clause WHERE :

Il faut donc utiliser ce style de syntaxe :

C’est à dire :

  • soit remplacer les valeurs nulles par des valeurs inexistantes dans les données réelles
  • soit explicitement indiquer la condition de nullité conjointe

Bref, syntaxiquement cela va rapidement se complexifier dans des requêtes plus évoluées.

Clé composée

Evidemment, c’est pire ! Imaginons que l’on ait une clé primaire/unique dans la table ARTICLE composée de 2 colonnes (CODE1, CODE2), et donc présentes toutes les deux dans la table COMMANDE :

Et les performances ?

En utilisant la jointure, l’optimiseur est capable de prendre en charge des accès par index :

Mais en utilisant IFNULL/COALESCE, ces valeurs deviennent des valeurs calculées, ce qui invalide l’usage des index :

Ce n’est donc pas viable sur des volumes plus importants. Il existe des solutions (index dérivés par exemple) mais la mécanique se complique encore !

Préconisations

De façon générale pour vos données de gestion, en excluant les fichier de travail (QTEMP a d’autres propriétés), les fichiers de logs, les fichier d’import/export …

  • Pas de valeur NULL dans vos clés
    • Pour les clés atomique c’est une évidence, pour les clés composées c’est beaucoup plus simple
  • Une contrainte de clé primaire pour toutes vos tables !
    • N’hésitez pas à utiliser des clés auto-incrémentées
  • Des contraintes d’unicités ou des index uniques pour vos autres contraintes d’unicité, techniques ou fonctionnelles
    • Pas d’excès, sinon il y a un défaut de conception (cf les formes normales)
  • Si possible des contraintes de clé étrangère pour matérialiser les relations entre les tables
    • Délicat sur l’existant, les traitements doivent tenir compte du sens de la relation
  • Favorisez l’usage des clés, contraintes et index par l’optimiseur
    • Scalabilité entre vos environnements de développement/test et la production
, , , Gérer vos listes de validation avec SQL !

Vous connaissez bien désormais les services SQL qui vous permettent d’administrer de plus en plus facilement votre IBM i.

IBM fournit des services pour gérer les listes de validation :

Mais rien pour faire la liste des entrées, comme on peut trouver dans l’administration HTTP par exemple.

Nous vous proposons donc une fonction table SQL permettant d’avoir l’intégralité de la gestion via DB2 : cf https://github.com/FrenchIBMi/Outils/tree/master/Validation%20List

Cela permet de revenir sur le principe de l’implémentation via du code RPG :

  • Le code est basé sur les APIs QsyFindFirstValidationLstEntry et QsyFindNextValidationLstEntry
  • Le moteur DB2 appelle l’implémentation :
    • 1 appel initial
    • 1 appel par poste de liste à retourner
    • 1 appel final

Nous utilisons __errno pour retrouver les codes erreur de l’APIs. Les différentes valeurs sont déclarées sous forme de constante.

La fonction SQL retourne les SQL STATE suivants :

  • 02000 lorsque l’on attend la fin des données (fin normale)
  • 38999 pour les erreurs. Cette valeur est arbitraire

Si possible, nous retrouvons le libellé de l’erreur retournée par l’API via strerror et on le retourne à DB2.

Code RPG :

**free

// Compilation / liage :
// CRTRPGMOD MODULE(NB/VLDLUDTF) SRCFILE(NB/QRPGLESRC) 
//           OPTION(*EVENTF) DBGVIEW(*SOURCE)
// CRTSRVPGM SRVPGM(NB/VLDLUDTF) EXPORT(*ALL) ACTGRP(*CALLER)

// Implémentation de la fonction UDTF VALIDATION_LIST_ENTRIES
// Liste les entrées d'une liste de validation
// Utilise l'API QsyFindFirstValidationLstEntry et QsyFindNextValidationLstEntry

// @todo :
// - ajouter le support de la conversion de CCSID
// - améliorer la gestion des erreurs


ctl-opt nomain option(*srcstmt : *nodebugio) ;


// Déclarations pour APIs : QsyFindFirstValidationLstEntry et QsyFindNextValidationLstEntry

dcl-ds Qsy_Qual_Name_T qualified template ;
  name char(10) inz ;
  lib  char(10) inz ;
end-ds ;

dcl-ds Qsy_Entry_ID_Info_T qualified template ;
  Entry_ID_Len int(10) inz ;
  Entry_ID_CCSID uns(10) inz ;
  Entry_ID    char(100) inz ;
end-ds ;

dcl-ds Qsy_Rtn_Vld_Lst_Ent_T qualified template ;
  dcl-ds Entry_ID_Info likeds( Qsy_Entry_ID_Info_T) inz ;

  dcl-ds Encr_Data_Info ;
    Encr_Data_len   int(10) inz;
    Encr_Data_CCSID uns(10) inz;
    Encr_Data       char(600) inz ;
  end-ds ;

  dcl-ds Entry_Data_Info  ;
    Entry_Data_len   int(10) ;
    Entry_Data_CCSID uns(10) ;
    Entry_Data       char(1000) ;
  end-ds ;

  Reserved          char(4) inz ;
  Entry_More_Info   char(100) inz ;
end-ds ;

dcl-pr QsyFindFirstValidationLstEntry int(10) extproc('QsyFindFirstValidationLstEntry');
  vldList       likeds(Qsy_Qual_Name_T) const ;
  vldListEntry  likeds(Qsy_Rtn_Vld_Lst_Ent_T) ;
end-pr ;

dcl-pr QsyFindNextValidationLstEntry int(10) extproc('QsyFindNextValidationLstEntry');
  vldList       likeds(Qsy_Qual_Name_T) const ;
  entryIdInfo   likeds(Qsy_Entry_ID_Info_T)   ;
  vldListEntry  likeds(Qsy_Rtn_Vld_Lst_Ent_T) ;
end-pr ;

// Retrouver le code erreur de l'API
dcl-pr getErrNo int(10) ;
end-pr ;
// Code erreur
dcl-c EACCES   3401 ;
dcl-c EAGAIN   3406 ;
dcl-c EDAMAGE  3484 ;
dcl-c EINVAL   3021 ;
dcl-c ENOENT   3025 ;
dcl-c ENOREC   3026 ;
dcl-c EUNKNOWN 3474 ;

// Retrouver le libellé du code erreur
dcl-pr strError pointer extproc(*CWIDEN : 'strerror') ;
  errNo int(10) value ;
end-pr ;

// gestion UDTF
dcl-c CALL_OPEN     -1;
dcl-c CALL_FETCH     0;
dcl-c CALL_CLOSE     1;
dcl-c PARM_NULL     -1;
dcl-c PARM_NOTNULL   0;


// Liste les entrées de la liste de validation
// ==========================================================================
dcl-proc vldl_list export ;

// Déclarations globales
  dcl-s  ret int(10) inz ;
  dcl-s  errno int(10) inz ;
  dcl-ds vldListEntry likeds(Qsy_Rtn_Vld_Lst_Ent_T) inz static ;
  dcl-ds vldlname  likeds(Qsy_Qual_Name_T) inz          static ;
  dcl-s  first ind inz(*on)                             static ;


  dcl-pi *n ;
    // input parms
    pvldl_lib  varchar(10) const ;
    pvldl_name varchar(10) const ;
    // output columns
    pEntry_ID    varchar(100) ;
    pEntry_Data  varchar(1000) ;
    // null indicators
    pvldl_lib_n    int(5) const ;
    pvldl_name_n   int(5) const ;
    pEntry_ID_n    int(5)       ;
    pEntry_Data_n  int(5)       ;
    // db2sql
    pstate    char(5);
    pFunction varchar(517) const;
    pSpecific varchar(128) const;
    perrorMsg varchar(1000);
    pCallType int(10) const;
  end-pi ;


// Paramètres en entrée
  if pvldl_name_n = PARM_NULL or pvldl_lib_n = PARM_NULL;
    pstate = '38999' ;
    perrorMsg = 'VALIDATION_LIST_LIBRARY ou VALIDATION_LIST_NAME est null' ;
    return ;
  endif ;

  select;
    when ( pCallType = CALL_OPEN );
    // appel initial : initialisation des variables statiques
      vldlname.name = pvldl_name ;
      vldlname.Lib  = pvldl_lib ;
      clear vldListEntry ;
      first = *on ;

    when ( pCallType = CALL_FETCH );
    // retrouver l'entrée suivante
      exsr doFetch ;

    when ( pCallType = CALL_CLOSE );
    // rien à faire

  endsl;


  // traitement de l'entrée suivante
  begsr doFetch ;
    if first ;
      ret = QsyFindFirstValidationLstEntry( vldlname : vldListEntry);
      first = *off ;
    else ;
      ret = QsyFindNextValidationLstEntry( vldlname :
                                           vldListEntry.Entry_ID_Info : vldListEntry);
    endif ;

    if ret = 0 ;
      // Entrée trouvée
      monitor ;
        pEntry_ID   = %left(vldListEntry.Entry_ID_Info.Entry_ID :
                          vldListEntry.Entry_ID_Info.Entry_ID_Len);
        pEntry_Data = %left(vldListEntry.Entry_Data_Info.Entry_Data :
                          vldListEntry.Entry_Data_Info.Entry_Data_len) ;
        pEntry_ID_n   = PARM_NOTNULL ;
        pEntry_Data_n = PARM_NOTNULL ;
      on-error ;
        // Erreur de conversion
        pstate = '38999' ;
        perrorMsg = 'Erreur de conversion' ;
      endmon ;
    else ;
      // Entrée non trouvée : erreur ou fin de lecture
      errno = getErrNo() ;
      select ;
        when errno in %list( ENOENT : ENOREC ) ; // fin de lecture
          pstate = '02000' ;
          return ;
        other ; // Erreur
          pstate = '38999' ;
          perrorMsg = %str(strError(errno)) ;
      endsl ;
    endif ;
  endsr ;

end-proc ;



// Retrouver le code erreur de l'API
dcl-proc getErrNo ;

  dcl-pr getErrNoPtr pointer ExtProc('__errno') ;
  end-pr ;

  dcl-pi *n int(10) ;
  end-pi;

  dcl-s errNo int(10) based(errNoPtr) ;

  errNoPtr = getErrNoPtr() ;
  return errNo ;

end-proc;

Code SQL :

set current schema = NB ;
set path = 'NB' ;

Create or replace Function VALIDATION_LIST_ENTRIES ( 
                  VALIDATION_LIST_LIBRARY  varchar(10),
                  VALIDATION_LIST_NAME     varchar(10) )
               Returns Table                    
               (                   
                 VALIDATION_USER   varchar(100),                   
                 ENTRY_DATA        varchar(1000)
               )                
               external name 'VLDLUDTF(VLDL_LIST)'                
               language rpgle                
               parameter style db2sql                
               no sql                
               not deterministic                
               disallow parallel;


cl: DLTVLDL VLDL(NB/DEMO) ;
cl: CRTVLDL VLDL(NB/DEMO) TEXT('Démo VALIDATION_LIST_ENTRIES')   ;

VALUES SYSTOOLS.ERRNO_INFO(SYSTOOLS.ADD_VALIDATION_LIST_ENTRY(
                             VALIDATION_LIST_LIBRARY => 'NB', 
                             VALIDATION_LIST_NAME    => 'DEMO',
                             VALIDATION_USER         => 'user 1',
                             PASSWORD                => 'MDP user 1',
                             ENTRY_DATA              => 'Client 1'));
VALUES SYSTOOLS.ERRNO_INFO(SYSTOOLS.ADD_VALIDATION_LIST_ENTRY(
                             VALIDATION_LIST_LIBRARY => 'NB', 
                             VALIDATION_LIST_NAME    => 'DEMO',
                             VALIDATION_USER         => 'user 2',
                             PASSWORD                => 'MDP user 2',
                             ENTRY_DATA              => 'Client 1'));
VALUES SYSTOOLS.ERRNO_INFO(SYSTOOLS.ADD_VALIDATION_LIST_ENTRY(
                             VALIDATION_LIST_LIBRARY => 'NB', 
                             VALIDATION_LIST_NAME    => 'DEMO',
                             VALIDATION_USER         => 'user 3',
                             PASSWORD                => 'MDP user 3',
                             ENTRY_DATA              => 'Client 2'));

select * from table(VALIDATION_LIST_ENTRIES( VALIDATION_LIST_LIBRARY => 'NB',
                                             VALIDATION_LIST_NAME    => 'DEMO' )) ;

Cela produit :

Libre à vous maintenant d’utiliser ce résultat pour jointer avec vos fichiers de log HTTP (autorisation basique sur une liste de validation par exemple), avec le service USER_INFO_BASIC, croiser les profils présents dans vos différentes listes …

, , Fonction usage / Exit programme

Vous êtes de plus en plus confronter à la mise en place de sécurité sur des services (ODBC, FTP, etc..)
il existe 2 solutions sur l’ibmi qui vont vous aider dans votre démarche

Les fonctions usage et les exit programmes

Vous avez des interfaces 5250 pour les administrer, mais aussi maintenant Navigator for i
WRKFCNUSG et WRKREGINF
Vous avez également des services SQL
QSYS2.SQL_CHECK_FUNCTION_USAGE(), QSYS2.FUNCTION_USAGE, QSYS2.FUNCTION_INFO
QSYS2.EXIT_POINT_INFO,QSYS2.EXIT_PROGRAM_INFO

Rapidement voici une petite comparaison

D’abord un essai de comparaison entre les principales valeurs

Accès / FonctionnalitéFonction d’usage (Function Usage ID)Programme d’exit (Exit Point)Description / Concerne
Accès base de données (ODBC, JDBC…)QIBM_DB_OPENQIBM_QZDA_INIT, QIBM_QZDA_NDB1Autorise/refuse l’ouverture de base de données (SQL, ODBC, etc.)
SQL Server Mode (QSQSRVR)QIBM_DB_QSQSRVRN/AContrôle l’accès aux jobs QSQSRVR (SQL server mode)
DDM/DRDAQIBM_DB_DDMDRDAQIBM_QDDMDRDASERVER, QIBM_QDDSQLDRDAAccès aux bases via DDM / DRDA
ODBC SpécifiqueN/AQIBM_QZDA_SQL1, QIBM_QZDA_INITAppels SQL via ODBC / DRDA
JDBCN/AQIBM_QZDA_SQL1, QIBM_QZDA_INITJDBC via Toolbox ou Native JDBC
FTPQIBM_FTP_SERVERQIBM_QTMF_SVR_LOGON, QIBM_QTMF_SVR_EXITContrôle l’accès FTP
TelnetQIBM_TELNET_SERVERQIBM_QTV_TELNETContrôle l’accès Telnet
Remote Command (RUNRMTCMD)QIBM_NETWORK_SERVERQIBM_QZRC_RMTExécution de commandes distantes
Client Access (iSeries Access, ACS…)QIBM_ACCESS3270, QIBM_DB_OPENQIBM_QZDA_* (exits ODBC), QIBM_QZRC_RMTAccès via IBM i Access clients (ODBC, FTP, etc.)

Fonctions d’usage (Function Usage) :
Elles permettent d’activer ou de restreindre une fonctionnalité par utilisateur (sans programmation).
Elles ont un mode par défaut ouvert ou fermé
Vous devez ensuite gérer les exceptions à cette règle
C’est en œuvre par défaut sur votre système
C’est dynamique vous pouvez par exemple avoir un scripte qui modifie le paramétrage pour les heures HNO
C’est compliquer pour tracer les refus

Exit Programs (Programmes d’exit) :
Permettent une personnalisation des contrôles d’accès .
Vous pouvez indiquer des critères d’actions, de ressources, de temporalité
exemple, Michel à droit à uniquement le bibliothèque compta en HNO
Vous maitrisez entièrement la logique de contrôle
Vous pouvez auditer ou loguer avant d’interdire par exemple
Vous pouvez impacter les performances de votre système avec un mauvais programme, voir même bloquer sur un plantage
Ce n’est pas en œuvre par défaut sur votre système
Ce n’est pas dynamique vous devrez arrêter le service concerné pour une prise en compte
Simple pour tracer les refus, c’est votre programme

Conclusion
J’espère que ca vous aidera à faire votre choix
dans tous les cas c’est absolument indispensable aujourd’hui de se préoccuper de ces problèmes à minima des accès ODBC

Toujours d’actualité un service qui n’est pas utilisé doit être arrêter

Quand vous passez de FTP à SFTP vous ne bénéficier plus de ces 2 options

, , Conserver des informations avant migration

Vous changez de machine ou de version et les dates de référence de vos objets et vos sources vont être remise à zéro,
grâce aux services SQL , vous pouvez facilement conserver temporairement ces informations

Vous pourrez par exemple avoir besoin de ces informations pour faire une analyse d’impact sur l’utilisation de certain programmes ou de sources

Pour garder une trace de ces informations voici ce que vous pouvez faire

créer une bibliothèque

==> CRTLIB MIGRATION

vous avez intérêt à extraire les informations juste avant la migration

1) Sur les objets

Vous pouvez utiliser la fonction table QSYS2.OBJECT_STATISTICS

exemple :

create table migration.lstobj as(
SELECT * FROM TABLE (
QSYS2.OBJECT_STATISTICS(‘ALL’,’ALL’) ) AS X
) with data

Vous pouvez si vous le voulez choisir ou éliminer des objets ou des bibliothèques.

2) sur les sources

Si vous avez des fichiers sources QRPGLESRC, QCLSRC etc …

Vous pouvez utiliser la vue QSYS2.SYSPARTITIONSTAT

exemple :

create table migration.lstsrc as(
SELECT *
FROM qsys2.SYSPARTITIONSTAT WHERE
not source_type is null and NUMBER_ROWS > 0
) with data

ici on limite aux membres sources non vide

Si vous avez des fichiers sources dans L’ifs, nodejs, php, python, ou même des développements traditionnels en RPGLE ou CLLE

Vous pouvez utiliser la fonction table QSYS2.IFS_OBJECT_STATISTICS

exemple :

create table migration.lstifs1 as(
SELECT *
FROM TABLE (
qsys2.ifs_object_statistics(
start_path_name => ‘/Votre_repert/’ ,
subtree_directories => ‘YES’
)
)
) with data

Vous devrez limiter à vos repertoires de sources , vous pouvez en faire plusieurs

Ensuite vous devrez envoyer votre bibliothèque sur le systéme cible

Soit par la migration naturelle qui emmènera toutes les bibliothèques ou par une opération spécifique d’envoi de la bibliothèque FTP, SAVRSTLIB etc…

Attention :

Après 6 mois cette bibliothèque devra être supprimée, elle ne servira plus à rien

Remarque :

Vous pouvez également inclure dans cette bibliothèque d’autres éléments qui pourront être utile comme :

La liste des valeurs systèmes , QSYS2.SYSTEM_VALUE_INFO
Le planning des travaux , QSYS2.SCHEDULED_JOB_INFO
les programmes d’exit , QSYS2.EXIT_PROGRAM_INFO
les watchers , QSYS2.WATCH_INFO
les bases de données DRDA , QSYS2.RDB_ENTRY_INFO
les reroutages de travaux , QSYS2.ROUTING_ENTRY_INFO
la table des réponses par défaut , QSYS2.REPLY_LIST_INFO
etc …

, Débuter avec les webservices

Vous voulez commencer à utiliser des webservices en étant consommateur à partir de votre partition IBMi
Et vous de ne savez pas par ou commencer voici un exemple très simple, prêt à l’emploi que vous pourrez améliorer
on a choisi le site jsonplaceholder.typicode.com qui permet un accès libre , merci à eux
On peut l’utiliser à partir d’un GET et le flux renvoyé est du JSON
On utilisera la fonction QSYS2.HTTP_GET de la manière la plus basic
On parsera le flux recu en utilisant la fonction JSON table

2 prérequis :

Vous devrez avoir une version V7R4

Votre partition devra sortir vers le site jsonplaceholder.typicode.com

**free
ctl-opt dftactgrp(*no) actgrp(*caller);
// Un exemple simple d'un appel de webservice
// on utilise le site   jsonplaceholder.typicode.com
// Vous avez les numéros de 1 à 11
// Le flux renvoyé est du JSON on le parse dans une deuxième requête
//
dcl-s url varchar(256) inz('https://jsonplaceholder.typicode.com/users/1');
dcl-s Response Varchar(10000);
dcl-s nom varchar(100);
dcl-s email varchar(100);
dcl-s ville varchar(100);
dcl-s erreur varchar(200);
// Utilisation de la fonction QSYS2.HTTP_GET
exec sql
 set :response = QSYS2.HTTP_GET(:url) ;
if sqlcode <> 0;
  erreur = 'Erreur appel HTTP_GET : SQLCODE = ' + %char(sqlcode);
  SND-MSG *INFO erreur;
  return;
endif;
// Parsing du JSON avec JSON_TABLE
exec sql select name, email, address_city into
:nom, :email, :ville
 from json_table(
:response, '$' columns ( name varchar(100) path '$.name',
                         email varchar(100) path '$.email',
                         address_city varchar(100) path '$.address.city' ) ) as JT;
if sqlcode <> 0;
  erreur = 'Erreur parsing JSON : SQLCODE = ' + %char(sqlcode);
  SND-MSG *INFO erreur;
  return;
endif;
// Affichage du résultat
SND-MSG *INFO ('Nom : ' + nom);
SND-MSG *INFO ('Email : ' + email);
SND-MSG *INFO ('Ville : ' + ville);
// Fin de traitement
return;

.

Conclusion

Simple et efficace

Vous voyez qu’une connaissance de JSON et indispensable et donc au moins une V7R4

Appeler un webservice c’est assez simple
Attention la mise au point dans certains cas peut être compliqué
les idées d’amélioration seront :
Tester le httpstatus qui peut être renvoyer dans le Header
Parser le flux directement dans la requete http_get

etc …

Pour en savoir plus adressez vous à mes collègues qui sont des spécialistes

Une liste de sites libres pour vous entrainer

Une citation du philosophe Chuck Norris
https://api.chucknorris.io/jokes/random
indispensable, pour bien démarrer la journée


Bored API
https://www.boredapi.com/api/activity
Suggestion d’activité fun


Cat Facts
https://catfact.ninja/fact
Donne un fait amusant sur les chats, pour les « matouvus »


IP API (test IP)
https://ipinfo.io/json

Retourne ton IP, localisation, etc. moins fun mais intéressant

Open-Meteo (météo)
https://api.open-meteo.com/v1/forecast?
Météo gratuite sans clé , les autres nécessitent une clé, vous devrez lui passer des coordonnées

Vous devez échanger un fichier avec un partenaire qui vous le demande au format JSON

Il n’existe pas de CVTJSONxx comme la commande CPYTOIMPF pour le CSV

Pas de panique vous avez des services SQL qui font ceci voici un exemple :

Création d’une table de test

SET SCHEMA = ‘votre base’;
CREATE OR REPLACE TABLE CLIENTS
(
ID_CLIENT INTEGER NOT NULL WITH DEFAULT,
NOM_CLIENT VARCHAR ( 50) NOT NULL WITH DEFAULT,
VILLE VARCHAR ( 50) NOT NULL WITH DEFAULT
) ;
INSERT INTO CLIENTS VALUES(1, ‘Dupont’, ‘Paris’) ;
INSERT INTO CLIENTS VALUES(2, ‘Durand’, ‘Lyon’) ;

Voici la syntaxe qu’il vous faudra utiliser pour le convertir sous forme de flux json

SELECT JSON_OBJECT(
    'id_client' VALUE ID_CLIENT,
    'nom_client' VALUE NOM_CLIENT,
    'ville' VALUE VILLE
)  AS CLIENT_JSON
FROM CLIENTS;   

On utilise la fonction JSON_OBJECT

plus d’information ici

https://www.ibm.com/docs/fr/i/7.5.0?topic=functions-json-object

Maintenant il va falloir générer le fichier .JSON dans l’IFS par exemple en utilisant la procédure IFS_WRITE_UTF8

Malheureusement ce service n’existe pas sous forme de fonction on devra utiliser une des possibilités de SQL « Dynamic compound statement » qui permet de compiler un module dynamiquement

Ce qui donnera donc

BEGIN
    CALL QSYS2.IFS_WRITE_UTF8(
        PATH_NAME => 'PLB_CLIENTS.json',
        LINE => '',
        OVERWRITE => 'REPLACE',
        END_OF_LINE => 'NONE'
    );
    FOR SELECT TRIM(CAST(JSON_OBJECT(
                    'id_client' VALUE ID_CLIENT, 
                    'nom_client' VALUE NOM_CLIENT, 
                    'ville' VALUE VILLE
                ) AS VARCHAR(32000))) AS Line_to_write
        FROM PLB.CLIENTS
        DO
            CALL QSYS2.IFS_WRITE_UTF8(
                PATH_NAME => 'PLB_CLIENTS.json',
                LINE => Line_to_write
            );
    END FOR;
END;   

Si vous avez beaucoup de fichiers à convertir ca peut être fastidieux pas de panique nous avons fait une commande qui va vous aider

https://github.com/Plberthoin/PLB/tree/master/GTOOLS, vous avez l’habitude un source SQLRPGLE et un source CMD à compiler , voila SME

Vous pouvez enregistrer le scripte SQL (CVTFICJSON.SQL) avec l’option Sauvegarder le scripte

Vous pourrez le customiser :

en le formatant par ACS ,

en enlevant des zones

en sélectionnant des enregistrements

etc …

Remarque :

Vous avez une option pour agréger

Vous pouvez faire beaucoup mieux , c’est juste pour vous aider à démarrer dans le domaine

, Contrôler la cohérence des services SQL

On utilise de plus en plus les services SQL sur IBMi

Il peut être important de contrôler les services SQL installés sur votre machine

Vous avez 2 programmes qui sont fournis, nous vous proposons un petit habillage pour vous faciliter leurs utilisations
QSQIBMCHK et QSQSYSIBM

La commande

CMD        PROMPT('Contrôles services SQL')               
 PARM       KWD(TYPE) TYPE(*CHAR) LEN(4) RSTD(*YES) +      
              DFT(*CHK) VALUES(*CHK *FIX) PROMPT('Type +   
              de vérification') 

Le programme CLLE

pgm parm(&type)                                                         
dcl &type *char 4                                                       
dclf qsys/QADSPFFD                                                      
/* Récupération du CCSID de la première zone de qadbxref */             
             DSPFFD     FILE(QSYS/QADBXREF) OUTPUT(*OUTFILE) +          
                          OUTFILE(QTEMP/WADSPFFD) /* w */               
             OVRDBF     FILE(QADSPFFD) TOFILE(QTEMP/WADSPFFD) +         
                          LVLCHK(*NO)                                   
             RCVF                                                       
/* Changement du job au bon CCSID                              */       
             chgjob ccsid(&WHCSID)                                      
/* Pour contrôler les objets fournis par IBM qui sont manquants      */ 
if cond(&type = '*CHK') then(do)                                        
CALL QSYS/QSQIBMCHK                                                     
enddo                                                                   
/* Pour corriger                                                     */ 
if cond(&type = '*FIX') then(do)                                        
CALL QSYS/QSQSYSIBM                                                     
enddo                                                                   
             dltovr     FILE(QADSPFFD)  
endpgm    

Pour contrôler

==>CTLSRVSQL *CHK

Vous allez avoir ces messages dans la LOG

QSQXRLF OBJECTS FOUND = 55
QSQXRLF OBJECTS UNKNOWN = 0
QSQXRLF OBJECTS MISSING = 0
QSQSYSIBM OBJECTS FOUND = 716
QSQSYSIBM OBJECTS UNKNOWN = 0
QSQSYSIBM OBJECTS MISSING = 0
SYSTOOLS OBJECTS FOUND = 105
SYSTOOLS OBJECTS UNKNOWN = 0
SYSTOOLS OBJECTS MISSING = 0
TOTAL IBM OBJECTS FOUND = 876
TOTAL IBM OBJECTS UNKNOWN = 0
TOTAL IBM OBJECTS MISSING = 0
QSQIBMCHK – OBJECT VERIFICATION COMPLETE

Si vous avez des erreurs par exemple 1 dans OBJECTS UNKNOWN vous devrez corriger

Pour corriger

==>CTLSRVSQL *FIX

Vous allez avoir ces messages dans la LOG

QSQSYSIBM ASNEEDED PROCESSING SUCCESSFUL FOR 1236 COMPONENTS.

PS :
Vous n’avez pas besoin d’être en mode restreint
Mais vous devez être *SECADM et *ALLOBJ

Pour en savoir plus
https://www.ibm.com/support/pages/qsqibmchk-tool

Merci Jean-Marie pour le thème suggéré