We help IT Professionals succeed at work.

Tracking DB2 database transactions delays

LeTay
LeTay used Ask the Experts™
on
We have a large application that accesses a DB2 database
That application processes "batch" files that arrives in a special directory
Upon "receiving" of a file, the application (JBoss, Java) processes it by accessing the DB for many many queries, updates and inserts
Several files, and this is often the case, can be processes simultaneously.
However when this is the case, it seems that there are some long locking period  in the database as the completion of the work is longer and longer
We wonder if there is a DB2 tool (monitoring) that we can activate, and that will trace all requests sent to the RDBMS, but also indicating how long it took for each of these to complete
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
which platform of db2?
and which version?
Commented:
If this is DB2 UDB (Linux, Windows or Unix), you can have your DBA setup a Statement Event Monitor.  This will capture all SQL and give the amount of time each took.  However, this will not report LOCK-WAIT time which you are concerned with - and it will spew out tons of information and could easily consume all of your free disk space.

In order to determine what is causing the database contention, the best "free" tools available are the LIST APPLICATION SHOW DETAIL command to see which connections are in a LOCK-WAIT state and/or the GET SNAPSHOT FOR ALL APPLICATIONS which will show what each connection is doing and if they are in lock-wait, what are they waiting for and from who.  Both of these commands will have to be run at regular intervals to generate the type of information needed to diagnose the problem.  Start running  every minute and run more often if necessary.

Both solutions will require that you turn on the MONITOR SWITCHES - I recommend turning on all of them to get the most information possible to diagnose this problem.

There are tools available - such as the ACTIVITY MONITOR which is part of the DB2 V9 client  - that can help diagnose the issue.  Other monitoring tools such as the Optim Performance Manager and Quest Spotlight which can be bought to help diagnose these types of problems.

Personally, I have a set of homegrown scripts that use the List Application and Get Snapshot infotrmation to monitor and diagnose these types of problems 24x7.  They are not pretty (no GUI, just test) but they get the job done quickly and for free.

Greg

Author

Commented:
DB2 version is 9.7 and the platform is Windows server 2003

Author

Commented:
Hello gmarino

I give a little bit more details about our application : when a "batch" is processed, it follows four phases
I summarise them shortly
1. The file is transferred "inside" the application
2. Each line (record) is "processed" and generates lines in several tables
3. Each generate line of a few of these tables are processed again "internally"
4. An output file is created based on data stored in the DB for this batch
The completion of each of these four steps is recorded in an external log
On a day, a few hundred of batches are submitted, all in a couple of hours at the beginning of the day (not possible to spread over a longer period)
What this log shows (it took me hours to see this behavior) is that at a "certain moment", instead of reaching completion of step 4 "smoothly" and "regularly", all batches stays in step 4 until the last submitted batch reaches step 4
At that time, all batches complete one by one rapidly

So I guess that for some reason, all these batches are just locked at a certain DB operation, due to the activity of other "arriving" batches

Probably that the first solution (event monitor) is the best for us as we may see there some transaction waiting for a long time to complete

As far as I know, the queries (select,update,insert) are very simple one and only process one or a few lines in tables...

you can try to run this statement
select * from table(MON_GET_APPL_LOCKWAIT (null, null))

to see currently waiting applications
which isolation level do you use?
maybe you can switch to cs or ur and it will fix your problem
Kent OlsenData Warehouse / Database Architect

Commented:
Hi LeTay,

If all of your batches seem to proceed gracefully until they reach "Step 4", then hang until they end in unison, it would seem that all of the jobs are hanging on the same object.  Perhaps a query is locking a table while step 4 is in progress and a later update (or COMMIT statement) releases the lock.

Momi is absolutely correct that a simple change in the isolation level may solve this.  But you should check with the applications developer(s) to make sure.

I'm a bit concerned over the nature of the updates, too.  You indicate that you're using a Java program to load the data.  The fastest way to load data into a single table is using the LOAD FROM CURSOR statement.  The fastest way to load data where some data results in a INSERT and other data results in an UPDATE is usually with the MERGE statement.


Good Luck,
Kent

Commented:
LeTay,

Until there is more evidence gathered, it is just speculation that the batches were sitting in the DB waiting until all of them got to "Step 4".  

Using a statement event monitor may be somewhat helpful, but it only reports SQL statements when they END (successfully or in error).  You could use this monitor to track how long SQL statements are taking - as well as how long the app is taking to issue SQL requests.  Many times this event monitor will show when the app sends a SQL, DB2 completes it and then the app takes seconds or even minutes to send the next SQL request (all within a single DB Transaction - no COMMIT issued). Again, this event monitor spews a ton of information that can be momentous to go through if you are not used to working with it.

My recommendation - If this condition happens again, use the GET SNAPSHOT FOR ALL APPLICATIONS a number of times while the batches are running and see if there is any LOCK-WAIT conditions occurring.  If there are, look at what connections are causing the contentions (and what SQL they are running.)  This along with your knowledge of how the app works should lead to conclusions that explain the behavior you are seeing.  (Again, this requires that you turn on the MONITOR SWITCHES to get meaningful information to diagnose the problem.)

Short of me being able to see the monitor output, it would be difficult for me to comment more on what is happening.  Of course, for a small consulting fee, I'd be more that willing... :-)  (It would be cheaper to get your DBA to help you with this...)

Greg