Discussion :

[michael08]

Bonjour,

Dans le cadre de l'optimisation de nos traitements, je m'occupe d'un accesseur cobol à une table DB2 sous ZOS.
La requète sql est assez basique :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
EXEC SQL              
DECLARE CURSEUR 
CURSOR FOR            
SELECT   REFERENCE,      
             NUMERO, 
             ...
FROM  TABLE_REF                                
WHERE   PART    =       :W-IN-PART        
AND     NUMERO    =       :W-IN-NUMERO        
AND     SIRET    BETWEEN :W-IN-SIRET-INF AND
                        :W-IN-SIRET-SUP
...
             FOR FETCH ONLY       
OPTIMIZE FOR 20 ROWS 
END-EXEC.

Le numéro avec lequel on accède au données de la base peut retourner plus de 2000 références.
L'utilisateur renseigne donc un type en entrée pour cibler les références voulues.
Le type pouvant prendre plusieurs valeurs, le programme en entrée reçoit ainsi une table avec toutes les valeurs désirées pour le numéro.
Le nombre d'indices valorisés de la table est variable:
Ex: TYPE = 148 (3 valeurs -> 1, 4 et 8)
TYPE = 1235689(7 valeurs -> 1,2,3,5,6,8 et 9)

Ne pouvant pas créer une requète dynamique, la selection de réferences selon les types voulues est effectué en cobol.
Le problème est que pour certains numéros avec énormément de références et trés peu correspondant aux types en entrée, le nombre d'ordre db2 devient vite trés élevé!(on fetch jusqu'à obtenir les bons candidats).

J'aimerai donc ne plus gérer le TYPE au niveau Cobol mais en SQL pour diminuer le nombre de fetchs.

Savez vous s'il y a l'équivalent en db2 de la fonction contains?
Ainsi je placerai le prédicat suivant dans ma requète

Code :

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

WHERE CONTAINS(:W-TYPE, TYPE)

Merci d'avance pour votre réponse et n'hésitez pas si vous voyez d'autres solutions.

[Luc Orient]

...
J'aimerai donc ne plus gérer le TYPE au niveau Cobol mais en SQL pour diminuer le nombre de fetchs ...

Certes, c'est une intention louable car c'est (presque) toujours mieux de faire bosser DB2 à la place du programme COBOL, mais à mon sens, le vrai gain en performances va dépendre de la présence de la colonne TYPE dans un index ...

Quel est le chemin d'accès choisi par DB2 sur la requête actuelle ?

[michael08]

Certes, c'est une intention louable car c'est (presque) toujours mieux de faire bosser DB2 à la place du programme COBOL, mais à mon sens, le vrai gain en performances va dépendre de la présence de la colonne TYPE dans un index ...

Quel est le chemin d'accès choisi par DB2 sur la requête actuelle ?

Le chemin actuel choisi est un indexscan par l'index contenant le NUMERO.

Rajouter un index sur la colonne TYPE est difficilement envisageable car il existe déjà 5 indexes pour la table. Etant donné les traitements journaliers assez volumineux de load (environ 2 millions de données par jour), l'ajout d'un index doit être fortement justifié.
Or comme je l'ai dit lors de mon premier message, le problème de performance ne se pose que pour quelques cas précis où l'on est obligé de réaliser plus de 2000 fetchs pour ramener moins d'une vingtaine de références.
Les cas plus fréquents où les personnes désirent les références de tous types ou les numéros avec peu de références, ont des temps de réponses plus que corrects.

Mais en supposant que je crée un index sur la colonne TYPE, comment aurais tu fait pour placer un prédicat sur la colonne TYPE?

[Luc Orient]

Le chemin actuel choisi est un indexscan par l'index contenant le NUMERO.

L'index utilisé ne contient que la colonne NUMERO ?

Mais en supposant que je crée un index sur la colonne TYPE, comment aurais tu fait pour placer un prédicat sur la colonne TYPE?

Quel est le nombre maximum de valeurs pour TYPE passées à l'accesseur ?

[michael08]

L'index utilisé ne contient que la colonne NUMERO ?

Oui.

Quel est le nombre maximum de valeurs pour TYPE passées à l'accesseur ?

Pour le moment, on a 7 valeurs possibles. Mais cela peut être amené à évoluer.

[skuatamad]

Je ne sais pas si j'ai bien compris le besoin, mais il est possible de décomposer le nombre 148 en 3 lignes contenant 1, 4 et 8 en utilisant un WITH Récursif et je pense que ça peut aider à faire un simple IN dans la requête de base.
J'ai développé l'exemple sur Oracle (et je ne connais pas DB2) mais je sais que le WITH recursif est normatif et existe en DB2 depuis longtemps :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SQL> select * from t;
 
      TYPE
