, , , Surveillez vos requêtes SQL sur votre #IBMi

Il est possible que vous ayez des doutes sur les performances de vos requêtes SQL, voici comment avoir une idée rapide de ce qui tourne

On va utiliser ACS
dans Base de données choisir
SQL Performance center

Vous avez un onglet Affichage des instructions

Vous choisissez le filtre à appliquer, ici on choisit les actives, vous pouvez être beaucoup plus pertinent en limitant votre choix.

Une fois que vous avez repéré une requête candidate, il vous suffit de faire un clic droit sur celle ci et vous pouvez lancer directement Visual Explain, qui vous expliquera le comportement de cette requête, vous pourrez alors faire les ajustements qui s’imposent

Le résultat dans V-E

PS:
Le principal critère de performance sur SQL, c’est les index, pensez à surveiller les suggestions faites par Index Advisor

/par
, , Variables SQL utilisables dans vos scripts ?

Voici quelques variables que vous pouvez utiliser dans vos requêtes SQL

par exemple Client_Ipaddr qui est arrivée avec la TR4

Quelques registres et variables d’environnement

select
current time as heure_en_cours,
current date as date_en_cours,
current user as utilisateur_courant,
current timestamp as timestamp_en_cours,
CURRENT CLIENT_ACCTNG as Client_connexion,
CURRENT CLIENT_APPLNAME as Client_Application,
Current timezone as Fuseau_Horaire,
Current server as Current_Server ,
current path as Current_path,
CURRENT CLIENT_APPLNAME as Programme_client,
CURRENT CLIENT_USERID as Utilisateur_client,
CURRENT CLIENT_PROGRAMID as Programme_client
from sysibm.sysdummy1

Quelques Variables globales dans SYSIBM

select
SYSIBM.Client_Host as Client_Host,
SYSIBM.Client_Ipaddr as Client_Ipaddr,
SYSIBM.Client_Port as Client_Port,
SYSIBM.Package_Name as Package_Name,
SYSIBM.Package_Schema as Package_Schema,
SYSIBM.Package_Version as Package_Version,
SYSIBM.Routine_Schema as Routine_Schema,
SYSIBM.Routine_Specific_Name as Routine_Specific_Name,
SYSIBM.Routine_Type as Routine_Type
from sysibm.sysdummy1

Comme dans nos exemples, ces variables sont utilisables dans vos requêtes

exemple

les travaux actifs de l’utilisateur en cous

/par
, , , , Utiliser ACS pour produire des fichiers Excel depuis l’IBM i

Access Client Solutions (ACS)

Pour rappel, ACS est le successeur de Client Access et permet toujours les exports de données.

ACS est également un produit entièrement Java, et utilisable en mode ligne de commande.

