Analyzing Deadlocks – the new way

You may also like...

17 Responses

  1. Sheila Yao says:

    Not working for me:
    java db2evmonfmt -u mydbuser -d mydb -ue DB2EVM.LOCK_INFO -ftext
    Exception in thread “main” java.lang.NoClassDefFoundError: db2evmonfmt
    Caused by: java.lang.ClassNotFoundException: db2evmonfmt
    at java.lang.ClassLoader.loadClass(
    at sun.misc.Launcher$AppClassLoader.loadClass(
    at java.lang.ClassLoader.loadClass(
    Could not find the main class: db2evmonfmt. Program will exit.

  2. Sheila Yao says:

    Never mind, it works now. Need to export CLASSPATH=$CLASSPATH:$HOME/bin



  3. isaac munoz says:

    Hi Ember
    I wonder why LOCKTIMEOUT event type does not have a value in ROLLED_BACK_PARTICIPANT_NO column. Aren’t lock timeouts supposed to be rolled back as well?. Thanks and regards

  4. Noel Jones says:

    Very useful as ever Ember and has helped me greatly this week. Thanks. I used the EVMON_FORMAT_UE_TO_TABLES function. I did note that it produced quite a load when creating the tables for the first time (or subsequently if using the RECREATE_FORCE option). May need to take care on a production system. Its annoying that the function can create duplicates in the formatted tables so you either need to truncate these tables beforehand or be more selective eg; SELECT * FROM DBA.MY_LOCKS WHERE EVENT_TIMESTAMP > (current timestamp – 12 hours).

  5. Kaushik says:

    Worked perfectly. thank you so much. Just wondering if I have to do anything else to collect deadlock queries.

    • Ember Crooks says:

      I certainly recommend testing to see that you’re getting and able to parse your output. This should capture what DB2 is capable of capturing. I still find scenarios where the statement is missing for a specific deadlock or locktimeout.

  6. Bruno says:

    Hi Everybody,

    you can’t find the files and the 5 xsl files, in the server distribution. You will find it in the db2 client. Download it and without doing installation you can fin the files.
    Here is an examples with db2 10.1 FP4 :

  7. Julie Glotzbach says:

    Hi Ember! – I have used this page quite a bit – as with a lot of your write-ups! I’m wondering what type of overhead you’ve seen with this set up, but I guess it depends on the number of locks we have…Also, I’ll bet we could export the data every month or so and archive it if we need to. I’d still rather see the output in a table than in the home directory where it grows and grows, then stops working.
    I’m also wondering if this same set up can be trusted in version 10.5. I think YES!

    • Ember Crooks says:

      Hi Julie! Hope the new job is going well!

      I use this methodology constantly across many clients. Since the locking event monitor is writing to an unformatted table, the overhead is much lower than I typically associate with write-to-table event monitors. I have even run this in environments that were experiencing significant CPU pressure and never once worried about it. The information is so critical, and the impact not that significant. Obviously, the more locking events it is tracking, the bigger the impact. I only track deadlocks and lock timeouts and don’t have an environment with more than a few of these a week on an ongoing basis. I have thought about the fact that if I had an environment where there were significant ongoing locking issues, I would need to clear everything up once a month or so by extracting all the data to a formatted table and then dropping the event monitor and verifying that the unformatted table used also got dropped, and then re-creating the even monitor.

      I use it across versions – 9.7, 10.1 and 10.5.

      The one issue I have seen is that sometimes reorgchk indicates that the unformatted event table needs reorging, but the event monitor ALWAYS has a lock on that table. So I explicitly exclude that table from my reorg scripts. If I don’t, because reorg does not respect LOCTIMEOUT, my script gets hung up on that table every time. Even if you’re not clearing the data out every month, if there is a significant amount of data, you may want to stop the event monitor, reorg the table, and then start it up again.

  8. lakshmikanth MS says:

    Hello Ember,

    We have setup this new way of analyzing deadlocks in our environment and it was all working fine.
    But from yesterday, when we try to fetch the event monitor details,we are getting only locktimeout information but not deadlock event details from the event monitoring file.
    any how I can see deadlocks occurrence by querying event monitor table.
    Can you please help me in this regard ?

    Thank you,
    Lakshmikanth MS

    • Ember Crooks says:

      I have not seen that occur. I would check your database config settings as far as what data is collected, but beyond that, I don’t have any ideas.

  9. Hung Tam Nguyen says:

    This procedure applies perfectly even in latest version of DB2 (11.1 FP1). Still need to get files from outside the installation, compile them, etc. I ran into this error at the last step.

    Exception in thread “main” java.lang.UnsupportedClassVersionError: db2evmonfmt : Unsupported major.minor version 52.0

    Turns out ~/sqllib/java/jdk64/bin/java is more recent than /usr/bin/java on our machine even after a yum install/update. So if you have that error just make sure you use the binaries from sqllib.

    Also if you created the event monitor without specifying a ue table, it will simply default to the event monitor name.
    select evmonname from syscat.eventmonitors

  1. February 27, 2012

    […] first addressed the new 9.7 methodology for deadlock and locktimeout analysis in my post Analyzing Deadlocks – The New Way. I’ve been playing with it a bit, and wanted to share what I’ve found. First of all, so […]

  2. March 2, 2012

    […] Analyzing Deadocks – the new way Covers some of the same material as the DB2′s Got Talent presentation. It is a step-by-step instruction set for implementing this method and includes even more SQL for analyzing the output. […]

  3. March 16, 2012

    […] Event Monitors, and was not suprised to see that like the locking event monitors I used in Analyzing Deadlocks – the new way, Activity Event Monitors require two things to caputure data – the existence of the event […]

  4. December 3, 2013

    […] having worked some with the newer event monitoring interfaces (see db2caem: The Untold Story and Analyzing Deadlocks – the new way), I found that even once I knew that this is what I needed to get detailed statement information […]

Leave a Reply

Your email address will not be published. Required fields are marked *