Les bonnes pratiques de développements SQL sur DB2 Z/OS

Bonjour,
existerait-il une doc sur les bonnes pratiques à suivre dans le cadre du développements SQL sur des bases de données type DB2 Z/OS ?

Exemple ...
Ne pas trop utiliser les fonctions de convertions
Bien cibler les données
etc ....

Bonjour

je pense qu'il n'y a pas de doc spécifique.

Ma bible est "Application Programming and SQL Guide", surtout les chapitres "tuning your query" et "explain".

selon mes activités, je complete avec les redbooks (redbooks.ibm.com).

bonne lecture

Bonjour,

il me semble qu'en haut de cette page, caché entre plusieurs boutons, il y en a qui sont appellés tutoriel DB2 et tutoriel SQL.

N'as tu pas trouvé les informations que tu recherche dans ces tuto ?

Cordialement,

Bonsoir,

Le sujet est vaste, c'est le moins que l'on puisse dire. Qui dit bonne pratique du langage SQL, commence par dire bonne modélisation. En effet, un modèle de données non orienté relationnel, débouchera quasi à coup sur sur des problèmes de perf par exemple. Ex typique : un logiciel écrit à l'oirigine en vsam. Pour faire bien, on passe à DB2 en faisant du copier/coller des anciens fichiers vsam en table DB2. C'est une hérésie et pourtant de nombreux logiciles sont ainsi !

Ensuite, toute la partie système : bon paramétrage de la ZPARM, optimisation des différents espace mémoire (bufferpool, edmpool, ...).

Enfin les accès aux données : il existe des formations d'une semaine pour répondre à ta question. En quelques lignes dans un message, c'est donc mission impossible. Ceci dit, les docs DB2 cités sont pleines de bonnes idées. Restent à les trouver dans des docs de 1000 pages auxquelles on peut ajouter tout de qui concerne les utilitaires, sql references, ...

Très honnêtement, il est préférable de commencer par une bonne formation qui te donnera les grandes orientations.

Bonne utilisation de DB2.

Citation:

---

Envoyé par SuperWaza

... existerait-il une doc sur les bonnes pratiques à suivre dans le cadre du développements SQL sur des bases de données type DB2 Z/OS ?

---

Normalement, il devrait exister de la documentation sur les normes et standards en vigueur sur votre site, non ?

Bonsoir,

Citation:

---

Envoyé par pdz74

un logiciel écrit à l'oirigine en vsam. Pour faire bien, on passe à DB2 en faisant du copier/coller des anciens fichiers vsam en table DB2. C'est une hérésie

---

Et si au niveau logique, les structures des données hébergées par les fichiers VSAM (ou BDAM, etc.) respectent l’intégrité d’entité (clés NOT NULL) et la cinquième forme normale ? Il restera à nommer et typer les attributs des tables, définir les clés candidates, établir les contraintes d’intégrité référentielle. Dans ces conditions, quels défauts présenteraient ces tables du point de vue structurel vis-à-vis de la théorie relationnelle ? Et si par-dessus le marché, le MCD (ou le diagramme de classes) obtenu par rétroconception de l’ensemble des tables obtenu est irréprochable ?

Citation:

---

Envoyé par Luc Orient

Normalement, il devrait exister de la documentation sur les normes et standards en vigueur sur votre site, non ?

---

Complètement de l'avis de Luc Orient, contact le support dev ou bien l'équipe DBA qui seront sans doute ravis que tu te préoccupes de cette problématique.

.

Contacter l'équipe DBA, déjà fait mais pas de réponses concrètes ... voila pq ? je me tourne vers ce site

Bon je me lance, en vrac (merci de me reprendre si grosses bêtises de ma part) :