Il est donc possible de le piloter sur l’IBM i. Pour cela il vous faut :

  • Avoir le produit sous licence 5770JV1 (Java) version 8 ou supérieur
  • Access Client Solutions sur l’IFS (désormais installé via PTF (7.4: SI71900 / 7.3: SI71934 / 7.2: SI71935) dans /QIBM/ProdData/Access/ACS/Base

Exemples

Il est alors possible de provoquer le transfert de données de deux façons :

  • le fichier complet
  • une requête SQL permettant la sélection, transformation, jointure …

Syntaxe :

/PLUGIN=cldownload /system=<system>
                          [/userid=<userid>]
                          {/hostfile=<library/filename> | /sql="statement"}
                          {/clientfile=<path><filename>.<extension> | /display}
                          [/<options>]

    /userid     - user id to use when connecting to the target system
    /hostfile   - Source library and file on the IBM i system for the download
                  e.g. /hostfile=QIWS/QCUSTCDT
    /sql        - specify an SQL statement
                  e.g. /sql="select CUSNUM,LSTNAM,INIT,ZIPCOD from QIWS/QCUSTCDT"
    /clientfile - Target file location for the download.
                  The format of this file will be determined by the specified
                  extension (for example, .csv .ods .xlsx .xlsx)
                  If the file extension is not specified or is of a type
                  not supported, the data will be formatted as a .csv file
    /display    - write the output to the terminal
    
    Valid options are:
       /colheadings=<1/0> - Include column headings as the first row. When specified, the column names will be the heading.
       /usecollabels      - Use column labels for the heading.

Puisque ACS est directement sur votre IBM i, on peut utiliser localhost pour la valeur /system. Cela permet également la portabilité de la commande d’une machine à l’autre.

Pour transférer tout un fichier on utilisera le paramètre /hostfile. Ici sous QSH :

cd /QIBM/ProdData/Access/ACS/Base

java -jar acsbundle.jar
/PLUGIN=cldownload /system=localhost
/hostfile=sqlsample/employee
/clientfile=/home/nb/export/employe.xlsx

Cela produit :

Remarquer le nom de l’onglet.

Pour transférer par une requête SQL :

cd /QIBM/ProdData/Access/ACS/Base

java -jar acsbundle.jar
/PLUGIN=cldownload /system=localhost
/sql="select trim(firstnme) concat ' ' concat trim(lastname), hiredate, current date - hiredate as \"Ancienneté\" from sqlsample.employee" /clientfile=/home/nb/export/employesql.xlsx

Attention à l’échappement des caractères spéciaux …

On obtient :

Des options supplémentaires vous permettent d’affiner la sortie :

  • /colheadings=<1/0> – Inclure ou non l’entête
  • /usecollabels – Utiliser les labels au lieu des noms de colonne

Ces fonctions nécessitent une version de ACS > 1.1.8.6 pour fonctionner correctement.

Comment automatiser ?

Il est relativement simple d’intégrer cette commande Java dans un programme CL. Ici en utilisant /clientfile.

Par exemple avec RUNJVA :

Ou par QSH :

Ici on a paramétré plus d’éléments pour avoir une base de programme plus générique.

Avantages ?

Access Client Solutions permet facilement d’automatiser vos conversions depuis l’IBM i, et non depuis un poste client. Cela rend beaucoup plus naturel l’intégration de ces traitements dans vos chaines, la maitrise des transferts par l’IT et non par les utilisateurs, la maitrise des flux, des versions de produits utilisées etc …

/par
, , Exécuter une action sur une liste SQL service

Vous avez tous compris qu’une grande partie de l’administration de vos IBMi se fera par SQL service.

Pour faire des contrôles, l’accès aux vues est suffisante.

Par exemple, le contrôle du nombre de pages dans une OUTQ :

SELECT sum(TOTAL_PAGES)
FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
where OUTPUT_QUEUE_LIBRARY_NAME = ‘SRCEXPL’ and OUTPUT_QUEUE_NAME = ‘VOTREOUTQ’

Mais pour agir c’est plus compliqué, dans certains cas il existe des procédures pour les DTAQ par exemple.

Mais la plus part du temps, vous devrez passer par une commande système dans ce cas en SQL ; vous pouvez utiliser la procédure cataloguée QCMDEXC.

Elle fonctionne comme l’api du même nom.

Exemple :

Pour épurer l’OUTQ QEZJOBLOG

Call qcmdexc(‘CLROUTQ QEZJOBLOG’)

Mais comment faire pour agir sur une liste sans faire un programme ou une procédure qui lirait un curseur ?

L’astuce est d’utiliser un For et dans le select de générer directement la commande à exécuter

Voici un exemple de script qui déplace les spools des OUTQs, commençant par ACT en mettant en référence utilisateur l’OUTQ d’origine.

les zones SPOOLNAME, JOBNAME, FILENUM, OUTQ_QUEUE_NAME proviennent de la vue.

begin
for
SELECT ‘ chgsplfa FILE(‘ concat SPOOLNAME concat ‘) JOB(‘ concat JOB_NAME concat ‘) SPLNBR(‘ concat char(FILENUM) concat ‘) outq(Votreoutq) save(*yes) usrdta( »’ concat OUTPUT_QUEUE_NAME concat  »’)’ as chgspl
FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
where OUTPUT_QUEUE_LIBRARY_NAME = ‘Votrelib’ and OUTPUT_QUEUE_NAME like(‘ACT%’)
do
call qcmdexc(CHGSPL);
end for;
end

Conclusions :

Cette solution est souvent utilisée par exemple avec la fonction table OBJECT_STATISTICS.

Exemple :

pour faire la liste des fichiers SAVF

SELECT objname, objlib, ifnull(objtext, ‘ ‘) as objtext
FROM TABLE (QSYS2.OBJECT_STATISTICS(‘ALL’,’FILE’) ) AS X
WHERE OBJATTRIBUTE = ‘SAVF’

La seule limite est qu’il est un peu plus compliqué pour gérer les erreurs sur le qcmdexc …

Bien sûr pour la bible rendez vous sur la page de Scott Forstie

/par
, ACS sous Chromebook

Vous nous avez demandé plusieurs fois, si on pouvait installer et faire fonctionner ACS sur un Chromebook.

Donc nous avons fait une RFE chez IBM qui a été refusée .

Nous avons donc décider de faire un test par nous même :

Prérequis avoir un Chromebook à jour avec un processeur x86 en 64 bits

Installer Linux, vous pouvez laisser tout les paramètres par défaut
Après l’installation, lancer l’explorateur de fichier
Aller dans l’onglet ‘Linux fichiers’
Vous devez y extraire le fichier ZIP d’ACS
Vous devez maintenant installer Java
Par exemple ‘sudo apt install openjdk-11-jre’
Aller dans Linux_Application (‘cd Linux_Application’)
Exécuter la commande : ‘./install_acs_64’
Suivre le processus classique de setup (sélectionner les options nécessaires)
Une fois l’installation terminée, quitter le terminal
Chercher IBM dans les applications
Si il est présent, tout est bon
Sinon redémarrer le ChromeBook

Ca peux paraitre compliqué comme ca, mais c’est relativement simple en réalité et ça prend moins d’une heure.

Nous avons donc testé pendant une demi-journée le travail sur ce terminal.

Voici ce qu’on a constaté


Le lancement est un peu long
Pas de touche Fx sur le clavier vous devrez les mettre en incrusté dans votre session
Pas de clic droit
Des comportements d’affichage assez particuliers en cas de perte de focus.


Ma ca reste tout à fait utilisable.

Conclusion :


Ca fonctionne et sur un poste pour une utilisation occasionnelle c’est tout à fait acceptable

Merci à Clément pour ces tests.

/par
, , , IDE IBMi , une alternative à RDI

Vous n’avez pas forcément le budget pour acheter RDI , mais vous voudriez éditer vos sources avec un outil un peu plus FUN que SEU.

Voici un produit qui peut vous permettre de réaliser vos modifciations.

Il sagit de ILEditor qui est un produit open source et que vous pouvez télécharger ici :

https://worksofbarry.com/ileditor/#cta

Nos tests

L’installation est très simple


La prise en main est très simple aussi, vous définissez vos systèmes, ils doivent être accessibles en ODBC et FTP. Attention FTP est réglé en auto, il vaudra mieux essayer PASV.


L’interface est classique
Vous pouvez Browser vos membres sources
Vous pouvez paramétrer vos options de compile

Les plus qu’on aime :
Il est interfaçable avec ACS pour l’émulation 5250 et system debugger …
Vous avez une option Object Diagram qui permet de modéliser rapidement vos applications –ne rêvez pas ce n’est pas parfait– mais c’est utilisable et pratique.


Un comparateur de sources simple et efficace
Un interfaçage avec git qui permet des clones locaux et des pushs par la suite

En résumé

Si vous faites beaucoup RPG en mode FREE, peu de CL (pas d’invite)
C’est un très bon produit.
Pour le reste, du code colonné, des clp, des includes il y a quelques lacunes.

Mais c’est un produit gratuit et parfaitement opérationnel merci à ceux qui le proposent, et un petit don s’impose quand vous le pouvez.

Divers

On a également testé rpgnextgen qui n’a plus évolué depuis 2012 et qui semble un peu dépassé

Vous pouvez également regarder du coté de ILEditor2 à l’adresse suivante https://ileditor.dev/ , un peu plus complet mais payant

/par
, , Utiliser ACS depuis votre IBM i ?

Tout le monde connait ACS en tant que client sur un poste Windows, Mac ou Linux.

Je rappelle que la principale différence avec ses prédécesseurs c’est qu’ACS et un client léger et que l’exécutable Java acsbundle.exe suffit pour exécuter une requête.

Vous pouvez l’installer sur votre IBM i, pour les 3 raisons suivantes.

1) La mise à disposition et la mise à jour peut désormais se faire par PTF

