DB2 for i SQL Expert - IBM i

Etape 1 : Identifier le type de besoin

Analyser la demande et classifier :

Etape 2 : Appliquer les regles critiques

Regles OBLIGATOIRES dans tout SQL genere :

Ecriture SQL (fondamentaux) :

1. Commenter chaque bloc — Expliquer le POURQUOI de chaque decision
2. Qualifier les tables — SCHEMA.TABLE (ex: QSYS2.SYSTABLES, SQLFOR.EMPLOYE)
3. Nommer les colonnes — Jamais SELECT * en production (sauf exploration)
4. Utiliser des alias — AS pour les colonnes calculees et les tables dans les JOIN
5. DECIMAL pour les moyennes — DECIMAL(AVG(col), 7, 2) pour eviter les troncatures
6. Preferer JOIN a sous-requete — Quand les deux sont possibles, JOIN est souvent plus performant
7. Tester NULL explicitement — IS NULL / IS NOT NULL, jamais = NULL
8. ORDER BY explicite — Ne jamais supposer un ordre de retour
9. FETCH FIRST n ROWS ONLY — Standard DB2 pour les top-N
10. FOR READ ONLY — Sur les SELECT de consultation pour permettre l'optimisation
11. OPTIMIZE FOR n ROWS — Quand on sait combien de lignes on attend
12. CTE plutot que sous-requetes imbriquees — Plus lisible et souvent plus performant
13. Eviter LIKE '%valeur' — Le % en debut empeche l'utilisation de l'index
14. Eviter les derivations dans WHERE — SUBSTR(col, 1, 5) = x empeche l'optimiseur. Creer un index derive si necessaire

Environnement (Griere) : 15. Objectif d'optimisation correct — *ALLIO (OPTIMIZE FOR ALL ROWS) pour batch, *FIRSTIO (OPTIMIZE FOR n ROWS) pour interactif 16. *CLOSQLCSR(ENDJOB) — Garder les ODP ouverts pour les invocations futures 17. *ALWCPYDTA(OPTIMIZE) — Permettre a l'optimiseur de creer des copies temporaires 18. Verifier les statistiques — DSPSYSVAL QDBFSTCCOL doit etre actif

Modernisation (Hauser) : 19. Logique metier dans la base — Vues, contraintes, triggers, procedures. Pas dans les programmes 20. Vues SQL pour masquer la complexite — Une vue complexe + un SELECT simple = reutilisable par tous les langages 21. CREATE OR REPLACE — Toujours utiliser pour faciliter le deploiement

Etape 3 : Structurer la reponse

Pour chaque requete SQL fournie :

-- ============================================================
-- OBJECTIF : [Description claire de ce que fait la requete]
-- POURQUOI : [Justification des choix techniques]
-- PREREQUIS : [Tables, index, autorisations necessaires]
-- SOURCE   : [Redbook SG24-8326 / Birgitta Hauser / Christian Griere si applicable]
-- ============================================================

-- [Le code SQL commente ligne par ligne]

SECTION 1 : Procedures stockees (Redbook SG24-8326 Ch.2/4/8)

Structure d'une procedure SQL PSM

CREATE OR REPLACE PROCEDURE schema.nom_procedure (
    IN  p_param1  VARCHAR(50),         -- Parametre d'entree
    OUT p_result  INTEGER,             -- Parametre de sortie
    INOUT p_bidir VARCHAR(100)         -- Parametre bidirectionnel
)
LANGUAGE SQL
SPECIFIC nom_specifique                -- Nom unique pour identification
MODIFIES SQL DATA                     -- Peut INSERT/UPDATE/DELETE
COMMIT ON RETURN YES                  -- Commit automatique si succes
SET OPTION DBGVIEW = *SOURCE,          -- Permettre le debug
           DFTRDBCOL = MONSCHEMA       -- Schema par defaut
BEGIN
    -- Corps de la procedure
    DECLARE v_local VARCHAR(256);
    DECLARE v_sqlcode INTEGER DEFAULT 0;

    -- Handler pour gestion d'erreur
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        SET v_sqlcode = SQLCODE;

    -- Logique metier ici
    SET p_result = 0;
END;

Gestion d'erreur avancee (Redbook 2.6)

-- HANDLER types:
-- CONTINUE  : continue apres l'erreur
-- EXIT      : sort du bloc BEGIN/END
-- UNDO      : rollback + sort (dans BEGIN ATOMIC)

DECLARE EXIT HANDLER FOR SQLSTATE '23505'  -- Doublon cle primaire
BEGIN
    SET p_erreur = 'Doublon detecte sur cle primaire';
    SIGNAL SQLSTATE '70001'
        SET MESSAGE_TEXT = 'Erreur applicative : doublon';
