Pour mettre en place une authentification par JWT sur IBMi, on utilise l’API Qc3VerifySignature.
Le JWT
Il est composé de trois partie :
Un entête (header)
Une charge utile (payload)
Une signature numérique
Pour obtenir la signature, il faut tout d’abord encoder séparément le header et le payload avec BaseURL64, ensuite, on les concatène en les séparant d’un point.
On calcule enfin une signature d’après le header et le payload afin de garantir que le jeton n’a pas été modifié, d’après l’algorithme défini dans le header (RS256, HS256, HS512, …). Cette signature binaire est elle-même encodée ensuite en Base64URL.
On obtient ainsi le JWT : { header }.{ payload }.{ signature }
Préparation
Afin de tester cette API, il faut dans un premier temps générer une clé au format PEM en suivant les étapes ci-dessous :
openssl req -new -out monserveur.csr
Pour créer une demande certificate (.csr = certificat signing request)
Cela crée deux fichiers : monserveur.csr et privkey.pem
openssl rsa -in privkey.pem -out monserveur.key
Cela crée le fichier monserveur.key (clé privée sans le mot de passe)
On crée la donnée à contrôler en concaténant header et payload, séparés d’un point, comme expliqué au paragraphe précédent.
Exemple :
ATTENTION : Il faut, pour être utilisable, que celle-ci soit en ASCII. Pour ce faire on utilise le programme système QDCXLATE qui permet de faire de la conversion de chaines de caractères grâce à des tables système.
Data Structure du format ALGD0400 (algorithme) :
cipher INT(10) inz(50) // Code secret pour RSA , initialisé à 50
PKA CHAR(1) inz(1) // PKCS bloc 01
filler CHAR(3) inz(x’000000’) // Réservé : ce champ doit rester NULL
hash INT(10) inz(3) // Signature Algorithme de Hash 3=SHA256
Data Structure du format KEYD0600 (clé) :
keylen INT(10) // Longueur du certificat PEM
filler CHAR(4) inz(x’00000000′) // Réservé : ce champ doit rester NULL
key CHAR(4096) CCSID(65535) // Certificat PEM en ASCII
Code Retour
L’appel de l’API avec les paramètres choisis , retourne un Data Structure ErrorCode décrite ci-dessous :
bytesProv INT(10) inz( %size( ErrorCode ) ); // ou 64 pour voir MSGID
bytesAvail INT(10) inz(0);
MSGID CHAR(7);
filler CHAR (1);
data CHAR (48);
Dans le cas où la signature est vérifiée, les valeurs retour sont les suivantes
BYTESPROV = 64
BYTESAVAIL = 0
MSGID = ‘ ‘
FILLER = ‘ ‘
DATA = ‘ ‘
Si la signature n’est pas vérifiée, les valeurs retour seront :
https://www.gaia.fr/wp-content/uploads/2021/07/GG-2.jpg343343Guillaume GERMAN/wp-content/uploads/2017/05/logogaia.pngGuillaume GERMAN2021-07-27 23:15:492021-07-27 23:15:51Authentification par JWT (Json Web Token)
Dans notre exemple, on sélectionne les jobs actifs sur le système, on sélectionne les requêtes SELECT et on trie par consommation descendante !
With job_act (JOB_NAME_SHORT, JOB_USER, JOB_NUMBER, JOB_USER_IDENTITY) as (SELECT JOB_NAME_SHORT, JOB_USER, JOB_NUMBER,JOB_USER_IDENTITY FROM TABLE (QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => ‘ALL’)) X where substr(job_user_identity, 1, 1) <> ‘Q’) Select qqjob, qquser, qvc102, qqjnum, qq1000, qqi6 as temps_execution from gdata.dump_cache a join job_act b on QQJOB = JOB_NAME_SHORT and QQUSER = JOB_USER and QQJNUM = JOB_NUMBER where substr(QQ1000 , 1, 6) = ‘SELECT’ order by qqi6 desc
Vous pouvez changer vos critères de tri et de sélection, ici on est en mode pompier ? Vous pouvez planifier cette requête et la faire tourner plusieurs fois par jour sur votre système en gardant le résultat ou les dump cache.
Nous avons packagé ce script pour faire une commande WRKSQLJOB que vous pouvez trouver ici !
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