Using Override Transaction Behavior in TIBCO JDBC Palette

For database DML statements (Update, Insert, Delete), It is always a preferred approach to include all related activities in a JDBC Transaction group so that all statements are committed or rolled back together in order to ensure data accuracy and to avoid any anomalies in the database records. However, there are scenarios where a developer has to decide otherwise and exclude some activities from this behavior and override transaction behaviour. In this post, I will explain in detail about TIBCO Override Transaction Behavior functionality available for TIBCO JDBC Palette activities.

What is TIBCO Override Transaction Behavior

Override Transaction Behavior is a checkbox available in Advanced tab of JDBC activities in TIBCO BW which can be used to override the overall transaction behavior in a transaction group for any particular JDBC activitiy so that the DML operation in that activity is committed separately in the database no matter other activities in the JDBC group are committed or rolled back. This exclusive commit is achieved as the activity gets completed in the database by opening a separate database connection instead of using the same that is used by other JDBC activities in the transaciton group.

When to use Override Transaction Behavior?

Purpose of adding JDBC Palette activities in a transaction group is to ensure that all DML operations are committed or rolled back collectively and no data discrepency happens with individual commits. So the question arises that why we may feel a need to override the transaction behavior?

Let’s talk about an example scenario where using this Override Transaction Behavior feature will be handy and will help us achieving our goal.

There is a table of sales orders which gets processed through a TIBCO BW process. Every order with pending status is picked by the process and then a number of subsequent database activities are used to perform a number of database operations including order processing, order distribution, account updates, inventory updates etc. TIBCO process is deployed in such a way that multiple process instances can run simultaneously to handle large bulk of incoming orders. In this scenario, when a pending order is picked for processing, we need to immmediately change the order status from Pending to “Under Process” in order to ensure that same order doesn’t get picked by another instance of the process while it is being processed by one instance. If override transaction behavior is not checked in this scenario, there is a possibility that some activities in the transaction group take longer time to complete and the entire JDBC transaction group commit is delayed and another process instance might pick the same order. By using Override Transaction Behavior in JDBC Update activity just at the begining of the transaction group will ensure that the order isn’t captured by another process instance.

Ajmal Abbasi

Ajmal Hussain Abbasi is Integration Consultant By Profession with 13+ years experience in Integration domain mainly with TIBCO products. He has extensive practical knowledge of TIBCO Business Works, TIBCO Cloud, TIBCO Flogo, TIBCO Mashery, TIBCO Spotfire, EMS and TIBCO ActiveSpaces. He has worked on a number of highly critical integration projects in various sectors by using his skills in TIBCO Flogo, TIBCO API Management (Mashery), TCI, Tibco Designer, TIBCO Business Studio, Adapters, TIBCO EMS, RV, Administrator, TIBCO BE, TIBCO ActiveSpaces etc. Ajmal Abbasi has experience with MuleSoft ESB as well. Ajmal Abbasi is also experienced in the area of API Management particularly with WSO2 API management platforms. Ajmal Abbasi is also experienced in developing solutions using Core Java and J2EE Technologies. You can contact Ajmal Abbasi for Consultancy, Technical Assistance and Technical Discussions.

More Posts - Website - Facebook - LinkedIn - YouTube