Db2

Have you ever tried to perform locking contention analysis on Db2 ? 

Assisting an application in a real-time locking contention is relatively easy.   We have good tools for that like db2pd -wlock,  db2top, dmctop and so on.  Just run one of those during the lock contention scenario and you can easily pinpoint the culprit and take action.  

But, what about previous and historical Locking contention analysis ?

Those deadlocks that the application team is always complaining about. Or those events that are not long enough to be captured in a real-time situation, and by the time you jump in, it's gone already!  🙁

For those, Db2 offers the LOCKING Event Monitors, that can be configured to capture the locking events  - DEADLOCKS, LOCKTIMEOUTS and LOCKWAITS. 
Once configured, the lock events will be saved on target tables as they happens, allowing us to analyze the information at a later time.  Really useful! 

I won't spend your time explaining how to create and setup them, as this was covered already in Db2 Documentation,  and also in several other excellent blogs over there. 
One of my favorite is from @Ember Crooks, "Analyzing Deadlocks - the new way" in her DataGeek.blog website! 
Her blog describe everything you need to know in order to create them,  and also how to extract the information from them, using one of the only two built-in available methods: 

• Either you generate a text based flat file report using a java tool named db2evmonfmt ; 
  or
• You extract the information into 4 different relational tables ; 

Don't get me wrong, but frankly speaking, I was spending a lot of time with both methods to analyze the events with them:
So, I decided to invest some time in creating a new format for my personal needs, as I was doing a lot of locking analysis by that time and was wasting too much time with them. My purpose was to have an interactive HTML report,  where I could use the browser to have a broad picture of the locking events, but also navigate trough the details in a easier and more efficient way.  And I succeed! The final report was so easy to use, that I decided to share it with the community! 

The solution is 100% SQL code only. So, for DBAs like me..  no more hassle or struggling with external modules, libs, java, etc...  Really easy to implement on any regular db2 database out there,  and also really easy to extract the information and generate the HTML reports. 
Also, if you are familiar with html, css,  and xslt , the solution allows you to create your own html layouts and formatting, if you believe the basic one I have created is not enough for your needs! 😉

The Db2 Locking Events HTML Report solution is available, free of charge,  as a public github repository under IBM organization. In the wiki's repository page, you will find all the details you need to deploy and run it! 
But, let me share a few screenshots from the HTML reports pages, so you can have your first impression of it:

When you open the .html report on any browser,  you are presented with the Locking Events list, like bellow: 

You just need to choose the event you are interested in,  and when you click on it,  you get the Participant details. The image bellow shows a Dead-Lock situation: 


As you can see, the layout allows you to compare information from the participants simultaneously, side by side, including the list of current and past activities from each of them. This gives you a pretty clear and directly picture about the transactions being executed by both of them! 

And finally, if you click on any activity row,  you can get the entire SQL statement text,  together with the actual values from the parametrized prepared statements, if the event monitor was configured to collect them: 

I expect you enjoy it the same way I do!  

Ah,  and if you ever create a new html layout, please don't forget to share it as well for the rest of the community. 
Just place a Pull request in the github repository and I will be glad to review and publish it as part of the solution. 

​Regards 

Samuel Pizarro
#Db2