Doc Technique - DB2 for z/OS SQL / Explain Analyzer V2

DB2 for z/OS SQL / Explain Analyzer V2 – Analyseur SQL, access path, packages et RUNSTATS

Objectif du guide

Installer, tester et exploiter db2_zos_sql_explain_analyzer_v2.py, un utilitaire autonome qui analyse des exports DB2 for z/OS : PLAN_TABLE-like, SQL, predicates, index catalog, table statistics, packages et baseline.

Télécharger l’utilitaire DB2 for z/OS SQL / Explain Analyzer V2

Le bouton principal pointe explicitement vers /static/toolbox/db2_zos_sql_explain_analyzer_v2.py. Dépose ce script dans static/toolbox/ pour l’exposer depuis IDEO-Lab.

Download db2_zos_sql_explain_analyzer_v2.py Django static tag

Cet outil est conçu pour les DBA DB2, les ingénieurs performance, les équipes batch et les projets de modernisation. Il transforme plusieurs fichiers d’export en diagnostic actionnable : tablespace scans, index faibles, predicates stage 2, RUNSTATS obsolètes, packages risqués, regressions access path et candidats d’index.

Explain

Analyse PLAN_TABLE-like : access type, MATCHCOLS, method, sort, prefetch, getpages, cost, rows.

Catalog

Croisement avec predicates, indexes, table statistics, clustering ratio, RUNSTATS et packages.

Regression

Comparaison baseline/current pour repérer les changements d’access path, coût, rows et getpages.

Ce que le moteur analyse

Domaine	Signaux analysés	Diagnostic produit
Access path	`ACCESSTYPE`, `MATCHCOLS`, `METHOD`, `SORTN`, `PREFETCH`, `RID`.	Tablespace scan, index usage faible, sort coûteux, RID pool pressure, prefetch suspect.
SQL statements	`SELECT *`, fonctions sur colonnes, `LIKE '%...'`, `OR`, DML sans `WHERE`.	SQL non sargable, accès trop large, risque de scan ou risque production.
Predicates	Stage 1/2, indexable, columns, operators, filter factor.	Stage 2 coûteux, predicate non indexable, colonne candidate à index.
Indexes	Colonnes, cardinalité, clustering ratio, nlevels, duplication, unique/non unique.	Index faible, index non aligné predicates, clustering à corriger.
RUNSTATS	Date stats, cardinalité, NPAGES, rows, stats obsolètes.	Risque access path instable ou mauvais choix d’optimiseur.
Packages	Isolation, degree, validate, reopt, release, bind date, collection.	Options de bind risquées : RR/RS, DEGREE(ANY), VALIDATE(RUN), rebind nécessaire.

Positionnement : la V2 n’est pas un client DB2 live. Elle travaille sur exports CSV/SQL/JSONL afin de rester portable, testable hors mainframe et intégrable dans un portail d’audit ou un pipeline d’analyse de performance.

Téléchargement des fichiers

Cette page fournit les liens explicites vers le script Python, les samples DB2, la policy et le pack complet. Pour IDEO-Lab, les fichiers doivent être déposés sous /static/toolbox/.

Utilitaire principal

Script Python autonome, sans dépendance externe, à lancer sur des exports DB2 Explain / catalog / SQL.

Download db2_zos_sql_explain_analyzer_v2.py Static tag

Explain current

Export PLAN_TABLE-like courant contenant access path, cost, getpages et méthode.

Download current

Explain baseline

Export de référence pour détecter les regressions d’access path ou de coût.

Download baseline

SQL statements

Fichier SQL brut pour détecter SELECT *, DML risqué, fonctions, LIKE et OR predicates.

Download SQL

Predicates

Export DSN_PREDICAT_TABLE-like pour stage 2 et indexability.

Download predicates

Indexes / stats / packages

Exports de catalog, RUNSTATS et bind options pour diagnostic DBA.

Indexes

Stats

Packages

Pack ZIP

Script, samples, policy, outputs et rapport de démonstration.

Download ZIP

Arborescence static recommandée

static/
  toolbox/
    db2_zos_sql_explain_analyzer_v2.py
    db2_zos_sql_explain_analyzer_v2_pack.zip
    sample_db2_v2_explain_current.csv
    sample_db2_v2_explain_baseline.csv
    sample_db2_v2_sql.sql
    sample_db2_v2_predicates.csv
    sample_db2_v2_indexes.csv
    sample_db2_v2_table_stats.csv
    sample_db2_v2_packages.csv
    sample_db2_v2_policy.json
    db2_zos_sql_explain_analyzer_v2_guide.html

