ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Changed Data Capture ( CDC )

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

clip_image001

I am going to implement the Simple CDC concept on this particular table – SRC_AGE_GROUP.

clip_image001[1]

Step 1 – Adding the Table to CDC

clip_image002

clip_image003

Step 2 – Starting the journalizing

clip_image004

Select the user as Subscriber

clip_image005

Execute it

clip_image006

The journalizing process is established.

clip_image007

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.

clip_image008

Let’s do a simple mapping and test for CDC.

clip_image010

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

clip_image012

Modified source

clip_image014

I have removed the last column and change the third row age_max 39 to 35.

There are two ways of seeing Journalized Data.

clip_image015

On the Journalized Interface source and Right click on filter and click on data.

clip_image016

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.

clip_image017

Let’s capture the Journalized data to my Target table.

Target Table Before running the Interface

clip_image018

As you can see, one Update and one Deletes.

clip_image020

Target Table after running the Interface

clip_image021

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.

clip_image022

clip_image024

Source Data before Running the Journalized Interface

clip_image026

delete from dimtime_backup where TimeKey=6;
update dimtime_backup set DayNumberOfWeek=4 where TimeKey=4;

clip_image028

Journalized Data

clip_image030

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

clip_image032

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

clip_image033

clip_image034

53 Comments

  1. 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??????

    • 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…

  2. 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

    • 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.

  3. Can I capture the old value in the interface, let me know if possible

  4. Hi Cezar… Perfect Article! Tks!

  5. 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.

    • 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?

  6. 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?

  7. 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”);
    }

  8. Hi,

    Anyone knows about how 2 write mapping code in odi 12c sdk using new odi 12c sdk api……

  9. how 2 change source datastore in odi 12c sdk

  10. 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

    • 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…

  11. Hi Experts,

    How to create customized trigger(insert, update and delete) for the table instead of CDC trigger.

    Thanks in advance…:)

  12. Pingback: Chapter 6. Inside Knowledge Modules – SCD and CDC | Oracle-dba

  13. 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?

  14. HI Devendra,

    How to start journalization on view ?

    Regards,
    Rao

  15. 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?

  16. HI,

    please update.

    Regards,
    kumari

  17. 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

    • what is the exact error

      • 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

        • You need provide permission to the ODI user used in the topology so that it can perform the above operation.

          • 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

  18. 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)

  19. Pingback: Tutorial #44: Implement log based change data capture in ODI

  20. 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

  21. 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

  22. 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,

  23. 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.

  24. 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

    • 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

  25. Kshitiz,
    Thanks for the reply but if you could explain more it would be helpful.

    Thanks

  26. 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

    • 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

  27. 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

  28. 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)

  29. 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

  30. 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!

  31. Hi,
    Will the interface run automatically as soon the changes are made in the source table?
    Thanks,
    Ritika

    • 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.

  32. That was helpfull.
    I’ve achived a replication real-time using OdiWaitForLogData.

    Thank you very much.

  33. 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?

  34. 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?

  35. 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.

Leave a Reply

Required fields are marked *.