Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2009
    Posts
    42

    Unanswered: lock-wait with no other applications

    Hi,

    Application is in lock-wait, but when I list applications this is the only application connected to the database. What could be the reason?

    Thank you!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Did you try list applications show detail?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2009
    Posts
    42
    list applications show detail displays the same information - one application in lock-wait

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Use snapshot / db2pd to see what it's waiting for. If this is the only appl connected to the db, then I suspect that there is an indoubt transaction.

  5. #5
    Join Date
    Apr 2009
    Posts
    42
    How can I tell if we have indoubt transactions?

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    LIST INDOUBT TRANSACTIONS command

    Additional info :
    http://publib.boulder.ibm.com/infoce...n/t0004636.htm
    Last edited by sathyaram_s; 07-23-09 at 11:48.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Apr 2009
    Posts
    42
    Thank you. How do I tell if we have indoubt transactions from DB2 snapshot?

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Read prior post
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Apr 2009
    Posts
    42
    But prior post doesn't tell me how to tell if my application is in lock-wait with indoubt transactions. How to tell this information from DB2 snapshots?

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Does snapshot show you what it's waiting for?

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    If the snapshot shows that it's waiting for an appl with an appl handle 0, then this is a very good indicator that you have an indoubt transaction that is holding the lock(s):
    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

  12. #12
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Do you use big sized Global Temporary Tables in application ?
    Also the problem could be related to the sensitive cursors.

    Lenny K.

  13. #13
    Join Date
    Apr 2009
    Posts
    42
    Lenny, Can you explain how this problem can be related to sensitive cursors?

    Snapshot showed that it was in lock-wait with application 0.

  14. #14
    Join Date
    Jul 2009
    Posts
    150
    Check the Isolation Level. If Isolation Level > CS, change to CS.

    Also, if you make the massive update (insert or delete) inside of the program, don't forget to produce COMMIT after every, for example, 5000 updates.
    Last edited by DB2Plus; 07-24-09 at 22:38.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •