Oracle Data Integrator : 184.108.40.206
Oracle SOA Suite : 220.127.116.11
for one of my Integrations , I am invoking a ODI scenario through the "ODIInvoke" webservice from SOA Suite. Inside my ODI project, I use multiple INTERFACES that use the Oracle IKM(Integration Knowledge Module) for performing MERGE operations on Target records when enriching it through the different steps.
There are possibilities that my ODI Scenario could be executing in Parallel (multiple ODI Sessions running at the same time) due to multiple calls from SOA Suite when records from Different source systems arrive.
The different ODI sessions enter into a race condition while the IKM tries to drop / recreate the E$ and I$ tables and hence the other sessions which are executing a step on the IKM after table creation error out.
I have two options.
1. Modify IKM to stop it from dropping the E$ and I$ tables and include ODI_SESSION_ID as one of the columns during DML statements
2. Modify IKM to
- Make the IKM use E$_<SESSION_ID> and I$_<SESSION_ID> as the prefix for the run time tables so that they operate on different set of tables altogether (But the tables grow in number)
Please guide me if there is any IKM out of the box from ODI , which can handle this parallelisation and partition by ODI_SESSION_ID instead of me having to touch existing KMs.
why not just going simple?
i.e. add a step at the beginning and at the end to create and clean-up a blocker-flag i.e. by using a RDBMS-Table and inserting a row or by simply creating a text file using OdiOSCommand (if wanted you could i.e. put your session_ID into the above textfile for better troubleshooting)
and make use of OdiFileWait (or OdiWaitForData in case of an RDBMS-Table) to make sure only one of those jobs is running at any given moment.
I wish ODI can handle such kind of thing by its own but its not. I am expecting that ODI (in coming versions) should detect if one scenario us running simultaneously in multiple sessions then it should itself takecare of the temp tables. As of now appending session id is the only option i guess.For this you have to modify the KMs. :(
Instead of appending the complete session id you can append the last 5 digit only.This should work fine.