Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Posts
    7

    Unanswered: db2diag.log and deadlocks

    Hi All,

    I try to do some simulation of the deadlock and i manage to get -911 errcode 2. However, is not shown in the db2diag.log. In fact, even in instance.nfy only shows the successful escalation of locks but no Error was found.
    Is it because deadlock is not consider an Error in the DB2?
    So, i am wondering how does a DBA knows when a deadlock occures in DB2?

    2ndly, do we have some sort of deadlock capturing like Oracle? It seems the db2detaildeadlock is very user unfriendly where the DBA has to flush the event monitor and use db2evmon to filter it, after that have to reset the event monitor to store the fresh deadlocks..

    Thanks.

    Regards,
    Eddy Ng

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    what version of db2 are you on?

    event monitor is the best choice to capture deadlock details ... yes, you have to flush the event monitor and get the info (not sure how oracle does) ... no,it is not necessary to reset the event monitor for fresh deadlocks ... you can use the same,though new ones will be appended to the current events ...

    As an alternate to the default db2detaileddeadlock you can also setup your own 'WRITE TO TABLE' event monitor for easy access to the deadlock data, ie, you do not have to do the extra step of formatting the output


    hth

    sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2010
    Posts
    7
    hi Sathyaram,

    I am using db2 v9.1 FP 5 running on Redhat 4 Enterprise. I tried to set the diaglevel 4, but still no error on the db2diag.log. But anyway, setting diaglevel 4 is not feasible as this will generate tons of messages into the db2diag.log.

    well, my objective is trying to send out an email when db2 hit a deadlocks or locktimeout. I need to reset the monitor because i don't want the old deadlocks in the event monitor. Any idea on how to achieve this? I am currently running it with db2pdcfg to signal the error and having some issue if i come into more than 2 deadlocks at the same time.

    Thanks.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    if you want instant alert, i do not know of an option other than db2pdcfg .. what is the issue you have with db2pdcfg ?

    as i mentioned earlier, you can use write to table event monitor and then prune data on a periodic basis and/or extract data of the events after a specific point in time from the tables ..
    (or)
    if you have the time, budget, skills and interest, you can do your own parsing of the event monitor data stream and do whatever you want with it -- send alert, do not report on old ones etc, etc ... there is a sample evm in the sqllib/samples/c directory

    hth

    sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2010
    Posts
    7
    Hi Sathyaram,

    I did try to drop the default deadlock and create a new one with write to table. However, i am having the issues here...
    I simulate a deadlock and got err911 code 2 which is a deadlock.
    When i try to query the table, it is empty. But if re-run again the simulation, i would get some records
    2ndly, the records that i get is short of 1. In other words, for each deadlock occurs, i should be getting 2 statement. However, i am only getting 1 and the 1 other is still inside the event monitor. So each time if i have a deadlock, i will be missing 1 statement in the table..

    May i know is this a bug or it was designed? Why would DB2 designed that 1 statement to be push while the other statement is still stored in the event monitor? I am suspecting that the db2 are having some delay to push the 2nd statement to the output file/table.
    I try the manual way which is without writing to the table, is still the same result which is missing the 2nd statement.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    did you do FLUSH EVENT MONITOR before looking at the table ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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