ODI JMS JDBC driver gotcha

One fine morning ......

In the fullness of your enthusiasm you have built a data movement solution that takes trigger data from a JMS Queue, does some work and then writes out some information about what was actually done into another JMS Queue. But then, when the ODI Scenario is executed to your greatest horror nothing gets written out to the JMS Queue, although all tasks shine bright green in the ODI Operator.

Do you have commitment?

If this happens to you, here is one thing you can check. In ODI, tasks make use of JDBC connections. JDBC connections can be autocommit connections or be part of a transaction. JMS transactional connections have a weird behaviour. Nothing is actually written out to the JMS target until the transaction has been committed. This is a property of javax.jms.Session. For autocommit connection however, as soon as the ODI task ends, the data will be written out to JMS. In either case the one thing you can check is : after the task that writes out data to JMS do you have any task that executes using the same JMS connection, but has SQL code that consists only of SQL comment?

Explain .. explain

Assume that JMS connection is in autocommit mode. Writing out data to JMS immediately puts it in the Queue. Now you are asking JMS driver to execute some SQL that only has some comment content (no matter how this came about) using the same autocommit connection. When JMS driver tries to interpret this SQL it is unable to categorize it and as a result considers it to be a READ operation. As a result it just reads the data from the Queue. As you know a JMS read is a destructive read in that the message gets removed from the Queue. So hey presto! all your data is gone.

Now if the above sequence happens using a transactional connection during the course of the same transaction, you will not see this problem. The reason is the one mentioned above : until the transaction has been committed nothing is actually written to the Queue. Therefore the READ task is a no-op.

Coming and going

You will face the same problem if data is written using a transactional JMS connection, the transaction is committed and then you have an autocommit JMS connection that executes code that only has SQL comment.