Déploiement disponible par PTF V7.3 PTF 5770SS1-SI71934 et après mise à jour de l’exécutable, pratiquement plus d’administration à faire.

2) Le partager avec vos utilisateurs

Vous montez un partage sur le répertoire où vous l’avez installé et vous exécutez acsbundle.exe à partir de ce ce dernier.
Ca marche très bien, vous pouvez faire la mise à jour de tous vos postes en changeant l’exécutable de votre répertoire.
C’est à déconseiller, si vous avez des accès réseaux peut efficaces ou si vous avez des populations nomades qui accèdent à plusieurs IBM i.

3) Pour exécuter des requêtes ACS directement sur l’IBM i

C’est du Java et votre partition sait faire du Java, et vous pouvez lancer des commandes Unix par STRQSH .

La première chose à vérifier c’est que le produit est bien installé sur votre partition.

Par défaut, il se trouve dans le répertoire QIBM/ProdData/Access/ACS/Base/, mais il peut se trouver ailleurs, par exemple dans /home/ qui est souvent monté par NetServer.

Si vous ne l’avez pas utilisé depuis longtemps, il est conseillé de mettre une version ACSBUNDLE.EXE plus récente

En fonction de ce que vous voulez utiliser dans ACS, on parle de plugin

Dans notre exemple on veut créer un fichier xls à partir d’une requête SQL c’est le plugin cldownload

