Discussion :

[sly3333]

Bonjour

J'ai une table structuré comme indiqué ci dessous : (50 Millions d'enregistrement)

Matable(IdClient , IdProduit)

avec Idclient IDproduit comme clé primaire.

Je sais qu'un client peut avoir au maximum 6 enregistrements : 1 par produit existant. En effet nous n'avons que 6 produits différent. Un client peut avoir l'un de ses produits, aucun produit, plusieurs produits ou encore tous les produits

J'ai un programme qui parcoure un fichier d'environ 300000 enregistrements (1enregistrement contient un idClient) et qui pour chacun a besoin de connaitre tous les produits du client afin de mettre a jour des données dans d'autre tables

Pour faire cela j'ai deux solutions qui ne me vont qu'a moitié. Quelqu'un a t il une idée ?

Solution 1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Select * from Matable where idclient = :numclient_de_l_enregistrement_du_fichier

Problème :L'ouverture d'un curseur à chaque enregistrement ne me parait pas performant

Solution 2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Select * from matable A 
left outer join matable B on B.idclient = A.idclient 
left outer join matable C on C.idclient = A.idclient 
left outer join matable D on D.idclient = A.idclient 
left outer join matable E on E.idclient = A.idclient 
left outer join matable F on F.idclient = A.idclient 
Where 
A.idproduit = '1' and
B.idproduit = '2' and
C.idproduit = '3' and
D.idproduit = '4' and
E.idproduit = '5' and
F.idproduit = '6'

Probleme : l'utilisation de nombreuse jointure sur une table de 50 000 000 ne me parait pas adaptée

[Peut-êtreUneRéponse]

Problème :L'ouverture d'un curseur à chaque enregistrement ne me parait pas performant

L'utilisation d'un curseur est la seule solution à envisager et elle saura performante pour peu que que ton prédicat soit indexable.

NB : L'ouverture du curseur n'a pas lieu à chaque enregistrement, elle a lieu à chaque rupture d'IdClient. Par contre tu fetch pour chaque enregistrement.

.

[K2R400]

Quelle plate-forme ? Quelle version ?

Tu créés un EVI (Encoded Vector Index) sur IdClient , IdProduit pour obtenir les performances maximales, puis tu fais ta requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT distinct idclient, idproduit FROM Matable WHERE idclient = :numclient

[pdz74]

Bonjour,

Quels que soient les index que tu as sur ta table, les accès directs ne sont pas du tout préconisés dans un traitement batch. Si tu veux avoir les meilleurs temps de réponse possibles et profiter à fond du principe de prefetch de DB2 qui sait lire les lignes demandées avant même que le programme ne les demande, il faut appliquer la logique suivante.

Au lieu de lire ton fichier séquentiel et pour chaque ligne de ton fichier faire un accès direct sur ta table, tu transformes ton fichier séquentiel en table DB2 de travail. Soit une table standard, dans ce cas tu charges les lignes de ta table par un LOAD REPLACE dans un step juste avant ton programme (en entrée du LOAD, tu as bien sur ton fichier séquentiel). Soit, solution encore meilleure, tu te sers d'une table temporaire globale (ttg). Ces tables ne contiennent pas de données pérennes, les lignes d'une ttg sont supprimées automatiquement au premier commit (ou rollback). Elles ne peuvent donc servir qu'à l'intérieur d'une même unité de recovery, donc dans un et un seul programme par exemple. L'avantage, c'est que ces tables sont stockées dans la mémoire interne de DB2 (pas de tablespace) avec des temps de réponse extrêmement rapide. On ne peut pas faire de LOAD sur une ttg donc la logique est la suivante :
- En init de ton programme, tu fais une boucle d'insertions dans la ttg en lisant la totalité de ton fichier séquentiel (à noter : si 300.000 insertions dans une table standard, c'est 3 minutes par exemple, le même nombre dans une ttg, c'est 10 secondes).
- Ensuite le corps de ton programme devient une requête du style

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT ...
FROM TTG
JOIN TATABLE
ON TTG.IDCLIENT = TATABLE.IDCLIENT

- Que va faire DB2 : il va lire en premier la ttg et pour chaque ligne faire la jointure sur tatable avec l'index qui va bien. DB2 peut anticiper toutes les lectures puisqu'il n'attend pas la valeur d'une host-variable pour faire les accès, les temps de réponse sont sans commune mesure.

Pour info, DDL d'une TTG :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE GLOBAL TEMPORARY TABLE NOMTTG
(IDCLIENT CHAR(8))

C'est tout, pas de tablespace, pas d'index, ...

Si tu souhaites plus d'infos sur les ttgs, n'hésite pas, je ferai un copier/coller d'un support de cours que j'ai sur ces ttgs. A noter : tu peux aussi te servir de declared temporary table (équivalent des ttgs, mais la table ne sert que pour le programme concerné). Tout dépend des normes en vigueur : entre autres, les programmeurs ont ils le droit de gérer du DDL ou la gestion du DDL est elle dédiée à une équipe de DBA.

A ta disposition pour toute information complémentaire.

Bonne journée.

[Luc Orient]

Même question que K2R400 :