END;

-- GET DIAGNOSTICS pour details de l'erreur
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
        v_sqlstate = RETURNED_SQLSTATE,
        v_sqlcode  = DB2_RETURNED_SQLCODE,
        v_message  = MESSAGE_TEXT;
END;

Result sets (Redbook 4.7)

-- Procedure retournant un result set
CREATE OR REPLACE PROCEDURE schema.get_employes_dept (
    IN p_dept VARCHAR(3)
)
LANGUAGE SQL
RESULT SETS 1                           -- Nombre max de result sets
BEGIN
    DECLARE c1 CURSOR WITH RETURN FOR   -- WITH RETURN = result set
        SELECT empno, firstnme, lastname, salary
        FROM employee
        WHERE workdept = p_dept
        ORDER BY lastname
        FOR READ ONLY;

    OPEN c1;                            -- Ouvrir le curseur, le caller le fermera
END;

Consommation des result sets dans une procedure appelante (Redbook 4.7.2)

CREATE OR REPLACE PROCEDURE schema.consommer_results ()
LANGUAGE SQL
BEGIN
    DECLARE v_empno CHAR(6);
    DECLARE v_nom   VARCHAR(15);
    DECLARE v_loc   RESULT_SET_LOCATOR VARYING;
    DECLARE v_fin   INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fin = 1;

    -- Appeler la procedure qui retourne un result set
    CALL schema.get_employes_dept('A00');
    ASSOCIATE LOCATORS (v_loc) WITH PROCEDURE schema.get_employes_dept;
    ALLOCATE c_result CURSOR FOR RESULT SET v_loc;

    FETCH c_result INTO v_empno, v_nom;
    WHILE v_fin = 0 DO
        -- Traiter chaque ligne
        FETCH c_result INTO v_empno, v_nom;
    END WHILE;
    CLOSE c_result;
END;

Procedures reutilisables avec variables globales (Redbook Ch.8)

-- Variables globales pour partager des donnees entre procedures
CREATE OR REPLACE VARIABLE gv_sql_string CLOB(2M);
CREATE OR REPLACE VARIABLE gv_row_change_timestamp TIMESTAMP;

-- Vue flexible avec variable globale (technique Redbook Ch.8)
CREATE OR REPLACE VIEW dept_change AS
    SELECT * FROM department
    WHERE when_changed = gv_row_change_timestamp;
-- Assigner la variable AVANT d'interroger la vue

Transaction management (Redbook 3.4)

-- Niveaux d'isolation :
-- *NONE    (No Commit) - Pas de verrouillage
-- *CHG     (Uncommitted Read) - Lit les donnees non commitees
-- *CS      (Cursor Stability) - Verrouille la ligne courante
-- *ALL     (Read Stability) - Verrouille les lignes lues
-- *RR      (Repeatable Read) - Verrouillage maximal

-- SAVEPOINT pour rollback partiel
SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS;
INSERT INTO commandes VALUES (...);
-- Si erreur sur la deuxieme operation :
ROLLBACK TO SAVEPOINT sp1;
-- Sinon :
COMMIT;

SECTION 2 : Fonctions UDF et UDTF (Redbook SG24-8326 Ch.6)

Fonction scalaire simple

CREATE OR REPLACE FUNCTION schema.total_salary (
    salary     DECIMAL(9, 2),
    bonus      DECIMAL(9, 2),
    commission DECIMAL(9, 2)
)
RETURNS DECIMAL(9, 2)
LANGUAGE SQL
SPECIFIC totsal01
DETERMINISTIC                    -- Meme entree = meme sortie (permet cache)
CONTAINS SQL                     -- Ne lit/modifie pas de donnees
NOT FENCED                       -- Meme thread = meilleure performance
NO EXTERNAL ACTION               -- Pas d'effet de bord externe
RETURN COALESCE(salary, 0) + COALESCE(bonus, 0) + COALESCE(commission, 0);

Proprietes critiques des UDF (Redbook 6.5)

UDF Inlining (Redbook 6.9) — Performance critique

Une UDF peut etre "inlinee" (integree directement dans la requete appelante) si :

• Elle contient UNIQUEMENT un RETURN (pas de SET, pas de SELECT INTO)
• Elle est DETERMINISTIC
• Elle ne specifie pas ATOMIC
• Verifier avec : SELECT inline FROM QSYS2.SYSFUNCS WHERE routine_schema = 'MON_SCHEMA'

-- INLINE-capable (BIEN) :
CREATE OR REPLACE FUNCTION schema.discount (totalSales DECIMAL(11,2))
RETURNS DECIMAL(11,2)
LANGUAGE SQL
DETERMINISTIC
NOT FENCED
RETURN (CASE WHEN totalSales > 2000 THEN totalSales * 0.95 ELSE totalSales END);

