Vous connaissez l’option ACS qui vous permet de gérer vos SPOOLs , c’est une alternative intéressante à la commande WRKSPLF et si vos utilisateurs ont beaucoup de spools à gérer, ca peut leurs simplifier la tache, n’hésitez pas à leurs donner l’option, c’est relativement intuitif .
Vous pouvez par exemple faire simplement un fichier PDF et le joindre à un mail .
Vous voulez simplifiez la vie de vos utilisateurs en leurs présentant cette option à la place d’un WRKSPLF dans vos applications existantes
Voici une ébauche de solution
Un programme de lancement
Vous devrez d’abord écrire un programme qui lance l’option ACS à partir de votre programme IBMi à base des commandes STRPCO et STRPCCMD
Voici un exemple RPGLE
**free
// Ce programme permet de remplacer les commandes WRKSPLF par
// L'explorateur de spool ACS
// ici le javabundle est dans \Users\Public\IBM\ClientSolutions\
//
dcl-s cmd CHAR(1024) ;
// démarrage de PCO
cmd ='STRPCO' ;
exec sql call qsys2.qcmdexc(:cmd) ;
// Démarrage de l'explorer de spools
cmd =' +
STRPCCMD PCCMD(''java -jar C:\Users\Public\IBM\ClientSolutions\acsbundle.jar +
/PLUGIN=splf /system=neptune +
Picked up _JAVA_OPTIONS: -Djava.net.preferIPv4Stack=true'') PAUSE(*NO)' ;
exec sql
call qsys2.qcmdexc(:cmd) ;
*inlr= *on ;
On a fixé le répertoire du programme ici \Users\Public\IBM\ClientSolutions\
Si vous lancez ce programme, vous ouvrez alors l’écran ACS de gestion des spools, il est possible que cela vous redemande le mot de passe en fonction de votre paramétrage.
Un programme pour Associer à la commande WRKSPLF
Si vous voulez automatiser, vous pouvez utiliser un programme d’exit sur l’exit point QIBM_QCA_CHG_COMMAND
Vous avez la commande ADDEXITPGM pour ajouter un programme d’exit , vous pouvez également passer par la commande ==>WRKREGINF et faire l’option 8
Remarque :
Vous pouvez comme ici mettre une commande WRKSPLF dans une bibliothèque avant QSYS, qui vous permettra de bien gérer que les interactifs et de bypasser si besoin en faisant ==>QSYS/WRKSPLF .
Vous aurez intérêt a identifier les utilisateurs qui doivent bénéficier de la fonctionnalité, une solution très simple c’est l’utilisation d’une liste d’autorisation
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2025-07-05 16:06:412025-07-07 11:22:16Gestion des spools par ACS
Nous sommes de plus en plus nombreux à utiliser Code for IBM i !
Nous avons de nombreuses questions sur cet outil, en constante évolution.
Cette semaine, nous avons choisi de parler des profils, et de la confusion entre le profiles Visual Studio Code et les profils Code for IBM i.
Et les profils IBM i ?
Nous n’en parlerons pas ici !
Un profil IBM vous permet de vous connecter à la machine et n’existe que côté serveur.
La notion de profils dans Visual Studio Code (noté VSCode pour la suite) concerne la configuration des environnements de travail dans l’IDE.
Profil Code for IBM i
A la connexion à votre IBM i, VSCode établi une communication via un job SSH. Ensuite, l’interface propose plusieurs éléments de configuration et de navigation :
User Library List (partie utilisateur de la liste de bibliothèque) + current library (bibliothèque en cours)
Object browser (filtres sur objets / membres)
IFS shortcuts (filtres sur répertoires / fichiers)
Une fois connecté, la liste de bibliothèque affichée est celle utilisée à votre dernière connexion.
Vous pouvez modifier la bibliothèque en cours par click droit sur current library (ouvre un prompt) ou sur click droit sur une bibliothèque à définir comme en cours :
De même pour la liste de bibliothèque : ajout / suppression / réorganisation :
Une fois votre environnement configuré, la sauvegarde sous forme de profil vous permet de mémoriser cette configuration et de pouvoir revenir dessus plus rapidement par la suite :
Donner un nom à l’enregistrement :
Une nouvelle option de gestion des profils est alors affichée :
Elle vous permet de revenir à la situation d’origine de votre profil (si vous avez ajouter/supprimer des bibliothèques par exemple) :
Mais surtout vous pouvez créer d’autres profils, correspondants à d’autres situations :
Développement projet 1
Développement projet n …
Tests projet 1
Production
…
Vous pouvez aussi créer un profil directement en indiquant une commande de mise en place de l’environnement, basiquement un CHGLIBL :
Pour plus de souplesse, surtout lors de travail en équipe, nous vous conseillons de créer une *JOBD par « projet » côté serveur, avec une commande qui met en place les bibliothèque de la *JOBD. Cela vous permet de modifier la *JOBD sans intervenir sur l’ensemble des clients :
En réalité, le profil permet de stocker l’ensemble des éléments suivants :
Le profil Visual Studio Code vous permet d’avoir plusieurs configurations de VSCode avec une installation unique : des attributs de l’environnement peuvent être modifiés via un fichier de configuration.
Depuis le menu des paramètres, aller dans les profils :
Nous pouvons alors gérer les profils, en créer/supprimer, modifier les attributs :
Il est par exemple possible de créer un profil :
Par copie d’un profil existant, ou totalement vide. Et lors de la copie, vous choisissez les éléments de paramétrages, de personnalisation de clavier etc … Une icône spécifique peut être attribuée pour identifier rapidement les profils.
Certains profils types sont également fournis :
Une fois le profil créé, vous pouvez passer de l’un à l’autre :
Ou bien ouvrir une autre fenêtre avec un profil différent :
Depuis les propriétés du profil, les différentes catégories (Settings, keyboard shortcuts etc …) correspondent à des fichiers de configuration différents. Lorsque vous double-cliquer sur « Settings » :
Vous pouvez modifier les propriétés en direct, le changement est pris en compte à l’enregistrement du fichier :
Vous remarquez que l’ensemble des informations des profils Code for IBM i sont stockés ici, dans les profils Visual Studio Code :
Vous pouvez donc facilement éditer, modifier, échanger (regarder les options d’import/export) toutes les configurations afférentes.
Avec un peu d’habitude, vous pouvez ouvrir différentes instances pour différents usage.
https://www.gaia.fr/wp-content/uploads/2017/02/team1.png600600Nathanaël Bonnet/wp-content/uploads/2017/05/logogaia.pngNathanaël Bonnet2025-06-23 22:04:512025-06-23 22:04:52Visual Studio Code et Code for IBM i : profils
Dans une base de données bien définie, nos enregistrements sont identifiés par des clés (ie unique). Il existe toutefois différentes façon de matérialiser ces clés en SQL.
Première bonne résolution : on ne parlera pas ici des DDS (PF/LF) !
Quelques rappels
je n’insiste pas, mais une base de donnée relationnelle, DB2 for i dans notre cas, fonctionne à la perfection, à condition de pouvoir identifier nos enregistrements par des clés.
Une normalisation raisonnable pour une application de gestion est la forme normale de Boyce-Codd (dérivée de la 3ème FN).
Clés
Vous pouvez implémenter vos clés de différentes façons, voici une synthèse :
Type
Où
Support valeur nulle ?
Support doublon ?
Commentaire
Contrainte de clé primaire
Table
Non
Non
Valeur nulle non admise, même si la colonne clé le supporte
Contrainte d’unicité
Table
Oui
non : valeurs non nulles oui : valeurs nulles
Gère des clés uniques uniquement si non nulles
Index unique
Index
Oui
Non
Gère des clés uniques. La valeur NULL est supportée pour 1 unique occurrence
Index unique where not null
Index
Ouis
non : valeurs non nulles oui : valeurs nulles
Gère des clés uniques uniquement si non nulles
Attention donc à la définition de UNIQUE : à priori ce qui n’est pas NULL est UNIQUE.
Concrètement ?
Prenons un cas de test simpliste pour montrer la mécanique : un fichier article avec une clé et un libellé
Clé primaire
La colonne CODE admet des valeurs nulles, mais est fait l’objet de la contrainte de clé primaire.
A la création de la contrainte de clé primaire, le système créé automatiquement une contrainte de type CHECK pour interdire l’utilisation de valeur nulle dans cette colonne :
Avec :
La clé primaire joue son rôle avec des valeurs non nulles :
Et des valeurs nulles :
On retrouve ici le nom de la contrainte générée automatiquement !
Avec une contrainte de clé unique ?
Le comportement est identique sur une clé non nulle.
Mais avec une clé nulle (ou dont une partie est nulle si elle composée) :
On peut ajouter un index unique pour gérer le problème. Dans ce cas, une et une seule valeur nulle sera acceptée :
Mais dans ce cas pourquoi ne pas utiliser une clé primaire ??
Clé étrangère, jointure
Ajoutons un fichier des commandes, ici une simplification extrême : 1 commande = 1 article.
On ajoute une contrainte de clé étrangère qui matérialise la relation entre les tables commande et article. Pour cette contrainte commande_FK, il doit exister une contrainte de clé primaire ou de clé unique sur la colonne CODE dans la table article.
La contrainte se déclenche si l’article référencé n’existe pas :
Cas identique mais en s’appuyant sur la table article_unique qui dispose d’une clé unique et non primaire :
Dans ce cas les valeurs nulles sont supportées, en multiples occurrences (sauf à ajouter encore une fois un index unique au niveau de la commande).
Récapitulons ici nos données pour comprendre les jointures :
Démarrons par ARTICLE & COMMANDE :
La table ARTICLE ne peut pas avoir de clé nulle, donc pas d’ambiguïté ici
Avec right join ou full outer join nous accèderons au lignes de commande pour lesquelles CODE = null.
C’est le comportement attendu.
Voyons avec ARTICLE_UNIQUE et COMMANDE :
Ici on pourrait s’attendre à obtenir également les lignes 11 et 12 de la table COMMANDE : le CODE est nulle pour celles-ci, mais il existe une ligne d’ARTICLE pour laquelle le code est null. Il devrait donc y avoir égalité.
En réalité les jointures ne fonctionnent qu’avec des valeurs non nulles
De même que la clause WHERE :
Il faut donc utiliser ce style de syntaxe :
C’est à dire :
soit remplacer les valeurs nulles par des valeurs inexistantes dans les données réelles
soit explicitement indiquer la condition de nullité conjointe
Bref, syntaxiquement cela va rapidement se complexifier dans des requêtes plus évoluées.
Clé composée
Evidemment, c’est pire ! Imaginons que l’on ait une clé primaire/unique dans la table ARTICLE composée de 2 colonnes (CODE1, CODE2), et donc présentes toutes les deux dans la table COMMANDE :
Et les performances ?
En utilisant la jointure, l’optimiseur est capable de prendre en charge des accès par index :
Mais en utilisant IFNULL/COALESCE, ces valeurs deviennent des valeurs calculées, ce qui invalide l’usage des index :
Ce n’est donc pas viable sur des volumes plus importants. Il existe des solutions (index dérivés par exemple) mais la mécanique se complique encore !
Préconisations
De façon générale pour vos données de gestion, en excluant les fichier de travail (QTEMP a d’autres propriétés), les fichiers de logs, les fichier d’import/export …
Pas de valeur NULL dans vos clés
Pour les clés atomique c’est une évidence, pour les clés composées c’est beaucoup plus simple
Une contrainte de clé primaire pour toutes vos tables !
N’hésitez pas à utiliser des clés auto-incrémentées
Des contraintes d’unicités ou des index uniques pour vos autres contraintes d’unicité, techniques ou fonctionnelles
Pas d’excès, sinon il y a un défaut de conception (cf les formes normales)
Si possible des contraintes de clé étrangère pour matérialiser les relations entre les tables
Délicat sur l’existant, les traitements doivent tenir compte du sens de la relation
Favorisez l’usage des clés, contraintes et index par l’optimiseur
Scalabilité entre vos environnements de développement/test et la production
Cela permet de revenir sur le principe de l’implémentation via du code RPG :
Le code est basé sur les APIs QsyFindFirstValidationLstEntry et QsyFindNextValidationLstEntry
Le moteur DB2 appelle l’implémentation :
1 appel initial
1 appel par poste de liste à retourner
1 appel final
Nous utilisons __errno pour retrouver les codes erreur de l’APIs. Les différentes valeurs sont déclarées sous forme de constante.
La fonction SQL retourne les SQL STATE suivants :
02000 lorsque l’on attend la fin des données (fin normale)
38999 pour les erreurs. Cette valeur est arbitraire
Si possible, nous retrouvons le libellé de l’erreur retournée par l’API via strerror et on le retourne à DB2.
Code RPG :
**free
// Compilation / liage :
// CRTRPGMOD MODULE(NB/VLDLUDTF) SRCFILE(NB/QRPGLESRC)
// OPTION(*EVENTF) DBGVIEW(*SOURCE)
// CRTSRVPGM SRVPGM(NB/VLDLUDTF) EXPORT(*ALL) ACTGRP(*CALLER)
// Implémentation de la fonction UDTF VALIDATION_LIST_ENTRIES
// Liste les entrées d'une liste de validation
// Utilise l'API QsyFindFirstValidationLstEntry et QsyFindNextValidationLstEntry
// @todo :
// - ajouter le support de la conversion de CCSID
// - améliorer la gestion des erreurs
ctl-opt nomain option(*srcstmt : *nodebugio) ;
// Déclarations pour APIs : QsyFindFirstValidationLstEntry et QsyFindNextValidationLstEntry
dcl-ds Qsy_Qual_Name_T qualified template ;
name char(10) inz ;
lib char(10) inz ;
end-ds ;
dcl-ds Qsy_Entry_ID_Info_T qualified template ;
Entry_ID_Len int(10) inz ;
Entry_ID_CCSID uns(10) inz ;
Entry_ID char(100) inz ;
end-ds ;
dcl-ds Qsy_Rtn_Vld_Lst_Ent_T qualified template ;
dcl-ds Entry_ID_Info likeds( Qsy_Entry_ID_Info_T) inz ;
dcl-ds Encr_Data_Info ;
Encr_Data_len int(10) inz;
Encr_Data_CCSID uns(10) inz;
Encr_Data char(600) inz ;
end-ds ;
dcl-ds Entry_Data_Info ;
Entry_Data_len int(10) ;
Entry_Data_CCSID uns(10) ;
Entry_Data char(1000) ;
end-ds ;
Reserved char(4) inz ;
Entry_More_Info char(100) inz ;
end-ds ;
dcl-pr QsyFindFirstValidationLstEntry int(10) extproc('QsyFindFirstValidationLstEntry');
vldList likeds(Qsy_Qual_Name_T) const ;
vldListEntry likeds(Qsy_Rtn_Vld_Lst_Ent_T) ;
end-pr ;
dcl-pr QsyFindNextValidationLstEntry int(10) extproc('QsyFindNextValidationLstEntry');
vldList likeds(Qsy_Qual_Name_T) const ;
entryIdInfo likeds(Qsy_Entry_ID_Info_T) ;
vldListEntry likeds(Qsy_Rtn_Vld_Lst_Ent_T) ;
end-pr ;
// Retrouver le code erreur de l'API
dcl-pr getErrNo int(10) ;
end-pr ;
// Code erreur
dcl-c EACCES 3401 ;
dcl-c EAGAIN 3406 ;
dcl-c EDAMAGE 3484 ;
dcl-c EINVAL 3021 ;
dcl-c ENOENT 3025 ;
dcl-c ENOREC 3026 ;
dcl-c EUNKNOWN 3474 ;
// Retrouver le libellé du code erreur
dcl-pr strError pointer extproc(*CWIDEN : 'strerror') ;
errNo int(10) value ;
end-pr ;
// gestion UDTF
dcl-c CALL_OPEN -1;
dcl-c CALL_FETCH 0;
dcl-c CALL_CLOSE 1;
dcl-c PARM_NULL -1;
dcl-c PARM_NOTNULL 0;
// Liste les entrées de la liste de validation
// ==========================================================================
dcl-proc vldl_list export ;
// Déclarations globales
dcl-s ret int(10) inz ;
dcl-s errno int(10) inz ;
dcl-ds vldListEntry likeds(Qsy_Rtn_Vld_Lst_Ent_T) inz static ;
dcl-ds vldlname likeds(Qsy_Qual_Name_T) inz static ;
dcl-s first ind inz(*on) static ;
dcl-pi *n ;
// input parms
pvldl_lib varchar(10) const ;
pvldl_name varchar(10) const ;
// output columns
pEntry_ID varchar(100) ;
pEntry_Data varchar(1000) ;
// null indicators
pvldl_lib_n int(5) const ;
pvldl_name_n int(5) const ;
pEntry_ID_n int(5) ;
pEntry_Data_n int(5) ;
// db2sql
pstate char(5);
pFunction varchar(517) const;
pSpecific varchar(128) const;
perrorMsg varchar(1000);
pCallType int(10) const;
end-pi ;
// Paramètres en entrée
if pvldl_name_n = PARM_NULL or pvldl_lib_n = PARM_NULL;
pstate = '38999' ;
perrorMsg = 'VALIDATION_LIST_LIBRARY ou VALIDATION_LIST_NAME est null' ;
return ;
endif ;
select;
when ( pCallType = CALL_OPEN );
// appel initial : initialisation des variables statiques
vldlname.name = pvldl_name ;
vldlname.Lib = pvldl_lib ;
clear vldListEntry ;
first = *on ;
when ( pCallType = CALL_FETCH );
// retrouver l'entrée suivante
exsr doFetch ;
when ( pCallType = CALL_CLOSE );
// rien à faire
endsl;
// traitement de l'entrée suivante
begsr doFetch ;
if first ;
ret = QsyFindFirstValidationLstEntry( vldlname : vldListEntry);
first = *off ;
else ;
ret = QsyFindNextValidationLstEntry( vldlname :
vldListEntry.Entry_ID_Info : vldListEntry);
endif ;
if ret = 0 ;
// Entrée trouvée
monitor ;
pEntry_ID = %left(vldListEntry.Entry_ID_Info.Entry_ID :
vldListEntry.Entry_ID_Info.Entry_ID_Len);
pEntry_Data = %left(vldListEntry.Entry_Data_Info.Entry_Data :
vldListEntry.Entry_Data_Info.Entry_Data_len) ;
pEntry_ID_n = PARM_NOTNULL ;
pEntry_Data_n = PARM_NOTNULL ;
on-error ;
// Erreur de conversion
pstate = '38999' ;
perrorMsg = 'Erreur de conversion' ;
endmon ;
else ;
// Entrée non trouvée : erreur ou fin de lecture
errno = getErrNo() ;
select ;
when errno in %list( ENOENT : ENOREC ) ; // fin de lecture
pstate = '02000' ;
return ;
other ; // Erreur
pstate = '38999' ;
perrorMsg = %str(strError(errno)) ;
endsl ;
endif ;
endsr ;
end-proc ;
// Retrouver le code erreur de l'API
dcl-proc getErrNo ;
dcl-pr getErrNoPtr pointer ExtProc('__errno') ;
end-pr ;
dcl-pi *n int(10) ;
end-pi;
dcl-s errNo int(10) based(errNoPtr) ;
errNoPtr = getErrNoPtr() ;
return errNo ;
end-proc;
Code SQL :
set current schema = NB ;
set path = 'NB' ;
Create or replace Function VALIDATION_LIST_ENTRIES (
VALIDATION_LIST_LIBRARY varchar(10),
VALIDATION_LIST_NAME varchar(10) )
Returns Table
(
VALIDATION_USER varchar(100),
ENTRY_DATA varchar(1000)
)
external name 'VLDLUDTF(VLDL_LIST)'
language rpgle
parameter style db2sql
no sql
not deterministic
disallow parallel;
cl: DLTVLDL VLDL(NB/DEMO) ;
cl: CRTVLDL VLDL(NB/DEMO) TEXT('Démo VALIDATION_LIST_ENTRIES') ;
VALUES SYSTOOLS.ERRNO_INFO(SYSTOOLS.ADD_VALIDATION_LIST_ENTRY(
VALIDATION_LIST_LIBRARY => 'NB',
VALIDATION_LIST_NAME => 'DEMO',
VALIDATION_USER => 'user 1',
PASSWORD => 'MDP user 1',
ENTRY_DATA => 'Client 1'));
VALUES SYSTOOLS.ERRNO_INFO(SYSTOOLS.ADD_VALIDATION_LIST_ENTRY(
VALIDATION_LIST_LIBRARY => 'NB',
VALIDATION_LIST_NAME => 'DEMO',
VALIDATION_USER => 'user 2',
PASSWORD => 'MDP user 2',
ENTRY_DATA => 'Client 1'));
VALUES SYSTOOLS.ERRNO_INFO(SYSTOOLS.ADD_VALIDATION_LIST_ENTRY(
VALIDATION_LIST_LIBRARY => 'NB',
VALIDATION_LIST_NAME => 'DEMO',
VALIDATION_USER => 'user 3',
PASSWORD => 'MDP user 3',
ENTRY_DATA => 'Client 2'));
select * from table(VALIDATION_LIST_ENTRIES( VALIDATION_LIST_LIBRARY => 'NB',
VALIDATION_LIST_NAME => 'DEMO' )) ;
Cela produit :
Libre à vous maintenant d’utiliser ce résultat pour jointer avec vos fichiers de log HTTP (autorisation basique sur une liste de validation par exemple), avec le service USER_INFO_BASIC, croiser les profils présents dans vos différentes listes …
https://www.gaia.fr/wp-content/uploads/2017/02/team1.png600600Nathanaël Bonnet/wp-content/uploads/2017/05/logogaia.pngNathanaël Bonnet2025-05-26 11:45:252025-05-26 11:45:26Gérer vos listes de validation avec SQL !
L’utilisation des noms longs dans le code RPG est un atout supplémentaire pour le rendre plus lisible et en faciliter la maintenance.
On continue malheureusement trop souvent à utiliser les noms courts hérités de nos bases de données et du code RPG colonné, même converti en Free.
Pourtant, l’utilisation des alias dans les DDS et les déclaratives RPG ainsi que la redéfinition des indicateurs, permettent de s’affranchir totalement des noms courts au profit des noms longs.
Utilisez le mot clé ALIAS dans les DDS
Le mot-clé ALIAS permet d’associer un nom long à un champ très souvent nommé avec seulement 6 caractères, et maximum 10 caractères
ALIAS dans un PF
Exemple avec un fichier sans ALIAS :
.
.
.
Le mot clé ALIAS peut être rajouté dans les DDS de vos fichiers sans aucun impact sur vos applications :
.
.
Les ALIAS peuvent être appliqués sans perte de données par un CHGPF :
Cette opération ne modifie pas le niveau de format du fichier, pas de recompilation des applications
.
.
.
.
ALIAS dans un DSPF
Dans vos écrans 5250, vous pouvez aussi associer des noms longs aux noms courts sous forme d’ALIAS.
Les ALIAS longs peuvent être utilisés dans le code RPG à la place des noms courts.
.
Utilisez le mot clé ALIAS dans la déclaration d’ouverture du fichier
Il vous permet d’utiliser les ALIAS du fichier en tant que noms de variables, dans le code RPG
.
.
Utilisez le mot clé ALIAS dans la déclaration d’une DS externe
Il vous permet d’utiliser les ALIAS du fichier externe de la DS en tant que noms de variables, dans le code RPG
.
.
Noms longs et noms courts dans une table SQL
Lorsque vous créez une table SQL, vous lui attribuez un nom long SQL mais vous pouvez aussi lui attribuer un nom court de votre choix par la clause SYSTEM NAME afin d’éviter que le système ne lui attribue un nom court par défaut.
Pour la même raison, il est conseillé d’attribuer à chaque colonne un nom court en plus du nom long, par la clause FOR COLUMN
.
Dans le code RPG, vous pouvez déclarez la DS de votre table pour qu’elle utilise les noms courts (sans mot clé ALIAS) ou les noms longs (avec le mot clé ALIAS) :
Déclaration d’une DS externe pour une table SQL avec utilisation des noms courts
.
Déclaration d’une DS externe pour une table SQL avec utilisation des noms longs
.
Renommez les indicateurs *INxx
Vous pouvez vous affranchir des indicateurs *INxx dans le code RPG en les renommant :
https://www.gaia.fr/wp-content/uploads/2025/05/Laurent_Chavanel.png321322Laurent Chavanel/wp-content/uploads/2017/05/logogaia.pngLaurent Chavanel2025-05-12 11:42:532025-05-12 11:42:54S’affranchir des noms courts en RPG
Vous changez de machine ou de version et les dates de référence de vos objets et vos sources vont être remise à zéro, grâce aux services SQL , vous pouvez facilement conserver temporairement ces informations
Vous pourrez par exemple avoir besoin de ces informations pour faire une analyse d’impact sur l’utilisation de certain programmes ou de sources
Pour garder une trace de ces informations voici ce que vous pouvez faire
créer une bibliothèque
==> CRTLIB MIGRATION
vous avez intérêt à extraire les informations juste avant la migration
1) Sur les objets
Vous pouvez utiliser la fonction table QSYS2.OBJECT_STATISTICS
exemple :
create table migration.lstobj as( SELECT * FROM TABLE ( QSYS2.OBJECT_STATISTICS(‘ALL’,’ALL’) ) AS X ) with data
Vous pouvez si vous le voulez choisir ou éliminer des objets ou des bibliothèques.
2) sur les sources
Si vous avez des fichiers sources QRPGLESRC, QCLSRC etc …
Vous pouvez utiliser la vue QSYS2.SYSPARTITIONSTAT
exemple :
create table migration.lstsrc as( SELECT * FROM qsys2.SYSPARTITIONSTAT WHERE not source_type is null and NUMBER_ROWS > 0 ) with data
ici on limite aux membres sources non vide
Si vous avez des fichiers sources dans L’ifs, nodejs, php, python, ou même des développements traditionnels en RPGLE ou CLLE
Vous pouvez utiliser la fonction table QSYS2.IFS_OBJECT_STATISTICS
exemple :
create table migration.lstifs1 as( SELECT * FROM TABLE ( qsys2.ifs_object_statistics( start_path_name => ‘/Votre_repert/’ , subtree_directories => ‘YES’ ) ) ) with data
Vous devrez limiter à vos repertoires de sources , vous pouvez en faire plusieurs
Ensuite vous devrez envoyer votre bibliothèque sur le systéme cible
Soit par la migration naturelle qui emmènera toutes les bibliothèques ou par une opération spécifique d’envoi de la bibliothèque FTP, SAVRSTLIB etc…
Attention :
Après 6 mois cette bibliothèque devra être supprimée, elle ne servira plus à rien
Remarque :
Vous pouvez également inclure dans cette bibliothèque d’autres éléments qui pourront être utile comme :
La liste des valeurs systèmes , QSYS2.SYSTEM_VALUE_INFO Le planning des travaux , QSYS2.SCHEDULED_JOB_INFO les programmes d’exit , QSYS2.EXIT_PROGRAM_INFO les watchers , QSYS2.WATCH_INFO les bases de données DRDA , QSYS2.RDB_ENTRY_INFO les reroutages de travaux , QSYS2.ROUTING_ENTRY_INFO la table des réponses par défaut , QSYS2.REPLY_LIST_INFO etc …
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2025-05-08 07:31:422025-05-08 07:31:43Conserver des informations avant migration
Vous devez échanger un fichier avec un partenaire qui vous le demande au format JSON
Il n’existe pas de CVTJSONxx comme la commande CPYTOIMPF pour le CSV
Pas de panique vous avez des services SQL qui font ceci voici un exemple :
Création d’une table de test
SET SCHEMA = ‘votre base’; CREATE OR REPLACE TABLE CLIENTS ( ID_CLIENT INTEGER NOT NULL WITH DEFAULT, NOM_CLIENT VARCHAR ( 50) NOT NULL WITH DEFAULT, VILLE VARCHAR ( 50) NOT NULL WITH DEFAULT ) ; INSERT INTO CLIENTS VALUES(1, ‘Dupont’, ‘Paris’) ; INSERT INTO CLIENTS VALUES(2, ‘Durand’, ‘Lyon’) ;
Voici la syntaxe qu’il vous faudra utiliser pour le convertir sous forme de flux json
SELECT JSON_OBJECT(
'id_client' VALUE ID_CLIENT,
'nom_client' VALUE NOM_CLIENT,
'ville' VALUE VILLE
) AS CLIENT_JSON
FROM CLIENTS;
Maintenant il va falloir générer le fichier .JSON dans l’IFS par exemple en utilisant la procédure IFS_WRITE_UTF8
Malheureusement ce service n’existe pas sous forme de fonction on devra utiliser une des possibilités de SQL « Dynamic compound statement » qui permet de compiler un module dynamiquement
Ce qui donnera donc
BEGIN
CALL QSYS2.IFS_WRITE_UTF8(
PATH_NAME => 'PLB_CLIENTS.json',
LINE => '',
OVERWRITE => 'REPLACE',
END_OF_LINE => 'NONE'
);
FOR SELECT TRIM(CAST(JSON_OBJECT(
'id_client' VALUE ID_CLIENT,
'nom_client' VALUE NOM_CLIENT,
'ville' VALUE VILLE
) AS VARCHAR(32000))) AS Line_to_write
FROM PLB.CLIENTS
DO
CALL QSYS2.IFS_WRITE_UTF8(
PATH_NAME => 'PLB_CLIENTS.json',
LINE => Line_to_write
);
END FOR;
END;
Si vous avez beaucoup de fichiers à convertir ca peut être fastidieux pas de panique nous avons fait une commande qui va vous aider
https://github.com/Plberthoin/PLB/tree/master/GTOOLS, vous avez l’habitude un source SQLRPGLE et un source CMD à compiler , voila SME
Vous pouvez enregistrer le scripte SQL (CVTFICJSON.SQL) avec l’option Sauvegarder le scripte
Vous pourrez le customiser :
en le formatant par ACS ,
en enlevant des zones
en sélectionnant des enregistrements
etc …
Remarque :
Vous avez une option pour agréger
Vous pouvez faire beaucoup mieux , c’est juste pour vous aider à démarrer dans le domaine
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2025-05-02 12:16:162025-05-02 12:16:17Convertissez un fichier en JSON
Retour sur une problématique récurrente et souvent mal comprise, donc mal gérée … Et qui pourrait bien s’amplifier avec l’usage plus intensif de l’Open Source.
Vous utilisez historiquement des fichiers sources (objet *FILE attribut PF-SRC) pour stocker vos sources : ces fichiers sont créés avec un CCSID, par défaut le CCSID du job dans lequel vous exécutez la commande CRTSRCPF
Usuellement vous obtiendrez des fichiers sources avec un CCSID 297 ou 1147 pour la France. Si vos machines sont « incorrectement » réglées, un CCSID 65535 (hexadécimal).
Mais également, par restauration d’autres produits, certainement des fichiers avec un CCSID 37 (US).
Pour les langages de programmation (dont le SQL), le CCSID du fichier source est important pour les constantes, qui peuvent par définition êtres des caractères nationaux quelconques. Quant aux instructions, la grammaire des langages les définis sans ambiguïtés.
Caractères spéciaux / nationaux
Pour toutes les commandes, instructions, éléments du langage, pas de soucis d’interprétation
A la compilation, les constantes sont interprétées suivant le CCSID du job, pas celui du source !
En général, les deux CCSID sont identiques.
Par contre, pour les caractères spéciaux utilisés, si l’on regarde de plus près le cas du CL, la documentation indique :
Voilà qui explique les fameuses transformations de @ en à !
En synthèse : le compilateur considère tous les éléments du langage comme étant en CCSID 37, hors les constantes alphanumériques et les quelques caractères listés ici.
Pour le CL : impossible d’utiliser les opérateurs symboliques |> (*BCAT), |< (*TCAT) et || (*CAT)
Le caractère | est mal interprété. Vous devez le remplacer par un !, ou utiliser les opérateurs non symboliques (*BCAT, *TCAT et *CAT).
Pour le SQL : impossible d’utiliser l’opérateur || (même raison).
Vous devez le remplacer par un !!, ou utiliser l’opérateur concat.
Evolution du RPG
Le principe est le même.
Toutefois le langage vous permet également de contrôler le CCSID des variables déclarées. Et depuis la 7.2, de nouvelles directives de pré-compilation permettent d’indiquer des valeurs de CCSID par défaut par bloc de source :
Et pour l’IFS ?
Sur l’IFS, chaque fichier (source ou non) dispose également d’un CCSID. Sa valeur dépend principalement de la façon de créer le fichier (par un éditeur type RDi/VSCode, partage netserver, transfert FTP …).
Premier point d’attention : l’encodage du contenu du fichier doit correspondre à son attribut *CCSID !
VSCode vous indique le CCSID de la donnée, par exemple :
Mais :
1252 = Windows occidental (proche de l’UTF-8 mais pas identique). La raison est que VSCode travaille naturellement en UTF-8.
RDi gère correctement l’encodage/décodage par rapport à la description du fichier.
Pour les autres outils, à voir au cas par cas !
Evolution des compilateurs
Les compilateurs C, CPP, CL, RPG, COBOL supportent désormais (PTF en fonction des compilateurs) un paramètre TGTCCSID :
En réalité ce paramètre a été ajouté pour permettre la compilation plus facilement depuis l’IFS, principalement depuis des fichiers IFS en UTF-8.
Cela ne règle pas nos problèmes précédents, les éléments du langage n’étant pas concernés : nous auront toujours le problème d’interprétation du |
Par contre, c’est utile pour la bonne interprétation des constantes lorsque le job de compilation a un CCSID du source. Et cela permet une meilleure intégration dans les outils d’automatisation.
Le script propose d’indiquer un CCSID pour les fichiers sources. Mais la seule solution viable est de compiler avec un job en CCSID 37 :
soit CHGJOB CCSID(37) avant de lancer le script
soit vous pouvez vous créer un profil dédié en CCSID 37 si ces opérations sont récurrentes
Tant que vous n’avez pas de caractères nationaux dans le codes !
Retrouver le CCSID de ses fichiers sources
SELECT f.SYSTEM_TABLE_NAME, f.SYSTEM_TABLE_SCHEMA, c."CCSID" FROM qsys2.systables f JOIN qsys2.syscolumns c ON (c.SYSTEM_TABLE_NAME, c.SYSTEM_TABLE_SCHEMA) = (f.SYSTEM_TABLE_NAME, f.SYSTEM_TABLE_SCHEMA) WHERE f.file_type = 'S' AND LEFT(f.system_table_name, 8) <> 'EVFTEMPF' AND c.SYSTEM_COLUMN_NAME = 'SRCDTA' ORDER BY c."CCSID", f.SYSTEM_TABLE_SCHEMA, f.SYSTEM_TABLE_NAME;
Nouveau venu dans les bibliothèques middleware pour IBMi, MAPEPIRE est un outil simple pour récupérer des données de votre serveur et les travailler sur de applications tierces, telles que des outils d’analyse de données, de la bureautique, etc.
Nous allons vous présenter la possibilité d’installer et d’utiliser le produit simplement
sur le serveur
sur votre client, en fonction du langage que vous souhaitez utiliser.
Les langages disponibles pour l’instant sont :
JAVA, NODE.JS, PYTHON
Ici, l’exemple détaillé sera effectué avec le langage PYTHON.
Nous n’intervenons pas dans cet article sur les différents paramétrages de l’outil. Nous y reviendrons dans un article suivant. (exit points, ports, etc.)
Serveur
Installation de MAPEPIRE sur le serveur
yum install mapepire
mapepire sera installé dans le répertoire
/qOpenSys/pkgs/bin
Démarrage
Note : dans cet article, on ne détaille ps le démarrage automatique
nohup /qopensys/pkgs/bin/mapepire &
Client
Notre exemple consiste à lister tous les travaux actifs en cours, les répertorier dans une trame PANDAS, puis de sauvegarder les données dans une feuille EXCEL
Pré requis
Python 3 installé et fonctionnel
un répertoire pour le code
Facultatif : un environnement virtuel
EXCEL
librairies installées :
pandas
openpyxl (factultatif)
installation MAPEPIRE
sous l’environnement virtuel (si configuré) ou sur l’environnement global
pip install mapepire-python
le code de notre exemple TEST.PY
#mapepire
#mapepire
from mapepire_python.client.sql_job import SQLJob
from mapepire_python import DaemonServer
#pandas
import pandas as pd
#--------------------------------------------------
creds = DaemonServer(
host="serveuràcontacter",
port=8076,
user="utilisateur",
password='motdepasse',
ignoreUnauthorized=True,
)
job = SQLJob()
res = job.connect(creds)
#
# Travaux actifs
#
result = job.query_and_run("\
SELECT \
count(*) as totaltravaux\
FROM TABLE (QSYS2.ACTIVE_JOB_INFO()) \
")
countjobs = result['data'][0]['TOTALTRAVAUX']
startT = datetime.now()
result = job.query_and_run("SELECT \
JOB_NAME, JOB_TYPE, JOB_STATUS, \
SUBSYSTEM, MEMORY_POOL, THREAD_COUNT \
FROM TABLE ( \
QSYS2.ACTIVE_JOB_INFO(\
RESET_STATISTICS => 'NO',\
SUBSYSTEM_LIST_FILTER => '',\
JOB_NAME_FILTER => '*ALL',\
CURRENT_USER_LIST_FILTER => '',\
DETAILED_INFO => 'NONE'\
)\
) \
ORDER BY \
SUBSYSTEM, RUN_PRIORITY, JOB_NAME_SHORT, JOB_NUMBER\
",
rows_to_fetch=countjobs)
endT = datetime.now()
delta = endT - startT
print(f"travaux actifs récupérés en {str(delta)} secondes")
#insertion des résultats dans un Frame PANDAS
dframActj = pd.DataFrame(result['data'])
#print(dframActj)
#
#récupération des utilisateurs dans une 2ème Frame (dframUsesrs)
#
startT = datetime.now()
result = job.query_and_run("""
WITH USERS AS (
SELECT
CASE GROUP_ID_NUMBER
WHEN 0 THEN 'USER'
ELSE 'GROUP'
END AS PROFILE_TYPE,
A.*,
CAST(TEXT_DESCRIPTION AS VARCHAR(50) CCSID 1147)
AS TEXT_DESCRIPTION_CASTED
FROM (
SELECT *
FROM QSYS2.USER_INFO
) AS A
)
SELECT *
FROM USERS
""",
rows_to_fetch=500)
endT = datetime.now()
delta = endT - startT
print(f"Utilisateurs récupérés en {str(delta)} secondes")
#insertion des résultats dans un Frame PANDAS
dframUsers = pd.DataFrame(result['data'])
#print(dframUsers)
print("Sauvegarde vers Excel")
with pd.ExcelWriter('/users/ericfroehlicher/Documents/donnes_dataframe.xlsx') as writer:
dframActj.to_excel(writer, sheet_name='ACTjobs')
dframUsers.to_excel(writer, sheet_name='Utilisateurs')
Un peu d’explications
1 – Import des resources dont on a besoin
#mapepire
from mapepire_python.client.sql_job import SQLJob
from mapepire_python import DaemonServer
#pandas
import pandas as pd
2 – Déclaration des données de connexion (serveur, utilisateur, mot def passe)
CONSEIL: pour l’instant, toujours laisser le port 8076
Ici, on crée un travail simple, synchrone (SQLJob)
4 – les requêtes synchrones
#comptage des travaux (pour l'exemple de l'utilisation du json)
result = job.query_and_run("\
SELECT \
count(*) as totaltravaux\
FROM TABLE (QSYS2.ACTIVE_JOB_INFO()) \
")
# je récupère directement la valeur lue
countjobs = result['data'][0]['TOTALTRAVAUX']
result = job.query_and_run("SELECT \
JOB_NAME, JOB_TYPE, JOB_STATUS, \
SUBSYSTEM, MEMORY_POOL, THREAD_COUNT \
FROM TABLE ( \
QSYS2.ACTIVE_JOB_INFO(\
RESET_STATISTICS => 'NO',\
SUBSYSTEM_LIST_FILTER => '',\
JOB_NAME_FILTER => '*ALL',\
CURRENT_USER_LIST_FILTER => '',\
DETAILED_INFO => 'NONE'\
)\
) \
ORDER BY \
SUBSYSTEM, RUN_PRIORITY, JOB_NAME_SHORT, JOB_NUMBER\
",
rows_to_fetch=countjobs)
A
Les données obtenues sont au format JSON. (voir plus bas les données brutes)
5 – Insertion des données dans un frame PANDAS et sauvegarde vers EXCEL
#insertion des résultats dans un Frame PANDAS
dframe = pd.DataFrame(result['data'])
print(dframe)
print("Sauvegarde vers Excel")
dframe.to_excel(
"/users/ericfroehlicher/Documents/travaux_actifs.xlsx",
sheet_name="Travaux actifs",
index=False
)
Retour de mapepire
Le flux de données renvoyé par MAPEPIRE contient l’ensemble des données et méta données au format JSON.
Voici un extrait du flux retourné (exemple sur 5 travaux)
/wp-content/uploads/2017/05/logogaia.png00Eric Froehlicher/wp-content/uploads/2017/05/logogaia.pngEric Froehlicher2025-03-14 16:52:362025-03-14 16:52:37Générer des données avec MAPEPIRE en PYTHON
Cet article est librement inspiré d’une session animée par Birgitta HAUSER lors des universités de l’IBMi du 19 et 20 novembre 2024. Je remercie également Laurent CHAVANEL avec qui j’ai partagé une partie de l’analyse.
Présentation
Pour réaliser cet article, nous avons créé un fichier de données météorologiques quotidiennes de quatre villes françaises pendant cinq années (de 2020 à 2024).
Les données contenues dans le fichier CLIMAT sont :
La ville
Le jour (AAAA-MM-JJ)
Les précipitations en mm
La température minimale du jour (en °C)
La température maximale du jour (en °C)
La température moyenne du jour (en °C)
L’amplitude de température du jour (en °C)
Agréger les données avec LISTAGG
Cette fonction permet de rassembler dans un seul champ, les données issues de plusieurs lignes
SELECT VILLE,
YEAR(DATEREL) Annee,
MONTHNAME(DATEREL) Mois,
LISTAGG(TMOY || '°C', ', ') "Températures moyennes du Mois"
FROM CLIMAT
WHERE YEAR(DATEREL) = 2020
AND MONTH(DATEREL) = 1
GROUP BY VILLE,
YEAR(DATEREL),
MONTHNAME(DATEREL)
Données brutes
Données avec la fonction LISTAGG
Agréger les données avec GROUP BY
Comme première analyse, on souhaite faire des statistiques annuelles pour chaque ville sur chaque année.
On utilise les fonctions :
SUM qui va nous permettre de faire le total des précipitations
MIN pour extraire la température minimale
MAX pour extraire la température maximale
AVG pour faire une moyenne (de la température ainsi que de l’amplitude des températures)
On notera que TOUTES les colonnes sans fonction d’agrégation doivent être regroupées dans un GROUP BY et nous ajoutons un ORDER BY pour classer nos données.
SELECT YEAR(DATEREL) "Année",
VILLE,
SUM(MMPLUIE) "Total des précipitations",
MIN(TMIN) "Température Minimale",
MAX(TMAX) "Température Maximale",
CAST(AVG(TMOY) AS DEC(4, 2)) "Température Moyenne",
CAST(AVG(TAMPLI) AS DEC(4, 2)) "Amplitude Moyenne"
FROM CLIMAT
GROUP BY YEAR(DATEREL),
VILLE
ORDER BY VILLE,
"Année";
Utilisation de ROLLUP
Nous voulons réaliser un total des précipitations sur les cinq dernières années, pour chaque commune de notre fichier tout en conservant un total pour chaque année observée
SELECT VILLE,
YEAR(DATEREL) "Année",
SUM(MMPLUIE) "Total des précipitations"
FROM CLIMAT
GROUP BY ROLLUP (VILLE, YEAR(DATEREL))
ORDER BY VILLE,
"Année";
L’extension ROLLUP apportée au GROUP BY, nous permet d’avoir des sous totaux par :
VILLE / ANNEE
VILLE
Ainsi qu’un total général (ce qui, dans le cas présent n’a que peu d’intérêt, je vous l’accorde)
Autre exemple, le total des précipitations par mois pour une seule ville.
SELECT VILLE,
YEAR(DATEREL) "Année",
MONTH(DATEREL) Mois,
SUM(MMPLUIE) "Total des précipitations"
FROM GG.CLIMAT
WHERE VILLE = 'LYON'
GROUP BY ROLLUP (VILLE, YEAR(DATEREL), MONTH(DATEREL));
…
…
Utilisation de CUBE
Cette extension nous permet d’obtenir plusieurs type de sous-totaux dans une même extraction
SELECT VILLE, YEAR(DATEREL) Annee, MONTH(DATEREL) Mois, SUM(MMPLUIE) "Total des précipitations" FROM CLIMAT WHERE VILLE = 'LYON' GROUP BY CUBE (VILLE, YEAR(DATEREL), MONTH(DATEREL));
Par VILLE et ANNEE
Par VILLE et sur la période de mesure
Sur la période de mesure (valeur identique à la précédente car une seule ville sélectionnée ici)
Par VILLE pour chaque mois de la période sélectionnée (ou simplement pour chaque mois de la période sélectionnée)
Pour Lyon, on a, par exemple, un total de précipitations de 188.00 mm pour tous les mois de janvier ou 400.00 mm pour tous les mois de septembre entre 2020 et 2024
Utilisation de GROUPING SETS
Cette extension permet de faire des regroupements choisis. Cela permet de faire une sélection des regroupements plus fine que celle réalisée avec CUBE.
Select VILLE, Year(DATEREL) Annee, month(DATEREL) Mois,
sum(MMPLUIE) "Total des précipitations",
Cast(Avg(TMOY) as Dec(4, 2)) "Température Moyenne"
From CLIMAT
WHERE VILLE in ('LYON', 'MARSEILLE', 'PARIS')
Group By GROUPING SETS((VILLE, YEAR(DATEREL)), (VILLE, month(DATEREL)))
ORDER BY VILLE, YEAR(DATEREL), month(DATEREL);
Dans cet exemple, on fait des regroupements par VILLE/ANNEES et VILLE/MOIS dans une seule extraction
Tableau Croisé avec Agrégation et CASE
Avec SUM
Select VILLE, Year(DATEREL) Annee,
sum(case when month(DATEREL)= 1 then MMPLUIE else 0 end) as "mm Janvier",
sum(case when month(DATEREL)= 2 then MMPLUIE else 0 end) as "mm Février",
sum(case when month(DATEREL)= 3 then MMPLUIE else 0 end) as "mm Mars",
sum(case when month(DATEREL)= 4 then MMPLUIE else 0 end) as "mm Avril",
sum(case when month(DATEREL)= 5 then MMPLUIE else 0 end) as "mm Mai",
sum(case when month(DATEREL)= 6 then MMPLUIE else 0 end) as "mm Juin",
sum(case when month(DATEREL)= 7 then MMPLUIE else 0 end) as "mm Juillet",
sum(case when month(DATEREL)= 8 then MMPLUIE else 0 end) as "mm Aout",
sum(case when month(DATEREL)= 9 then MMPLUIE else 0 end) as "mm Septembre",
sum(case when month(DATEREL)=10 then MMPLUIE else 0 end) as "mm Octobre",
sum(case when month(DATEREL)=11 then MMPLUIE else 0 end) as "mm Novembre",
sum(case when month(DATEREL)=12 then MMPLUIE else 0 end) as "mm Décembre",
sum(MMPLUIE) as "Total Précipitations"
FROM CLIMAT
Group by Ville, Year(DATEREL)
order by Ville, Year(DATEREL);
Avec AVG
Select VILLE, Year(DATEREL) Annee,
cast(avg(case when month(DATEREL)= 1 then TMOY else NULL end) as Dec(4, 2)) as "°C Janvier",
cast(avg(case when month(DATEREL)= 2 then TMOY else NULL end) as Dec(4, 2)) as "°C Février",
cast(avg(case when month(DATEREL)= 3 then TMOY else NULL end) as Dec(4, 2)) as "°C Mars",
cast(avg(case when month(DATEREL)= 4 then TMOY else NULL end) as Dec(4, 2)) as "°C Avril",
cast(avg(case when month(DATEREL)= 5 then TMOY else NULL end) as Dec(4, 2)) as "°C Mai",
cast(avg(case when month(DATEREL)= 6 then TMOY else NULL end) as Dec(4, 2)) as "°C Juin",
cast(avg(case when month(DATEREL)= 7 then TMOY else NULL end) as Dec(4, 2)) as "°C Juillet",
cast(avg(case when month(DATEREL)= 8 then TMOY else NULL end) as Dec(4, 2)) as "°C Aout",
cast(avg(case when month(DATEREL)= 9 then TMOY else NULL end) as Dec(4, 2)) as "°C Septembre",
cast(avg(case when month(DATEREL)=10 then TMOY else NULL end) as Dec(4, 2)) as "°C Octobre",
cast(avg(case when month(DATEREL)=11 then TMOY else NULL end) as Dec(4, 2)) as "°C Novembre",
cast(avg(case when month(DATEREL)=12 then TMOY else NULL end) as Dec(4, 2)) as "°C Décembre",
cast(avg(TMOY) as Dec(4, 2)) as "°C Moyenne"
FROM CLIMAT
Group by Ville, Year(DATEREL)
order by Ville, Year(DATEREL);
Note sur l’utilisation de SUM vs AVG dans un tableau croisé
SUM totalise par mois, tandis que AVG calcule la moyenne.
Utilisation de ELSE NULL au lieu de ELSE 0 :
Avec ELSE 0, la fonction AVG prend en compte les zéros, ce qui fausse la moyenne si une valeur est absente.
NULL est ignoré par AVG, garantissant une moyenne correcte.
Par exemple, si nous écrivons
AVG(CASE WHEN MONTH(DATEREL)= 1 THEN TMOY ELSE 0 END)
Alors la requête va additionner les températures moyennes de janvier MAIS aussi ajouter 0 pour tous les jours qui ne sont pas en janvier, le résultat sera donc faux au regard des températures mesurées… il en sera de même pour chaque mois.
La bonne pratique, pour l’utilisation de la fonction AVG est donc :
AVG(CASE WHEN MONTH(DATEREL)= 1 THEN TMOY ELSE NULL END)
Utiliser SQL pour faire une analyse
Nous pouvons également combiner différentes fonctions de SQL pour effectuer une analyse avec un rendu facilement lisible.
Dans le cas ci-dessous nous combinons CASE à différents niveaux, avec SUM afin de voir si les précipitations annuelles de chaque ville sont au-dessus ou en dessous des moyennes connues et les classer par rapport à un niveau de 800mm (choisi arbitrairement pour l’exercice)
SELECT VILLE,
YEAR(DATEREL) Annee,
CASE
WHEN VILLE = 'KOUROU' THEN
CASE
WHEN SUM(MMPLUIE) > 2560 THEN 'Excédent'
ELSE 'Déficit'
END
WHEN VILLE = 'LYON' THEN
CASE
WHEN SUM(MMPLUIE) > 830 THEN 'Excédent'
ELSE 'Déficit'
END
WHEN VILLE = 'MARSEILLE' THEN
CASE
WHEN SUM(MMPLUIE) > 453 THEN 'Excédent'
ELSE 'Déficit'
END
WHEN VILLE = 'PARIS' THEN
CASE
WHEN SUM(MMPLUIE) > 600 THEN 'Excédent'
ELSE 'Déficit'
END
END "NIVEAU",
CASE
WHEN SUM(MMPLUIE) > 800 THEN SUM(MMPLUIE)
ELSE 0
END "> 800 mm",
CASE
WHEN SUM(MMPLUIE) <= 800 THEN SUM(MMPLUIE)
ELSE 0
END "<= 800 mm"
FROM CLIMAT
GROUP BY Ville, YEAR(DATEREL)
ORDER BY Ville, YEAR(DATEREL);
https://www.gaia.fr/wp-content/uploads/2021/07/GG-2.jpg343343Guillaume GERMAN/wp-content/uploads/2017/05/logogaia.pngGuillaume GERMAN2025-03-04 09:03:202025-03-04 09:28:33Regroupements et Analyses avec SQL