• Pas de SELECT *, nommer toutes les colonnes souhaitées et seulement celles dont vous avez besoin
• Penser à la gestion des COMMIT en batch
• Tester systématiquement le SQLCODE après tout ordre SQL. En cas de code SQL négatif, arrêter le traitement après avoir effectué un display de la SQLCA via DSNTIAR (ou GET DIAGNOSTICS)
• Dans le cas d'un index multi colonnes, penser à indiquer la 1ere colonne de l'index dans la WHERE condition même si la valeur est unique car l'index ne serait pas utilisé (Oracle, pas sûr pour DB2)
• Les requêtes doivent avoir un prédicat indexable et filtrant
• Préférer LIKE à SUBSTR
• Préférer NOT EXISTS à NOT IN
• Préférer BETWEEN à >= and <=
• INSERT par tableau multi-rows (à partire de v8) : éviter en TP, en batch se limiter à une centaine d'insertion
• Préférer les CTE common table expression (WITH XXX AS (SELECT...)) aux INNER JOIN (maintenance amélioré car meilleure lisibilité)
• En cas de jointure de 2 tables indexées, mettre la moins volumineuse en dernier (La 2eme table, directrice est parcourue séquentiellement) (Oracle)

Très interessant ... tu trouves cela ou ? Es-ce par expérience ?

Par expérience, par conseils successif de DBA, par lecture de docs internes sur différentes missions...

.

Quelques autres points extraits d'un cours Perf que je donne parfois. Plus une remarque par rapport à la préco sur les jointures comme quoi il faut les mettre dans un ordre précis. Non, l'ordre des tables dans une jointure n'a aucune importance, c'est DB2 qui décide de lui-même quelle table prendre en premier en fonction des statistiques. Heureusement, sinon cela signifierait que toute évolution de volumétrie ne serait pas prise en compte lors d'un rebind.

• N’accéder qu’aux données vraiment nécessaires aux traitements.
• Pour un enchaînement de programmes (transaction ou batch), une table est lue une seule fois, même si on a besoin de données différentes à des instants différents.
• Eviter les accès directs : utilisation de tables de travail en Batch -> chargement des identifiants dans une table de travail DB2, puis accès aux données par jointure entre la table de travail et les autres tables.
• Utilisation des jointures et des unions plutôt que des SELECT indépendants
• Eviter les mises à jour de masse dans des programmes -> utiliser l’utilitaire LOAD (avec option LOG NO).
• Lors des accès aux données, il faut toujours essayer d'utiliser des colonnes de sélection permettant le passage par les index pour accéder aux données.
• Il est très intéressant de sélecter des données à partir de l'index CLUSTER d'une table, en particulier pour les accès de masse dans un programme Batch.
• Il faut limiter le nombre d'index par table si cette table subit de nombreuses mises à jour. En effet, une maj (INSERT, UPDATE, DELETE) entraîne la modification des index de cette table, d'où des I/O supplémentaires et des temps de réponse plus importants en mise à jour.
• Il ne faut pas comparer des données de type et/ou longueur différentes
• Il faut mettre le plus de prédicats possibles dans un ordre SQL de manière à avoir un facteur de filtrage le plus élevé possible et donc de limiter les échanges entre DB2 et le programme.
• Une Host Variable doit toujours être du même format que la colonne DB2 référencée dans le prédicat.
• L'ordre dans lequel sont écrits les différents prédicats dans une clause WHERE n'a strictement aucune importance.
• Lorsqu'on se sert des fonctions de type "SUBSTR", "DATE", DIGITS, ..., c'est à dire de fonction permettant d'extraire une partie d'une colonne ou de la transformer, s'il existe un index sur cette colonne, DB2 ne passera pas par cet index, si ce n’est en scan complet de l’index.
• Utiliser BETWEEN au lieu de ">=" AND "<=".
• Ne pas utiliser l'ordre "LIKE" avec "%" ou "_" en début de host variable, cela évite des scans trop pénalisants (même si ce scan concerne un index).
• Eviter les opérations arithmétiques dans les prédicats.
• Eviter les comparaisons entre colonnes d'une même table, car, même s'il existe des index sur les colonnes concernées, DB2 ne passe pas par ces index.
• Lorsqu'il est nécessaire d'accéder à 2 tables dans un même programme, une jointure est préférable à deux ordres DB2 distincts.
• Une jointure externe est préférable à une UNION, dans la plupart des cas.
• L’option ‘WITH HOLD’ permet de ne pas clôturer les curseurs lors d'un COMMIT. Cette option est intéressante dans les programmes de mises à jour de masse. Cela permet de faire des COMMIT intermédiaires sans casser la logique du programme.
• FETCH FIRST n ROW ONLY -> ce paramètre du FETCH ou du SELECT, permet de préciser le nombre de lignes à lire.
• Lorsque l’on exécute ‘UNION’, ‘GROUP BY’ ou ‘DISTINCT’, DB2 élimine les doublons. Pour cela, DB2 trie les lignes résultats dans l’ordre des colonnes sélectionnées. Il est donc inutile de mettre un ‘ORDER BY’ dans les ‘UNION’, ‘DISTINCT’ et ‘GROUP BY’. Il suffit de sélectionner les colonnes dans l’ordre du tri voulu.
• Eviter de lancer un programme qui fait des mises à jour et qui dure trop longtemps sans faire de COMMIT.
• Eviter de faire des mises à jour dans un programme qui fait également des recherches -> travailler en 2 temps.

