Probleme sur requete "Select for update"

**claudge** · 11/09/2013, 17h37

Bonjour,

Je dois mettre en place un curseur "select for update" mais l'ordre de lecture des lignes se fait sur l'index cluster (colonnes de sélection par le WHERE) alors que je dois traiter les lignes dans un ordre différent. Bien sur la clause ORDER BY est interdite. Comment puis-je écrire autrement ce curseur.

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
EXEC SQL DECLARE C-09 CURSOR WITH HOLD FOR   
  SELECT  REF_MAND_FIX                    
          ,SUBSTR(CHAR(REF_MAND_IND),2,25) 
          ,NO_PART                         
          ,ID_CREANR                       
          ,NOM_CREANR                      
          ,IBAN_DB                         
          ,BIC_DB               
          ,NO_CTRAT                        
          ,D_CREAT             
   FROM   BMTMNDT                 
   WHERE  NO_PART    = :NO-PART  
     AND  NO_CTRAT  = :NO-CTRAT 
   ORDER BY D_CREAT_10   DESC     
            ,REF_MAND_FIX DESC     
            ,REF_MAND_IND DESC     
   FOR UPDATE OF                  
            IBAN_DB              
           ,BIC_DB               
           ,D_CREAT_10      
END-EXEC.

Merci de votre aide.

**Luc Orient** · 15/09/2013, 18h23

Bonjour,

Depuis des lustres (au moins ... ), les clauses FOR UPDATE et ORDER BY sont mutuellement exclusives.

Je crois, mais ça reste à vérifier, que depuis une version récente de DB2 for z/OS il est possible de jouer avec les curseurs dits SCROLLABLE.

**bernard59139** · 16/09/2013, 10h02

l'ordre de lecture des lignes se fait sur l'index cluster

Ca, ce n'est absolument pas garantie. Sans ORDER BY, db2 fournit les données comme ca l'arrange.

Pour répondre à ton besoin:

soit faire SELECT puis un UPDATE
soit mettre les données à plat (dans un seq) , travailler et faire un LOAD REPLACE.

**claudge** · 16/09/2013, 10h48

Envoyé par bernard59139

Ca, ce n'est absolument pas garantie. Sans ORDER BY, db2 fournit les données comme ca l'arrange.

Pour répondre à ton besoin:

soit faire SELECT puis un UPDATE
soit mettre les données à plat (dans un seq) , travailler et faire un LOAD REPLACE.

Bonjour,
Le Select+Update sont déjà dans le programme, mais ils sont trop consommateur de temps CPU, c'est pour cela que je penche pour le Select-For-Update.
L'autre solution (Load Replace) n'est pas possible car plusieurs traitements batch tournent et la table est utilisée en lecture dans certains.

N'y a-t-il pas une solution par jointure ou autre type de requête ?

Merci pour vos réponses.

**claudge** · 16/09/2013, 10h50

Envoyé par Luc Orient

Bonjour,

Depuis des lustres (au moins ... ), les clauses FOR UPDATE et ORDER BY sont mutuellement exclusives.

Je crois, mais ça reste à vérifier, que depuis une version récente de DB2 for z/OS il est possible de jouer avec les curseurs dits SCROLLABLE.

Bonjour,
Je ne connais absolument pas cette méthode.
Comment savoir si une telle option est accessible sur le site ?

Merci pour vos réponses.

**bernard59139** · 16/09/2013, 11h52

quelle est ta version de db2?

**claudge** · 16/09/2013, 12h40

Envoyé par bernard59139

quelle est ta version de db2?

La version sur le site est : DB2 V9 NFM (New Fonction Mode)

**fsmrel** · 16/09/2013, 18h38

Bonjour,

Envoyé par claudge

Bonjour,
Le Select+Update sont déjà dans le programme, mais ils sont trop consommateur de temps CPU, c'est pour cela que je penche pour le Select-For-Update.

Il faut montrer pourquoi cette solution est coûteuse. Que disent les EXPLAIN ? Les outils d’administration du genre Detector ? Combien d’index sont mis à jour ? A propos des index, les débrancher le temps de l’opération peut être bénéfique (quitte à ce que les autres batchs attendent un peu...)

Envoyé par claudge

