Archive pour la catégorie : SQL_DB2

Vous êtes ici : / SQL_DB2
, , Contrôler l’existence d’un fichier stream (IFS)

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

Lorsque l’on doit tester l’existence d’un objet dans QSYS.LIB la question ne se pose pas, on utilise la commande prévue à cet effet CHKOBJ. Pour l’IFS par contre il n’y a pas de commande toute faite.

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).

CHKOUT / CHKIN (clle)

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.

PGM        PARM(&FILEPATH)

DCL        VAR(&FILEPATH) TYPE(*CHAR) LEN(256)
DCL        VAR(&EXISTS) TYPE(*LGL)

CHGVAR     VAR(&EXISTS) VALUE('1')

/* Verrouillage de l'objet */
CHKOUT     OBJ(&FILEPATH)
MONMSG     MSGID(CPFA0A9) EXEC(DO)
    /* Fichier non trouvé */
    /* Traitement de l'exception... */
    CHGVAR     VAR(&EXISTS) VALUE('0')
    SNDPGMMSG  MSG('Fichier inexistant')
ENDDO

/* Libération de l'objet si existant */
IF         COND(&EXISTS) THEN(DO)
    CHKIN      OBJ(&FILEPATH)
ENDDO

ENDPGM

MOV (clle)

Une solution plus simple encore, lorsque l’on n’a pas besoin de gérer le verrouillage de l’objet, est l’utilisation de la commande MOV. Initialement, elle permet de déplacer un objet, mais en indiquant le chemin de l’objet d’origine comme objet de destination, aucune action ne sera effectuée sur l’objet (pas de changement de la date de dernière modification) et on pourra encore une fois tester le message CPFA0A9. 

PGM        PARM(&FILEPATH)

DCL        VAR(&FILEPATH) TYPE(*CHAR) LEN(256)

MOV        OBJ(&FILEPATH) TOOBJ(&FILEPATH)
MONMSG     MSGID(CPFA0A9) EXEC(DO)
    /* Fichier non trouvé */
    /* Traitement de l'exception... */
    SNDPGMMSG  MSG('Fichier inexistant')
ENDDO

ENDPGM

IFS_OBJECT_STATISTICS (SQL/SQLRPGLE)

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. 

SELECT path_name
  FROM TABLE (
      qsys2.ifs_object_statistics(
        start_path_name => '/home/jl/file.txt',
        subtree_directories => 'NO',
        object_type_list => '*STMF'
      )
    );

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
/par
, , 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).

/par
, 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

/par
, , , 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

/par
, , , SQL – appel de webservice

Depuis la V7R1 (SF99701 – DB2 – niveau 23), on peut invoquer des web service via SQL. Les fonctions se trouvent dans SYSTOOLS.

En V7R4 TR5, sont sorties de nouvelles fonctions, elles se trouvent dans QSYS2.

Outre les fonctions HTTP, celles pour encoder / décoder en base64 et pour encoder / décoder L’URL, ont aussi été implémentées dans QSYS2.

Rappel des différences entre ces fonctions

Tout d’abord les performances. Les fonctions de QSYS2 permettent un gain non négligeable, elles sont basé sur les fonctions AXIS en C natif, contrairement à celles de SYSTOOLS qui sont basées sur des classes java.

Les paramètres dans l’entête ou le corps du message sont transmis en JSON pour les fonctions de QSYS2, à la place de XML pour celle de SYSTOOLS.

La gestion des certificats est simplifiée par l’utilisation de DCM, alors qu’avec les fonctions de SYSTOOLS, il fallait pousser le certificat dans le magasin du runtime java utilisé par les fonctions HTTP. En cas de multiple versions de java installées, il fallait s’assurer de laquelle servait pour les fonctions HTTP. L’ajout du certificat, se faisait via des commandes shell.

Les types et tailles des paramètres des fonctions ont été adaptés pour ne plus être des facteurs limitants de l’utilisation des fonctions SQL, voici quelques exemples :

Certaines utilisations ont aussi été simplifiées en automatisant des tâches.

Prenons l’exemple d’un appel à un web service avec une authentification basique. Le couple profil / mot de passe doit être séparé par « : » et l’ensemble encoder en base64. C’est la norme HTTP.

Dans le cas des fonctions de SYSTOOLS, il fallait effectuer l’ensemble des opérations, alors qu’avec les fonctions de QSYS2, il suffit de passer le profil et le mot de passe dans la propriété BasicAuth. La mise en forme et l’encodage étant faits directement par les fonctions AXIS :

Il y a par contre un cas limitatif des fonctions QSYS2, que IBM a rajouté, alors que la norme HTTP autorise ce type d’appel.

