Changed Data Capture as the term implies is used to capture the data that is inserted, updated and deleted at the source side and replicating the same at the target. ODI have a Journalizing KM to do the required implementation and it is basically into two types simple and consistent.
Simple is used for CDC implementation on a single table and Consistent on Multiple tables or Model.
In this post i will be covering Simple CDC concept . Look for my future post for Consistent based CDC implementation.
Simple CDC – Oracle Table
I am going to implement the Simple CDC concept on this particular table – SRC_AGE_GROUP.
Step 1 – Adding the Table to CDC
Step 2 – Starting the journalizing
Select the user as Subscriber
Execute it
The journalizing process is established.
In the above execution ODI creates a Subscriber table in the work schema, J$ table, view and a trigger to capture any data changes when any Insert, Update and Deletion takes places.
After successful execution you will be able to see a small green clock icon.
Let’s do a simple mapping and test for CDC.
Drag the Journalized Table as source and the required target. On the Source Data store, check mark the option “ JOURNALIZED DATA ONLY “ and ODI will automatically add a filter with a required date condition and subscriber information, use the proper LKM and IKM as per your technology. For this Example I have used IKM Oracle Incremental Update.
I have run it once to check whether its working fine or not. Now let me show you an example.
Original Source
Modified source
I have removed the last column and change the third row age_max 39 to 35.
There are two ways of seeing Journalized Data.
On the Journalized Interface source and Right click on filter and click on data.
Here is the sql from where the journalized data is being retrived
select * from ODI_TEMP.JV$DSRC_AGE_GROUP SRC_AGE_GROUP where (JRN_SUBSCRIBER = ‘SUNOPSIS’ /* AND JRN_DATE < sysdate */)
The Other way is to right click on Journalized data store in Model and Changed Data capture – > Journal Data and you will see the above data again too.
Let’s capture the Journalized data to my Target table.
Target Table Before running the Interface
As you can see, one Update and one Deletes.
Target Table after running the Interface
MSSQL – SIMPLE
Repeat the above steps for Journalizing SQL Server table as we did for Oracle namely
Add to CDC
Start Journal
And if everything is start the journalizing will start.
Source Data before Running the Journalized Interface
delete from dimtime_backup where TimeKey=6;
update dimtime_backup set DayNumberOfWeek=4 where TimeKey=4;
Journalized Data
Although there is update and Delete on the source but there is just delete in the ODI reason being I have update the same value DayNumberOfWeek=4 again for update so ODI have correctly did only the Delete as the target already had the same value which is DayNumberOfWeek=4
How to say whether you have which JKM you have , right click on your model , Edit – >Journalizing Tab and you see what JKM is being used and whether Simple or Consistent
December 12, 2019 at 9:13 PM
How can we implement CDC on the Views. I have source as view and we want to implement CDC on it.
May 14, 2019 at 5:19 PM
Setting up Simple CDC based on the steps outlined above. However I get the JRN_FLAG error. Can you please help?
January 24, 2017 at 5:59 AM
Hello,
So far the best tutorial I have found on Changed Data Capture. What about Consistent based CDC implementation?
November 4, 2016 at 7:08 AM
HI your tutorial is awesome, but i have a doubt , my project is based on ebs to drmdb integration using odi, now when i extract & transformed from ebs(source) to drmdb(target) and used that data in hyperion budgeting report,,, i have a doubt if the data is updated in ebs in future , does i will have that change in target as well or not???? or again i have repeat the reverse engineer process for that??????
November 4, 2016 at 11:49 AM
Hi,
any change of data is captured thru CDC so it’ll perform the update automatically. You only need to execute a reverse if any table structure change…
November 29, 2016 at 10:01 AM
thanks i have done it, its executed successfully. !!!!!! i got a clear idea now.. thank you so much cezar 🙂
November 25, 2015 at 9:11 AM
Hi Team,
When i am trying to implement CDC in ODI 11g, after performing Add to CDC and Starting Journal on source system. on source table, property inspector Journalized data only check box is greyed-out. Can you please let me know on this.
Thanks
Sukumar
November 29, 2016 at 9:54 AM
its because in model, you would have not moved to journalizing tab and imported particular JKM(Simple or Consistent), move to designer tab -> particular model -> open -> move to Journalizing -> then import JKM-> select your particular Interface -> SAVE it. Then move to interface -> mapping , click on source tab -> then open property inspector -> JKM only option will be visible.
November 2, 2015 at 11:43 AM
Can I capture the old value in the interface, let me know if possible
October 8, 2015 at 2:49 PM
Hi Cezar… Perfect Article! Tks!
July 20, 2015 at 6:26 AM
Hi,
Thanks for this post. It is informative and provides a good guide about how to implement CDC using JKM. However, I have a very basic question about the usage of JKM, which I would like to put forward.
Why do we need to use JKM for CDC, when the IKM-s (like IKM Oracle Incremental Update) already provide the means to capture changed data and thereby applying incremental logic out of the box.
Thanks in advance.
July 20, 2015 at 1:49 PM
Hi,
The IKM’s like Incremental update provides a way to insert or update the target table.
The CDC allows you to get from source, only records that get some change from source. If you don’t know, at source side, what are the altered records, you always need to bring the entire source table to compare.
Make sense?
March 9, 2015 at 2:36 PM
Hello there,
Thanks for the post!
I have a question:
How can I write the mapping once (ODI 12.1.3), and reuse it for first population and for reading the changes without need of checking – unchecking “Journalized Data only” within the mapping itself?
Is there a central way in an ODI Package (for instance) to tell ODI to use the Jv$ tables instead?
November 30, 2014 at 12:15 PM
Hi,
now i am creating Mapping in odi 12c using odi sdk 12c api,when i am creating a mapping class then i getting this type of errors..it’s shown below
MasterRepositoryDbInfo masterInfo = new MasterRepositoryDbInfo(Url, Driver, Master_User,Master_Pass.toCharArray(), new PoolingAttributes());
WorkRepositoryDbInfo workInfo = new WorkRepositoryDbInfo(WorkRep, new PoolingAttributes());
OdiInstance odiInstance=OdiInstance.createInstance(new OdiInstanceConfig(masterInfo,workInfo));
Authentication auth = odiInstance.getSecurityManager().createAuthentication(Odi_User,Odi_Pass.toCharArray());
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth);
ITransactionStatus trans = odiInstance.getTransactionManager().getTransaction(new DefaultTransactionDefinition());
// Find the folder
Collection fold = ((IOdiFolderFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiFolder.class)).findByName(Folder_Name);
for (Iterator it = fold.iterator(); it.hasNext();) {
folder = (OdiFolder) it.next();
}
// Find the Context
context = ((IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class)).findByCode(Context_Code);
OdiDataStore targetDatastore = ((IOdiDataStoreFinder)odiInstance.getTransactionalEntityManager().
getFinder(OdiDataStore.class)).findByName(target_datastore, target_model_name);
System.out.println(“Interface Creation Started for …”+ target_datastore);
// Creating a New Interface
Mapping intf = new Mapping(target_datastore,folder,context);
// Setting the above Context as the Optimization Context
intf.setDefaultContext(context);
// Reading the Source Data Store
// Find the Data store using the IOdiDataStoreFinder
int order=0;
for (int i=0 ;i<source_datastore.length ;i++) {
sourceDatastore = ((IOdiDataStoreFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).
findByName(source_datastore[i], source_model_name);
// Creating DataSet to automatically assign different
// Source Data store
// DataSet(OdiInterface pInterface, java.lang.String pName)
if (i == 0) {
dataset = intf.getDataSets().iterator().next();
dataset.setName("DATASET"+i);
} else {
dataset=new Dataset(intf,"DATASET"+i);
dataset.setOperator(operator[i-1]);
dataset.setOrder(order);
}
srcset = new SourceSet("SrcSet01",dataset);
sd=new SourceDataStore(dataset,false,sourceDatastore.getName().toString(),0,sourceDatastore);
srcset.addSourceDataStore(sd);
dataset.addSourceSet(srcset);
order+=10;
}
// Helper is to manipulate Odi interfaces in an
// interactive way
InteractiveInterfaceHelperWithActions helper = new InteractiveInterfaceHelperWithActions
(intf, odiInstance, odiInstance.getTransactionalEntityManager());
helper.performAction(new InterfaceActionSetTargetDataStore(
targetDatastore, new MappingMatchPolicyLazy(),
new AutoMappingComputerLazy(),
new AutoMappingComputerLazy(),
new TargetKeyChooserPrimaryKey()));
helper.performAction(new InterfaceActionOnTargetDataStoreComputeAutoMapping());
// Add the Filter
//helper.performAction(new InterfaceActionAddFilter(dataset, sd.getName(),ExecutionLocation.WORK));
// Start mapping the KM
// LKM
Collection lkm1 = ((IOdiLKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiLKM.class)).findByName(
LKM,Project_Code);
for (Iterator iterator = lkm1.iterator(); iterator.hasNext();) {
OdiLKM odiLKM = (OdiLKM) iterator.next();
helper.performAction(new InterfaceActionSetKM(odiLKM,srcset, KMType.LKM,new KMOptionRetainerHomonymy()));
// Fetching each option of the LKM
for (ProcedureOption c : odiLKM.getOptions()) {
helper.performAction(new InterfaceActionSetKMOptionValue(srcset, KMType.LKM,”DELETE_TEMPORARY_INDEXES”, true));
}
}
// IKM
// Find the IKM using the IOdiIKMFinder
Collection ikm1 = ((IOdiIKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiIKM.class)).findByName(
IKM,Project_Code);
for (Iterator iterator = ikm1.iterator(); iterator.hasNext();) {
OdiIKM odiIKM = (OdiIKM) iterator.next();
// Setting the IKM in the interface
helper.performAction(new InterfaceActionSetKM(odiIKM, intf.getTargetDataStore(),KMType.IKM, new KMOptionRetainerLazy()));
// Fetching each option of the IKM
for (ProcedureOption c : odiIKM.getOptions()) {
// Modifying the Options of the IKM in the
// Interface
helper.performAction(new InterfaceActionSetKMOptionValue(intf.getTargetDataStore(), KMType.IKM,
“FLOW_CONTROL”, false));
helper.performAction(new InterfaceActionSetKMOptionValue(intf.getTargetDataStore(), KMType.IKM,
“STATIC_CONTROL”, true));
}
}
// CKM
Collection ckm1 = ((IOdiCKMFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiCKM.class)).findByName(
CKM,Project_Code);
for (Iterator iterator = ckm1.iterator(); iterator.hasNext();) {
OdiCKM odiCKM = (OdiCKM) iterator.next();
helper.performAction(new InterfaceActionSetKM(odiCKM, intf.getTargetDataStore(),KMType.CKM, new KMOptionRetainerLazy()));
}
// Compute the Interface sourceset
try {helper.computeSourceSets();}
catch (InexistentMappingException e) {throw new OdiRuntimeException(e);}
// Persisting the Interface
// Called to inform the ODI persistence layer that this
// interface will be persisted
try {helper.preparePersist();}
catch (oracle.odi.interfaces.interactive.exceptions.OdiInterfaceNotReadyForPersistException e) {e.printStackTrace();}
odiInstance.getTransactionalEntityManager().persist(intf);
/*// Generating Scenarios
System.out.println(“Generating Scenario for ..”+ target_datastore );
IOdiScenarioGenerator gene = new OdiScenarioGeneratorImpl(odiInstance);
OdiScenario newScen = gene.generateScenario(intf,target_datastore, “001”);
odiInstance.getTransactionalEntityManager().persist(newScen);*/
// Finally close the Instance
odiInstance.getTransactionManager().commit(trans);
odiInstance.close();
System.out.println(“Process Completed”);
}
November 27, 2014 at 1:27 PM
Hi,
Anyone knows about how 2 write mapping code in odi 12c sdk using new odi 12c sdk api……
November 27, 2014 at 1:26 PM
how 2 change source datastore in odi 12c sdk
May 8, 2014 at 11:01 AM
Hi,
i am new to odi.,i followed the above shown steps to generate a CDC on my emp table.,but after selecting sunopsis as my user and clicking ok it is showing error that no KM are available.,but i imported the necessary JKM’s in to the odi. please help me.
ODI-15020: No JKM available this is the error displayed
thanks
May 8, 2014 at 2:17 PM
Hi,
did you select the JKM at the model?
After import the JKM, you need to select it in the model where you wish to start the CDC…
March 11, 2014 at 11:46 AM
Hi Experts,
How to create customized trigger(insert, update and delete) for the table instead of CDC trigger.
Thanks in advance…:)
Pingback: Chapter 6. Inside Knowledge Modules – SCD and CDC | Oracle-dba
December 18, 2013 at 9:12 AM
Hi Devendra,
i am just new to ODI functionality since i am only in to product Installation an ddeployment on Weblogic server.
here i wanted to know like before we perform the step go to model->Data Store->CDC->Add to CDC , don’t we need to add Journalizing KM to the project?
My source here is a Flatfile and target is Oracle RDBMS table. i am able to do an ETL and was trying simple CDC on it by chaning the source flatfile .
so i was wondering how to do that and which is the exact KM i need to import in this case?
October 21, 2013 at 7:01 AM
HI Devendra,
How to start journalization on view ?
Regards,
Rao
September 23, 2013 at 6:51 AM
Hi
thanks for your tutorial.
Now I want to create a procedure(p_date out date) with the global variable P_DATE,
which I can use in any interface.
how can I do it?
October 5, 2013 at 12:35 PM
Sorry for the late reply.
you can use the proecure or fun directly in the interface.
September 4, 2013 at 6:30 AM
HI,
please update.
Regards,
kumari
September 5, 2013 at 12:07 AM
Iam sorry I dint understand you
August 27, 2013 at 11:12 AM
HI ,
I am facing the error while start journalizaion
schema:Retuser
workschema:ODITEMP
and i have configured the topology with ODITEMP
Please provide the ODITEMP user permissions to start journalization.
Regards,
kumari
August 27, 2013 at 7:08 PM
what is the exact error
August 28, 2013 at 8:06 AM
HI,
while create trigger to start journalization the below is the error:
org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
File “”, line 49, in
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1110)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2251)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2192)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:347)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
java.sql.SQLSyntaxErrorException: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)
Caused by: Traceback (most recent call last):
File “”, line 49, in
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1110)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2251)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2192)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:347)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
java.sql.SQLSyntaxErrorException: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
at org.python.core.PyException.fillInStackTrace(PyException.java:70)
at java.lang.Throwable.(Throwable.java:181)
at java.lang.Exception.(Exception.java:29)
at java.lang.RuntimeException.(RuntimeException.java:32)
at org.python.core.PyException.(PyException.java:46)
at org.python.core.PyException.(PyException.java:43)
at org.python.core.Py.JavaError(Py.java:455)
at org.python.core.Py.JavaError(Py.java:448)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:177)
at org.python.core.PyObject.__call__(PyObject.java:355)
at org.python.core.PyMethod.__call__(PyMethod.java:215)
at org.python.core.PyMethod.instancemethod___call__(PyMethod.java:221)
at org.python.core.PyMethod.__call__(PyMethod.java:206)
at org.python.core.PyObject.__call__(PyObject.java:397)
at org.python.core.PyObject.__call__(PyObject.java:401)
at org.python.pycode._pyx2.f$0(:55)
at org.python.pycode._pyx2.call_function()
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1204)
at org.python.core.Py.exec(Py.java:1248)
at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
… 19 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1110)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2251)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2192)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:347)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:175)
… 33 more
and the code as below:
triggerCmd = “””
create or replace trigger ODITEMP.T$AMACCOUNTNO
after insert or update or delete on INGECS.AMACCOUNTNO
for each row
declare
V_FLAG VARCHAR(1);
V_ID NUMBER(19);
begin
if inserting then
V_ID := :new.ID;
V_FLAG := ‘I’;
end if;
if updating then
V_ID := :new.ID;
V_FLAG := ‘I’;
end if;
if deleting then
V_ID := :old.ID;
V_FLAG := ‘D’;
end if;
insert into ODITEMP.J$AMACCOUNTNO
(
JRN_SUBSCRIBER,
JRN_CONSUMED,
JRN_FLAG,
JRN_DATE,
ID
)
select JRN_SUBSCRIBER,
‘0’,
V_FLAG,
sysdate,
V_ID
from ODITEMP.SNP_SUBSCRIBERS
where JRN_TNAME = ‘INGECS.AMACCOUNTNO’
/* The following line can be uncommented for symetric replication */
/* and upper(USER) upper(‘ODITEMP’) */
;
end;
“””
# Create the statement
myStmt = myCon.createStatement()
# Execute the trigger creation
myStmt.execute(triggerCmd)
myStmt.close()
myStmt = None
# Commit, just in case
myCon.commit()
then which privilege do i need to provide to ODITEMP ?
Regards,
Kumari
August 29, 2013 at 1:33 AM
You need provide permission to the ODI user used in the topology so that it can perform the above operation.
August 29, 2013 at 7:38 AM
HI,
Topology user has create trigger privilege but its failing with insufficient privileges.
So,what i need give to execute the above query.
Regards,
Kumari
August 14, 2013 at 7:13 AM
Hi,
I am facing the below warning bcoz of this data is not loading to the target.
Please help .
ODI-1227: Task SrcSet0 (Loading) fails on the source ORACLE connection HDI.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3954)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1539)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:163)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1889)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:580)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:513)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1066)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:722)
August 14, 2013 at 2:04 PM
Please check to see if the ODI user used in Topology have the required select permission.
Pingback: Tutorial #44: Implement log based change data capture in ODI
August 18, 2011 at 11:55 PM
Hi,
SAP not recommend config its Database directly.
Anyone run CDC on the Database where the SAP resides ?
and are you successful without error on SAPGUI ?
Thanks
August 17, 2011 at 1:28 AM
Hi,
My source technology is SAP ABAP, where can I find JKM for this source ?
Anyone work with CDC for SAP source system?
Thanks for help
August 17, 2011 at 10:47 AM
I believe there is no CDC for SAP you can verify with Oracle if they provide it but you can run the CDC on the Database where the SAP resides.
Thanks
Kshitiz Devendra
March 23, 2011 at 5:04 AM
Hi Guys ,
One of my customer is facing an issue. The issue description provided by the customer is as below :
(We have odi database where all ODI packages and interfaces stored. Our source and target database is same. We have consistent journailizing. We are using ORALCE 10g consistent(LOGMINER) JKM. Our ODI database was down but source & target database was up. When ODI database was up and running again. We found CDC was not running. We lost over 2000 transactions in the process. how we can keep CDC running for source database tables)
The customer wants to recover the data lost.
Can you please provide your points on this.
Thanks,
March 14, 2011 at 11:26 AM
Gourav,
This is Yasar. I am still struggling with the same scenario as discussed and mentioned above by you. In addition to the two interfaces you mentioned, I made a third interface with only child table(jounalized). This performs deletes if any rows are deleted from child table. If i try using the interface in which child table is marked as journalized data only and joined to parent table it does not work. No records seemd to be removed from Target.
Now as per Cezar’s question and my scenario inserts and updates will always come out of this join as both these columns are not null. However dml operations (insert update) can happen on any column not only the columns used to join the two source tables.
I am not sure if creating three interfaces to move changed data to target is a good way to go. What is there are more than 10 source tables? We will need to create number of inerfaces.
March 10, 2011 at 6:14 AM
How to implement cdc when there are 2 source tbls to populate one trge tb
Please let me know if following are correct steps to implement what your mentioned
1. To change to consistant model ==> double click the moel and chage from simple to consistant mode
2. Add subscriber to the model
3. Select the two datastore that need to be jounalized. Add them to CDC by right clicking the datastore and then start journals.
4. Arrange the data store. Reference tabel (parent comes first). child(with foreign key comes second). This is done on model level. do we also need to change the numbe shows like 0 for parent and 1 for child
4. Create the interface.
5. Drag target tables. (contains columns from both tables and has inital load in it). perform mapping.
6. Drag both source tables perform join(cant leave one table as join is required). Check jounalize data only for one table. Create another interface and now mark other source table as journalize data only.
Call both inerfaces in Package and execute package.
Please correct if I am wrong
Thanks
March 10, 2011 at 8:21 AM
Hi Gourav,
Good to see you here, at ODI Experts!!!
A small question to try to help you…
Of course that if exists 2 source tables there is a join between them. My question is, will records inserted/updated in any of the tables always “come out” of this join?
Cezar
March 7, 2011 at 12:31 PM
Kshitiz,
Thanks for the reply but if you could explain more it would be helpful.
Thanks
March 4, 2011 at 4:59 PM
I tried CDC Consistent. Only DML on datastore marked as “journalized Data only’ gets updated/deleted in target.
If data gets updated/deleted in datastore not makred as ‘jounalized data only then nothing gets changed in Target.
so my question is
Can one interface manage DML operation performed on two source tables (Journalized – consistant set) and insert/Update/delete target accordingly.
Database 10G AND 11G (Source & Target)
LKM Oracle to Oracle DBLINK
IKM Oracle Incremental Update
JKM Oracle Oracle consistant
We can only mark one datastore as -Journalized Data Only- .
Appreciated
March 7, 2011 at 8:05 AM
Per interface you can have only one jornalized table but, in a package
it’s possible trigger the process from several tables.
Take a look into the tools from package related to data capture.
If we think about a single interface, we can notice that with more on
table as source, of course a join is necessary and, no matter what is
the jornalized table, only data that “respond” to the join will come
out from query.
In this way, using a external trigger that “hear” all tables, any
sequence of loading tables is possible.
Make any sense????
Answer from Cezar Santos
February 22, 2011 at 1:25 PM
How to you implement CDC when there are two source tables and one target.
In inerface we can check box “Journalize data only for one table” How will he changed data from other table be moved into target table.
Thanks
February 23, 2011 at 5:08 AM
Try CDC Consistent where tables are connected by PL/FK relationship
January 25, 2011 at 12:39 AM
I tried to setup “start Journal;” in Oracle DB 10G using ODI 11G.
I am getting the following error. Any help is appreciated.
Error:
ODI-1228: Task src (Journalizing) fails on the target ORACLE connection src.
Caused By: java.sql.SQLException: ORA-29532: Java call terminated by uncaught Java exception: java.lang.StringIndexOutOfBoundsException: String index out of range: 4
ORA-06512: at “SYS.DBMS_CDC_SUBSCRIBE”, line 12
ORA-06512: at “SYS.DBMS_CDC_SUBSCRIBE”, line 63
ORA-06512: at line 1
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1035)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:953)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1224)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3386)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3467)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java:665)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.executeUpdate(SnpSessTaskSql.java:3218)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java:1785)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java:2805)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2515)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:534)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:449)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1954)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:322)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:224)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:246)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:237)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:794)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:114)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:619)
January 24, 2011 at 11:20 AM
Hi,
I tried to set up journalizing as mentioned in this post but I am still having problems syncronizing deletes using SYNC_JRN_DELETE option.
I created a thread(http://forums.oracle.com/forums/thread.jspa?threadID=2160037&tstart=15) at odi forums but haven’t got workable solution for this.
Appreciate if experts can point me in the right direction.
Cheers,
Mandeep
November 16, 2010 at 11:10 PM
Hi Dev,
Thanks for the reply 🙂
Does ODIExperts site have steps to step up consistent CDC?
I was trying to find out but was unsuccessful!
November 17, 2010 at 6:13 AM
Presently None but we will write one sooner or later.
November 16, 2010 at 1:29 AM
Hi,
Will the interface run automatically as soon the changes are made in the source table?
Thanks,
Ritika
November 16, 2010 at 7:27 AM
ritika,
CDC make use of the source trigger to capture the changed record and it wont trigger the interface ,but when you trigger the interface or if you call it in the package and scheduler it accordingly it can capture the changed records and sync the target.
October 8, 2010 at 11:45 AM
That was helpfull.
I’ve achived a replication real-time using OdiWaitForLogData.
Thank you very much.
October 6, 2010 at 12:13 AM
Hi Cezar
Thanks for explaining so nicely.
I tried the steps that you mentioned and was successful in doing so.
Now, as per this tutorial, we are deleting the records manually from the table which is journalized. What if we need to automate the deletion.
Eg: I extract members from Essbase database into a database table. I apply journalizing on this DB table. Now whenever a new member gets added or any member gets updated, the journalized data shows records with JRN_FLAG as ‘I’. What if i delete any member from Essbase? When these members are extracted, journalized data should show records with JRN_FLAG as ‘D’. Is it possible?
September 28, 2010 at 10:45 AM
Hi Alberto…
there is few tools into package that are used for “wait” new CDC records…
Take a look in OdiWaitForLogData or OdiWaitForLogData for instance….
Does it help you?
September 27, 2010 at 4:09 PM
Hello there,
Thanks for the tutorial.
The following question: How should be configured a replication real time?, How can I achive an automatic replication without manually executing the Interfaz?
Thanks in advance.