----------
       148
   1235689
 
SQL>   with rec_type (type,type_decompose,position) as (
  2  select type, substr(type,1,1), 1
  3    from t
  4   union all
  5  select t.type, substr(t.type,r.position + 1, 1), r.position + 1
  6    from t
  7    join rec_type r on r.type = t.type and r.position + 1 <= length(t.type)
  8  )
  9  select * from rec_type
 10  order by type, position;
 
      TYPE TYPE_DECOMPOSE                   POSITION
---------- ------------------------------ ----------
       148 1                                       1
       148 4                                       2
       148 8                                       3
   1235689 1                                       1
   1235689 2                                       2
   1235689 3                                       3
   1235689 5                                       4
   1235689 6                                       5
   1235689 8                                       6
   1235689 9                                       7
 
10 rows selected.

[Luc Orient]

Oui.

Tu as peut être interêt à envisager l'ajout d'aitres colonnes pour cet index puisque je vois d'autres colonnes, outre le type, qui interviennent dans ton prédicat de sélection :
PART = > un numéro de partition ?
SIRET

A mon sens le mieux est de voir avec ton DBA qui doit pouvoir réaliser facilement différentes simulations ...

Pour le moment, on a 7 valeurs possibles. Mais cela peut être amené à évoluer.

Je déclare 7 variables hôtes (T1, T2, T3, etc) et j'écris :

IN (:T1, :T2, :T3; :T4, :T5, :T6, :T7)

Chaque variable est valorisée jusqu'à atteindre le nomdre de valeurs de TYPE demandées et le complément est valorisée par une des valeurs déjà présente (la première ou la dernière par exemple ...).

[michael08]

Tu as peut être interêt à envisager l'ajout d'aitres colonnes pour cet index puisque je vois d'autres colonnes, outre le type, qui interviennent dans ton prédicat de sélection :
PART = > un numéro de partition ?
SIRET

A mon sens le mieux est de voir avec ton DBA qui doit pouvoir réaliser facilement différentes simulations ...

Il y a bien plus de prédicats dans la requête, d'où les pointillets que j'ai mis. De plus ce n'est pas la seule requête sur cette table.
PART fait partie de l'index cluster.

Je déclare 7 variables hôtes (T1, T2, T3, etc) et j'écris :

IN (:T1, :T2, :T3; :T4, :T5, :T6, :T7)

Chaque variable est valorisée jusqu'à atteindre le nomdre de valeurs de TYPE demandées et le complément est valorisée par une des valeurs déjà présente (la première ou la dernière par exemple ...).

Oui c'est une bonne idée, mais si les programmes qui appellent nos accesseurs envoient en entrée une nouvelle valeur de TYPE qui ne fait pas partie de la liste, on risque de ne pas être bon.
Disons que c'est ce que je ferais si je n'ai pas de solutions plus pérenne à l'image d'une fonction contains qui elle vérifierait si la valeur du type dans la table est contenue dans le mot formé par le type_composé reçu en entrée.

EDIT: Je pourrais éventuellement en prévoir 10 pour prévenir le cas où une nouvelle valeur de TYPE doit être pris en compte.

[michael08]

Je ne sais pas si j'ai bien compris le besoin, mais il est possible de décomposer le nombre 148 en 3 lignes contenant 1, 4 et 8 en utilisant un WITH Récursif et je pense que ça peut aider à faire un simple IN dans la requête de base.
J'ai développé l'exemple sur Oracle (et je ne connais pas DB2) mais je sais que le WITH recursif est normatif et existe en DB2 depuis longtemps :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SQL> select * from t;
 
      TYPE
----------
       148
   1235689
 
SQL>   with rec_type (type,type_decompose,position) as (
  2  select type, substr(type,1,1), 1
  3    from t
  4   union all
  5  select t.type, substr(t.type,r.position + 1, 1), r.position + 1
  6    from t
  7    join rec_type r on r.type = t.type and r.position + 1 <= length(t.type)
  8  )
  9  select * from rec_type
 10  order by type, position;
 
      TYPE TYPE_DECOMPOSE                   POSITION
---------- ------------------------------ ----------
       148 1                                       1
       148 4                                       2
       148 8                                       3
   1235689 1                                       1
   1235689 2                                       2
   1235689 3                                       3
   1235689 5                                       4
   1235689 6                                       5
   1235689 8                                       6
   1235689 9                                       7
 
10 rows selected.