Contrôle visuel : le bouton Download db2_zos_sql_explain_analyzer_v2.py est présent en haut de la page, dans Téléchargement, Installation et Fichiers input.

Installation de DB2 for z/OS SQL / Explain Analyzer V2

Le script est autonome. Il suffit d’une version Python moderne et d’un ou plusieurs fichiers d’export DB2 : Explain, SQL, predicates, indexes, stats, packages et baseline.

1. Arborescence locale recommandée

db2_explain_lab/
  bin/
    db2_zos_sql_explain_analyzer_v2.py
  input/
    sample_db2_v2_explain_current.csv
    sample_db2_v2_explain_baseline.csv
    sample_db2_v2_sql.sql
    sample_db2_v2_predicates.csv
    sample_db2_v2_indexes.csv
    sample_db2_v2_table_stats.csv
    sample_db2_v2_packages.csv
    sample_db2_v2_policy.json
  output/
    reports/
    csv/
    json/

Lien direct du script à installer

Ce bouton pointe explicitement vers /static/toolbox/db2_zos_sql_explain_analyzer_v2.py.

Download db2_zos_sql_explain_analyzer_v2.py Django static tag

2. Installation Linux / macOS

mkdir -p db2_explain_lab/bin db2_explain_lab/input db2_explain_lab/output/reports db2_explain_lab/output/csv db2_explain_lab/output/json
cp db2_zos_sql_explain_analyzer_v2.py db2_explain_lab/bin/
cp sample_db2_v2_* db2_explain_lab/input/
cd db2_explain_lab
python3 bin/db2_zos_sql_explain_analyzer_v2.py --demo

3. Installation Windows PowerShell

New-Item -ItemType Directory -Force db2_explain_lab\bin, db2_explain_lab\input, db2_explain_lab\output\reports, db2_explain_lab\output\csv, db2_explain_lab\output\json
Copy-Item .\db2_zos_sql_explain_analyzer_v2.py .\db2_explain_lab\bin\
Copy-Item .\sample_db2_v2_* .\db2_explain_lab\input\
Set-Location .\db2_explain_lab
python .\bin\db2_zos_sql_explain_analyzer_v2.py --demo

4. Test de validation

python bin/db2_zos_sql_explain_analyzer_v2.py input/sample_db2_v2_explain_current.csv \
  --sql input/sample_db2_v2_sql.sql \
  --baseline input/sample_db2_v2_explain_baseline.csv \
  --predicates input/sample_db2_v2_predicates.csv \
  --indexes input/sample_db2_v2_indexes.csv \
  --table-stats input/sample_db2_v2_table_stats.csv \
  --packages input/sample_db2_v2_packages.csv \
  --policy input/sample_db2_v2_policy.json \
  --profile production \
  --html output/reports/sample_db2_v2_report.html \
  --json output/json/sample_db2_v2_analysis.json \
  --csv-findings output/csv/sample_db2_v2_findings.csv

Résultat attendu : le sample doit produire un statut BLOCKED, un score de risque élevé, des findings DB2, des candidats d’index, et un rapport HTML consultable.

Fichiers input attendus

La V2 accepte plusieurs sources complémentaires. L’analyse devient vraiment puissante quand on croise Explain, SQL, predicates, catalog indexes, RUNSTATS et packages.

Utilitaire nécessaire pour analyser les inputs

Télécharger d’abord le script Python, puis lancer l’analyse sur les exports DB2.

Download db2_zos_sql_explain_analyzer_v2.py

1. Sources acceptées

Source	Contenu typique	Usage
`Explain current`	PLAN_TABLE-like CSV/JSON/JSONL, access type, cost, rows, getpages.	Base principale du diagnostic access path.
`Explain baseline`	Ancien plan ou plan attendu.	Détection des regressions avant/après rebind ou livraison.
`SQL file`	Statements SQL bruts.	Détection syntaxique : SELECT *, DML sans WHERE, fonctions, LIKE, OR.
`Predicates export`	Stage, indexability, predicate text, columns, filter factor.	Repérer stage 2 et candidats d’index.
`Index catalog`	Index name, columns, clustering, cardinality, nlevels.	Comprendre pourquoi l’optimiseur choisit ou évite un index.
`Table stats`	Rows, pages, RUNSTATS date, cardinalité.	Détecter RUNSTATS obsolètes ou stats insuffisantes.
`Packages`	Collection, package, isolation, degree, validate, reopt, bind date.	Détecter bind options risquées ou packages à rebind.

2. Exemple minimal d’Explain CSV

