, Rechercher dans l’IFS avec OMNIFIND

Vous trouvez que la recherche de fichier dans l’IFS par les services SQL n’est pas assez performante ;
Par exemple vous gérez des archives PDF sur l’IFS (toujours penser à épurer régulièrement).

Vous avez un logiciel gratuit qui s’appelle Omnifind, celui-ci permet de gérer des index textes sur l’IFS
mais aussi sur les spools si vous le désirez.

Notre besoin était sur l’IFS nous allons donc étudier ce cas au cours de cet article.

Nous avons déjà posté un autre article sur la mise en œuvre de Omnifind

Vous pouvez en savoir plus ici

https://www.ibm.com/support/pages/omnifind-ibm-i

Une fois que vous avez mis en place Omnifind et créé l’index qui va bien.

Vous retrouvez des procédures dans la collection que vous avez créée, chez nous elle s’appelle OMNIFIND.

Dans ces procédures vous en avez une qui s’appelle SEARCH qui va scanner dans vos fichiers
ceux qui contiennent une chaine de caractères.

Cette procédure renvoie un resultset ; Ce qui explique qu’en retour dans une interface ACS vous avez une liste et dans
un STRSQL juste un message de fin.

La problématique est donc de savoir comment traiter un resultset dans un programme avec, en plus, un résultat renvoyé dans un CLOB au format XML

Nous avons choisi de faire cet exercice en RPGLE.

Je vous joins un exemple ici, vous pouvez l’améliorer par exemple pour le parsage XML, ou la gestion des variables :

**free
//
// Options de compile
//
ctl-opt
   DFTACTGRP(*NO) ;
//
// Paramètre chaine à scanner
//
Dcl-Pi *N;
  p_texte char(30) ;
End-Pi;
//
// Initialisation des options de compile SQL
//
EXEC SQL
        Set Option
          Naming    = *SQL,
          Commit    = *None,
          UsrPrf    = *User,
          DynUsrPrf = *User,
          Datfmt    = *iso,
          CloSqlCsr = *EndMod;
// Zone du result set  pour la procedure SEARCH
dcl-s objtype char(10) ;
dcl-s objattr char(10) ;
dcl-s objlib  char(10) ;  // peut être nul int1
dcl-s objname char(10) ;  // peut être nul int2
dcl-s objinfo SQLTYPE(CLOB:65531)  ;
dcl-s objmod  TIMESTAMP  ;
dcl-s objtime int(5)     ;
// Déclaration de  l'adresse du result set
dcl-s RS1 SQLTYPE(RESULT_SET_LOCATOR) ;
// pour les variables à valeurs nulles
dcl-s Ind1 int(5)     ;
dcl-s Ind2 int(5)     ;
// Variables de travail
Dcl-S w_objinfo    Varchar(65531);
//
// Contrôle des paramètres
//
if %parms  = 0 ;
  dsply ('Vous devez saisir une recherche')  ;
  return ;
  *inlr = *on ;
endif;
// Appel de la procédure ici de la collection OMNIFIND
// la procédure est dans la collection qui contient l'index TXT
exec sql
   Call OMNIFIND.SEARCH(:p_texte);
// Si result set créé
if SQLCODE = +466;

// Vous associez le resultset de la procédure à votre curseur 
  exec sql ASSOCIATE LOCATORS (:RS1) WITH PROCEDURE OMNIFIND.SEARCH ;
  exec sql ALLOCATE C1 CURSOR FOR RESULT SET :RS1;
  // boucle de traitement du curseur associé
  exec sql fetch c1 into :objtype   ,
                         :objattr   ,
                         :objlib:ind1 ,
                         :objname:ind2 ,
                         :objinfo   ,
                         :objmod    ,
                         :objtime   ;
  //  ici traitement du fichier
  //     récupération des données du CLOB
  Traitement() ;
  dow sqlcode = 0;
    // traitement des variables lues...
    exec sql fetch c1 into :objtype   ,
                           :objattr   ,
                           :objlib:ind1,
                           :objname:ind2,
                           :objinfo   ,
                           :objmod    ,
                           :objtime   ;
    //     récupération des données du CLOB
    Traitement() ;
  //  ici traitement du fichier
  //
  ENDDO;
  exec sql close C1;
