Discussion :

[greatmaster1971]

Hello,

j'utilise un outil qui s'appelle DBUnit pour nettoyer les données d'une base DB2.
J'ai un problème quand il essaye de faire un delete from UNETABLE ou UNETABLE est une table qui contient environ 400 000 lignes.

Y'a plein de locks qui sont générés de type Row et exclusive et c'est très lent (j'ai laissé tourner le delete pendant 3 heures et c'était pas fini)

Evidemment dans ma boite les DBA sont pas bons et ils ont pas le temps de regarder.

Je tente de monitorer la base avec db2mon et voila ce que donne le snapshot du DATABASE MANAGER (xml)

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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
 
<snapshot time="16/05/2008 09:49:31" type="db and dbm" node="TCP9E3F1" db="BE1D_ITC"><dbm>
<group name="General">
<param name="Committed private Memory (Bytes)" value="8110080"/>
<param name="DB2START timestamp" value="13/05/2008 08:45:24"/>
<param name="Database manager status" value="Active"/>
<param name="Last reset timestamp" value=""/>
<param name="Number of nodes" value="1"/>
<param name="Product name" value="DB2 v8.1.0.136"/>
<param name="Service level" value="s070720 (MI00188)"/>
</group>
<group name="Sort">
<param name="Piped sorts accepted" value="0"/>
<param name="Piped sorts requested" value="0"/>
<param name="Post threshold sorts" value="0"/>
<param name="Total private sort heap allocated" value="10299"/>
<param name="Total sort heap allocated" value="0"/>
</group>
<group name="Hash">
<param name="Hash joins after heap threshold exceeded" value="0"/>
</group>
<group name="Switch">
<param name="N0. BUFFERPOOL" value="0, Time = "/>
<param name="N0. LOCK" value="1, Time = 16/05/2008 09:01:26"/>
<param name="N0. SORT" value="0, Time = "/>
<param name="N0. STATEMENT" value="1, Time = 16/05/2008 09:01:26"/>
<param name="N0. TABLE" value="0, Time = "/>
<param name="N0. TIMESTAMP" value="1, Time = 13/05/2008 08:45:24"/>
<param name="N0. UOW" value="0, Time = "/>
</group>
<group name="Connection">
<param name="Active local databases" value="1"/>
<param name="Local connections" value="0"/>
<param name="Local connections executing in db manager" value="0"/>
<param name="Remote connections executing in db manager" value="1"/>
<param name="Remote connections to db manager" value="2"/>
</group>
<group name="Agent">
<param name="Agents assigned from pool" value="3923"/>
<param name="Agents created from empty pool" value="45"/>
<param name="Agents registered" value="44"/>
<param name="Agents stolen from another application" value="0"/>
<param name="Agents waiting for a token" value="0"/>
<param name="High water mark for agents registered" value="44"/>
<param name="High water mark for agents waiting for a token" value="0"/>
<param name="Idle agents" value="40"/>
<param name="Max agents overflow " value="0"/>
<param name="Maximum coordinating agents" value="43"/>
</group>
<group name="Gateway">
<param name="Current number of gateway connections" value="0"/>
<param name="Gateway connection pool agents stolen" value="0"/>
<param name="Gateway connections waiting for client request" value="0"/>
<param name="Gateway connections waiting for host reply" value="0"/>
<param name="Total number of gateway connections" value="0"/>
</group>
<group name="Memory Pool">
<param name="Database Monitor" value="147456--147456--376832"/>
<param name="Other Memory" value="1409024--1638400--11943936"/>
</group>
</dbm>
<db>
<group name="General">
<param name="Database OS" value="Linux"/>
<param name="Database catalog network node name" value=""/>
<param name="Database catalog node number" value="0"/>
<param name="Database name" value="BE1D_ITC"/>
<param name="Database path" value="/appli/db2/BE1D_ITC/db2inst3/NODE0000/SQL00001/"/>
<param name="Database status" value="0"/>
<param name="First database connect timestamp" value="14/05/2008 17:37:46"/>
<param name="Input database alias" value="BE1D_ITC"/>
<param name="Last backup timestamp" value=""/>
<param name="Last reset timestamp" value=""/>
<param name="Location of database" value="Remote"/>
</group>
<group name="Calculated">
<param name="%, Application section hit ratio" value="99,65"/>
<param name="%, Buffer pool hit ratio" value="N/A"/>
<param name="%, Buffer pool index hit ratio" value="N/A"/>
<param name="%, Catalog cache hit ratio" value="98,13"/>
<param name="%, Package cache hit ratio" value="95,72"/>
</group>
<group name="Application">
<param name="Application connects" value="2158"/>
<param name="Applications connected currently" value="2"/>
<param name="Appls. executing in db manager currently" value="1"/>
<param name="Elapsed execution time" value="0,009766"/>
<param name="High water mark for connections" value="41"/>
<param name="Indoubt transactions" value="0"/>
<param name="Secondary connects total" value="0"/>
</group>
<group name="Lock">
<param name="Deadlocks detected" value="0"/>
<param name="Exclusive lock escalations" value="0"/>
<param name="Lock escalations" value="5"/>
<param name="Lock list memory in use (Bytes)" value="1924720"/>
<param name="Lock timeouts" value="1"/>
<param name="Lock waits" value="0"/>
<param name="Locks currently held" value="24041"/>
<param name="Time database waited on locks (ms)" value="0"/>
</group>
<group name="Sort">
<param name="Sorts currently active" value="0"/>
<param name="Total shared sort heap allocated" value="0"/>
<param name="Total sort heap allocated" value="0"/>
<param name="Total sort overflow" value="3"/>
<param name="Total sort time (ms)" value="0"/>
<param name="Total sorts" value="114"/>
</group>
<group name="Statement">
<param name="Commit statements attempted" value="11255"/>
<param name="DDL statements executed" value="113"/>
<param name="Dynamic statements attempted" value="425337"/>
<param name="Failed statement operations" value="38"/>
<param name="Rollback statements attempted" value="58"/>
<param name="Select SQL statements executed" value="10750"/>
<param name="Static statements attempted" value="13297"/>
<param name="Update/Insert/Delete statements executed" value="378548"/>
</group>
<group name="Rows">
<param name="Binds/precompiles attempted" value="0"/>
<param name="Internal automatic rebinds" value="0"/>
<param name="Internal commits" value="2158"/>
<param name="Internal rollbacks" value="3"/>
<param name="Internal rollbacks due to deadlock" value="2"/>
<param name="Internal rows deleted" value="0"/>
<param name="Internal rows inserted" value="0"/>
<param name="Internal rows updated" value="0"/>
<param name="Rows deleted" value="56733"/>
<param name="Rows inserted" value="373040"/>
<param name="Rows read" value="4333537"/>
<param name="Rows selected" value="845936"/>
<param name="Rows updated" value="24897"/>
</group>
<group name="Buffer pool">
<param name="Asynchronous data read requests" value="0"/>
<param name="Asynchronous index read requests" value="0"/>
<param name="Asynchronous pool data page reads" value="0"/>
<param name="Asynchronous pool data page writes" value="0"/>
<param name="Asynchronous pool index page reads" value="0"/>
<param name="Asynchronous pool index page writes" value="0"/>
<param name="Buffer pool data logical reads" value="0"/>
<param name="Buffer pool data physical reads" value="0"/>
<param name="Buffer pool data writes" value="0"/>
<param name="Buffer pool index logical reads" value="0"/>
<param name="Buffer pool index physical reads" value="0"/>
<param name="Buffer pool index writes" value="0"/>
<param name="Buffer pool temp data logical reads" value="0"/>
<param name="Buffer pool temp data physical reads" value="0"/>
<param name="Buffer pool temp index logical reads" value="0"/>
<param name="Buffer pool temp index physical reads" value="0"/>
<param name="Data pages copied from extended storage" value="0"/>
<param name="Data pages copied to extended storage" value="0"/>
<param name="Database files closed" value="0"/>
<param name="Direct read requests" value="0"/>
<param name="Direct reads" value="0"/>
<param name="Direct reads elapsed time (ms)" value="0"/>
<param name="Direct write requests" value="0"/>
<param name="Direct writes" value="0"/>
<param name="Direct writes elapsed time (ms)" value="0"/>
<param name="Dirty list threshold cleaner triggers" value="0"/>
<param name="Dirty page steal cleaner triggers" value="0"/>
<param name="Index pages copied from extended storage" value="0"/>
<param name="Index pages copied to extended storage" value="0"/>
<param name="LSN Gap cleaner triggers" value="0"/>
<param name="No victim buffers available" value="0"/>
<param name="Shared sort heap high water mark" value="0"/>
<param name="Time waited for prefetch (ms)" value="0"/>
<param name="Total buffer pool read time (ms)" value="0"/>
<param name="Total buffer pool write time (ms)" value="0"/>
<param name="Total elapsed asynchronous read time" value="0"/>
<param name="Total elapsed asynchronous write time" value="0"/>
<param name="Unread prefetch pages" value="0"/>
</group>
<group name="Log">
<param name="Appl id holding the oldest transaction" value="224"/>
<param name="Bytes of log that are held up by minbuf" value="49196548"/>
<param name="Bytes of log that need to be redone during recovery" value="49196548"/>
<param name="High water mark for database heap" value="1462965"/>
<param name="Log data found in buffer" value="282366"/>
<param name="Log file archive" value="4294967295"/>
<param name="Log file number, current" value="1760"/>
<param name="Log file number, first" value="1758"/>
<param name="Log file number, last" value="1763"/>
<param name="Log pages read" value="5864"/>
<param name="Log pages written" value="31646"/>
<param name="Log read time (sec.ns)" value="0,034408"/>
<param name="Log space available" value="-1"/>
<param name="Log space used" value="32855918"/>
<param name="Log write time (sec.ns)" value="109,286904"/>
<param name="Maximum secondary log space used" value="0"/>
<param name="Maximum total log space used" value="32855918"/>
<param name="Number log buffer full" value="0"/>
<param name="Number of secondary logs allocated" value="0"/>
<param name="Number partial page log IOs" value="5327"/>
<param name="Number read log IOs" value="733"/>
<param name="Number write log IOs" value="17857"/>
</group>
<group name="Package cache">
<param name="Application section lookups" value="425397"/>
<param name="Application sections inserted" value="1475"/>
<param name="Package cache high water mark (Bytes) " value="2457706"/>
<param name="Package cache inserts" value="891"/>
<param name="Package cache lookups" value="20839"/>
<param name="Package cache overflows" value="0"/>
</group>
<group name="Catalog cache">
<param name="Catalog cache heap full" value="0"/>
<param name="Catalog cache high water mark" value="0"/>
<param name="Catalog cache inserts" value="203"/>
<param name="Catalog cache lookups" value="10850"/>
<param name="Catalog cache overflows" value="0"/>
</group>
<group name="Hash">
<param name="Hash join loops" value="0"/>
<param name="Hash join number" value="27"/>
<param name="Hash join overflows" value="0"/>
<param name="Hash joins (small) overflows" value="0"/>
</group>
<group name="Workspace">
<param name="Corresponding private overflows" value="0"/>
<param name="Corresponding shared overflows" value="0"/>
<param name="Private high water mark" value="385176"/>
<param name="Shared high water mark" value="0"/>
<param name="Total private section inserts" value="1475"/>
<param name="Total private section lookups" value="16646"/>
<param name="Total shared section inserts" value="0"/>
<param name="Total shared section lookups" value="0"/>
</group>
<group name="Agent">
<param name="Agents associated with applications" value="2"/>
<param name="Agents currently waiting on locks" value="0"/>
<param name="Maximum agents associated with applications" value="41"/>
<param name="Maximum coordinating agents" value="41"/>
</group>
<group name="Memory Pool">
<param name="Backup/Restore/Util" value="16384--32768--20496384"/>
<param name="Buffer Pool" value="147456--147456--4186963968"/>
<param name="Buffer Pool" value="168869888--168869888--4186963968"/>
<param name="Buffer Pool" value="212992--212992--4186963968"/>
<param name="Buffer Pool" value="344064--344064--4186963968"/>
<param name="Buffer Pool" value="606208--606208--4186963968"/>
<param name="Catalog Cache" value="1376256--1376256--4186963968"/>
<param name="Database" value="11911168--11911168--21774336"/>
<param name="Lock Manager" value="21200896--21200896--21217280"/>
<param name="Other Memory" value="0--0--12353536"/>
<param name="Package Cache" value="2621440--2621440--4186963968"/>
</group>
<group name="Ungrouped">
<param name="849" value="0"/>
<param name="850" value="0"/>
</group>
</db>
</snapshot>

