Suite à des demandes multiples, je propose une implémentation de « DUMP » des enregistrements d’une table (plutôt d’un objet *FILE / PF-DTA, que ce soit un PF ou une table).
L’idée est d’obtenir u script SQL contenant les instructions INSERT permettant de reproduire les données dans une autre base.
Premièrement, la réplication de données sans utiliser les commandes de sauvegarde/restauration. Ces dernières nécessitent des droits élevés, alors qu’ici nous ne faisons que manipuler de la donnée.
Deuxièmement, dans le cadre de traitement de journaux (initialement en vue d’une fonction de type CDC), pour permettre d’isoler un enregistrement que l’on souhaite répliquer (avec ou sans transformation) dans une autre table.
Vous trouverez certainement d’autres usages !
Limites
Le code est fourni « as is », pour démonstration.
Quelques limites d’usage actuellement
Types de colonnes non supportées actuellement : CLOB, BLOB, DATALINK, XML, GRAPHIC, VARGRAPHIC, {VAR}CHAR CCSID 65535
Pas plus de 16Mo par enregistrement
On ne gère pas les alias, partitions, IASP
250 colonnes maximum
En cas de multi-membres, seul le premier membre est traité
On peut bien évidemment ajouter de nouvelles fonctionnalités !
N’hésitez pas à donner un feedback, améliorer le code
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
J’ai utilisé la convention de nommage *SYS
et dans tous les cas on met à jour la date de modification
-- voici les scripts pour tester
-- Création de la table des produits
-- Option *SYS et *NONE
CREATE TABLE GDATA/PRODUCTS (
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 du fichier
INSERT INTO GDATA/PRODUCTS VALUES(1, 'CLOU', 50, current timestamp) ;
INSERT INTO GDATA/PRODUCTS VALUES(2, 'VIS', 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.
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.
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 !
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2025-07-12 09:07:372025-07-12 09:09:04Gestion du SQLCODE dans un SQL embarqué
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.
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 :
Type
Où
Support valeur nulle ?
Support doublon ?
Commentaire
Contrainte de clé primaire
Table
Non
Non
Valeur nulle non admise, même si la colonne clé le supporte
Contrainte d’unicité
Table
Oui
non : valeurs non nulles oui : valeurs nulles
Gère des clés uniques uniquement si non nulles
Index unique
Index
Oui
Non
Gère des clés uniques. La valeur NULL est supportée pour 1 unique occurrence
Index unique where not null
Index
Ouis
non : 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
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 …
https://www.gaia.fr/wp-content/uploads/2017/02/team1.png600600Nathanaël Bonnet/wp-content/uploads/2017/05/logogaia.pngNathanaël Bonnet2025-05-26 11:45:252025-05-26 11:45:26Gérer vos listes de validation avec SQL !
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_OPEN
QIBM_QZDA_INIT, QIBM_QZDA_NDB1
Autorise/refuse l’ouverture de base de données (SQL, ODBC, etc.)
SQL Server Mode (QSQSRVR)
QIBM_DB_QSQSRVR
N/A
Contrôle l’accès aux jobs QSQSRVR (SQL server mode)
DDM/DRDA
QIBM_DB_DDMDRDA
QIBM_QDDMDRDASERVER, QIBM_QDDSQLDRDA
Accès aux bases via DDM / DRDA
ODBC Spécifique
N/A
QIBM_QZDA_SQL1, QIBM_QZDA_INIT
Appels SQL via ODBC / DRDA
JDBC
N/A
QIBM_QZDA_SQL1, QIBM_QZDA_INIT
JDBC via Toolbox ou Native JDBC
FTP
QIBM_FTP_SERVER
QIBM_QTMF_SVR_LOGON, QIBM_QTMF_SVR_EXIT
Contrôle l’accès FTP
Telnet
QIBM_TELNET_SERVER
QIBM_QTV_TELNET
Contrôle l’accès Telnet
Remote Command (RUNRMTCMD)
QIBM_NETWORK_SERVER
QIBM_QZRC_RMT
Exécution de commandes distantes
Client Access (iSeries Access, ACS…)
QIBM_ACCESS3270, QIBM_DB_OPEN
QIBM_QZDA_* (exits ODBC), QIBM_QZRC_RMT
Accè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
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 …
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2025-05-08 07:31:422025-05-08 07:31:43Conserver des informations avant migration
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
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2025-05-05 08:06:122025-05-06 08:50:01Débuter avec les webservices
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;
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
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2025-05-02 12:16:162025-05-02 12:16:17Convertissez un fichier en JSON