Merci pour ta réponse.
En fait le type composé (appelons le TYPE_COMPOSE)ne correspond pas à l'enregistrement en table qui lui est sur une position.
Le type composé est envoyé en entrée par le programme appelant.

Mon but est de trouver un prédicat qui dit WHERE TYPE est_contenu_dans TYPE_COMPOSE.

[Luc Orient]

Il y a bien plus de prédicats dans la requête, d'où les pointillets que j'ai mis. De plus ce n'est pas la seule requête sur cette table.
PART fait partie de l'index cluster.

Ce n'est en rien contradictoire avec ce que j'écris. Plus ton index est sélectif plus tes performances seront meilleures. Aprés c'est à étudier en foncrion de la requête d'une part, et du nombre de valeurs différentes de chacune des colonnes dans le prédicat d'autre part .... Le mieux est de faite des simulations je pense ...

Oui j'y ai pensé aussi, mais si les programmes qui appellent nos accesseurs envoient en entrée une nouvelle valeur de TYPE qui ne fait pas partie de la liste, on risque de ne pas être bon.

Non, Je ne vois pas en quoi. Je parle bien de variables ici.

[skuatamad]

Remplace T dans ma requête par :

Code :

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

select :W-TYPE (from dual) -- je ne sais pas s'il faut un FROM en DB2

Ca devrait fonctionner (par contre il y a peut être de la conversion implicite, il faudra probablement caster en chaine :W-TYPE puis recaster en integer le type_decomposé)

En tout cas avec cette méthode pas besoin de paramètres en statique dans la requête.

[michael08]

Ce n'est en rien contradictoire avec ce que j'écris. Plus ton index est sélectif plus tes performances seront meilleures. Aprés c'est à édutier en foncrion de la requête d'une part, et du nombre de valeurs différentes de chacune des colonnes dans le prédicat d'autre part .... Le mieux est de faite des simulations je pense ...

Oui je suis d'accord.

Non, Je ne vois pas en quoi. Je parle bien de variables ici.

Ce que je voulais dire c'est que si une nouvelle valeur se rajoutait, le nombre max de valeurs possibles passait à 8.

[skuatamad]

Je voyais quelque chose comme (désolé toujours sur oracle mais je pense très facilement adaptable à DB2):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SQL> select * from table_ref;
 
REFERENCE                                         TYPE
------------------------------------------- ----------
XXX1                                                 1
XXX2                                                 2
XXX3                                                 3
XXX4                                                 4
XXX5                                                 5
XXX6                                                 6
XXX7                                                 7
XXX8                                                 8
XXX9                                                 9
XXX10                                               10
 
10 rows selected.
 
SQL> var l_type number;
SQL> exec :l_type := 148;
 
PL/SQL procedure successfully completed.
 
SQL>   with t as (
  2  select :l_type as type from dual
  3  ),
  4         rec_type (type,type_decompose,position) as (
  5  select type, substr(type,1,1), 1
  6    from t
  7   union all
  8  select t.type, substr(t.type,r.position + 1, 1), r.position + 1
  9    from t
 10    join rec_type r on r.type = t.type and r.position + 1 <= length(t.type)
 11  )
 12  select *
 13    from table_ref
 14   where type in (select type_decompose from rec_type)
 15  /
 
REFERENCE                                         TYPE
------------------------------------------- ----------
XXX1                                                 1
XXX4                                                 4
XXX8                                                 8
 
SQL>

Comme mentionné précédemment, je pense qu'il y a des conversions implicites qu'il sera bon de traiter.

Après si avoir 10 entrées statiques dans le IN est acceptable pour ta boîte, ça fait un code plus simple.

[michael08]

Je voyais quelque chose comme (désolé toujours sur oracle mais je pense très facilement adaptable à DB2):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SQL> select * from table_ref;
 
REFERENCE                                         TYPE
------------------------------------------- ----------
XXX1                                                 1
XXX2                                                 2
XXX3                                                 3
XXX4                                                 4
XXX5                                                 5
XXX6                                                 6
XXX7                                                 7
XXX8                                                 8
XXX9                                                 9
XXX10                                               10
 
10 rows selected.
 
SQL> var l_type number;
SQL> exec :l_type := 148;
 
PL/SQL procedure successfully completed.
 
SQL>   with t as (
  2  select :l_type as type from dual
  3  ),
  4         rec_type (type,type_decompose,position) as (
  5  select type, substr(type,1,1), 1
  6    from t
  7   union all
  8  select t.type, substr(t.type,r.position + 1, 1), r.position + 1
  9    from t
 10    join rec_type r on r.type = t.type and r.position + 1 <= length(t.type)
 11  )
 12  select *
 13    from table_ref
 14   where type in (select type_decompose from rec_type)
 15  /
 
