| View previous topic :: View next topic |
| Author |
Message |
videlord
Beginner
Joined: 09 Dec 2004
Posts: 147
Topics: 19
|
 Posted: Wed Feb 07, 2007 9:30 pm Post subject: About drain lock |
 |
|
Usually DB2 utility will acquire drain locks.
Is there any possibility that application would aquire a drain lock? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Feb 07, 2007 10:30 pm Post subject: |
|
|
videlord,
May be Stored Procedures ?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
videlord
Beginner
Joined: 09 Dec 2004
Posts: 147
Topics: 19
|
| Posted: Wed Feb 07, 2007 10:43 pm Post subject: |
|
|
in PM account report for a bacth plan(CONNTYPE: IMS-BMP), I found this:
| Code: |
CLASS 3 SUSPENSIONS ELAPSED TIME EVENTS HIGHLIGHTS
-------------------- ------------ -------- --------------------------
LOCK/LATCH(DB2+IRLM) 36.284072 485 THREAD TYPE : ALLIED
SYNCHRON. I/O 8.408029 1393 TERM.CONDITION: NORMAL
DATABASE I/O 0.592706 280 INVOKE REASON : DEALLOC
LOG WRITE I/O 7.815322 1113 COMMITS : 1109
OTHER READ I/O 2.072357 900 ROLLBACK : 0
OTHER WRTE I/O 0.000000 0 SVPT REQUESTS : 0
SER.TASK SWTCH 8.345966 1113 SVPT RELEASE : 0
UPDATE COMMIT 8.282990 1107 SVPT ROLLBACK : 0
OPEN/CLOSE 0.034137 1 INCREM.BINDS : 0
SYSLGRNG REC 0.012618 3 UPDATE/COMMIT : 1.00
EXT/DEL/DEF 0.011344 1 SYNCH I/O AVG.: 0.006036
OTHER SERVICE 0.004878 1 PROGRAMS : 4
ARC.LOG(QUIES) 0.000000 0 MAX CASCADE : 0
ARC.LOG READ 0.000000 0 PARALLELISM : NO
DRAIN LOCK 0.374479 1
CLAIM RELEASE 0.000000 0
PAGE LATCH 0.000000 0
NOTIFY MSGS 0.000000 0
GLOBAL CONTENTION 0.791169 101
COMMIT PH1 WRITE I/O 0.000000 0
ASYNCH CF REQUESTS 0.106651 111
TOTAL CLASS 3 56.382722 4104
|
I can't explain why there is a drain lock.
4 packages in the plan were executed, they are all PL/1 IMS BMP |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Feb 08, 2007 7:18 am Post subject: |
|
|
videlord,
It may be that the access path is being changed at execution time. If the Explain access path uses List Prefetch then it is possible that at execution time DB2 is hitting the 25% RDS limit and falling back to a tablespace scan. Check the accounting trace record to see if that is what happened.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
videlord
Beginner
Joined: 09 Dec 2004
Posts: 147
Topics: 19
|
| Posted: Thu Feb 08, 2007 8:55 am Post subject: |
|
|
Thanks koluso,
But there is no index on the table which the package accessed. There are less than 300 rows in the table.
(the package just select the table, and then update if found, but it didn't use cursor)
Another question, if the access path change (like what you said), it may acquire a drain lock? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Feb 08, 2007 9:41 am Post subject: |
|
|
| Quote: |
(the package just select the table, and then update if found, but it didn't use cursor)
|
videlord,
Do you have any LOB tablespaces and are you updating them in the program ? If so then there is a chance of acquiring a drain lock.
| Quote: |
Another question, if the access path change (like what you said), it may acquire a drain lock?
|
Change of access path can sometime acquire a drain lock. However in your case that is ruled out as your table has only 300 rows.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