queryno,stmtno,applname,collid,progname,query_cost,access_type,matchcols,method,sortn,prefetch,getpages,table_schema,table_name,index_name
1001,1,PAYROLL,COLLPAY,PAYRPT01,12500.0,R,0,0,Y,S,920000,PRODDB,PAYMENT,
1002,1,CUSTAPI,COLLAPI,CUSTAPI,820.0,I,1,0,N,L,21000,PRODDB,CUSTOMER,IX_CUSTOMER_01

3. Samples disponibles

Explain current

Plan courant utilisé pour le scoring principal.

Download

Baseline

Plan de référence pour comparer les changements de coût et access path.

Download

Predicates

Export stage/indexability pour diagnostic avancé.

Download

Indexes

Catalogue index et clustering pour recommandations.

Download

Table stats

RUNSTATS, rows et pages pour évaluer la fraîcheur des stats.

Download

Packages

Bind options pour détecter RR/RS, DEGREE, VALIDATE et REOPT.

Download

Règle de sécurité : les exports Explain/catalog peuvent révéler schémas, tables, packages, collections, index et conventions applicatives. Utiliser --redact avant partage externe.

Utilisation quotidienne

1. Mode démo intégré

python db2_zos_sql_explain_analyzer_v2.py --demo

2. Analyse simple d’un fichier Explain

python db2_zos_sql_explain_analyzer_v2.py input/sample_db2_v2_explain_current.csv

3. Analyse complète avec tous les exports

python db2_zos_sql_explain_analyzer_v2.py input/sample_db2_v2_explain_current.csv \
  --sql input/sample_db2_v2_sql.sql \
  --baseline input/sample_db2_v2_explain_baseline.csv \
  --predicates input/sample_db2_v2_predicates.csv \
  --indexes input/sample_db2_v2_indexes.csv \
  --table-stats input/sample_db2_v2_table_stats.csv \
  --packages input/sample_db2_v2_packages.csv \
  --policy input/sample_db2_v2_policy.json \
  --profile production \
  --json output/json/db2_analysis.json \
  --html output/reports/db2_report.html \
  --csv-findings output/csv/db2_findings.csv \
  --csv-plan output/csv/db2_plan_rows.csv \
  --csv-statements output/csv/db2_statements.csv \
  --csv-predicates output/csv/db2_predicates.csv \
  --csv-index-candidates output/csv/db2_index_candidates.csv \
  --csv-compare output/csv/db2_compare.csv \
  --csv-categories output/csv/db2_categories.csv \
  --csv-indexes output/csv/db2_indexes.csv \
  --csv-stats output/csv/db2_stats.csv \
  --csv-packages output/csv/db2_packages.csv

4. Mode anonymisé

python db2_zos_sql_explain_analyzer_v2.py input/production_explain.csv \
  --sql input/production_sql.sql \
  --redact \
  --html output/reports/production_db2_redacted.html \
  --json output/json/production_db2_redacted.json

5. Mode batch strict

python db2_zos_sql_explain_analyzer_v2.py input/nightly_explain.csv \
  --profile performance \
  --html output/reports/nightly_db2_report.html \
  --csv-findings output/csv/nightly_db2_findings.csv \
  --fail-on ERROR

Lecture recommandée : commencer par le HTML pour le diagnostic humain, puis utiliser les CSV pour Excel, SQL, BI, historique, comparaison de releases et discussion avec DBA / performance engineer.

Paramètres de ligne de commande

1. Paramètres d’entrée et de sortie

Paramètre	Description	Exemple
`input_file`	Fichier Explain principal.	`input/explain.csv`
`--explain`	Alias explicite du fichier Explain courant.	`--explain input/explain.csv`
`--sql`	Fichier de statements SQL bruts.	`--sql input/sql.sql`
`--predicates`	Export predicates stage/indexability.	`--predicates input/predicates.csv`
`--indexes`	Export catalogue index.	`--indexes input/indexes.csv`
`--table-stats`	Export table statistics / RUNSTATS.	`--table-stats input/table_stats.csv`
`--packages`	Export package / bind options.	`--packages input/packages.csv`
`--baseline`	Fichier Explain de référence.	`--baseline input/baseline.csv`
`--policy`	Policy JSON pour seuils et objets sensibles.	`--policy input/policy.json`
`--json`	Export JSON complet.	`--json output/analysis.json`
`--html`	Rapport HTML.	`--html output/report.html`
`--csv-findings`	Anomalies détectées.	`--csv-findings output/findings.csv`
`--csv-index-candidates`	Candidats d’index.	`--csv-index-candidates output/index_candidates.csv`
`--csv-compare`	Comparaison baseline/current.	`--csv-compare output/compare.csv`

