This discussion is archived
6 Replies Latest reply: Sep 8, 2011 10:22 AM by SutirthaRoy RSS

cdc

GRK Journeyer
Currently Being Moderated
Hi,

My Source: Views created in Schema 'A' (Based on different tables in Schema 'A')
Target: Tables Created in Schema 'B'

Both Source and Target: Oracle DB
ODI - 10g.

I was able to move data from source to target. I used LKM: Sql to oracle and IKM: Oracle Incremental update, Sql Incremental update based on interfaces.

I was able to update records and insert new records.

But, I couldn't capture deleted records on source (i.e. from views ) and delete the same on target ?
Is is possible by CDC technique available in ODI ? I heard that CDC cannot be applied on views (as jkm creates triggers ,drops triggers on tables) but applicable only if source is tables ?

If possible, what kind of jkm can be used ? Priveleges required ?

Please, guide me.

Thank you.
  • 1. Re: cdc
    SutirthaRoy Guru
    Currently Being Moderated
    >
    But, I couldn't capture deleted records on source (i.e. from views ) and delete the same on target ?
    Is is possible by CDC technique available in ODI ? I heard that CDC cannot be applied on views (as jkm creates triggers ,drops triggers on tables) but applicable only if source is tables ?
    Normally speaking you can apply CDC on a view , but only after customizing the JKM.
    Copy the JKM say JKM Oracle Simple , this is to make sure that you have a backup to fall back on.
    Now go to the step named "Create trigger"
    You will find the code as
    create or replace trigger <%=odiRef.getJrnInfo("JRN_FULL_TRIGGER")%> after insert or update or delete on ...

    change it to

    create or replace INSTEAD OF trigger <%=odiRef.getJrnInfo("JRN_FULL_TRIGGER")%> after insert or update or delete on ...

    In theory it should work , so test it and update about the outcome .

    Thanks,
    Sutirtha
  • 2. Re: cdc
    GRK Journeyer
    Currently Being Moderated
    Sure, i shall
  • 3. Re: cdc
    GRK Journeyer
    Currently Being Moderated
    @Sutirtha Roy

    Before trying on source (views) i tried to implement cdc technique on source(tables).

    My source: Oracle tables in schema A (oracle 11g)
    My target: Oracle tables in schema B.

    ODI version - 10.1.3.5.0

    My mapping was working fine. But, in order to capture deleted records and implement the same. ( i started to work on cdc )

    case1:
    Created a registry file in C:\orahome\oracledi\lib\scripting and said - python.security.respectJavaAccessibility=false
    My Environment variable ODI_JAVA_HOME poitning to C:\programfiles\java\jdk1.6.0_20
    I imported jkm - oracle simple, edited my source model journalizing mode - simple, selected km oracle simple.
    I added the source table to - cdc and started journal ( it asked me to select the subscriber ) - selected default sunopsis - now session started.

    Now, getting following error -

    org.apache.bsf.BSFException: exception from Jython:
    Traceback (innermost last):
    File "<string>", line 36, in ?
    java.lang.IllegalAccessException: Class org.python.core.PyReflectedFunction can not access a member of class oracle.jdbc.driver.OraclePreparedStatementWrapper with modifiers "public"

         at sun.reflect.Reflection.ensureMemberAccess(Reflection.java:65)

         at java.lang.reflect.Method.invoke(Method.java:588)

         at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java)

         at org.python.core.PyMethod.__call__(PyMethod.java)

         at org.python.core.PyObject.__call__(PyObject.java)

         at org.python.core.PyInstance.invoke(PyInstance.java)

         at org.python.pycode._pyx6.f$0(<string>:36)

         at org.python.pycode._pyx6.call_function(<string>)

         at org.python.core.PyTableCode.call(PyTableCode.java)

         at org.python.core.PyCode.call(PyCode.java)

         at org.python.core.Py.runCode(Py.java)

         at org.python.core.Py.exec(Py.java)

         at org.python.util.PythonInterpreter.exec(PythonInterpreter.java)

         at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)

         at com.sunopsis.dwg.codeinterpretor.j.a(j.java)

         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java)

         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)

         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)

         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)

         at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java)

         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)

         at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)

         at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)

         at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)

         at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)

         at com.sunopsis.dwg.cmd.DwgCmd.k(DwgCmd.java)

         at com.sunopsis.dwg.cmd.f.z(f.java)

         at com.sunopsis.dwg.cmd.DwgCmd.run(DwgCmd.java)

         at java.lang.Thread.run(Thread.java:619)


    java.lang.IllegalAccessException: java.lang.IllegalAccessException: Class org.python.core.PyReflectedFunction can not access a member of class oracle.jdbc.driver.OraclePreparedStatementWrapper with modifiers "public"

         at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
         at com.sunopsis.dwg.codeinterpretor.j.a(j.java)
         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java)
         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)
         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)
         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
         at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java)
         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
         at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
         at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
         at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)
         at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
         at com.sunopsis.dwg.cmd.DwgCmd.k(DwgCmd.java)
         at com.sunopsis.dwg.cmd.f.z(f.java)
         at com.sunopsis.dwg.cmd.DwgCmd.run(DwgCmd.java)
         at java.lang.Thread.run(Thread.java:619)
  • 4. Re: cdc
    SutirthaRoy Guru
    Currently Being Moderated
    >
    case1:
    Created a registry file in C:\orahome\oracledi\lib\scripting and said - python.security.respectJavaAccessibility=false
    What is that registry file name ? Does it have any file extension like .txt ,.reg etc ?
    If yes then remove the file extension ... that file name should be just "registry"

    Thanks,
    Sutirtha
  • 5. Re: cdc
    GRK Journeyer
    Currently Being Moderated
    No, it doesn't have any extension.
  • 6. Re: cdc
    SutirthaRoy Guru
    Currently Being Moderated
    Hum .. not getting any idea .
    Better you contact oracle support people and see if they can provide any help .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points