Il s’agit d’avoir une authentification basique sur un appel en http.

Ce cas n’est pas trop contraignant, aujourd’hui le https est la norme et le http quasiment disparu…. quasiment !
Nous rencontrons encore chez nos clients des web services « interne » en http. La migration en https n’étant pas vendeur auprès des directions qui n’y voit aucun gain pour le métier. C’est l’éternel problème des changements structurels en IT.

Dans ces cas, la fonction de QSYS2, renverra une erreur, assez claire !

Le premier réflexe est de voir avec le fournisseur du service s’il ne dispose pas d’une version en https.

Maintenant, si vous n’avez pas d’autre choix que d’appeler un web service en http avec authentification basique, il faudra continuer d’utiliser les fonctions de SYSTOOLS. Dans tous les autres cas, aucune hésitation, utilisez les fonctions de QSYS2.

Mais mettons nous d’accord, de l’authentification basique en http, ce n’est pas de la sécurité, c’est une absurdité.

En http, le message passe en clair sur la trame réseau, avec votre profil / mot de passe, encodé en base 64, et non encrypté, donc en clair eux aussi.

Edit : Précision apportée par Gautier Dumas de CFD-innovation. Merci à lui.
On peut contourner le problème avec les fonctions de QSYS2. Il ne faut pas utiliser la propriété BASICAUTH, mais construire l’authentification basique comme on le faisait avec celle de SYSTOOLS.
VALUES QSYS2.HTTP_GET(
‘http://hostname/wscommon/api/contacts’,
‘{« header »: »Authorization, BASIC dGVzdHVzZXI6dGVzdHB3ZA== »}’);
Il n’y a donc vraiment plus de raison de continuer avec les fonctions de SYSTOOLS !

/par
, 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é

/par
, 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.

/par
, Connaitre la taille vos MTI

Vous désirez connaitre la taille occupée par les MTI (Maintained Temporary Index) sur votre machine, voici une Méthode.
Vous avez des buckets (espaces dans votre mémoire centrale) sur votre partition.

Vous avez un service QSYS2.SYSTMPSTG qui permet de voir ces buckets

La taille des MTI, c’est le bucket 14

Voici la requête pour voir cette taille

SQL SELECT CURRENT TIMESTAMP AS date_heure,
BUCKET_NUMBER,
GLOBAL_BUCKET_NAME,
BUCKET_CURRENT_SIZE,
BUCKET_LIMIT_SIZE
FROM QSYS2.SYSTMPSTG
WHERE bucket_number = 14

Pour diminuer cette taille il faut créer les index qu’Index advisor vous suggère.

Vous avez une fonction table qui vous indique les MTIs de votre système, QSYS2.MTI_INFO

Ceux qui concernent votre base de données doivent être construits

Ici ceux utilisés depuis une semaine sur la bibliothèque exploit !

SELECT *
FROM TABLE (
QSYS2.MTI_INFO(‘EXPLOIT’, ‘*ALL’)
)
WHERE LAST_BUILD_START_TIME > CURRENT DATE – 7 DAYS

Remarque :

Cet espace sera réutilisé par les autres Buckets

Vous pouvez faire un suivi de ces buckets , par exemple ceux de la base de données pour voir les grandes variations

La mise à jour du fichier d’index Advisor dépendra de la méthode utilisée pour générer l’index, n’hésitez pas à effacer des enregistrements dans le fichier QSYS2.SYSIXADV.

/par
, , Générer un XLS avec SQL

La TR 3 de la V7R5 nous apporte un nouveau service qui va permettre de générer un fichier XLS à partir d’une requête SQL

Exemple :

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
PATH_NAME => ‘LST_option.xls’,
SPREADSHEET_QUERY => ‘Select * from qgpl.qauoopt’ ,
COLUMN_HEADINGS => ‘COLUMN’ ,
SPREADSHEET_TYPE => ‘xlsx’)

Remarque:

ACS doit être installé sur votre partition et si possible à jour

Attention par défaut il génère du csv

/par
, Analyse CPF1164

La TR3 de la V7R5, nous apporte une nouvelle fonction table qui va nous permettre d’analyser plus finement et surtout plus simplement la fin d’un travail, soit en réalité le message CPF1164, en effet on a accès directement au code secondaire .

Exemple :

SELECT FROM_JOB,
JOB_END_CODE,
JOB_END_DETAIL,
SECONDARY_ENDING_CODE,
SECONDARY_ENDING_CODE_DETAIL
FROM TABLE (
SYSTOOLS.ENDED_JOB_INFO()
)
WHERE JOB_END_CODE > 10

Remarque :


Par défaut il analyse la veille soit (current date – 1)

/par