Citation:

---

Envoyé par pdz74

[*]FETCH FIRST n ROW ONLY -> ce paramètre du FETCH ou du SELECT, permet de préciser le nombre de lignes à lire.[*]Lorsque l’on exécute ‘UNION’, ‘GROUP BY’ ou ‘DISTINCT’, DB2 élimine les doublons. Pour cela, DB2 trie les lignes résultats dans l’ordre des colonnes sélectionnées. Il est donc inutile de mettre un ‘ORDER BY’ dans les ‘UNION’, ‘DISTINCT’ et ‘GROUP BY’. Il suffit de sélectionner les colonnes dans l’ordre du tri voulu.[/LIST]

---

En complément,

Concernant le premier point, y a également le OPTIMIZE FOR n ROWS qui peut etre intéressant à utiliser

Pour le second point, j'ajouterai que lorsque l'on souhaite simplement faire un union, sans tri et sans dédoublonnage, un UNION ALL est plus performant et plus indiqué

Citation:

---

Envoyé par pdz74

Non, l'ordre des tables dans une jointure n'a aucune importance, c'est DB2 qui décide de lui-même quelle table prendre en premier en fonction des statistiques. Heureusement, sinon cela signifierait que toute évolution de volumétrie ne serait pas prise en compte lors d'un rebind.

---

Au temps pour moi... Oracle semble-t-il, de même pour ma remarque sur

Citation:

---

Dans le cas d'un index multi colonnes, penser à indiquer la 1ere colonne de l'index dans la WHERE condition même si la valeur est unique car l'index ne serait pas utilisé

---

.

Je rectifie dans le billet origine.

.

Citation:

---

Utiliser BETWEEN au lieu de ">=" AND "<=".

---

Si je peux affiner, je dirais plutôt :

Lors d’un test sur des colonnes INDEX :

- l’ordre BETWEEN doit être utilisé si l’ordre est du type
Colonne BETWEEN Valeur1 AND Valeur2.

- Les comparateurs >, <, >=, <= doivent être utilisé si l’ordre est du
type
Valeur BETWEEN Colonne1 AND Colonne2.
car ce prédicat n'est ni indexable, ni sargable.

Citation:

---

Envoyé par Peut-êtreUneRéponse

...
Dans le cas d'un index multi colonnes, penser à indiquer la 1ere colonne de l'index dans la WHERE condition même si la valeur est unique car l'index ne serait pas utilisé (Oracle, pas sûr pour DB2)

---

Si la première colonne d'un index n'a qu'une valeur (FULLKEYCARDF = 1), cette colonne n'est pas du tout discriminante et on peut vraiment de poser la question de ce choix.

Voir aussi ce fil :
DB2 z/OS Index et performances

Par contre, dans le cas où l'on souhaite qu'une requête soit indexé via un index multi-colonnes, je confirme la nécessité de valoriser dans le prédicat de celle-ci le maximum de colonnes, et que la valorisation doit toujours se faire dans l'ordre des colonnes de l'index.
En d'autres mots, si la valorisation est partielle, les colonnes manquantes doivent être à la fin plutôt qu'au début de l'index.
Cette règle générale s'applique bien évidemment en particulier pour la première colonne de l'index.

Citation:

---

Envoyé par Luc Orient

Si la première colonne d'un index n'a qu'une valeur (FULLKEYCARDF = 1), cette colonne n'est pas du tout discriminante et on peut vraiment de poser la question de ce choix.

---

--> Un progiciel par exemple, malheureusement :(

.