ENDIF;
*inlr = *on;
//
//  Procédure de traitement
//
dcl-proc Traitement ;
  dcl-s w_file    char(256);
  dcl-s pos1      int(5) ;
  dcl-s pos2      int(5) ;
  // dcl-s w_text    char(50);
  w_objinfo = %subst(objinfo_data : 1 : objinfo_len) ;
  //  traiter XML ca serait mieux
  // recherche <file_path>
  pos1 = %scan('<file_path>' : w_objinfo ) ;
  // recherche </file_path>
  pos2 = %scan('</file_path>' : w_objinfo ) ;
  w_file = %subst(w_objinfo : (pos1 + 11) : (pos2 - (pos1 + 11))) ;
//
// Traitement du fichier  w_file
//
//w_text = %subst(w_file   : 1 : 50) ;
//dsply w_text ;
end-proc ; 


Les points importants à comprendre :

C’est la déclaration et l’utilisation d’un curseur basé sur un resultset – j’ai mis un commentaire dans le source.

Et la manipulation d’une variable CLOB.

Voila les points principaux à retenir :

Quand vous déclarez une dans votre programme RPGLE votre variable CLOB,

DCL-S XXX SQLTYPE(CLOB:nnnn);

En réalité, après traduction c’est déclaré comme ça :

DCL-DS XXX;
XXX_LEN UNS(10);
XXX_DATA CHAR(nnnn) CCSID(*JOBRUNMIX);
END-DS XXX;

nnnn étant la longueur de la zone
XXX_LEN contient la longueur du contenu de la zone
XXX_DATA contient la donnée

Dans la partie traitement vous pouvez facilement faire un QCMDEXC en SQL, par exemple pour faire une copie, un envoi ou une suppression

Voila un exemple concret que vous pouvez adapter, n’hésitez pas à tester Omnifind car il est gratuit et comme on met de plus en plus de chose dans l’IFS, il est de plus en plus utile !

, , , Convertir un SAVF en PF

il faut savoir qu’un fichier SAVF est un fichier PF de 528 de long.

Vous pouvez avoir besoin pour différentes raisons de convertir SAVF en PF pour manipuler plus simplement par SQL, pour contourner des restrictions d’utilisations etc ..

Vous trouverez ici 2 commandes qui vont vous permettent de réaliser cette opération : https://github.com/Plberthoin/PLB/tree/master/CPYSAVF

Copier un SAVF dans un PF

Voir le PF

Copier un PF dans un SAVF

Voir le Résultat

Pour voir ce résultat taper la commande DSPSAVF , Voir WRKSAVF si vous avez installez notre outil

Remarque :

Outil simple mais efficace qui peut rendre des services à des administrateurs

Vous voulez mettre à jour des informations dans une table sans faire de boucle de lecture ? Cet article est pour vous.

Prenons un exemple. Vous consommez un service web qui vous transmet une liste d’article que vous devez stocker en base de données.

L’ordre DB2 MERGE vous permettra d’ajouter les articles qui n’existent pas et de mettre à jour ceux qui existent.

Création de table ARTICLE

CREATE TABLE ARTICLE ( 
	CDART CHAR(10) CCSID 1147 DEFAULT ' ' , 
	DESART CHAR(30) CCSID 1147 DEFAULT ' ' )   
	  
	RCDFMT ARTICLE    ; 
  
LABEL ON TABLE ARTICLE 
	IS 'Base article' ; 
  
LABEL ON COLUMN ARTICLE 
( CDART IS 'Code article         ' , 
	DESART IS 'Désignation article  ' ) ;

Code RPG avec ajout ou mise à jour d’un article.

ctl-opt alwnull(*usrctl) option(*nodebugio:*srcstmt);

//Variables
dcl-s code_article        char(10);
dcl-s designation_article char(30);