2. Paramètres d’analyse et de gouvernance

Paramètre	Effet	Usage recommandé
`--profile`	Profil : `production`, `performance`, `db2`, `batch`, `api`, `packages`, `capacity`, `training`, `strict`.	Ajuster la lecture métier et la priorité des catégories.
`--write-samples`	Génère les fichiers samples dans un dossier cible.	Créer un environnement de test complet.
`--demo`	Lance le moteur contre des samples générés.	Validation rapide après installation.
`--redact`	Masque les noms sensibles dans les sorties.	Partage externe ou support.
`--fail-on`	Retourne un code erreur à partir d’une sévérité donnée.	`--fail-on ERROR` en contrôle batch, `--fail-on CRITICAL` en audit moins strict.

3. Exemple profil packages

python db2_zos_sql_explain_analyzer_v2.py input/package_explain.csv \
  --packages input/packages.csv \
  --profile packages \
  --html output/reports/packages_report.html \
  --csv-findings output/csv/packages_findings.csv

Exemples d’utilisation

Scénario 1 – Audit d’un plan avant mise en production

Cas DevOps/DBA : contrôler un nouveau plan d’accès avant livraison applicative.

python db2_zos_sql_explain_analyzer_v2.py input/release_explain.csv \
  --sql input/release_sql.sql \
  --profile production \
  --html output/reports/release_db2_report.html \
  --csv-findings output/csv/release_db2_findings.csv \
  --fail-on ERROR

Scénario 2 – Régression access path après rebind

Cas critique : comparer un Explain avant/après rebind ou livraison pour détecter une dégradation.

python db2_zos_sql_explain_analyzer_v2.py input/current_explain.csv \
  --baseline input/baseline_explain.csv \
  --profile performance \
  --html output/reports/access_path_regression.html \
  --csv-compare output/csv/access_path_compare.csv

Scénario 3 – Analyse RUNSTATS et catalog index

Cas DBA : vérifier si un mauvais access path vient de stats obsolètes ou d’index inadaptés.

python db2_zos_sql_explain_analyzer_v2.py input/explain.csv \
  --indexes input/indexes.csv \
  --table-stats input/table_stats.csv \
  --predicates input/predicates.csv \
  --profile db2 \
  --html output/reports/db2_catalog_diagnostic.html \
  --csv-index-candidates output/csv/db2_index_candidates.csv

Scénario 4 – Analyse des packages et options de bind

Cas production : repérer les options d’isolation, degree, validate ou reopt qui exposent à des comportements coûteux.

python db2_zos_sql_explain_analyzer_v2.py input/explain.csv \
  --packages input/packages.csv \
  --profile packages \
  --html output/reports/package_audit.html \
  --csv-packages output/csv/packages_out.csv

Scénario 5 – Contrôle nocturne automatisé

0 5 * * * /opt/db2-explain/bin/python /opt/db2-explain/db2_zos_sql_explain_analyzer_v2.py \
  /data/db2/nightly_explain.csv \
  --sql /data/db2/nightly_sql.sql \
  --baseline /data/db2/baseline_explain.csv \
  --indexes /data/db2/indexes.csv \
  --table-stats /data/db2/table_stats.csv \
  --packages /data/db2/packages.csv \
  --profile production \
  --html /var/www/reports/db2/nightly_explain_report.html \
  --csv-findings /var/log/db2/nightly_findings.csv \
  --json /var/log/db2/nightly_analysis.json \
  --fail-on ERROR >> /var/log/db2/db2_explain_analyzer.log 2>&1

Règles DB2, scoring et diagnostic

La V2 applique une logique de scoring par catégories. Le but n’est pas de remplacer un DBA DB2, mais de trier vite les risques et de faire émerger les points à vérifier en priorité.

1. Catégories de risques

Catégorie	Signaux typiques	Lecture DBA
Access path	Tablespace scan, index scan faible, MATCHCOLS=0, nested loop coûteux.	Le plan choisi risque de consommer trop d’I/O ou de CPU.
Predicates	Stage 2, non-indexable, functions on indexed columns, leading wildcard.	Les predicates ne filtrent pas assez tôt ou ne peuvent pas utiliser l’index.
Sort / workfile	Sort obligatoire, sort gros volume, manque d’index order compatible.	Risque de workfile pressure et temps elapsed élevé.
RUNSTATS	Stats absentes, obsolètes, cardinalité incohérente.	L’optimiseur peut prendre une mauvaise décision.
Packages	RR/RS, DEGREE(ANY), VALIDATE(RUN), bind ancien.	Risque de lock, parallélisme non désiré, ou erreur runtime.
Regression	Cost, rows, getpages, elapsed ou access path dégradés vs baseline.	Plan potentiellement dangereux avant production.