Est ce normal qu'il y ait autant de locks quand on fait le delete sur la table ?
Est ce qu'il faut regarder la taille des logs ?


Merci de votre aide car je ne suis pas assez expérimenté en DB2.

[fsmrel]

S'il y a des index branchés sur la table, chaque DELETE les implique et ça coûte. Vous pourriez voir ce que cela donne de les droper, effectuer le DELETE et les recréer dans une étape suivante.

[greatmaster1971]

Merci pour cette suggestion, je viens d'enlever tous les index et de relancer le traitement.
Il reste seulement un index qui est posé par le Système et que je ne peux pas enlever.
Je constate que c'est un peu plus rapide mais toujours lent quand meme ...
Et il y a toujours tous les LOCKS.

[fsmrel]

Quel DB2 utilisez-vous ? (Unix, z/OS, 400, ...)

QUel pourcentage de lignes de la table supprimez-vous ? (S'il est élevé, plutôt que supprimer, vous pouvez décharger les lignes à conserver puis les recharger et seulement celles-là).

Si vous utilisez DB2 for z/OS (je ne connais pas les autres), avez-vous essayé un LOCK TABLE IN EXCLUSIVE MODE ? Un ALTER TABLESPACE LOCKSIZE TABLE ou PAGE ?

[greatmaster1971]

Quel DB2 utilisez-vous ? (Unix, z/OS, 400, ...)

QUel pourcentage de lignes de la table supprimez-vous ? (S'il est élevé, plutôt que supprimer, vous pouvez décharger les lignes à conserver puis les recharger et seulement celles-là).

Si vous utilisez DB2 for z/OS (je ne connais pas les autres), avez-vous essayé un LOCK TABLE IN EXCLUSIVE MODE ? Un ALTER TABLESPACE LOCKSIZE TABLE ou PAGE ?

DB2 8.1 LINUX

c'est un delete complet des éléments de la table : delete from MATABLE

que peut apporter un LOCK TABLE IN EXCLUSIVE MODE ???

[fsmrel]

Dans le contexte de DB2 for z/OS, pour faire court, disons que ça permet au système de poser un verrou au niveau de la table et d'éviter de perdre du temps à poser des montagnes de verrous au niveau ligne et ce jusqu'au prochain COMMIT/ROLLBACK.

D'une façon générale, vous avez des granules de verrouillage plus ou moins fins, par exemple :

ROW
PAGE
TABLE
TABLESPACE

Évidemment plus le granule est fin (ROW), moins on gêne les autres dans un contexte à forte concurrence d'accès, mais en contrepartie ça coûte cher. Inversement avec un gros granule (TABLE), ça ne coûte pas cher, mais la concurrence sur la table devient impossible. Mais dans votre cas, c'est peut-être ce qu'il faut : bloquer le temps de réaliser les DELETE.

Avez-vous les pointeurs sur la documentation de votre DB2 ? Je pourrai ainsi comparer avec DB2 for z/OS.

[rberthou]

DB2 8.1 LINUX
c'est un delete complet des éléments de la table : delete from MATABLE

Dans ce cas le plus simple et le plus rapide, à mon avis, est un drop table puis une re-création.

[greatmaster1971]

De retour sur ce post avec la solution à mon problème.
Etant donné que je travaille sur des bases de test
je me suis contenté d'un
LOAD DATA FROM UnFichierVide
pour chaque table que je voulais vider.
Du coup plus de LOCK car contrairement aux DELETE, les LOAD n'écrivent pas dans les LOGS et c'est donc rapide.
Ca prend environ 5 minutes pour vider approximativement 3 millions de lignes.

Voila probleme résolu ...

@+ et merci à tous ceux qui ont pris le temps de répondre

[battistuta]

Parfait. Attention tout de même il s'agit là des options par défaut de l'utilitaire de rechargement.
Si non spécifié => LOAD REPLACE LOG NO, sinon il est toujours possible de tracer dans la log DB2 pour éviter les potentiels CHECK sur table.

[pdz74]

Je suis surpris qu'un LOAD REPLACE à vide dure 5 minutes. Le temps de faire delete/define des fichiers vsam correspondant à tablespace et index ne devrait prendre que 5 secondes maxi, pas 5 minutes. Tu devrais vérifier les paramètres techniques de tablespace et index, en particulier le ERASE. Si YES, il est préférable de metre NO.

[Luc Orient]

Je suis surpris qu'un LOAD REPLACE à vide dure 5 minutes. Le temps de faire delete/define des fichiers vsam correspondant à tablespace et index ne devrait prendre que 5 secondes maxi, pas 5 minutes. Tu devrais vérifier les paramètres techniques de tablespace et index, en particulier le ERASE. Si YES, il est préférable de metre NO.

Attention !
C'est du DB2 LUW (Linux pour être précis) et pas du DB2 z/OS.
Donc il n'y a pas de de fichiers VSAM car ce type de fichiers ne se trouve que sur le z.
Maintenant, c'est vrai que le principe ne devrait pas être très différent sous Linux ...