REFERENCE                                         TYPE
------------------------------------------- ----------
XXX1                                                 1
XXX4                                                 4
XXX8                                                 8
 
SQL>

Comme mentionné précédemment, je pense qu'il y a des conversions implicites qu'il sera bon de traiter.

Après si avoir 10 entrées statiques dans le IN est acceptable pour ta boîte, ça fait un code plus simple.

Merci beaucoup pour ta solution! Je vais l'étudier et la comparer en terme de performance avec la solution statique au moyen d'un explain plan.

[michael08]

Je voyais quelque chose comme (désolé toujours sur oracle mais je pense très facilement adaptable à DB2):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SQL> select * from table_ref;
 
REFERENCE                                         TYPE
------------------------------------------- ----------
XXX1                                                 1
XXX2                                                 2
XXX3                                                 3
XXX4                                                 4
XXX5                                                 5
XXX6                                                 6
XXX7                                                 7
XXX8                                                 8
XXX9                                                 9
XXX10                                               10
 
10 rows selected.
 
SQL> var l_type number;
SQL> exec :l_type := 148;
 
PL/SQL procedure successfully completed.
 
SQL>   with t as (
  2  select :l_type as type from dual
  3  ),
  4         rec_type (type,type_decompose,position) as (
  5  select type, substr(type,1,1), 1
  6    from t
  7   union all
  8  select t.type, substr(t.type,r.position + 1, 1), r.position + 1
  9    from t
 10    join rec_type r on r.type = t.type and r.position + 1 <= length(t.type)
 11  )
 12  select *
 13    from table_ref
 14   where type in (select type_decompose from rec_type)
 15  /
 
REFERENCE                                         TYPE
------------------------------------------- ----------
XXX1                                                 1
XXX4                                                 4
XXX8                                                 8
 
SQL>

Comme mentionné précédemment, je pense qu'il y a des conversions implicites qu'il sera bon de traiter.

Après si avoir 10 entrées statiques dans le IN est acceptable pour ta boîte, ça fait un code plus simple.

Je l'ai testé et ça marche nikel. J'avoue que la requète me fait un peu mal à la tête lol. Surtout la partie ou tu définis rec_type à partir d'un select sur une jonction avec rec_type.
Je vais voir si je peux mettre les with au meme endroit que mes declare cursor.

[skuatamad]

Surtout la partie ou tu définis rec_type à partir d'un select sur une jonction avec rec_type.

Il y a le tutoriel DB2 et le SQL récusif qui peut aider.

[michael08]

Il y a le tutoriel DB2 et le SQL récusif qui peut aider.

Merci. Donc si j'ai bien compris, le résultat du premier select renvoie la ligne (148, 1, 1) qui est utilisé dans le deuxième select.

le deuxième select est utilisé jusqu'à ce que r.postion + 1 soit supérieur à 3.

On aura ainsi les enregistrements suivants : (148, 4, 2) et (148, 8, 3)

Au final rec_type sera une table contenant les enregistrements suivants :
(148, 1, 1)
(148, 4, 2)
(148, 8, 3)

Est ce que quelqu'un sait comment mettre cette requète sur un programme cobol?
Si je fais ainsi:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
EXEC SQL             
DECLARE CURSEUR 
CURSOR FOR    
WITH T AS ( SELECT DISTINCT :W-TYPE AS TYPE FROM TABLE),         
REC_TYPE (TYPE, TYPE_DECOMPOSE, POSITION) AS (                         
SELECT TYPE, SUBSTR(TYPE, 1, 1), 1                                     
FROM T                                                                 
UNION ALL                                                              
SELECT T.TYPE, SUBSTR(T.TYPE, R.POSITION + 1, 1), R.POSITION + 1       
FROM T                                                                 
JOIN REC_TYPE R ON R.TYPE = T.TYPE AND R.POSITION + 1 <= LENGTH(T.TYPE)
)    
 
SELECT   REFERENCE,      
             NUMERO, 
             ...
FROM  TABLE_REF                                
WHERE   PART    =       :W-IN-PART        
AND     NUMERO    =       :W-IN-NUMERO  
AND TYPE IN (SELECT TYPE_DECOMPOSE FROM REC_TYPE)      
AND     SIRET    BETWEEN :W-IN-SIRET-INF AND
                        :W-IN-SIRET-SUP
...
             FOR FETCH ONLY       
OPTIMIZE FOR 20 ROWS 
END-EXEC.

ça marche?