-- NON INLINE (utilise SET) :
-- Eviter cette forme quand l'inlining est souhaite

UDTF — User-Defined Table Function (Redbook 6.10)

-- UDTF simple : employes par projet
CREATE OR REPLACE FUNCTION schema.emp_by_project (
    p_project VARCHAR(6)
)
RETURNS TABLE (
    empno     CHAR(6),
    firstname CHAR(20),
    lastname  CHAR(20),
    birthdate DATE
)
LANGUAGE SQL
SPECIFIC empbyprj
NOT FENCED
DETERMINISTIC
READS SQL DATA
RETURN
    SELECT e.empno, e.firstnme, e.lastname, e.birthdate
    FROM employee e
    WHERE e.empno IN (
        SELECT empno FROM empprojact WHERE projno = p_project
    );

-- Utilisation :
-- SELECT * FROM TABLE(schema.emp_by_project('OP1010')) AS x;

UDTF avec OLAP (Redbook 6.10.4)

CREATE OR REPLACE FUNCTION schema.salary_rank()
RETURNS TABLE (
    position   INTEGER,
    empno      CHAR(6),
    firstname  CHAR(20),
    lastname   CHAR(20),
    salary     DECIMAL(13,2),
    rank       INTEGER,
    dense_rank INTEGER
)
SPECIFIC sal_rank
LANGUAGE SQL
READS SQL DATA
NOT FENCED
DETERMINISTIC
BEGIN
    RETURN
        SELECT
            ROW_NUMBER()  OVER(ORDER BY salary DESC) AS position,
            empno, firstnme, lastname, salary,
            RANK()        OVER(ORDER BY salary DESC) AS rank,
            DENSE_RANK()  OVER(ORDER BY salary DESC) AS dense_rank
        FROM employee
        ORDER BY salary DESC
        FETCH FIRST 10 ROWS ONLY;
END;

Pipelined Table Function (Redbook 6.11)

Retourne les resultats ligne par ligne avec PIPE, sans table temporaire :

CREATE OR REPLACE FUNCTION schema.transform()
RETURNS TABLE (employee_name CHAR(20), unique_nbr INT)
LANGUAGE SQL
BEGIN
    DECLARE v_name VARCHAR(15);
    DECLARE v_num  INTEGER DEFAULT 1;
    DECLARE v_end  INTEGER DEFAULT 0;
    DECLARE c1 CURSOR FOR SELECT lastname FROM employee ORDER BY 1;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_end = 1;

    OPEN c1;
    myloop: LOOP
        FETCH c1 INTO v_name;
        IF v_end = 1 THEN LEAVE myloop; END IF;
        PIPE (v_name, v_num);           -- Retourne UNE ligne a la fois
        SET v_num = v_num + 1;
    END LOOP;
    CLOSE c1;
    RETURN;                             -- Signal fin de donnees
END;
-- Utilisation : SELECT * FROM TABLE(schema.transform()) x;

Avantages des pipelined functions :

• Pas de table temporaire en memoire/disque
• Le consommateur recoit les lignes au fur et a mesure
• Ideal pour gros volumes, ETL, transformation de donnees
• Peut combiner donnees de sources multiples (API, fichiers IFS, autres systemes)

SECTION 3 : Triggers SQL (Redbook SG24-8326 Ch.5)

Structure d'un trigger

CREATE OR REPLACE TRIGGER schema.audit_salary
    AFTER UPDATE OF salary ON schema.employee    -- Quand ? Sur quoi ?
    REFERENCING NEW ROW AS new OLD ROW AS old    -- Acces aux valeurs avant/apres
    FOR EACH ROW MODE DB2ROW                     -- Par ligne (pas par instruction)
    WHEN (new.salary <> old.salary)              -- Condition optionnelle
BEGIN
    INSERT INTO schema.salary_audit (
        empno, old_salary, new_salary, changed_by, changed_at
    ) VALUES (
        new.empno, old.salary, new.salary, CURRENT USER, CURRENT TIMESTAMP
    );
END;

Types de triggers

Trigger self-referencing (Redbook 5.5.8)

-- Regle metier : le manager recoit 10% du bonus de l'employe
CREATE OR REPLACE TRIGGER schema.manager_comm
    AFTER UPDATE OF comm ON schema.employee
    REFERENCING NEW ROW AS new OLD ROW AS old
    FOR EACH ROW MODE DB2ROW
    WHEN (new.comm <> old.comm AND new.job <> 'MANAGER')
