Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2005
    Posts
    24

    Unanswered: Where can I find the deadlock info in db and how to see the log files?

    Dear All,

    I have 2 questions.Help me pls.
    1.May I know where can I find the db2 deadlocks info in database and how to unlock them?
    2.I do not know how to see the transaction log files.HOw to see it by text format?

    I am looking forward your response.


    Thank u all,
    Last edited by Coolhard; 12-01-06 at 05:05.

  2. #2
    Join Date
    Jun 2006
    Posts
    471

    log files

    the log files are created in internal format and are not described
    there are no tools/command to display the contents
    some products : like DB2 RECOVERY EXPERT : can extract the statements from the log files and create files to replay the already executed commands or to undo the executed commands.

    deadlock are not registered in db2
    I think that only audit or many snapshots could point to the cause of deadlock
    maybe some document in dev.works or db2 books that guides you how to handle this situation.
    will check...
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    deadlock is no need to unlock, because DB2 unlock them automatically. You can get info about locking with "db2 get snapshot" command or you can download simple db2 monitoring tool from http://members.tripod.com/chuzhoi_files/index.html - this tool is for windows only. In main program window click right button and then "Show lock dependency". In the left site of the windows there are lock dependency displayed. If deadlock appears then "deadlock" is desplayed.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can set up a deadlock event monitor to log information about deadlocks. I believe that for instances/databases created in V8.2 and above, there is a deadlock event monitor that is automatically set up for you.

    Please see the manuals for creating and reporting the results of deadlock event monitors.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2005
    Posts
    24
    Hi,

    I have been installed the tool that you mensioned here.I see a lot of lock wait but I couldn't do anything and have to reboot the server.
    But I do not want to reboot the server.Is there any other way to unlock the lock?(No other way at all?)
    Even I tried to run the statistics,it can't work.Pending and so I see a lot of lock wait there.
    As per your msg,I understand as the DB2 will unlock the locks automatically.When the DB2 unlock the locks?
    Is there any way to solve this kind of problems unless rebooting the server?


    Thank you all.

    Quote Originally Posted by grofaty
    Hi,
    deadlock is no need to unlock, because DB2 unlock them automatically. You can get info about locking with "db2 get snapshot" command or you can download simple db2 monitoring tool from http://members.tripod.com/chuzhoi_files/index.html - this tool is for windows only. In main program window click right button and then "Show lock dependency". In the left site of the windows there are lock dependency displayed. If deadlock appears then "deadlock" is desplayed.

  6. #6
    Join Date
    Jun 2006
    Posts
    471

    lock

    - automatic : set locktimeout to a specific nbr of seconds and db2 will force the lock waiter after this time in lockwait
    - manual : update monitor switch locks - get snapshot for user in lockwait
    check whom is locking this user - force the lock holder
    check why this uow keeps the lock - released after commit rollback
    is this a long running uow - maybe error in application - hanging application ??
    see infocenter for detailed commands : get snapshot - force application - update monitor switches.....
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Coolhard
    Hi,

    I have been installed the tool that you mensioned here.I see a lot of lock wait but I couldn't do anything and have to reboot the server.
    But I do not want to reboot the server.Is there any other way to unlock the lock?(No other way at all?)
    Even I tried to run the statistics,it can't work.Pending and so I see a lot of lock wait there.
    As per your msg,I understand as the DB2 will unlock the locks automatically.When the DB2 unlock the locks?
    Is there any way to solve this kind of problems unless rebooting the server?

    Thank you all.
    Issue the following command to limit the number of seconds that applications wait on locks before they are cancelled with a -911 reason code 68:

    db2 update db cfg for sample using locktimeout 30

    The default (-1) will wait forever. You may have to disconnect all applications for the above change to take effect.

    If DB2 were to determine that a lockwait would never resolve itself because it is involved in a deadlock, then the DB2 deadlock detector would cancel one of the applicaitons with a -911 reason code 2 to let the other one finish. By default, DB2 checks for deadlocks every 10 seconds.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Aug 2005
    Posts
    24
    Thank you all......

    Thanks Marcus.I have been run the cmd that you mensioned to change the lock timeout.Thanks for your explanation also.
    Let me see for next time whether it has been resolved it or not.

    Again,thanks so much all......

  9. #9
    Join Date
    Aug 2005
    Posts
    24
    Hi Marcus_A,

    If I give the locktimeout less than 30,is there any problem???
    Do u think it should be???

    Thanks,

    Quote Originally Posted by Marcus_A
    Issue the following command to limit the number of seconds that applications wait on locks before they are cancelled with a -911 reason code 68:

    db2 update db cfg for sample using locktimeout 30

    The default (-1) will wait forever. You may have to disconnect all applications for the above change to take effect.

    If DB2 were to determine that a lockwait would never resolve itself because it is involved in a deadlock, then the DB2 deadlock detector would cancel one of the applicaitons with a -911 reason code 2 to let the other one finish. By default, DB2 checks for deadlocks every 10 seconds.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Coolhard
    Hi Marcus_A,

    If I give the locktimeout less than 30,is there any problem???
    Do u think it should be???

    Thanks,
    You can set it to anything you want. It is the number of seconds that an application will wait on a lock before being cancelled with a -911 RC 68.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Aug 2005
    Posts
    24
    Ok.But I would like to know the disadvantage of the lesser lockwait time.
    Is there any idea pls?

    Thanks so much for your help.......


    Quote Originally Posted by Marcus_A
    You can set it to anything you want. It is the number of seconds that an application will wait on a lock before being cancelled with a -911 RC 68.

Posting Permissions

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