Data Integration Tips: ODI 12c - Substitution API GUIDs
With the release of Oracle Data Integrator 12c and Oracle GoldenGate 12c still fresh in our minds, I thought I would start an ongoing series focused on Data Integration Tips - specifically around the latest version of Oracle's data integration tools. The goal is to provide insight into some of the hidden, undocumented, or lesser known features of these products. Most of these tips and tricks will come from our experiences when solving challenges for our clients. I'm sure some of my data integration colleagues at Rittman Mead will jump on board and share their knowledge as well.
The Scenario
While working with a client who is using ODI 12c, we were attempting to capture the session ID (known as the Batch ID) for each execution of a Load Plan. The process uses an ODI Variable, which, when refreshed, would capture the identifier from the following substitution API call. This variable would then be mapped to the audit dimension key column in each dimension mapping for use in ETL process auditing.
<%=odiRef.getLoadPlanInstance( "BATCH_ID" )%>
Much to my surprise, this code produced an error. Even more surprising, the ODI 12c documentation showed that I had the syntax and parameter value correctly defined for this API call. The error received was different than any I had seen before:
java.lang.Exception: The application script threw an exception: com.sunopsis.tools.core.exception.SnpsSimpleMessageException: Exception getLoadPlanInstance("BATCH_ID") : ### KEY com.sunopsis.res.gen / ODI-17769: OdiRef: Internal ID 5 of IObjects SnpLoadPlanInstance should not be used when the repository is 12c mode.### BSF info: null at line: 0 column: columnNo
The message states that this code "should not be used when the repository is in 12c mode". Hmm...something about this call is clearly not compatible with ODI 12c.
Here's the Tip...
As you may recall, ODI 11g used numeric IDs, made up of the object ID and the Work Repository ID, to uniquely identify objects stored in the repository. This had the potential for issues when migrating objects between repositories, should the repository ID be the same across environments. This led Oracle to change how objects are stored in Oracle Data Integrator 12c - switching from numeric IDs to GUIDs.
During my research of this error, I was reminded of this change and directed to the ODI 12c "Upgrading your environment" documentation. Throughout this document, the decision on which upgrade path to take depends on the use of "legacy numeric identifiers" in odiRef API calls versus the new GUID approach in ODI 12c. This is a hint that certain substitution API calls using identifiers are not going to work in a standard ODI 12c installation.
I was on the right track, but still hadn't found anything that looked like a solution. I reached out on Twitter, and once again Mr. David Allan of Oracle's Data Integration team had the "unwritten answer" to which the documentation was referring.
@mRainey you have to use odiRef.getLoadPlanInstance("BATCH_GUID") in #odi12c ... Guids(12c) versus Ids(11g)
— David Allan (@i_m_dave) March 25, 2014
Now we're getting somewhere. I made the change in the ODI variable call to use the GUID, and it worked as expected.
<%=odiRef.getLoadPlanInstance( "BATCH_GUID" )%>
Final thought - if you're using any "ID" parameter calls in your substitution API code, be sure to modify them to use GUIDs. And, as in this case, make sure you make the change to any logical and physical table attributes to handle the difference in datatypes (numeric vs varchar2).
More Data Integration Tips will be coming soon - I look forward to your feedback on the blog series.