BEGIN
    DECLARE v_mgrcomm  DECIMAL(9,2);
    DECLARE v_mgrempno CHAR(6);

    IF new.comm > old.comm THEN
        SET v_mgrcomm = (new.comm - old.comm) / 10;
        SET v_mgrempno = (
            SELECT mgrno FROM dept
            WHERE deptno = (SELECT workdept FROM employee WHERE empno = new.empno)
        );
        IF v_mgrempno IS NOT NULL THEN
            UPDATE employee SET comm = comm + v_mgrcomm
            WHERE empno = v_mgrempno;
        END IF;
    END IF;
END;

Trigger avec SIGNAL pour validation (Redbook 5.5.7)

CREATE OR REPLACE TRIGGER schema.check_salary_increase
    BEFORE UPDATE OF salary ON schema.employee
    REFERENCING NEW ROW AS new OLD ROW AS old
    FOR EACH ROW MODE DB2ROW
BEGIN
    -- Augmentation max 20%
    IF new.salary > old.salary * 1.20 THEN
        SIGNAL SQLSTATE '75001'
            SET MESSAGE_TEXT = 'Augmentation superieure a 20% interdite';
    END IF;
END;

Catalogues triggers utiles

-- Voir tous les triggers d'un schema
SELECT trigger_name, event_manipulation, event_object_table,
       action_timing, action_orientation
FROM QSYS2.SYSTRIGGERS
WHERE trigger_schema = 'MONSCHEMA';

-- Voir les dependances d'un trigger
SELECT trigger_name, object_name, object_type
FROM QSYS2.SYSTRIGDEP
WHERE trigger_schema = 'MONSCHEMA';

SECTION 4 : Indexation avancee (Christian Griere)

Philosophie Griere : "Toutes les exploitations IBM i souffrent d'un manque d'index"

Causes historiques :

• L'AS/400 n'avait pas la puissance hardware pour supporter beaucoup de logiques
• Les developpeurs RPG faisaient le SELECT/OMIT avec du code, pas des index
• Reflexe de limiter les index encore present dans les equipes de developpement

Aujourd'hui : la puissance d'une partition IBM i est limitee par ses IOs, JAMAIS par sa capacite de traitement.

Les trois types d'index sur IBM i

1. Index binaires — Les classiques (fichier logique / CREATE INDEX). Arbre B+.
2. Index EVI (Encoded Vector Index) — Specifiques IBM i, brevetes. Pour colonnes a faible cardinalite (statut, type, categorie). Se cumulent parfaitement en technique bitmap.
3. Index texte — OmniFind Text Search. Fonctions CONTAINS et SCORE.

Strategie d'indexation (Griere)

Ordre des cles dans un index :

1. Predicats d'egalite (=) en premier — ils reduisent le plus vite le nombre de lignes
2. Predicats de selection + predicats de jointure
3. Colonnes les plus selectives en premier
4. Colonnes du ORDER BY en dernier (pour eviter un tri)

Index parfait : Un index est "parfait" quand il couvre tous les predicats de selection ET de jointure de la requete.

-- Requete :
SELECT nom, prenom FROM client WHERE codsoc = 'SOC1' AND nodoc = '12345';

-- Index parfait :
CREATE INDEX idx_client_soc_doc ON client (codsoc, nodoc);
-- Les deux colonnes du WHERE dans l'ordre de selectivite

Index Only Access (IOA) : L'index contient TOUTES les colonnes necessaires a la requete. Aucun acces a la table.

-- Pour la requete : SELECT nom FROM client WHERE codsoc = 'SOC1'
-- IOA = inclure 'nom' dans l'index :
CREATE INDEX idx_client_ioa ON client (codsoc) INCLUDE (nom);
-- ou avec les colonnes dans la cle :
CREATE INDEX idx_client_ioa2 ON client (codsoc, nom);

MTI — Maintained Temporary Index (Griere)

Les MTI sont crees automatiquement par SQE quand l'optimiseur detecte un manque d'index.

• Memes caracteristiques qu'un index permanent
• Faiblesse : disparaissent a l'IPL — proscrire les IPL frequents !
• Preuve que l'indexation est insuffisante si beaucoup de MTI existent

-- Voir les MTI en cours
SELECT * FROM QSYS2.SYSIXADV
WHERE TIMES_ADVISED > 10                   -- Suggere plus de 10 fois
ORDER BY TIMES_ADVISED DESC;

-- Creer automatiquement les index recommandes (> 500 suggestions)
CALL SYSTOOLS.ACT_ON_INDEX_ADVICE('MON_SCHEMA', NULL, NULL, 500, NULL);

-- Analyser les index MTI existants pour les rendre permanents
SELECT MTI_CREATED, MTI_USED, MTI_KEY_COLUMNS_ADVISED,
       TABLE_SCHEMA, TABLE_NAME
FROM QSYS2.SYSIXADV
WHERE MTI_CREATED = 'YES'
ORDER BY MTI_USED DESC;

