, Triggers instead of

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

/par
, , , Requêtes SQL dans ACS extraites de Navigator for i

Navigator for i utilise les services SQL, il vous indique les requêtes qui ont été utilisées.

Vous pouvez les rejouer dans ACS exécuteur de scripts.

Voici comment :

Vous avez un bouton SQL qui permet d’accéder à la requête

Par contre quand vous voulez exécuter cette requête, vous avez ce message :

Il vous faut démarrer un service sur ACS pour accepter l’exécution de ces scripts

Vous voyez votre service à l’état démarré

Vous récupérez la requête SQL dans ACS

Voila c’est simple et efficace vous pouvez également copier la requête dans propriétés si vous préférer

/par
, , , SQL – appel de webservice

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 !

/par
, Zones avec informations confidentielles

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é

/par
, Vue rapide du catalogue DB2

Comment à voir une vue rapide du catalogue de votre base de données ?

Voici une solution qui consiste à utiliser le centre de santé

Dans l’onglet « Schémas »
sur la base de donnée faites un clic droit
sélectionnez « Centre de santé »

Les fichiers du catalogue DB2 sont dans QSYS et ils commencent par QADB.

Commencez par mettre en place ce filtre et cliquez sur régénération

Vous allez utiliser 2 vues

« limites de taille » , qui vous permettra de voir le nombre d’enregistrements

Vous voyez par exemple dans QADBFILD la liste des zones de votre DB

« Activité« , qui vous donnera des informations sur les mises à jour sur vos fichiers.

Conclusion :


Vous avez une vue syntaxique de la taille et des mouvements de votre catalogue DB2.

L’exécution de la requête peut être un peu longue quand vous cliquez sur régénération.

Pour voir les informations contenues dans votre catalogue, utilisez les vues SYS* de QSYS2 ou de vos SCHEMAS.

/par
, Connaitre la taille vos MTI

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.

/par
, , Générer un XLS avec SQL

La TR 3 de la V7R5 nous apporte un nouveau service qui va permettre de générer un fichier XLS à partir d’une requête SQL

Exemple :

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
PATH_NAME => ‘LST_option.xls’,
SPREADSHEET_QUERY => ‘Select * from qgpl.qauoopt’ ,
COLUMN_HEADINGS => ‘COLUMN’ ,
SPREADSHEET_TYPE => ‘xlsx’)

Remarque:

ACS doit être installé sur votre partition et si possible à jour

Attention par défaut il génère du csv

/par
, Analyse CPF1164

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)

/par
, Comprendre 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

/par
, Tables avec informations de création et modification

Comment gérer simplement les informations de modifications sur les enregistrements
utilisateur + date de création ou de modification

Vous connaissez la méthode applicative par sql ou update

Vous connaissez la méthode par trigger after, vous modifiez les zones dans le buffer après avec les informations en cours.

Voici un solution ou vous n’avez rien à faire, vous laissez faire SQL

Soit la table suivante des applications :

CREATE TABLE APPLICATION1 FOR SYSTEM NAME APPLICAT1 (
DESCRIPTION_APPLICATION FOR COLUMN DES_APP CHAR(50) NOT NULL WITH DEFAULT,
APPLICATION_CODE FOR COLUMN APP_COD CHAR(8) NOT NULL WITH DEFAULT,

/* Informations de mise à jour */
APPAUSRCHG VARCHAR(18) GENERATED ALWAYS AS (USER),
APPATMPCHG TIMESTAMP FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL,
APPAUSRCRT CHAR(18) NOT NULL DEFAULT USER IMPLICITLY HIDDEN,
APPATMPCRT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP IMPLICITLY HIDDEN
)
Explications :

GENERATED ALWAYS AS (USER), indique que la zone est systématiquement forcée avec l’utilisateur en cours
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP indique qu’a chaque modification, vous aurez la date en cours
DEFAULT USER indique valeur par défaut utilisateur en cours
DEFAULT CURRENT_TIMESTAMP indique valeur par défaut date en cours
HIDDEN indique que la zone n’apparaitra pas dans un select *

Exemple d’insertion

INSERT INTO GDATA.APPLICATION1 (
DESCRIPTION_APPLICATION,APPLICATION_CODE ) VALUES ( ‘TEST’, ‘TEST’ )

Exemple de mise à jour

UPDATE GDATA.APPLICATION1
SET
DESCRIPTION_APPLICATION = ‘TEST2’,
APPLICATION_CODE = ‘test2’
WHERE DESCRIPTION_APPLICATION = ‘TEST’

Visualisation du résultat

Sauf zones cachées


SELECT * ROM GDATA.APPLICATION1

Avec les zones cachés

SELECT DESCRIPTION_APPLICATION, APPLICATION_CODE, APPAUSRCHG,
APPATMPCHG, APPAUSRCRT, APPATMPCRT FROM GDATA.APPLICATION1

Remarques :
Les zones user devront être sur 18 en varchar
Vous devez utiliser le format explicite pour la création
Voila , Simple et efficace
Merci à Patrick pour son exemple de code

/par