ODI JMS driver IKMs in 12c

IKM...LKM...knowledge!

ODI has the concept of knowledge modules that encapsulate and re-use the heavy lifting logic required for data movement. There are several KMs authored targeting different data sources. A big shift occurred in the world of ODI data flow control by the introduction of flow-based mapping in 12c. This component obviated many of the limitations imposed by ODI Interfaces in pre-12c days. 12c also introduced the concept of component KMs and global KMs. When desinging a mapping, most of the time an appropriate component KM gets chosen by default - whether it be for loading or integration.

JMS....JMS..wherefore art thou JMS

A pretty significant gotcha crept in for the particular case of JMS or JMS XML as target. If you design a mapping with a datastore from these technology types as target then you know that for the data to be actually sent to the JMS Queue/Topic you need to choose the JMS-specific IKM. Here lies the catch. Although you may have imported the IKM into your project when you go to the target datastore configuration to set the IKM you may not find it there. If this happens to you check the LKM that is being used. The JMS-specific IKMs are designated as multi-connect KMs and in ODI 12c if you want to use a multi-connect IKM then your LKM must also be multi-connect.
So the solution is to switch your LKM to one of the multi-connect component KMs or template KMs.

If you see similar symptoms with other KM combinations remember this : check your KM to see if they are multi-connect.

ODI JMS (XML) data server peculiarities

ODI JMS data servers

ODI has JMS Queue, JMS Topic, JMS XML Queue and JMS XML Topic drivers. The Queue/Topic drivers are slightly different from XML Queue/Topic drivers. Of course the obvious difference is that the former is for Delimited/Fixed width message types and the latter is for XML message types. In addition to this there is one more subtle difference.

Go on and create a new JMS Queue data server. Give it some name and then switch to the JNDI tab. You will see drop-down for authentication type, text fields for user name and password, drop-down for JNDI protocol, text fields for JNDI Driver (the fully qualified name of the InitialContextFactory class for your JNDI server), JNDI URL and JNDI Resource.

Gotcha 101

 Many people mistakenly think that JNDI Resource is where you put the JNDI name of your Queue/Topic. This is wrong. This field is for entering the JNDI name of your JMS connection factory. It can be a custom connection factory created on the JMS server by you or an in-built one for the JMS server. If you are using Weblogic you will have the following connection factories available without any configuration : javax.jms.QueueConnectionFactory, javax.jms.TopicConnectionFactory, weblogic.jms.ConnectionFactor and weblogic.jms.XAConnectionFactory. This concludes all the configuration you can do the data server.

Gotcha 102

Did you notice anything funny? Even on the Physical or Logical schema you have no place to provide the name of JMS Queue/Topic. For this you actually have to jump through another hoop. It will be explained below. But first think about the impact of this limited configuration. You have provided JNDI URL to your JNDI server, credentials, authentication mechanism and connection factory, but no Queue/Topic information. This means that when you press that nice helpful 'Test connection' button all it is testing is creation of the InitialContext object. It does not test actual connectivity to the Queue/Topic.

Now let us say you want to connect to your Queue. Create a Model baased on the Logical Schema of your JMS Queue data server. Again, you cannot reverse engineer a JMS Queue/Topic. So you have two options :

1. create a Datastore under this model and define the column structure by hand or
2. make a copy of a Delimited/Fixed width File Datastore

 After the Datastore has been created there is one more thing to do : there is a text field on the Definition tab of the Datastore named 'Resource name'. For a File Datastore this would point to the actual file. This is where you enter the JNDI name of your JMS Queue/Topic.

Phew! That was a tortuous setup.

What about JMS XML Queue/Topic? Now that has some differences. For example the JNDI URL takes a key-value pair where the key is JMS_DESTINATION and the value is the JNDI name of your Queue/Topic. So all the JMS configuration is in one place. Also, a JMS XML Model can be reverse engineered.
You may now be saying to yourself 'All right!!!!!! Let me make all my JMS Queue/Topics XML-based messages'. Hold your horses, because there is a down side : you cannot right-click a JMS XML Queue/Topic Datastore and do 'Data' or 'View data'. It is not supported.

So there you are. A little the worse for wear owing to this trip over ODI JMS. Wait for the next few blog posts that will reveal to you some more hidden pitfalls and capabilities.

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.