Quel DB2 ?

[sly3333]

Tout d'abord merci pour vos réponses

Luc Orient, K2R400 ==> DB2V8 sur Z/OS

pdz74 ==> Ton support de cours m'interresse.

Par contre je ne comprend pas la différence entre global temporary table, created temporary table et declared temporary table.

La table qui remplace mon fichier en entrée de mon programme ne sera utilisé que dans ce programme autant utiliser une declared temporary table et la remplir avec des INSERTs classiques?

Autre problème :

Pour chaque enregistrement en entrée ==> deux mises a jour dans des tables différentes.

Le comportement du programme doit être le suivant : si une erreur intervient sur la seconde mise a jour, je dois annuler la première

J'ai donc plusieurs solutions :

• Commiter a chaque fin de traitement d'un enregistrement en entrée ==> Solution pas bonne du tout surtout avec 300 000 enregistrements en entrée (= 300 000 commits)
  
• Poser un savepoint avant le traitement de chaque enregistrement en entrée et commiter tous les 500 enregistrements par exemple. Dans le cas où la seconde mise a jour est KO, je fais un rollback to savepoint.
  

J'opte pour la seconde solution. Cependant, je n'ai aucun retour d'expérience sur l'utilisation des savepoint. Quels sont les pieges a éviter ? Poser un savepoint avant chaque traitement d'un enregistrement ne va t il pas être trop couteux ?

Merci à tous pour vos réponses

[Luc Orient]

Quelques questions complémentaires :

Bonjour

J'ai une table structuré comme indiqué ci dessous : (50 Millions d'enregistrement)

Matable(IdClient , IdProduit)

avec Idclient IDproduit comme clé primaire.

Est-ce que l'index supportant la clé primaire est aussi l'index CLUSTER ?

J'ai un programme qui parcoure un fichier d'environ 300000 enregistrements (1enregistrement contient un idClient) et qui pour chacun a besoin de connaitre tous les produits du client afin de mettre a jour des données dans d'autre tables

Est-ce que ce fichier est trié sur l'IdClient ?

Pour chaque enregistrement en entrée ==> deux mises a jour dans des tables différentes.

Le comportement du programme doit être le suivant : si une erreur intervient sur la seconde mise a jour, je dois annuler la première

Quel genre d'erreur va détecter le programme ?

Une fois l'erreur détectée quel va être le comportement du programme ?

J'ai donc plusieurs solutions ...

Sur votre site, il n'y a pas de politique générale de traitement de cette nature de problèmes ( prise de point de synchro, gestion d'un contexte, gestion de la reprise dans le programme, etc ) ?

[sly3333]

L'index portant la clé primaire est cluster.
Le fichier n'est pas trié sur idClient ( les enregistrements doivent être traités dans l'ordre)

Pour ce qui est de l'erreur, je prend un exemple tout bête :
Dans l'enregistrement du fichier en entrée, se trouve une date qui n'est pas forcément bien formaté. Si je fais un update avec cette date sans la contrôler au préalable, j'obtient un -180. Dans ce cas, je veux annuler la première mise a jour et passer à l'enregistrement suivant.

Sur le site, il y a bien une politique de gestion des commits et de la reprise mais cette gestion n'est pas adaptée à tous les programmes et en particulier pas adaptée au mien.
Pour ce qui est des SAVEPOINT je n'est pas trouvé de programme en utilisant sur mon site .
Les avez vous déjà utilisés ?

Le ROLLBACK To SAVEPOINT annule t-il également les modifications réalisées par un sous programme ? (il n'y a pas de commit dans le sous programme)

[Peut-êtreUneRéponse]

L'index portant la clé primaire est cluster.
Le fichier n'est pas trié sur idClient ( les enregistrements doivent être traités dans l'ordre)

Sauf pb de cinématique, de synchronisation et d'appareillage de fichiers en entrée, mieux vaut attaquer ta table dans l'ordre cluster...

Pour ce qui est de l'erreur, je prend un exemple tout bête :
Dans l'enregistrement du fichier en entrée, se trouve une date qui n'est pas forcément bien formaté. Si je fais un update avec cette date sans la contrôler au préalable, j'obtient un -180. Dans ce cas, je veux annuler la première mise a jour et passer à l'enregistrement suivant.

Ton programme doit veiller à ce que l'update soit réalisable par des contrôles de suface et eventuellement d'existence dans le cas d'insert pour éviter des duplicates. Ton pgm pilote les updates, ce ne sont pas les updates qui pilotent ton pgm. Dans le cas que tu cites ton enregistrement doit être écarté avant les updates (ou bien mis en cohérence : date valide)

Sur le site, il y a bien une politique de gestion des commits et de la reprise mais cette gestion n'est pas adaptée à tous les programmes et en particulier pas adaptée au mien.

Mwoui... à voir.

Pour ce qui est des SAVEPOINT je n'est pas trouvé de programme en utilisant sur mon site .
Les avez vous déjà utilisés ?

C'est plutôt utilisé pour du transactionnel. Attention tout commit placé entre un savepoint et un rollback to savepoint annule le savepoint (tout comme le release savepoint).