Index EVI (Encoded Vector Index) — Griere

Quand les utiliser :

• Colonnes a faible cardinalite (statut, type, pays, categorie)
• Requetes avec plusieurs predicats sur differentes colonnes (star join)
• L'optimiseur peut combiner plusieurs EVI en technique bitmap

-- Creer un EVI
CREATE ENCODED VECTOR INDEX evi_client_statut
    ON client (statut);

CREATE ENCODED VECTOR INDEX evi_client_pays
    ON client (pays);

-- DB2 combinera les deux EVI pour :
-- SELECT * FROM client WHERE statut = 'A' AND pays = 'FR';

Surveillance des EVI (Griere) :

• Verifier regulierement la zone d'overflow des EVI a code 4 octets
• Un EVI peut etre recree automatiquement par DB2 si overflow plein (bloquant temporairement)

Index derives et index epars (sparse)

-- Index derive (sur expression) — depuis IBM i 6.1
CREATE INDEX idx_year_sales ON orders (YEAR(order_date));
-- Utilise par : WHERE YEAR(order_date) = 2024

-- Index sparse (avec WHERE) — remplace SELECT/OMIT des logiques DDS
CREATE INDEX idx_active_clients ON client (nom)
    WHERE statut = 'A';
-- Maintenu uniquement pour les lignes actives — plus petit, plus rapide

Trouver les index inutilises (Griere)

-- Les index qui n'ont JAMAIS ete utilises (2 compteurs a 0)
-- mais dont la table EST utilisee (au moins un index utilise)
SELECT DISTINCT
    a.TABLE_SCHEMA  AS "Schema",
    a.TABLE_NAME    AS "Table",
    a.INDEX_NAME    AS "Index",
    a.COLUMN_NAMES  AS "Cles",
    a.QUERY_STATISTICS_COUNT AS "Nb util. stats",
    a.QUERY_USE_COUNT        AS "Nb util. plan"
FROM QSYS2.SYSIXSTAT a
WHERE a.QUERY_STATISTICS_COUNT = 0
  AND a.QUERY_USE_COUNT = 0
  AND a.IS_UNIQUE = 'D'              -- Pas les index UNIQUE (contrainte)
  AND EXISTS (                        -- La table doit etre utilisee
      SELECT 1 FROM QSYS2.SYSIXSTAT b
      WHERE b.TABLE_SCHEMA = a.TABLE_SCHEMA
        AND b.TABLE_NAME = a.TABLE_NAME
        AND (b.QUERY_STATISTICS_COUNT > 0 OR b.QUERY_USE_COUNT > 0)
  )
ORDER BY a.TABLE_SCHEMA, a.TABLE_NAME;

Visual Explain — Erreurs courantes (Griere)

3 erreurs d'interpretation a eviter :

1. Comparer des temps avec des objectifs differents — Un plan *FIRSTIO (OPTIMIZE FOR 30 ROWS) affiche 15ms, un plan *ALLIO affiche 1200ms. Le premier n'est PAS plus rapide : il montre le temps pour 30 lignes seulement.

2. Test d'index vs Scannage d'index — Le "Test d'index" (probe) est un acces direct dans l'arbre. Le "Scannage d'index" est une lecture sequentielle des postes. DB2 peut enchainer les deux.

3. MTI jamais cree si un index est deja utilise — Si la requete utilise deja un index (meme mauvais), DB2 ne creera PAS de MTI. Il faut analyser manuellement.

QAQQINI — Parametres de performance (Griere)

-- Modifier temporairement un parametre d'optimisation
CALL QSYS2.OVERRIDE_QAQQINI(1, 'REOPTIMIZE_ACCESS_PLAN', '*YES');
-- Force la reoptimisation a chaque execution (utile pour les tests)

-- Interdire la creation de MTI (pour tests de performance stables)
CALL QSYS2.OVERRIDE_QAQQINI(1, 'ALLOW_TEMPORARY_INDEXES', '*NO');

-- Reinitialiser
CALL QSYS2.OVERRIDE_QAQQINI(1, '', '');

SECTION 5 : Vues SQL et modernisation (Birgitta Hauser)

Philosophie Hauser : "Plus on met de logique metier dans la base, moins on a a faire dans les programmes"

Les vues SQL sont l'outil #1 de modernisation :

• Masquent la complexite des jointures et calculs
• Reutilisables par TOUS les langages (RPG, PHP, Python, Node.js)
• Le moteur SQE optimise automatiquement (statistiques, index advisors)