exec sql SET OPTION commit = *none;

code_article        = 'AB123456';
designation_article = 'Ma désignation';

Exec Sql
   MERGE INTO ARTICLE as ARTICLE
   USING(VALUES (:code_article, :designation_article))
   AS SOURCE(CDART,DESART)
   ON ARTICLE.CDART = SOURCE.CDART
   -- Mise à jour de la désignation si code article trouvé
   WHEN MATCHED THEN UPDATE
         SET DESART  = SOURCE.DESART
   -- Ajout de l'article
   WHEN NOT MATCHED THEN INSERT VALUES (SOURCE.CDART,
                                        SOURCE.DESART)
   NOT ATOMIC CONTINUE ON SQLEXCEPTION;


*inLR=*ON;

Vous pouvez également supprimer l’article s’il est trouvé.

WHEN MATCHED THEN DELETE

On peut rajouter des conditions en plus du MATCHED. Dans la ligne ci-dessous je ne veux mettre à jour que l’article ‘TEST’. Pas grand intérêt mais c’était pour montrer un exemple.

WHEN MATCHED AND CDART='TEST' THEN UPDATE SET DESART  = SOURCE.DESART

Vous pouvez aussi tester la valeur pour la contrôler avant de faire la mise à jour.

WHEN MATCHED AND SOURCE.DESART IS NOT NULL THEN UPDATE SET DESART  = SOURCE.DESART

Il est également possible de prendre le résultat d’un select au lieu des deux variables

MERGE INTO ARTICLE as ARTICLE
   USING(VALUES (SELECT CDART, DESART FROM ARTICLE2))
   AS SOURCE(CDART,DESART)
   ON ARTICLE.CDART = SOURCE.CDART
   -- Mise à jour de la désignation si code article trouvé
   WHEN MATCHED THEN UPDATE
         SET DESART  = SOURCE.DESART
   -- Ajout de l'article
   WHEN NOT MATCHED THEN INSERT VALUES (SOURCE.CDART,
                                        SOURCE.DESART)
   NOT ATOMIC CONTINUE ON SQLEXCEPTION;

Je ne l’ai pas dit au début de l’article mais ça fonctionne aussi sur les PF.

, , Savoir où un programme est utilisé

Vous voulez savoir où un programme est utilisé sur votre partition.

Généralement vous avez une cross référence de vos programmes basée sur des sorties de DSPPGMREF, et vous retrouvez assez facilement les programmes qui l’utilisent dans votre application.

Mais votre programme peut être tagué dans d’autres ressources, (Systèmes, SQL, etc …), voici une liste et comment les analyser

1) Table des travaux planifiés WRKJOBSCDE

SELECT * FROM QSYS2.SCHEDULED_JOB_INFO where command_string like(‘%VOTREPGM%’);

2) Table des travaux planifiés AJS (Advanced Job Scheduler)

SELECT * FROM QUSRIJS.QAIJS1CM where cmcmd like(‘%VOTREPGM%’)

Rappel, il est gratuit à partir de la version V7R5

3) Triggers, fonctions, procédure (catalogage SQL)

select * from qsys2.sysroutine where external_name like(‘%VOTREPGM%’)
and routine_body = ‘EXTERNAL’;

4) Webservices