Pour finir, une piste supplémentaire : si ton traitement se déroule sans concurrence d'accès et que la volumétrie d'update dépasse 10 à 15 % de la volumétrie totale de la table il serait peut être envisageable de travailler en LOAD REPLACE

@pdz74 la plupart des sites pour lesquels j'ai travaillé refuse l'utilisation des GTT en prod

.

[pdz74]

Bonjour,

A Peut-êtreUneRéponse : tu précises que la plupart des sites sur lesquels tu as travaillé interdisent les ttg. Je pense franchement que c'est par méconnaissance de leur utilisation. Ca marche super bien, c'est extrêmement rapide, nous n'avons jamais eu de problème quelconque. Nous nous servons de plusieurs centaines de ttg dans des centaines de programmes (batch comme tp) et ça marche nickel. Et le SI sur lequel je bosse, il y a des dizaines de tables de plusieurs millions voire dizaines de millions de lignes (maxi dans les 300 millions) que nous prenons en jointure avec des ttg sans souci. Franchement, pour cibler les lignes à lire, les ttgs, c'est top. Tu peux également t'en servir pour faire des tris en interne d'un programme : tu reçois des lignes d'une TS par exemple que tu souhaites restituer à l'utilisateur dans un ordre précis. Tu charges les lignes dans une ttg et tu relis order by ton critère : simple et rapide.

Pour Sly3333 et toute personne intéressée, ci-dessous un copier/coller de mon support de cours. Quant à la différence entre global temporary table ou declared, il n'y en a pas concernant leur utilisation. Une "global" est créée une bonne fois pour toute, une "declared" juste le temps du programme. Dans ma boite, le DDL est interdit à tout développeur, donc on ne se sert que de ttg. Mais c'est juste une norme, pas une obligation.


Ces tables (ttg dans la suite du document) sont utilisées pour stocker des données temporaires.

Il peut exister plusieurs instances d’une même ttg mais le contenu sera unique pour chaque thread. Les données insérées dans une ttg par un programme A ne seront jamais visibles par un programme B tournant en parallèle et accédant à la même ttg (et inversement).

De même, aucune contention ne peut exister sur une ttg.

Toutes les lignes d’une table temporaire sont supprimées

• après un COMMIT,
• après un ROLLBACK,
• à la fin du thread.

Par rapport à cette remarque, une exception : pour pouvoir se servir d'une ttg dans un curseur et faire des commits intermédiaires sans perdre les lignes de la ttg, il faut déclarer le curseur WITH HOLD.

Les tables temporaires globales peuvent servir pour :

• Servir de tables de travail pour ne cibler que des lignes précises à lire (ca le plus courant).
• Charger des tables de codification Armide ou Spitab dans une table DB2 pour profiter de la facilité du langage SQL et être sur d’avoir des données à jour.
• Réaliser des tris avant affichage de données à l’utilisateur en TP.
• Autres (fonction des besoins des différents développeurs)…

Exemple d’utilisation

Début programme

Tant que non fin

Lecture VSAM
INSERT table temporaire

Fin tant que
DECLARE cursor for SELECT table temporaire en jointure avec des autres tables
Tant que sqlcode = 0

FETCH cursor
.......................

Fin tant que

Fin programme -> Le contenu de la table est détruite sans intervention du programmeur


Limites : une table temporaire ne peut pas :

• avoir de contraintes référentielles ou sur colonnes,
• avoir d’index,
• avoir de valeurs par défaut autres que NULL,
• avoir de clé unique, primaire ou étrangère,
• être parent,
• être référencée dans des utilitaires,
• faire l’objet d’un ordre LOCK TABLE,
• faire l’objet d’un DELETE sélectif,
• être mise à jour par UPDATE,
• seuls les ordres INSERT, SELECT, DELETE de masse et DECLARE, OPEN, FETCH, CLOSE cursor sont acceptés.

Warning : en théorie, il est inutile de supprimer les lignes d'une ttg après utilisation, c'est fait au premier commit, cad à la fin du programme. Gaffe, dans le cas d'utilisation de ttgs dans un enchainement d'appel de sous-programmes, le RETURN ne générant pas de commit, les lignes d'une ttg insérées dans un ss-pro sont toujours présentes lorsqu'on retourne au programme appelant. Si on appelle ensuite un autre ss-pro qui se sert de la même ttg, cela peut poser problème... Nous avons donc pris l'habitude (norme obligatoire) de toujours faire un DELETE FROM TTG juste avant le RETURN, en environnement TP. Cela ne coûte rien et sert d'assurance...

[Peut-êtreUneRéponse]

tu précises que la plupart des sites sur lesquels tu as travaillé interdisent les ttg. Je pense franchement que c'est par méconnaissance de leur utilisation.

Je ne pense pas que l'on puisse dire que les DBA des différents sites sur lesquels j'ai travaillé méconnaissent leur sujet je pense plutôt qu'ils redoutent l'abus de cette pratique par des développeurs sans connaissance avancée de DB2 et je plussoie.

Ex : GTT avec index associé qui explose, le traitement ne plante pas (cela fonctionne sans index) mais la log indique un fail tous le long du traitement; utilisation en TP ...


.