-- Vue complexe cachant la logique metier
CREATE OR REPLACE VIEW schema.v_employe_complet AS
    SELECT
        e.empno,
        RTRIM(e.lastname) || ', ' || RTRIM(e.firstnme) AS nom_complet,
        e.salary,
        COALESCE(e.bonus, 0) + COALESCE(e.comm, 0)     AS primes_totales,
        e.salary + COALESCE(e.bonus, 0) + COALESCE(e.comm, 0) AS remuneration_totale,
        d.deptname,
        d.mgrno AS manager_empno
    FROM employee e
    INNER JOIN dept d ON e.workdept = d.deptno;

-- Utilisation simple par tous les langages :
-- SELECT * FROM schema.v_employe_complet WHERE deptname LIKE 'SOFT%';

Generation JSON depuis SQL (Hauser — IBM i 7.3+)

-- JSON_OBJECT : genere un objet JSON par ligne
SELECT JSON_OBJECT(
    'empno'  : empno,
    'nom'    : RTRIM(lastname),
    'prenom' : RTRIM(firstnme),
    'salaire': salary
) AS json_employe
FROM employee
WHERE workdept = 'A00';

-- JSON_ARRAY : genere un tableau JSON
SELECT JSON_ARRAY(
    SELECT JSON_OBJECT(
        'empno' : empno,
        'nom'   : RTRIM(lastname)
    )
    FROM employee
    WHERE workdept = 'A00'
) AS json_array_dept;

-- Lecture JSON avec JSON_TABLE (IBM i 7.4+)
SELECT jt.*
FROM JSON_TABLE(
    '{"employes": [{"nom": "Dupont", "age": 35}, {"nom": "Martin", "age": 42}]}',
    '$.employes[*]'
    COLUMNS (
        nom VARCHAR(50) PATH '$.nom',
        age INTEGER     PATH '$.age'
    )
) AS jt;

Ecriture IFS avec SQL (Hauser — GitHub)

-- Ecrire du JSON dans l'IFS
-- Methode 1 : via SYSTOOLS
CALL SYSTOOLS.IFS_WRITE_UTF8(
    PATH_NAME => '/home/myuser/export.json',
    LINE      => CAST('{"data": "valeur"}' AS CLOB(1M) CCSID 1208),
    OVERWRITE => 'REPLACE',
    END_OF_LINE => 'NONE'
);

-- Methode 2 : via QSYS2 (IBM i 7.4+)
SELECT * FROM TABLE(QSYS2.IFS_WRITE(
    PATH_NAME => '/home/myuser/data.csv',
    LINE      => 'NOM;PRENOM;SALAIRE',
    OVERWRITE => 'REPLACE'
));

Generation XML depuis SQL (Hauser)

-- XML natif DB2
SELECT XMLELEMENT(
    NAME "employe",
    XMLELEMENT(NAME "nom", RTRIM(lastname)),
    XMLELEMENT(NAME "prenom", RTRIM(firstnme)),
    XMLELEMENT(NAME "salaire", salary)
) AS xml_employe
FROM employee
WHERE workdept = 'A00';

-- XMLAGG pour agreger en un seul document
SELECT XMLELEMENT(
    NAME "departement",
    XMLAGG(
        XMLELEMENT(NAME "employe",
            XMLELEMENT(NAME "nom", RTRIM(lastname)),
            XMLELEMENT(NAME "salaire", salary)
        )
    )
) AS xml_dept
FROM employee
WHERE workdept = 'A00';

SQL Dynamic Compound Statement (Hauser)

Scripts SQL autonomes sans creer de procedure :

-- Modifier des profils utilisateurs via SQL dynamique
BEGIN
    DECLARE v_cmd VARCHAR(256);
    FOR v_user AS c1 CURSOR FOR
        SELECT authorization_name
        FROM QSYS2.USER_INFO
        WHERE authorization_name LIKE 'LAB%'
    DO
        SET v_cmd = 'CHGUSRPRF USRPRF(' || authorization_name || ') LMTDEVSSN(*YES)';
        CALL QCMDEXC(v_cmd);
    END FOR;
END;

Deployer du SQL comme REST API (Hauser — IBM i 7.4)

Avec le HTTP Web Administration GUI, on peut deployer des SELECT, des procedures et des fonctions comme des endpoints REST. DB2 for i agit comme fournisseur RESTful.

SECTION 6 : Services SQL IBM i (QSYS2)

Services essentiels

-- Jobs actifs
SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) x
WHERE JOB_TYPE = 'INT'
ORDER BY ELAPSED_CPU_PERCENTAGE DESC
FETCH FIRST 20 ROWS ONLY;

-- PTF installes
SELECT * FROM QSYS2.PTF_INFO
WHERE PTF_PRODUCT_ID = '5770SS1'
ORDER BY PTF_IDENTIFIER DESC;

-- Niveau Technology Refresh
SELECT * FROM QSYS2.GROUP_PTF_INFO
WHERE PTF_GROUP_DESCRIPTION LIKE '%Technology%';

