Db2 (On Premises and Cloud)

Db2 Locking contention analysis made easier, as you never did before with HTML reports directly from LOCKING event monitors!

By Samuel Pizarro posted 15 days ago

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 -wlockdb2top, 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 ; 
  • 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:
The relational tables approach can't be called as a Report!. The information is spread into 4 different tables,  and even if you manually write a good set of sql queries to grab the information,  the final tabular format is a nightmare to find what you need...  If you select only a few columns to have a nicer format to read, there is always something important that is left behind in the output..  If you select too much,  the final width of the information complicates it even further ...  

The db2evmonfmt tool is not shipped in a "ready to use" fashion,  you have to deal with java stuff and compile it.. Seriously ?!?!?!
Sorry but, I am a DBA,  I am not a java developer, and every time I faced issues in order to setup the java environment properly in order to compile it.  And when you finally get it working, the flat text based output is a little better than the tabular output,  but the queries information from each participant get too far from each other,  which complicates the analysis. It's really hard to navigate through the file, specially if you have more than 10 events on it. 

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: 
Lock Event List
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: 

Participant Details
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: 

Activity Details

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. 


Samuel Pizarro