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
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2024-01-18 16:59:252024-01-30 17:00:12Requêtes SQL dans ACS extraites de Navigator for i
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é
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2023-12-18 10:00:302023-12-18 15:55:55Vue rapide du catalogue DB2
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.
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2023-11-27 17:15:012023-11-28 09:55:34Connaitre la taille vos MTI
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2023-11-19 20:07:162023-11-19 20:07:17Générer un XLS avec SQL
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)
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 !
/wp-content/uploads/2017/05/logogaia.png00Damien Trijasson/wp-content/uploads/2017/05/logogaia.pngDamien Trijasson2023-11-06 11:53:362023-11-16 09:58:34SQL – appel de webservice
Administration, SQL_DB2Comprendre comment un travail interactif est affecté à un sous système.
C’est des entrées écran qui peuvent être indiquées sur des sous systèmes, par les commandes ADDWSE et CHGWSE.
Il va utiliser le nom de l’unité écran, il va faire la recherche dans cet ordre :
-Par nom -par nom générique -*all
Vous avez un service SQL qui permet de les visualiser QSYS2.WORKSTATION_INFO
Voici une requête qui permet de voir et comprendre les entrées de votre système
SELECT A.SUBSYSTEM_DESCRIPTION_LIBRARY, A.SUBSYSTEM_DESCRIPTION, WORKSTATION_NAME
FROM QSYS2.WORKSTATION_INFO as A Join QSYS2.SUBSYSTEM_INFO AS B
on A.SUBSYSTEM_DESCRIPTION_LIBRARY= b.SUBSYSTEM_DESCRIPTION_LIBRARY and
A.SUBSYSTEM_DESCRIPTION = b.SUBSYSTEM_DESCRIPTION
and B.STATUS = 'ACTIVE' and ALLOCATION = '*SIGNON'
where WORKSTATION_NAME not like('%*%')
union
SELECT A.SUBSYSTEM_DESCRIPTION_LIBRARY, A.SUBSYSTEM_DESCRIPTION, WORKSTATION_NAME
FROM QSYS2.WORKSTATION_INFO as A Join QSYS2.SUBSYSTEM_INFO AS B
on A.SUBSYSTEM_DESCRIPTION_LIBRARY= b.SUBSYSTEM_DESCRIPTION_LIBRARY and
A.SUBSYSTEM_DESCRIPTION = b.SUBSYSTEM_DESCRIPTION
and B.STATUS = 'ACTIVE' and ALLOCATION = '*SIGNON'
where WORKSTATION_NAME like('%*%')
union
SELECT A.SUBSYSTEM_DESCRIPTION_LIBRARY, A.SUBSYSTEM_DESCRIPTION, WORKSTATION_TYPE
FROM QSYS2.WORKSTATION_INFO as A Join QSYS2.SUBSYSTEM_INFO AS B
on A.SUBSYSTEM_DESCRIPTION_LIBRARY= b.SUBSYSTEM_DESCRIPTION_LIBRARY and
A.SUBSYSTEM_DESCRIPTION = b.SUBSYSTEM_DESCRIPTION
and B.STATUS = 'ACTIVE' and ALLOCATION = '*SIGNON'
where WORKSTATION_TYPE = '*ALL'
En principe ca représentera l’ordre de recherche dans votre #ibmi
Remarque :
Quand 2 entrées sont démarrés avec le même critère c’est le premier qui est pris en compte Ici les entrées QPADEV* entrerons dans QINTER On peut également faire des entrées par type les 2 principaux sont IBM-3477-FC 132 IBM-3179-2 80 https://www.rfc-editor.org/rfc/rfc1205 Mais à éviter pas simple à gérer
https://www.gaia.fr/wp-content/uploads/2017/02/team3.png600600Pierre-Louis BERTHOIN/wp-content/uploads/2017/05/logogaia.pngPierre-Louis BERTHOIN2023-11-03 11:43:412023-11-03 11:43:44Comprendre comment un travail interactif est affecté à un sous système.
Concernant les fichiers JSON, on observe plusieurs types de géométries, principalement POLYGON et MULTIPOLYGON. C’est pourquoi il nous faut définir dans nos fichiers, une colonne qui puisse englober plusieurs types de géométries. Pour ce faire, le document Database Geospatial Analytics nous fournit quelques informations …
Nous choisirons donc, pour nos fichiers, une colonne basée sur la fonction ST_GEOMETRY, qui nous permet d’englober les deux type nommés ci-dessus. Voici donc comment nous constituerons nos tables.
-- Table des états américains
CREATE TABLE GGEOLOC.US_STATES (
STATE_ID CHAR(2) PRIMARY KEY,
STATE_FULL_NAME VARCHAR(50),
STATE_GEO QSYS2.ST_GEOMETRY);
-- Table des pays
CREATE TABLE GGEOLOC.COUNTRIES (
CODE_ISO VARCHAR(3) PRIMARY KEY,
NAME VARCHAR(50),
CNTRY_GEO QSYS2.ST_GEOMETRY);
-- Table des villes
CREATE TABLE GGEOLOC.MYCITIES (
CTY_NAME VARCHAR(50) ,
CTY_GEO QSYS2.ST_GEOMETRY);
Cet article étant dédié aux fonctions géospatiales, nous n’expliciterons pas la récupération des données.
Bienvenue à bord
ST_ISSIMPLE & ST_GEOMETRYTYPE …
… attachez vos ceintures
ST_ISSIMPLE nous permet de savoir si la géométrie de la figure sélectionnée est simple (valeur 1) ou bon (valeur 0).
SELECT STATE_FULL_NAME,
CASE QSYS2.ST_ISSIMPLE(STATE_GEO)
WHEN 0 THEN 'Geometry is not simple'
WHEN 1 THEN 'Geometry is simple'
END
FROM GGEOLOC.US_STATES where STATE_ID in ('WI', 'IL', 'IN', 'HI', 'AK');
Alaska
Geometry is not simple
Hawaii
Geometry is simple
Illinois
Geometry is simple
Indiana
Geometry is simple
Wisconsin
Geometry is simple
ST_GEOMETRYTYPE nous permet de savoir de quel type de géométrie nous parlons, et nous pouvons donc constater que la simplicité de la géométrie n’a pas de lien avec le caractère « MULTI » de la figure.
SELECT STATE_FULL_NAME, QSYS2.ST_GEOMETRYTYPE(STATE_GEO)
FROM GGEOLOC.US_STATES where STATE_ID in ('WI', 'IL', 'IN', 'HI', 'AK');
Alaska
ST_MULTIPOLYGON
Hawaii
ST_MULTIPOLYGON
Illinois
ST_POLYGON
Indiana
ST_POLYGON
Wisconsin
ST_POLYGON
ST_ASTEXT & ST_ASBINARY …
… briefing avant décollage
Si nous exécutons une extraction brute de nos données, on ne comprend pas immédiatement
select STATE_ID, STATE_FULL_NAME, STATE_GEO
from GGEOLOC.US_STATES where STATE_ID in ('OK', 'TX', 'AL', 'AR', 'CO');
ST_AREA nous donne la surface en m² d’une aire géographique (POLYGON ou MULTIPOLYGON)
on ajoute une colonne ici pour avoir une idée de l’aire en km²
select STATE_ID, STATE_FULL_NAME, QSYS2.ST_AREA(STATE_GEO), integer(QSYS2.ST_AREA(STATE_GEO)/1000000)
from GGEOLOC.US_STATES
where STATE_ID in ('OK', 'TX', 'AL', 'AR', 'HI');
AL
Alabama
1.3409800288446873E11
134098
AR
Arkansas
1.3838751120399905E11
138387
HI
Hawaii
1.4748657954505682E10
14748
OK
Oklahoma
1.8250255202012402E11
182502
TX
Texas
6.886199875225208E11
688619
ST_BUFFER nous donne les coordonnées d’une surface élargie du nombre de mètres voulus
voici un exemple de calcul de surfaces en élargissant de 1000 m les frontières de deux états
select STATE_ID, STATE_FULL_NAME, integer(QSYS2.ST_AREA(STATE_GEO)/1000000), integer(QSYS2.ST_AREA(QSYS2.ST_BUFFER(STATE_GEO, 1000))/1000000)
from GGEOLOC.US_STATES
where STATE_ID in ('OK', 'AL');
AL
Alabama
134098
135822
OK
Oklahoma
182502
184806
ST_DISJOINT & ST_WITHIN …
… garder le cap
ST_DISJOINT retourne 1 si deux figures n’ont rien en commun.
select CTY_NAME, CODE_ISO
from GGEOLOC.MYCITIES, GGEOLOC.COUNTRIES
where QSYS2.ST_DISJOINT(CTY_GEO, CNTRY_GEO) = 0 ;
HELSINKI
FIN
TEGUCIGALPA
HND
NAIROBI
KEN
GUADALAJARA
MEX
COPENHAGEN
DNK
LYON
FRA
NANTES
FRA
OSLO
NOR
ROCHESTER
USA
ST_WITHIN retourne 1 si la première figure est complètement dans la seconde.
Exemple : Une ville est-elle contenue dans un pays ? Un pays est-il contenu dans une ville ?
select CTY_NAME, CODE_ISO, QSYS2.ST_WITHIN(CTY_GEO, CNTRY_GEO), QSYS2.ST_WITHIN(CNTRY_GEO, CTY_GEO)
from GGEOLOC.MYCITIES, GGEOLOC.COUNTRIES
where CTY_NAME in ('LYON', 'ROCHESTER') and CODE_ISO in ('FRA', 'USA') ;
LYON
FRA
1
0
ROCHESTER
FRA
0
0
LYON
USA
0
0
ROCHESTER
USA
1
0
ST_INTERSECTS & ST_INTERSECTION …
… passer la frontière
ST_INTERSECTS nous permet de savoir si deux figures ont une intersection (la fonction retourne 1 si tel est le cas)
Dans l’exemple suivant, on cherche parmi une liste d’états, à savoir si ceux-ci sont directement voisins du Michigan
select t1.STATE_FULL_NAME, t2.STATE_FULL_NAME,
CASE WHEN QSYS2.ST_INTERSECTS(t1.STATE_GEO, t2.STATE_GEO) = 1
THEN 'Etats Voisins'
ELSE 'Etats éloignés'
END as config
from GGEOLOC.US_STATES t1, GGEOLOC.US_STATES t2
where t1.STATE_ID = 'MI'
and t2.STATE_ID in('WI', 'IL', 'IN', 'OH', 'PA', 'MN') ;
Michigan
Illinois
Etats éloignés
Michigan
Indiana
Etats Voisins
Michigan
Minnesota
Etats éloignés
Michigan
Ohio
Etats Voisins
Michigan
Pennsylvania
Etats éloignés
Michigan
Wisconsin
Etats Voisins
Il suffisait de voir la carte pour s’en rendre compte !! Heureusement, ST_INTERSECTION nous en dit beaucoup plus puisqu’elle nous indique la forme de l’intersection entre deux figures géométriques.
select t1.STATE_FULL_NAME, t2.STATE_FULL_NAME,
QSYS2.ST_ASTEXT(QSYS2.ST_INTERSECTION(t1.STATE_GEO, t2.STATE_GEO)),
CASE WHEN QSYS2.ST_INTERSECTS(t1.STATE_GEO, t2.STATE_GEO) = 1
THEN 'Etats Voisins'
ELSE 'Etats éloignés'
END as config
from GGEOLOC.US_STATES t1, GGEOLOC.US_STATES t2
where t1.STATE_ID = 'MI'
and t2.STATE_ID in('WI', 'IL', 'IN', 'OH', 'PA', 'MN');
ST_DISTANCE va retourner la distance entre deux points, mais il est intéressant de l’utiliser sur des figures de type POLYGON …
select t1.STATE_FULL_NAME, t2.STATE_FULL_NAME,
QSYS2.ST_DISTANCE(t1.STATE_GEO, t2.STATE_GEO)/1000
CASE WHEN QSYS2.ST_INTERSECTS(t1.STATE_GEO, t2.STATE_GEO) = 1
THEN 'Etats Voisins'
ELSE 'Etats éloignés'
END as config
from GGEOLOC.US_STATES t1, GGEOLOC.US_STATES t2
where t1.STATE_ID = 'MI'
and t2.STATE_ID in('WI', 'IL', 'IN', 'OH', 'PA', 'MN');
Michigan
Illinois
58.493941547601004
Michigan
Indiana
0.0
Michigan
Minnesota
33.60195301382611
Michigan
Ohio
0.0
Michigan
Pennsylvania
179.1488383130458
Michigan
Wisconsin
0.0
… pour lesquelles on se rend compte que la fonction retourne la distance (ramenées en km ici) entre les points les plus proches des deux figures comparées.
Atterrissage
Nous n’avons exploré ici qu’une partie des fonctions géospatiales disponibles. Il en existe bien d’autres fonctions pour savoir si une figure recouvre complètement une autre, si une figure est contenue dans une autre si une figure en traverse une autre, … Il existe également des fonctions de manipulation des GEOHASHES (système de géocodage basé sur la division d’une zone géographique en cellules).
Bref, tout une panoplie de fonctions que l’on peut combiner à l’infini et au-delà !
https://www.gaia.fr/wp-content/uploads/2021/07/GG-2.jpg343343Guillaume GERMAN/wp-content/uploads/2017/05/logogaia.pngGuillaume GERMAN2023-10-09 18:03:122023-10-09 18:03:14LE TOUR DU MONDE EN 10 (+1) FONCTIONS GEOSPATIALES