-- Verifier la devise des PTF
SELECT ptf_group_currency, ptf_group_id, ptf_group_title,
       ptf_group_level_installed, ptf_group_level_available
FROM SYSTOOLS.GROUP_PTF_CURRENCY
WHERE ptf_group_currency <> 'INSTALLED LEVEL IS CURRENT';

-- Informations systeme
SELECT * FROM QSYS2.SYSTEM_STATUS_INFO;

-- Espaces disque
SELECT * FROM QSYS2.ASP_INFO;

-- Travaux par sous-systeme
SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO(SUBSYSTEM_LIST_FILTER => 'QINTER'));

Health Center (Redbook 9.1)

-- Vue d'ensemble base de donnees
CALL QSYS2.HEALTH_DATABASE_OVERVIEW('MONSCHEMA');

-- Activite sur les objets
CALL QSYS2.HEALTH_ACTIVITY('MONSCHEMA');

-- Limites environnementales (top jobs)
CALL QSYS2.HEALTH_ENVIRONMENTAL_LIMITS();

Plan cache (Redbook 9.3 / Griere)

-- Dumper les top 20 requetes les plus lentes
CALL QSYS2.DUMP_PLAN_CACHE_TOPN('SNAPSHOTS', 'TOP20', 20);

-- Extraire les requetes d'un utilisateur (Redbook 9.2)
CALL QSYS2.EXTRACT_STATEMENTS('SNAPSHOTS', 'MYDUMP',
    ADDITIONAL_SELECT_COLUMNS =>
        'DEC(QQI6)/1000000.0 AS Total_time_sec, QVC102 AS Current_User',
    ADDITIONAL_PREDICATES =>
        ' AND QQI6 > 1000000 AND QVC102 = ''MONUSER'' ',
    ORDER_BY => ' ORDER BY QQI6 DESC ');

Lecture IFS

-- Lire un fichier CSV
SELECT *
FROM TABLE(QSYS2.IFS_READ_UTF8(
    PATH_NAME => '/home/myuser/data.csv'
)) AS t;

-- 10 plus gros fichiers dans /home
SELECT path_name, object_type,
       DECIMAL(data_size / 1048576.0, 10, 2) AS taille_mo,
       create_timestamp
FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(
    START_PATH_NAME    => '/home',
    SUBTREE_DIRECTORIES => 'YES',
    OBJECT_TYPE_LIST    => '*STMF'
)) AS t
ORDER BY data_size DESC
FETCH FIRST 10 ROWS ONLY
FOR READ ONLY
OPTIMIZE FOR 10 ROWS;

SECTION 7 : Catalogues systeme essentiels

-- Tables d'un schema
SELECT table_name, table_type, row_count, data_size
FROM QSYS2.SYSTABLESTAT
WHERE table_schema = 'MONSCHEMA'
ORDER BY data_size DESC;

-- Colonnes d'une table
SELECT column_name, data_type, length, numeric_scale, is_nullable, column_default
FROM QSYS2.SYSCOLUMNS
WHERE table_schema = 'MONSCHEMA' AND table_name = 'MATABLE'
ORDER BY ordinal_position;

-- Procedures stockees
SELECT routine_name, specific_name, routine_type, sql_data_access
FROM QSYS2.SYSROUTINES
WHERE routine_schema = 'MONSCHEMA' AND routine_type = 'PROCEDURE';

-- Fonctions (UDF + UDTF) avec info INLINE
SELECT routine_name, specific_name, function_type, is_deterministic,
       fenced, inline, secure
FROM QSYS2.SYSFUNCS
WHERE routine_schema = 'MONSCHEMA';

-- Triggers
SELECT trigger_name, event_manipulation, event_object_table, action_timing
FROM QSYS2.SYSTRIGGERS
WHERE trigger_schema = 'MONSCHEMA';

-- Regenerer le DDL d'un objet
CALL QSYS2.GENERATE_SQL('NOM_OBJET', 'MONSCHEMA', 'TABLE', REPLACE_OPTION => '0');

SECTION 8 : Troubleshooting et reference SQLCODE DB2 for i

Comprendre les SQLCODE sur IBM i

Un SQLCODE est un code retour envoye par DB2 for i apres chaque instruction SQL :

• SQLCODE = 0 : succes
• SQLCODE > 0 : succes avec avertissement (warning)
• SQLCODE < 0 : erreur, l'instruction a echoue
• SQLCODE = 100 : aucune ligne trouvee (NOT FOUND)

Pour afficher le detail d'un SQLCODE sur IBM i :

DSPMSGD MSGID(SQL0204) MSGF(QSQLMSG)

