https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2021-07-01 16:02:542022-04-12 12:18:39QCMDEXC en Fonction SQL
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
L’idée est de limiter les requêtes selon certain critères (de temps d’exécution, d’espace temporaire occupé etc… ) Jusqu’à présent c’était pas toujours simple et un peu binaire
Ajout d’un seuil à contrôler
Se fait par la procédure QSYS2.ADD_QUERY_THRESHOLD
Vous devez indiquer : – Un nom ici Seuil – Le seuil à contrôler – Une valeur pour ce seuil – Un filtre d’inclusion ou d’exclusion ici inclusion du profil PLB – Un délai de rafraichissement en seconde
Vous avez une vue qui permet de voir les seuils définis sur votre partition
C’est la vue QUERY_SUPERVISOR
exemple
voir tous les seuils définis pour Query supervisor
SELECT * FROM QSYS2.QUERY_SUPERVISOR ORDER BY THRESHOLD_TYPE, THRESHOLD_VALUE DESC;
;
Vous pouvez indiquer un programme d’exit QIBM_QQQ_QRY_SUPER
ci joint un exemple basique pour expliquer ce qui ce passe
Il existe un grand nombre d’API aux fonctionnalités diverses dont certaines nous permettent de récupérer des données structurées dans différents formats (XML, JSON, …).
Grace aux fonctions SQL de l’IBMi nous pouvons récupérer ces données pour les insérer dans les fichiers de la base de données.
La commande SQL suivante permet d’afficher les données dans un champ DATA
SELECT DATA FROM (values char(SYSTOOLS.HTTPGETCLOB('https://api.openweathermap.org/data/2.5/weather?q={city name}&appid={API key}&mode=xml',''), 4096)) ws(data);
Sortie API en JSON
La commande SQL suivante permet d’afficher les données dans un champ DATA
SELECT DATA FROM (values char(SYSTOOLS.HTTPGETCLOB('api.openweathermap.org/data/2.5/weather?q={city name}&appid={API key}',''), 4096)) ws(data);
Sortie API en HTML
La commande SQL suivante permet d’afficher les données dans un champ DATA
SELECT DATA FROM (values char(SYSTOOLS.HTTPGETCLOB('https://api.openweathermap.org/data/2.5/weather?q={city name}&appid={API key}&mode=html',''), 4096)) ws(data);
Récupération des données
En XML
On crée un fichier qui contiendra les colonnes que l’on veut récupérer (Ville, Température en cours, date, …)
CREATE TABLE GG/METEODB (VILLE_ID DECIMAL (9, 0) NOT NULL WITH DEFAULT, VILLE_NOM CHAR (50) NOT NULL WITH DEFAULT, TEMPERATURE DECIMAL (5, 2) NOT NULL WITH DEFAULT, TEMP_MIN DECIMAL (5, 2) NOT NULL WITH DEFAULT, TEMP_MAX DECIMAL (5, 2) NOT NULL WITH DEFAULT, DATE_MAJ CHAR (20) NOT NULL WITH DEFAULT);
Récupérer les données de l’API dans le fichier créé :
INSERT INTO GG.METEODB select xdata.* FROM xmltable('$doc/cities/list/item' PASSING XMLPARSE(document SYSTOOLS.HTTPGETCLOB('https://api.openweathermap.org/data/2.5/find?lat=45.75&lon=4.5833&cnt=10&appid={API key}&mode=xml','')) AS "doc" COLUMNS ville_id decimal(9, 0) PATH 'city/@id', ville_nom varchar(50) PATH 'city/@name', temperature decimal(5, 2) PATH 'temperature/@value', temp_min decimal(5, 2) PATH 'temperature/@min', temp_max decimal(5, 2) PATH 'temperature/@max', date_maj varchar(20) PATH 'lastupdate/@value' ) as xdata;
En JSON
Contrairement à XML, on peut créer tout de suite un fichier qui contiendra les colonnes que l’on veut récupérer.
CREATE TABLE GG.METEOBD (VILLE_ID DECIMAL (9, 0) NOT NULL WITH DEFAULT, VILLE_NOM CHAR (50) NOT NULL WITH DEFAULT, TEMPERATURE DECIMAL (5, 2) NOT NULL WITH DEFAULT, TEMP_MIN DECIMAL (5, 2) NOT NULL WITH DEFAULT, TEMP_MAX DECIMAL (5, 2) NOT NULL WITH DEFAULT, DATE_UX_MAJ DECIMAL (12, 0) NOT NULL WITH DEFAULT)
Récupérer les données de l’API dans le fichier créé :
En utilisant une API de LA POSTE qui ne nécessite pas d’inscription au préalable, ni d’identification. Nous pouvons réaliser un programme qui nous aide à retrouver une commune à partir d’un code postal, dans l’optique d’aider au remplissage de certains formulaires. On crée un fichier temporaire en interrogeant directement l’API.
https://www.gaia.fr/wp-content/uploads/2021/07/GG-2.jpg343343Guillaume GERMAN/wp-content/uploads/2017/05/logogaia.pngGuillaume GERMAN2021-06-09 13:56:312022-04-12 12:28:31UTILISATION DES API EN SQL
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
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2021-05-26 12:59:392022-04-12 14:18:01Conversion d’une OUTQ en PDF par GENERATE_PDF
Une définition simple, ce sont des traitements qui se déclenchent pour traiter des entrées d’une pile et qui ne renvoient pas de résultats directs au proccess émetteur
Il existe des produits spécifiques pour faire ça, sur l’IBMi le plus connu est bien sur mqseries . Mais il est maintenant possible d’installer des produits open source comme RabbitMQ , etc…
Il existe également des solutions natives de la plateforme.
Parmi ces solutions il y a 2 méthodes
Cyclique ( toutes les 30 secondes)
A l’événement qui ne se déclencheront qu’en cas de nécessité (exemple triggers)
On ne présentera ici que les secondes.
Les triggers
Ce sont des programmes qu’on va enregistrer au niveau de la base de données, ils vont se déclencher sur un update, insert ou delete avant ou après l’action.
On peut les déclarer par commande ADDPFTRG ou par l’instruction SQL CREATE TRIGGER, on peut être plus précis sur l’exécution en SQL (Niveau zone modifiée par exemple)
Les DTAQs
Ce sont des files d’attentes que l’on crée par CRTDTAQ , on peut écrire dedans par API (QSNDDTAQ, QRCVDTAQ) ou par SQL QSYS2.SEND_DATA_QUEUE() et QSYS2.RECEIVE_DATA_QUEUE()
Pour la réception, on peut indiquer un temps négatif souvent -1, et le traitement se déclenchera quand il y aura un entrée dans la file !
soit dans le programme de traitement CALL PGM(QRCVDTAQ) PARM(&DTAQNOM &DTAQBIB &DTAQLEN &DATA &WAIT) /* &wait = -1 */
Les msgqs en wait
historiquement, souvent utilisé, par exemple pour superviser la msgq qsysopr
dans le programme de traitement qui boucle sur cette instruction RCVMSG … WAIT(*MAX)
Les fichiers à fin retardée
Le principe est le suivant, votre programme attendra un enregistrement quand il aura fini de lire les enregistrements trouvés
Avant le programme RPGLE OVRDBF FILE(VOTREFIC) EOFDLY(99999)
dans le programme RPGLE
dou %eof() ; …. read VOTREFIC ; … endif ;
Les watchers
Permettent d’analyser en temps réels des messages qui arrive dans les joblog, les historiques de log voir une autre file de message (MSGQ) . Pour démarrer un watcher, on utilise la commande STRWCH STRWCH SSNID(ANAWCH) WCHPGM(votre bib/votre programme) + CALLWCHPGM(WCHEVT) WCHMSG((ALL)) WCHMSGQ((Votre bib/votre msgq))
votre programme reçoit 4 paramètres L’option, la session, l’erreur et la donnée du message
C’est des actions système enregistrées que vous pouvez voir par la commande WRKREGINF, et seules les actions définies dans cette liste sont utilisables.
Pour ajouter un programme c’est soit la commande addexitpgm ou l’option 8 dans wrkreginf.
Le principe , on reçoit un buffer avec les données en cours et on renvoie status pour dire ok ou ko, vous pouvez dire OK systématiquement et traiter ou faire un contrôle d’autorisation applicatif
Exemple sur FTP
PGM PARM (& APPID & OPID & USRPRF & REMOTEIP & REMOTELEN & OPINFO & OPLEN & OK) DCL & APPID * CHAR 4 /* ID D’APPLICATION, NUM BINAIRE */ DCL & OPID * CHAR 4 /* ID D’OPERATION, NUMERO BINAIRE */ DCL & OPNUM * 4 /* OPERATION ID, UTILISABLE DANS CL */ DCL & USRPRF * CHAR 10 /* PROFIL UTILISATEUR UTILISANT FTP */ DCL & REMOTEIP * CHAR 251 /* ADRESSE IP */ DCL & REMOTELEN * CHAR 4 /* LONGUEUR DU PARAMETRE PRECEDENT */ DCL & OPINFO * CHAR 251 /* INFORMATIONS SPECIFIQUES OP */ DCL & OPLEN * CHAR 4 /* LONGUEUR DU PARAMETRE PRECEDENT */ DCL & OK * CHAR 4 /* SIGNAL DE CONFIRMATION / / seulement utilisateur FTPUSR */ if cond(&USRPRF *ne ‘FTPUSR’) then(do) chgvar &ok (X’00000001′) enddo ENDPGM
Vous devez arrêter le service FTP pour que cela soit pris en compte ENDTCPSVR *FTP puis STRTCPSVR
Conclusions
Vous avez plusieurs solutions dans certains cas , et certaines sont plus à jour
les 4 à utiliser aujourd’hui sont Les triggers pour la base de données, si possible en SQL Les watchers pour les événements de log systèmes Les progammes d’exit pour les actions Système, par exemple pour les connexions ODBC Les dtaq pour gérer des piles de données applicatives
Tous ces programmes sont appelés souvent, ils doivent être donc optimisés et ils ne doivent pas planter pour éviter de bloquer la file !
Il faut arrêter d’utiliser la directory SNA, l’objectif est se passer complètement de SNA c’est le paramètre DIRTYPE, exemple CHGSMTPA DIRTYPE(*SMTP) Il vous faudra inscrire les utilisateurs à SMTP , addsmtpusr ou wrksmtpusr pour qu’ils puissent envoyer des mails
Sinon vous recevrez un message TCP5090 comme celui ci !
2) Utilisez un user mail en NOREPLY
Créer un utilisateur NOREPLY sans mot de passe inscrivez le à la directory SMTP wrksmtpusr ou addsmtpusr
et ensuite envoyer vos mails en les soumettant avec cet utilisateur
A partir du moment ou vous êtes en DIRTYPE(*SMTP) Vous n’écrivez plus dans le journal QZMF de QUSRSYS, il est conseillé de passer à JOURNAL(*NO) et ALLMAILMSF(*NO)
pour arrêter MSF qui ne sert plus à rien vous devez supprimer le job à démarrage automatique avec la jobd QZMFEJBD ou vous pouvez changer la jobd et remplacer le STRMSF … par un SNDMSG (‘coucou’) TOUSR(*SYSOPR)
5) Utilisez un sous-système spécifique
Pour des questions d’administration, vous pouvez mettre un sous système spécifique qui permet de mieux gérer les travaux relatives aux mails et qui par défaut sont dans QSYSWRK
CHGSMTPA SBSD(QSYS/QSMTP)
Vous devez arrêter et redémarrer le service pour la prise en compte
Conclusion:
C’est la meilleur solution pour envoyer des mails, il y a des nouveautés à chaque version.