L'autre solution (Load Replace) n'est pas possible car plusieurs traitements batch tournent et la table est utilisée en lecture dans certains.

Même remarque.

**claudge** · 16/09/2013, 19h32

L'Explain a été fait par les DBA et il s'avère que l'Update coute autant que l'ouverture du curseur (ils sont présents dans la même boucle de traitement).

Il n'y a pas d'index de mis à jour sur l'Update.

**bernard59139** · 17/09/2013, 10h13

Les Scrollable Cursor existent depuis db2 v8.

Je ne pense pas que cela resolve tes problèmes de perf, mais je ne suis pas spécialiste dév sql.

La question est pourquoi l'open est-il si consommateur?

**claudge** · 17/09/2013, 10h43

Bonjour,
Voici le résultat d'une trace DB2 fourni par le DBA sur le traitement (les requêtes sont dans le programme MBMTMDT0) :

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
STOP.....05SEP 00.57.09.59 PLAN..............DA7ICR20 TYPE..............ALLIED 
START....05SEP 00.55.46.02 AUTHID..............XBATCH CONNECT....X46941AI/DLIB 
ELAPSED...........00:01:24 ORIG PRIM AUTH......XBATCH CORR ID.....0000IMSDB2X  
TERM........NORMAL/DEALLOC COMMITS.................66 ROLLBACKS..............0 
------------------------------------------------------------------------------ 
RUNTIME ANALYSIS   IN DB2     IN APPL.      TOTAL      %IN DB2(=)     TOTAL(*) 
----------------   --------   --------   --------     0 ...25...50...75..100%  
ELAPSED TIME       00:01:18   5,714 ms   00:01:24     ! ==================** ! 
CPU TIME           1,798 ms     892 ms   2,690 ms     ! <                    ! 
DB2 WAIT TIME      00:01:11                           ! =================    !


 

- - - - - - - - - - - - SQL STATEMENT EXECUTION COUNTS  - - - - - - - - - - - 
SELECT...........23,375    LOCK TABLE...............0    CREATE.............0 
INSERT............5,841    GRANT/REVOKE.............0    DROP...............0 
UPDATE............2,165    SET CURR.SQLID...........0    ALTER..............0 
DELETE................0    SET HOST VAR. ...........0                         
MERGE.................0    SET CURR.DEGREE..........0    COMMENT ON.........0 
TRUNCATE TABLE........0    SET CURR. RULES..........0    LABEL ON...........0 

                           CONNECT TYPE 1...........0                         

PREPARE...............0    CONNECT TYPE 2...........0    CREATE TEMP........0 
DESCRIBE..............0    SET CONNECTION...........0    RENAME TBL.........0 

                           RELEASE..................0    RENAME IDX.........0 

OPEN..............5,837    CALL.....................0                         
FETCH............13,592    ASSOCIATE LOCATOR........0                         
CLOSE.............5,829    ALLOCATE CURSOR..........0    OTHER DDL..........0

 

  - - - - - - - - - - - - - - BUFFER POOL ACTIVITY  - - - - - - - - - - - - - - 

- ACTIVITY                TOTAL       BP0       BP1       BP2       BP5       BP
6 -------------------  --------  --------  --------  --------  --------  -------
- GETPAGES...........   111,727       105    14,526    18,058    60,044        6
4 SYNC READS.........     8,172         6     2,230     2,577         0         
0 GETPAGES/READIO....      13.7      17.5       6.5       7.0       0.0       0.
0 COND. GP FAILURES..         0         0         0         0         0         
0                                                                               
  SEQ. PREFETCH REQS.         0         0         0         0         0         
0 LIST PREFETCH REQS.         0         0         0         0         0         
0 DYNAMIC PREFETCHES.       267         0        21        46         4         
0 ASYNC PAGES READ...     7,394         0       653     1,257         0         
0 PAGES/PREFETCH REQ.      27.7       0.0      31.1      27.3       0.0       0.
0                                                                               
  PAGE UPDATES.......    26,968         0    10,508     8,608         0         
0 IMMEDIATE WRITES...         0         0         0         0         0         