Voici la syntaxe

STRQSH CMD( »java –jar votre_exe_java +
/plugin=cldownload +
/system=votre_systéme +
/clientfile=votre_fichier.xls +
/sql= »votre_requete_sql« ‘)

Exemple :

STRQSH CMD(‘java -jar /QIBM/ProdData/Access/ACS/Base/acsbundle.jar

/plugin=cldownload

/system=NEPTUNE

/clientfile=/tmp/liste_options_pdm.xls

/sql= »Select* from qgpl.qauoopt »‘)

Nous avons packagé un outil (CRTXLSFIC) qui permet de faire ça et vous pouvez le trouver sur Github à l’adresse :

https://github.com/Plberthoin/PLB/tree/master/GTOOLS

Dans le plugin cldownload vous avez des options, par exemple, pour ajouter des entêtes de colonne, /colheadings=1 , etc …
il existe d’autres plugins dans ACS pour exécuter des commandes, exécuter des transferts etc …

Quelques liens intéressants à connaitre

ftp://ftp.software.ibm.com/as400/products/clientaccess/solutions/GettingStarted_en.html
https://www.volubis.fr/news/liens/courshtm/clientaccess/client%20solution_install.html
https://www.ibm.com/support/pages/automating-acs-data-transfer
https://developer.ibm.com/technologies/systems/articles/i-acs-commandline/
https://ibmsystemsmag.com/Power-Systems/02/2020/acs-shipped-with-ibm-i