2. Exemple de candidats d’index

table_schema,table_name,columns,reason,confidence
PRODDB,PAYMENT,CUSTOMER_ID,PAY_DATE,Stage 2 predicate and high getpages,HIGH
PRODDB,CUSTOMER,STATUS,LAST_UPDATE,Frequent predicate and low matchcols,MEDIUM

3. Bon réflexe d’analyse

Ne jamais conclure sur COST seul : croiser avec GETPAGES, rows, predicates et access type.
Vérifier d’abord les tablespace scans sur tables volumineuses.
Sur index existant mais MATCHCOLS=0, vérifier l’ordre des colonnes et les predicates réellement indexables.
Quand un plan change, comparer baseline/current avant de valider le rebind.
Une recommandation d’index doit toujours être validée avec volumétrie, fréquence SQL, coût DML et politique DBA.

Attention : un candidat d’index n’est pas un ordre de création. C’est une hypothèse à vérifier avec EXPLAIN, cardinalité, fréquence d’exécution, coût update/delete/insert et normes DBA.

Exports et exploitation des résultats

Rapport HTML

Vue lisible : score, catégories, findings, access path, candidates, baseline compare et recommandations.

Analyse JSON

Format complet pour API, ingestion SQL, archivage ou comparaison automatique.

Findings CSV

Une ligne par anomalie DB2, avec sévérité, catégorie et explication.

Plan rows CSV

Plan rows normalisés pour tri Excel, SQL ou dashboard.

Index candidates CSV

Hypothèses d’index avec raisons et niveau de confiance.

Compare CSV

Diff baseline/current pour détecter régressions de plan.

Exemple de synthèse console

DB2 for z/OS SQL / Explain Analyzer V2
Status             : BLOCKED
Highest severity   : CRITICAL
Risk score         : 100/100
Explain rows       : 12
SQL statements     : 7
Predicates         : 12
Indexes            : 9
Table stats        : 10
Packages           : 5
Findings           : 174
Index candidates   : 5
Baseline compare   : 23

Idée de table SQL pour historisation

CREATE TABLE db2_explain_analyzer_run (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    source_name VARCHAR(255),
    profile VARCHAR(32),
    status VARCHAR(32),
    highest_severity VARCHAR(16),
    risk_score INTEGER,
    explain_row_count INTEGER,
    statement_count INTEGER,
    finding_count INTEGER,
    index_candidate_count INTEGER,
    baseline_regression_count INTEGER,
    report_path VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Vision produit : la V2 peut devenir un véritable module de Release Gate DB2 : analyse SQL, Explain, packages, RUNSTATS, régression access path, scoring et recommandation DBA avant mise en production.

Dépannage et bonnes pratiques

1. Le script ne détecte aucun plan

Cause possible	Correction
Le CSV n’a pas d’en-têtes reconnus.	Vérifier les noms de colonnes ou normaliser l’export en PLAN_TABLE-like.
Le fichier Explain est vide ou filtré trop fortement.	Exporter tous les rows utiles pour le queryno/stmtno analysé.
Les types numériques sont exportés avec un format incompatible.	Utiliser point décimal et colonnes non formatées.
Le fichier SQL ne correspond pas aux queryno Explain.	Vérifier le mapping SQL / Explain / package / statement.

2. Trop de findings remontées

En environnement réel, beaucoup de signaux peuvent être légitimes. Commencer avec un profil training ou performance, puis activer strict seulement pour les gates critiques.

python db2_zos_sql_explain_analyzer_v2.py input/explain.csv \
  --profile training \
  --html output/reports/db2_training_report.html \
  --fail-on CRITICAL

3. Check-list avant partage externe

Utiliser --redact pour masquer schémas, tables, packages et index sensibles.
Vérifier manuellement les statements SQL contenant des données métier ou commentaires internes.
Conserver une copie brute interne pour valider les mappings queryno/stmtno.
Ne pas créer un index uniquement parce que l’outil le suggère : tester avec EXPLAIN et analyser l’impact DML.
Comparer baseline/current avant tout rebind en production.

Point critique : les exports DB2 peuvent révéler la structure métier du SI. Ils doivent être traités comme des documents sensibles, surtout avec SQL, catalog indexes et packages.