La regle IBM : prendre la valeur absolue du SQLCODE et la prefixer par SQL (ou SQ si >= 10000). Ex: SQLCODE -204 → message SQL0204, SQLCODE -30000 → message SQ30000.

Reference complete des SQLCODE DB2 for i

Succes et avertissements (SQLCODE >= 0)

Erreurs de syntaxe et structure SQL (SQLCODE -100 a -199)

Erreurs d'objet et de reference (SQLCODE -200 a -299)

Erreurs de variables et donnees (SQLCODE -300 a -399)

Erreurs d'objet SQL (SQLCODE -400 a -499)

Erreurs de curseur (SQLCODE -500 a -599)

Erreurs d'index et DDL (SQLCODE -600 a -699)

Erreurs de trigger et routine (SQLCODE -700 a -799)

Erreurs de donnees et integrite (SQLCODE -800 a -899)

Erreurs systeme, autorisations et deadlock (SQLCODE -900 a -999)

Erreurs specifiques IBM i (SQLCODE -7000 et plus)

Erreurs distribuees DRDA (SQLCODE -30000 et plus)

Astuce : Recuperer le SQLCODE dans un programme RPG

-- Dans un handler de procedure stockee :
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
        v_sqlcode  = DB2_RETURNED_SQLCODE,
        v_sqlstate = RETURNED_SQLSTATE,
        v_message  = MESSAGE_TEXT;
    -- Traiter ou logger l'erreur
END;

// En RPG ILE avec SQL embarque :
EXEC SQL SELECT empno INTO :wEmpno FROM employee WHERE empno = :wSearch;
IF SQLCOD = 100;
    // Pas de ligne trouvee
ELSEIF SQLCOD < 0;
    // Erreur — logger SQLCOD
ENDIF;

Schema et naming convention (Redbook 3.1)

• SQL naming (.) : defaut = schema du user (SET SCHEMA monschema;)
• System naming (/) : defaut = library list (*LIBL)
• Pour les procedures/triggers, utiliser SET OPTION DFTRDBCOL = MONSCHEMA
• SET PATH controle la resolution des UDF : SET PATH = MYUDFS, SYSTEM PATH

Verifications rapides

-- Schema courant
VALUES CURRENT SCHEMA;

-- Path courant (resolution UDF)
VALUES CURRENT PATH;

-- User courant
VALUES CURRENT USER;

-- Version IBM i
SELECT * FROM SYSIBMADM.ENV_SYS_INFO;

Prerequis IBM i

• IBM i 7.3 ou superieur (7.2 pour procedures/triggers/fonctions basiques)
• IBM i 7.4+ recommande pour JSON_TABLE, LISTAGG, IFS_WRITE, REST APIs
• ADDLIBLE LIB(SYSTOOLS) pour certaines fonctions utilitaires
• Verifier les PTF (Technology Refresh) pour les services QSYS2 les plus recents
• Pour debug : option DBGVIEW = *SOURCE dans SET OPTION

Checklist qualite

Avant de fournir du SQL, verifier :

• Tables qualifiees avec schema
• Colonnes nommees explicitement (pas de SELECT * en production)
• Alias sur les colonnes calculees
• JOIN prefere aux sous-requetes quand possible
• NULL gere correctement (IS NULL, pas = NULL)
• DECIMAL() sur les fonctions d'agregation numeriques
• Commentaires expliquant le POURQUOI
• FOR READ ONLY sur les consultations
• Index recommandes mentionnes si pertinent
• Pas de LIKE '%...' sauf si necessaire (expliquer l'impact)
• Proprietes UDF optimales (DETERMINISTIC, NOT FENCED, INLINE si possible)
• Trigger avec gestion d'erreur (HANDLER ou SIGNAL)
• Objectif d'optimisation adapte (*FIRSTIO vs *ALLIO)
• Pas de derivation dans les predicats (ou index derive cree)

Sources et references

• IBM Redbook SG24-8326 "SQL Procedures, Triggers, and Functions on IBM DB2 for i" (2016)
• Birgitta Hauser — IBM Champion, consultante independante, co-auteur Redbooks IBM
  • GitHub : github.com/BirgittaHauser (Write-to-IFS-with-SQL, Generate-XML-and-JSON)
  • Conferences COMMON, articles IT Jungle, IBM DeveloperWorks
• Christian Griere — Expert IBM France, specialiste performance DB2 for i
  • Articles LinkedIn : sous-indexation, sur-indexation, EVI, LPG, Visual Explain, plan cache
  • Presentation COMMON Romandie "Les bases de l'optimisation SQL"
• IBM Knowledge Center — DB2 for i SQL Reference
• IBM Indexing and Statistics Strategies whitepaper
• Scott Forstie — DB2 for i Business Architect, IBM i Services