/par
, , Utiliser un script SQL pour faire du FTP

On est souvent amené à envoyer des fichiers soit par mail ou par FTP, voici une solution simple en utilisant SQL.

— génération de la table à transférer
create table votrebib.votrefic as (select …) with data;
— conversion en CSV , paramétrage Excel
CL: CPYTOIMPF FROMFILE(votrebib.votrefic)
TOSTMF(votrefic.csv)
STMFCCSID(PCASCII) RCDDLM(CRLF)
STRDLM(NONE) FLDDLM(‘;’) ADDCOLNAM(SQL) ;

— Envoi par mail

cl: SNDSMTPEMM RCP((‘plb@gaia.fr’))
SUBJECT(‘liste du fichier votrefic’)
NOTE(‘Ci-joint le fichier votrefic.csv’)
ATTACH((VOTREFIC.CSV));

— Envoi par ftp

CL: CRTPF FILE(votrebib/FTPSRC) RCDLEN(192) ;
INSERT INTO votrebib/QFTPSRC VALUES(‘User_FTP Mot_de_passe’) ;
INSERT INTO votrebib/QFTPSRC VALUES(‘put votrefic.csv’) ;
INSERT INTO votrebib/QFTPSRC VALUES(‘quit’) ;

–re routage stdin
cl: OVRDBF FILE(INPUT)
TOFILE(votrebib/QFTPSRC) +
OVRSCOPE(*JOB) ;
— lancement ftp
CL: FTP RMTSYS(VOTRE_SYSTEME);
— suppression du fichier de commande FTP
CL: DLTF FILE(votrebib/FTPSRC);

REMARQUE :

Vous pourrez améliorer ce script en utilisant un mot de passe que vous récupérez à l’exécution, pensez bien à supprimer le fichier script FTP même dans QTEMP.

Vous pouvez également demander la log, en re-routant le stdout

Si vous utilisez le mail, l’utilisateur doit être inscrit à smtp.

/par
, Journalisation Bibliothèque STRJRNLIB

Jusqu’en version 7.3, on utilisait une dtaara QFDTJRN qui indiquait comment journaliser les objets de la bibliothèque.

En version 7.4, les informations de journalisation sont inclues dans la description de la bibliothèque et la dtaara QDFTJRN n’est plus utilisée.

Il n’y aura pas d’erreur, juste les nouveaux fichiers ne seront pas journalisés, ce qui peut générer des problèmes plus tard …

On peut les voir par la commande
==>DSPLIBD votre_bib puis <F10> Affichage des règles d’héritage

Vous avez une ligne par type d’objets, *FILE, *DTAARA, *DTAQ

Pour les mettre en place, on peut utiliser les commandes suivante
STRJRNLIB démarrer la journalisation
ENDJRNLIB arrêter la journalisation
CHGJRNOBJ *LIB modifier les attributs de journalisation

Exemple :

STRJRNLIB LIB(VOTREBIB)
JRN(VOTREBIB/VOTREJRN)
INHRULES((*FILE *ALLOPR *INCLUDE *BOTH *OPNCLO))

Ici les fichiers seront journalisés, pour toutes les opérations et on gardera l’image avant et après …

Si vous créer une bibliothèque par un create collection en SQL, vous aurez les valeurs suivantes

*FILE *CREATE *INCLUDE *BOTH *OPNCLO

Donc quand vous migrez vers la version 7.4, vous devrez migrer les informations de la dtaara QDFTJRN, vers la description de la bibliothèque.

Si vous êtes en V7R3, vous pouvez anticiper et déjà mettre en oeuvre les règles d’héritage.

Remarque :

Vous pouvez changer les règles d’héritage existantes par la commande
CHGJRNOBJ.

Ces informations ne concerne pas les fichiers journalisés avant la commande STRJRNLIB

