0 Replies Latest reply on Nov 14, 2019 7:03 PM by Ollie MS

    Synchronize Deletions From Journal not workin

    Ollie MS

      Hello,

      I've been trying to use CDC to synchronize two tables and I can only do it when there's new data on the source table, but not when I remove any row. I've selected the option to use journaled data only, the IKM that it's used by my target table is the IKM Oracle Control Append and from the Session List I noticed that the flow table is only filled with new values if it's an insert operation. If my j$ table has the JRN_FLAG set to "D", the I$.IND_UPDATE is never set to "D", and for that reason my code for deletion never works because it uses the IND_UPDATE flag:

       

      delete from ZZZ.REF_WS_REDE_ESCOLAR_ATIVA

      where exists (

                     select 'X'

                     from ZZZ.I$_REF_WS_REDE_ESCOLAR_ATIVA  I

                     where ZZZ.REF_WS_REDE_ESCOLAR_ATIVA.ID_ESCOLA = I.ID_ESCOLA

                     and IND_UPDATE = 'D'

      )

       

      This is only my assumption, but I might be wrong. I've read on other places that an edit to the "Synchronize Deletions From Journal" code should do it, but I feel that the problem has to do with the insertion in the flow table:

       

        insert /*+ <%=odiRef.getOption("HINT_INSERT_FLOW")%> */ into <%=odiRef.getTable("L","INT_NAME","A")%>

        (

                <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "","((INS and !TRG) and REW)")%>

        )

        <%for (int i=odiRef.getDataSetMin(); i <= odiRef.getDataSetMax(); i++){%>

        <%=odiRef.getDataSet(i, "Operator")%>

        select <%=odiRef.getOption("HINT_SELECT")%>

               <%=odiRef.getPop("DISTINCT_ROWS")%>

               <%=odiRef.getColList(i,"", "[EXPRESSION]", ",\n\t", "", "((INS and !TRG) and REW)")%>

        from <%=odiRef.getFrom(i)%>

        where <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %>

                JRN_FLAG <> 'D'

                <%} else {%>

                (1=1)

                <% } %>

        <%=odiRef.getJoin(i)%>

        <%=odiRef.getFilter(i)%>

        <%=odiRef.getJrnFilter(i)%>

        <%=odiRef.getGrpBy(i)%>

        <%=odiRef.getHaving(i)%>

        <%}%>

       

      I'm still trying to figure what the whole command does, but the statements in bold only seem to insert anything in the flow table if the JRN_FLAG is different than "D". That would explain my previous assumption, but I don't know how to correct it.

       

      Can you please help? I've been trying to figure this but I need some enlightenment. Thank you.