-------------------  --------  --------  --------  --------  --------  -------
 GETPAGES...........       271       228     3,573     3,235    11,603        2
 SYNC READS.........         3         7     3,073       258         0        1
 GETPAGES/READIO....      90.3      32.6       1.2      12.5       0.0       1.
 COND. GP FAILURES..         0         0         0         0         0         

 SEQ. PREFETCH REQS.         0         0         0         0         0         
 LIST PREFETCH REQS.         0         0         0         0         0         
 DYNAMIC PREFETCHES.         1         0       125        70         0         
 ASYNC PAGES READ...        31         0     3,443     2,010         0         
 PAGES/PREFETCH REQ.      31.0       0.0      27.5      28.7       0.0       0.


 PAGE UPDATES.......       103        14         0         0     7,735         
 IMMEDIATE WRITES...         0         0         0         0         0         

 

- - - - - - - PACKAGE / DBRM OVERVIEW (ACCTG CLASSES 7,8 ONLY)  - - - - - - - 
                                                                              

PACKAGE/                                        %TOTAL                        
DBRM      NO. SQL  CPU TIME  WAIT TIM  ELAPSED   ELAP.                        
-------- --------  --------  --------  -------- ------ 0 ...25...50...75..100
! MF3AG320    1,934     88 ms     16 s      17 s   19.89 ! ***                  
! MD2TCTR0    1,930     73 ms     10 s      10 s   12.26 ! **                   
! MBMTMDT0    7,807    530 ms     29 s      30 s   36.32 ! *******              
! MA7TORD0    2,079     81 ms    468 ms    593 ms   0.70 ! <                    
! MA7TOPE0    1,934     85 ms  1,460 ms  1,638 ms   1.96 ! <                    
! MA7THIS0    1,934    171 ms     11 s      11 s   13.16 ! **                   
! MA7TICR0    1,934     64 ms    531 ms    749 ms   0.89 ! <                    
! MH2TRFR0        4  1,459 us    168 ms    204 ms   0.24 ! <                    
 

 

CLOSE    1546      8     22 us   0.0     22 us   2.0     0     0    0    0    
OPEN     1527      8     12 ms  15.9    112 us  10.5     8    15    7   16    
FETCH    1561      8      9 us   0.0      9 us   0.8     0     0    0   16    
UPDATE   1874      8  7,265 us   9.9     91 us   8.4     0    11    7    0    
PGM:MBMTMDT0      32            25.9            21.7     8    26   14   32

Le curseur tel qu'il est actuellement :

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
EXEC SQL DECLARE C-07 CURSOR WITH HOLD FOR   
   SELECT    REF_MAND_FIX                ,   
             SUBSTR(CHAR(REF_MAND_IND),2,25),
             NO_PART                     ,   
             ID_CREANR                   ,   
             NOM_CREANR                  ,   
             D_REGL_FRST                   
   FROM      BMTMNDT                       
   WHERE  NO_PART             = :NO-PART   
   AND    NO_CTRAT            = :NO-CTRAT  
   AND    D_CREAT_10         <= :D-CREAT-10
   ORDER BY D_CREAT_10   DESC,             
            REF_MAND_FIX DESC,             
            REF_MAND_IND DESC
   END-EXEC.

Les colonnes NO_PART et NO_CTRAT forment l'index cluster (IBMTMNDT1), les colonnes REF_MAND_FIX et REF_MAND_IND forment un autre index (IBMTMNDT2).

Le résultat de l'explain sur le curseur :

Code :

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

1
2
3
 QRYNO | QBKNO | PROG     |   |       | TABLE      |  ACCES |          |   IXNAME   | XONLY |  TRIS, LOCKMODE, PREFETCH                        
 00334 | 00001 | MBMTMDT0 |1|0| PROD  | BMTMNDT    |1| I  |0| PROD     | IBMTMNDT1  | N     | N | N | N | N | N | N | N | N |  IS |  | D |   |0
 00334 | 00001 | MBMTMDT0 |2|3|       |            |0|    |0|          |            | N     | N | N | N | N | N | N | Y | N |     |  |   |   |0

**bernard59139** · 17/09/2013, 12h48

Combien de lignes sont-elles sélectionnées par le SELECT?

Dans l'explain, quelle est la valeur de MATCHCOL? elle devrait être à 2.
Le SELECT est fait combien de fois dans le programme?