Voici un lien ou vous trouverez un outil qui permet de migrer d’une manière à l’autre indispensable si vous passez en V7R4.

https://github.com/Plberthoin/PLB/tree/master/MGRJRNLIB

/par
, 5 conseils pour utiliser CPYTOIMPF efficacement

Vous conaissez tous la commande CPYTOIMPF qui va vous permettre de générer un fichier d’exportation, que vous pourrez fournir à un partenaire .

Voici quelques informations complémentaires qui pourront vous aider

1) C’est un moyen simple de centraliser sur L’IBMi des conversions de fichier base de données vers du CSV ou du EXCEL.

Ça vous évitera les .TTO dispatchés partout sur vos environnements Windows et difficiles à maintenir.
L’utilisateur retrouvera ses fichiers dans l’IFS, les bonnes pratiques poussent vers le répertoire /home/votreuser/
L’utilisateur pourra également se faire envoyer son fichier par mail, la commande IBMi native SNDSMTPEMM permettant de joindre une pièce jointe.
Si vous utiliser un outil complémentaire vous pouvez même faire des envois FTP vers des serveurs annexes.

2) Gestion du CCSID

Vous allez avoir à gérer celui du fichier source est celui du fichier cible

Pour le fichier source, il est conseillé si ce n’est pas le cas de forcer un ccsid

Par la commande en début de programme
CHGJOB CCSID(1147) ou 297 pour la France, vous éviterez ainsi les problèmes d’extractions intermédiaires.

L’ennemi c’est le CCSID(65535) qui d’ailleurs va vous poser de plus en plus de problèmes notamment avec les produits Open source.

Pour le fichier cible il vous suffit d’indiquer le paramètre STMFCCSID(*PCASCII) ou 1252 qui est le CCSID de windows par défaut, sur la commande CPYTOIMPF.

Oui on pourrait faire de l’unicode …

3) CSV ou XLS

il y a peu de différence entre un CSV et un XLS que sait produire votre IBMi.

La vérité, elle se joue quand votre utilisateur double clique sur le fichier et qu’il ne s’ouvre pas directement par excel.

voici les paramètres pour un CSV
CPYTOIMPF
TOSTMF(‘xxxx.csv’) +
RCDDLM(CRLF) STRDLM(NONE)
FLDDLM(‘;’)

le fichier s’ouvrira directement dans EXCEL

voici les paramètres pour un EXCEL
CPYTOIMPF
TOSTMF(‘xxxx.XLS’)
RCDDLM(CRLF) STRDLM(NONE)
FLDDLM(‘*TAB’)

Vous pouvez avoir malgrè tout un message à l’ouverture, mais aucune manipulation à faire.

4) Tri du fichier

Vous avez un paramètre qui va vous permettre de trier votre fichier comme un order by SQL

c’est le paramètre ORDERBY , exemple ORDERBY(‘Datecreat desc’) qui triera par ordre de création

5) Entête de colonne

Si vous désirez un entête de colonne, le meilleur compromis c’est de mettre le paramètre ADDCOLNAM(*SQL)

Si vous avez un nom long SQL provenant de votre table, vous aurez ceci

;nom_utilisateur;prenom_utilisateur;dat_naissance
;Berthoin;Pierre-Louis;28-08-1964

Vous aurez ceci, si vous avez un PF

;nomuti;preuti;datnai
;Berthoin;Pierre-Louis;28-08-1964

Il y a toujours la solution de passer par une requête intermédiaire sur les zones vous pourrez indiquer la clause AS .

Par exemple

select numcli as ‘Numero_client’ ….

Conclusion :

La commande CPYTOIMPF est une commande simple pour convertir vos fichiers Base de données.
Elle vous permet de garder la main, un batch de nuit peut générer des extractions, les utilisateurs n’ont pas besoin d’ ODBC, ni de droit sur le fichier d’origine.

/par