Sometimes while writing transactional code in ESQL, we encounter need for committing certain database operations irrespective of success or failure of parent transaction. For example, writing information to a log; updating a sequence number in a table based on some business logic etc.
Recently we encountered such a situation in a healthcare project I was working, where a sequence based on table was used by multiple processes (batch and real-time) by incrementing and then updating the sequence with new number based on some business logic. Since batch process may take longer time to complete transaction than real-time, database locks were occurring on the common resource (Sequence table).
One way to avoid database locks in these situations are by committing after the UPDATE operation on this table. But this “commit” will also commits all the prior database changes (DML) from the start of the transaction. Which may not be suitable if transaction fails in later steps.
Better way is to create autonomous transaction for those operations. But there is no autonomous transaction support in ESQL. Luckily DB2 has a mechanism, to commit the only part of the code (a single statement or operation) without committing parent transaction.
Autonomous Transaction in DB2 is handled by DB2 Procedure. Transfer the contention database operations( statements) into DB2 Procedure and declare that procedure as AUTONOMOUS and Commit inside the PROCEDURE . This way it doesn’t affect parent transaction.
Here is the link for more information on Autonomous Transaction in DB2.
http://www.ibm.com/developerworks/data/library/techarticle/dm-0907autonomoustransactions/index.html
What’s next?
If you’re still using WebSphere Message Broker, it might be time to think about upgrading your ESB. Whether you’re using an unsupported version of WMB or want the features of the newest IIB release, use our best practices compiled over the course of countless migrations to ease the process and maximize your IBM investment.