echo "Recherche : VOTREPGM" > lst_webservices.txt ;
echo "/www/*/webservices/services/*/WEB-INF/classes/*.config" >> lst_webservices.txt ;  
echo "---------------" >> lst_webservices.txt ;       
/usr/bin/grep -i -n "VOTREPGM" /www/*/webservices/services/*/WEB-INF/classes/*.config  >> lst_webservices.txt

On suppose que vos configurations sont dans www ce qui est le défaut, et on écrit dans un fichier lst_webservices.txt de votre répertoire courant.

5) Menus SDA

SELECT *
FROM QSYS2.MESSAGE_FILE_DATA
where
MESSAGE_ID like(‘USR%’) and
MSG_TEXT like(‘%VOTREPGM%’)

6) Exit PGM

select *
from qsys2.exit_program_info where exit_program = ‘VOTREPGM‘ ;

7) Les watchers

select * from qsys2.watch_info where WATCH_PROGRAM = ‘VOTREPGM‘;

8) Les postes travaux à démarrage automatique des sous-systèmes

SELECT a.autostart_job_name, a.job_description_library, a.job_description, b.request_data
FROM QSYS2.AUTOSTART_JOB_INFO a
JOIN QSYS2.JOB_DESCRIPTION_INFO b
ON a.job_description_library = b.job_description_library
AND a.job_description = b.job_description
WHERE UPPER(b.request_data) Like(‘%VOTREPGM%’);

Remarques :

C’est sans doute pas exhaustif , mais c’est déjà ça

Pensez à regarder les planificateurs si vous en avez ?

Si vous avez une machine de PROD et de DEV, il peut être intéressant d’analyser les 2 partitions.

Voila, simple mais efficace

, , Contrôler l’existence d’un fichier stream (IFS)

Voici donc trois exemples de solutions pour tester l’existence d’un fichier dans l’IFS en CL et en SQLRPGLE.
(Il existe d’autres méthodes, mais celles-ci sont les plus simples).

La commande CHKOUT permet de verrouiller un objet, ainsi les autres utilisateurs et travaux ne peuvent plus que le lire ou le copier. Il suffit de monitorer cette commande en attendant le message CPFA0A9 qui indique que le fichier n’existe pas.

Cette méthode est donc utile lorsque l’on souhaite par la même occasion verrouiller l’objet recherché.

Il ne faut pas oublier de déverrouiller l’objet une fois votre opération terminée avec la commande CHKIN.

En SQL et SQLRPGLE, le plus simple reste d’utiliser la fonction table IFS_OBJECT_STATISTICS. Pour s’assurer ne pas tomber sur un répertoire portant le nom du fichier ou autre, il est préférable de renseigner les paramètres subtree_directories et object_type_list (bien entendu en renseignant *DIR si on cherche un répertoire).

Il suffit ensuite de tester le sqlCode, s’il est égal à 100 cela signifie que le fichier est inexistant.

Remarque

Il faut tout de même prendre en compte les droits de l’utilisateur qui réalise ces tests, en fonction de la méthode utilisée, un autre message pourrait être émit ou le fichier pourrait lui apparaitre comme inexistant.

Pour plus de détails

Documentation IBM – CHKOUT : https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/chkout.html
Documentation IBM – CHKIN : https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/chkin.html
Documentation IBM – MOV : https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/mov.html
Documentation IBM – IFS_OBJECT_STATISTICS : https://www.ibm.com/docs/en/i/7.5?topic=services-ifs-object-statistics-table-function
, , L’appel d’API de traduction sur IBM i

Pour traduire du texte dans un programme RPGLE, on peut utiliser un appel à une API de traduction (via SQL). Les principales API de traductions publiques sont DeepL API et Google Cloud Translation. Dans cet article nous utiliserons l’API de DeepL dans sa version gratuite, limitée à 500 000 caractères par mois, mais DeepL propose également d’autres offres payantes pour son API.

Documentation de l’API DeepL : https://www.deepl.com/docs-api

Introduction

Pour réaliser simplement un appel API en RPGLE, il est possible d’utiliser plusieurs méthodes SQL qui permettent de :

  • Formater le corps d’une requête API en JSON
  • Exécuter la requête API en POST
  • Récupérer des informations dans la réponse JSON

Nous allons voir ensemble un exemple d’utilisation de ces méthodes pour créer un programme qui traduit un texte donné dans la langue choisie.

Cas d’exemple

Prenons donc le cas d’un programme simple qui récupère les paramètres suivants :

  1. Le texte à traduire
  2. Le code de la langue ciblée (‘FR’, ‘EN’, ‘ES’, ‘DE’, …)

Le programme affichera ensuite via un dsply le résultat de la traduction et le code retour HTTP de la requête.

On commence par les déclarations du programme.

On y retrouve :

  • nos paramètres texte et langue_cible
  • une ds qui contiendra les données retournées par la requête API (traduction et code HTTP)
  • deux variables pour l’URL de la requête et le token d’authentification (qui s’obtient en créant un compte API sur DeepL)

On peut maintenant construire notre requête API en utilisant SQL.

La requête HTTP faite à l’API est exécutée via la fonction de table QSYS2.HTTP_POST_VERBOSE (qui est similaire à QSYS2.HTTP_POST, mais avec plus de détails en retour).

Elle prend en paramètres :

  • l’URL d’appel de l’API
  • le body de notre requête -> un objet JSON contenant le texte et la langue cible
  • le header de notre requête -> un objet JSON contenant des informations supplémentaires requises comme le token d’authentification

Ici la traduction se fera avec une détection automatique de la langue source (celle du texte qu’on demande à traduire), mais on peut également ajouter le paramètre source_lang dans notre body si on veut préciser la langue source de notre texte.

On remarque l’usage des fonctions SQL JSON_OBJECT et JSON_ARRAY qui permettent de formater des données au format JSON (JSON_ARRAY pour un tableau JSON)

Les éléments que l’on récupère grâce à cette fonction sont au format JSON, on utilise donc la fonction JSON_VALUE pour les récupérer en VARCHAR dans notre ds résultat.
Dans notre cas, on s’intéresse au texte traduit et au code retour HTTP, c’est donc les valeurs translations et HTTP_STATUS_CODE qui sont extraites du JSON.


Vous pouvez obtenir plus d’informations sur la structure des requêtes API DeepL et leurs retours sur la documentation en ligne de l’API : https://www.deepl.com/docs-api

Pour finir, on affiche avec un simple dsply nos éléments de retour (dans le cas où la requête SQL a été exécutée sans erreur).

Test du programme

Lorsqu’on appelle notre programme avec les bons paramètres :

On obtient bien une traduction du texte saisi, et le code retour 200 (réussite).

, Triggers instead of

On connait les triggers before et after, mais on peut créer des triggers instead of pour remplacer l’action prévue, par exemple en écrivant dans une autre table

Ce type de trigger ne peut être mis que sur des vues

Voici un exemple

Création d’une table des frais

CREATE TABLE PLB/CLIENT_FRAIS (
NOM CHAR ( 40) NOT NULL WITH DEFAULT,
PRENOM CHAR ( 30) NOT NULL WITH DEFAULT,
MONTANT DEC ( 9, 2) NOT NULL WITH DEFAULT,
NUMEMP CHAR ( 6) NOT NULL WITH DEFAULT)

Création d’une table audit des frais

CREATE TABLE PLB/AUDIT_FRAIS (
NUMEMP CHAR ( 06) NOT NULL WITH DEFAULT,
MONTANT DEC ( 9, 2) NOT NULL WITH DEFAULT,
DATFRAIS DATE NOT NULL WITH DEFAULT,
HEUREFRAIS TIME NOT NULL WITH DEFAULT)

Création d’une vue sur la table des frais

CREATE VIEW PLB/CLIENT_FRAIS_vue AS SELECT * FROM PLB/CLIENT_FRAIS

Création d’un trigger Instead of sur cette vue
Qui quand on écrit dans la table des frais remplace cette écriture par une écriture dans le fichiers des audits de frais.

CREATE TRIGGER INSTEADTEST
INSTEAD OF
INSERT ON CLIENT_FRAIS_vue
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2ROW
BEGIN ATOMIC
INSERT INTO AUDIT_FRAIS
VALUES(N.NUMEMP, N.MONTANT, current date , current time);

END;

Test ajout d’un enregistrement

INSERT INTO PLB/CLIENT_FRAIS VALUES(‘Bouzin’, ‘Maurice’, 12100, 253)

Vous retrouvez l’enregistrement dans le fichier audit de frais et non celui des frais

, , , Requêtes SQL dans ACS extraites de Navigator for i

Navigator for i utilise les services SQL, il vous indique les requêtes qui ont été utilisées.

Vous pouvez les rejouer dans ACS exécuteur de scripts.

Voici comment :

Vous avez un bouton SQL qui permet d’accéder à la requête

Par contre quand vous voulez exécuter cette requête, vous avez ce message :

Il vous faut démarrer un service sur ACS pour accepter l’exécution de ces scripts

Vous voyez votre service à l’état démarré

Vous récupérez la requête SQL dans ACS

Voila c’est simple et efficace vous pouvez également copier la requête dans propriétés si vous préférer

, Zones avec informations confidentielles

Vous pouvez utiliser le catalogue base de données pour identifier vos informations sensibles

Pour cela vous allez utiliser les vues syscolumns de QSYS2 (norme IBMi) ou sqlcolumns de SYSIBM (norme DB2)

Vous pouvez rechercher toutes les zones qui contiennent (email, mail, RIB, IBAN, ETC..)

Dans notre exemple , on recherchera les zones IBAN dans toutes les tables en analysant :
Nom de zone SQL
Nom de zone IBMI
Entête de colonne
Texte de colonne

Sans différentiation de majuscule minuscule

Et on sortira la liste des droits public sur ces objets


— liste des fichiers avec une zone IBAN

— Avec un IBAN

SELECT COLUMN_NAME AS Zone,
IFNULL(COLUMN_HEADING, ' ') AS Entete,
IFNULL(COLUMN_TEXT, ' ') AS Text,
TABLE_SCHEMA AS bibliotheque,
TABLE_NAME AS fichier,
(SELECT OBJECT_AUTHORITY
FROM QSYS2.OBJECT_PRIVILEGES
WHERE SYSTEM_OBJECT_SCHEMA = TABLE_SCHEMA
AND OBJECT_NAME = TABLE_NAME
AND OBJECT_TYPE = '*FILE'
FETCH FIRST ROW ONLY) AS DROIT_public
FROM qsys2.syscolumns
WHERE COLUMN_NAME LIKE ('%IBAN%')
OR SYSTEM_COLUMN_NAME LIKE ('%IBAN%')
OR UCASE(COLUMN_HEADING) LIKE ('%IBAN%')
OR UCASE(COLUMN_TEXT) LIKE ('%IBAN%');

Conclusion :

Ca veut dire que toutes les tables avec *public à *USE sont visualisables par tous les utilisateurs de la machine.

Pour sécuriser vous devez le faire sur les fichiers
Soit
– Mettre en place des droits sur l’objet, attention l’utilisateur peut avoir droit à ce fichier quand il est dans l’application.
Soit
– Sécuriser un service d’accès par exemple ici ODBC, pour éviter un accès remote, (Fonctions usage ou programme d’exit sont les meilleurs solutions)

Bien sur si vous créez de nouvelle table vous pouvez crypter ces zones, ce qui la rendrait illisible à un utilisateur qui ne connait pas la clé

, Vue rapide du catalogue DB2

Comment à voir une vue rapide du catalogue de votre base de données ?

Voici une solution qui consiste à utiliser le centre de santé

Dans l’onglet « Schémas »
sur la base de donnée faites un clic droit
sélectionnez « Centre de santé »

Les fichiers du catalogue DB2 sont dans QSYS et ils commencent par QADB.

Commencez par mettre en place ce filtre et cliquez sur régénération

Vous allez utiliser 2 vues

« limites de taille » , qui vous permettra de voir le nombre d’enregistrements

Vous voyez par exemple dans QADBFILD la liste des zones de votre DB

« Activité« , qui vous donnera des informations sur les mises à jour sur vos fichiers.

Conclusion :


Vous avez une vue syntaxique de la taille et des mouvements de votre catalogue DB2.

L’exécution de la requête peut être un peu longue quand vous cliquez sur régénération.

Pour voir les informations contenues dans votre catalogue, utilisez les vues SYS* de QSYS2 ou de vos SCHEMAS.