Isolation Levels in ODI procedures
Hi,
I have a package with 1 interfaces and 2 procedures which I want to configure in as one transactional unit
The package executes the following sequence
1. Interface loads data from the source to target table
2. Procedure updates a statuscode on the source table to S
3. Procedure updates target table sequence number, but the procedure contains a where clause which filter records to updates
based on records in the source table with statuscode = S.
Step 3's execution is dependent on update in step 2. These steps behave as expected and all units tests pass when I
leave the transaction porperty of the procedures as AutoCommit.
However I want the interface and packages to be executed as a transcational unit so I configued them as follows.
The 2 procedures are configured to execute in the same Transaction (Transaction 0) with Commit property as "No Commit".
I based this on other forum entries.
With this configuration my unit tests seem to fail, the 3 step does not result in any record being updated.
I think the issue may be related to the isolation levels on the procedures, so I then configured the procedure's "Transaction Isolation" property to be "Read Uncommited".
When I ran the test in ODI I got the following error below.
Can you let me know if what I am trying to do is possible ?. Can you have a number of procedures in a single transactional unit,
where uncommited changes carried out in the first procedure(not committed), can be read in subsequent steps within that same transactional unit.
I am current using ODI Repository: Development_ODI_D8B4A_RC5
Any feedback would be appreciated.
Thanks,
Breda
ODI-1226: Step UPD REVN LSM fails after 1 attempt(s).
ODI-1232: Procedure UPD REVN LSM execution fails.
ODI-1228: Task UPD REVN LSM (Procedure) fails on the target ORACLE connection SALES_IMP_DATA_SERVER.
Caused By: java.sql.SQLException: ODI-10068: SnpsConnection: Isolation level not supported
at com.sunopsis.sql.SnpsConnection.setTransactionIsolation(SnpsConnection.java:905)
at com.sunopsis.sql.SnpsConnection.changeIsolationLevel(SnpsConnection.java:329)
at com.sunopsis.dwg.dbobj.SnpSession.changeIsolationLevel(SnpSession.java:3519)
at com.sunopsis.dwg.dbobj.SnpSessTask.getDefaultDwgConnectConnection(SnpSessTask.java:518)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.getExecutor(SnpSessTaskSql.java:2936)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2878)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2602)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:546)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:461)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1736)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1591)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:568)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:224)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:489)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1062)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:118)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$1.run(DefaultAgentTaskExecutor.java:49)
at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor.executeAgentTask(DefaultAgentTaskExecutor.java:41)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.doExecuteAgentTask(TaskExecutorAgentRequestProcessor.java:94)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.process(TaskExecutorAgentRequestProcessor.java:85)
at oracle.odi.runtime.agent.RuntimeAgent.startScenario(RuntimeAgent.java:700)
at oracle.odi.runtime.agent.InternalRuntimeAgent.startScenario(InternalRuntimeAgent.java:52)
at com.sunopsis.dwg.tools.StartScen.startScenOnLocalAgent(StartScen.java:983)
at com.sunopsis.dwg.tools.StartScen.actionExecute(StartScen.java:245)
at com.sunopsis.dwg.function.SnpsFunctionBaseRepositoryConnected.execute(SnpsFunctionBaseRepos
Edited by: user807308 on 14-Jan-2011 01:04
Edited by: user807308 on 14-Jan-2011 01:04