4 Replies Latest reply: May 15, 2013 8:47 AM by knayam247 RSS

    JKM Oracle Consistent (Update Date) CDC

    knayam247
      Hi all, I'm trying to implement CDC using the JKM Oracle Consistent (Update Date) for a rather straightforward interface (one source table and one target table). After I did a regular load to the target table (only one record), I set up Journalizing as specified below:


      1) Enable the model for Journalizing's Consistent Set mode. Selected the JKM Oracle Consistent (Update Date) and specified the column name (LAST_UPDATE_DT) from the source table for the UPDATE_DATE_COL_NAME option.
      2) Enable the source datastore them for CDC (Changed Data Capture -> Add to CDC)
      3) Start Journals for the datastore (Changed Data Capture ->Start Journal)
      4) Add a subscriber for the Journal (Changed Data Capture -> Subscriber-> Subscribe)
      5) Insert one new record in the source table with the appropriate timestamp for the LAST_UPDATE_DT
      6) Check Journal data and make sure the inserted record is there (Right click Datastore and Changed Data Capture and then Journal Data). I can see the record the Journal Data window.
      7) Create a copy of the interface above and check the Journalized Data Only to use the journaled data for the CDC load.

      Now I'm running this interface in simulation mode to see if the new record is picked up to be loaded and here's the issue. It's not. Weird considering it shows up in the Journal Data. So I checked the query that is executed to select new records and below is what I get, COLUMN1 being the PK of the source table:
      insert /*+ APPEND */  into SCHEMA.I$_TARGET
           (
           COLUMN1,
           COLUMN2
           ,IND_UPDATE
           )
      select       
           SOURCE_CDC.COLUMN1,,
           SOURCE_CDC.COLUMN2,
           JRN_FLAG IND_UPDATE
      from     SCHEMA.JV$SOURCE_CDC SOURCE_CDC
      where     (1=1)
      And JRN_SUBSCRIBER = 'SUNOPSIS' /* AND JRN_DATE < sysdate */ 
      Running this select statement doesn't show the new record. Back tracing and checking the definition of the J$ view, JV$SOURCE_CDC:
      CREATE OR REPLACE FORCE VIEW ETL_ASTG.JV$TRADER_CDC (JRN_FLAG, JRN_DATE, JRN_SUBSCRIBER, COLUMN1, COLUMN2) AS 
        select      
           decode(TARG.ROWID, null, 'D', 'I') JRN_FLAG,
           sysdate JRN_DATE, 
           JRN.COLUMN1, 
           JRN.COLUMN2
      from     
      (select JRN.COLUMN1 ,SUB.CDC_SUBSCRIBER, SUB.MAX_WINDOW_ID_INS, max(JRN.WINDOW_ID) WINDOW_ID
           from      SCHEMA.J$SOURCE_CDC    JRN,
                    SCHEMA.SNP_CDC_SUBS        SUB 
           where     SUB.CDC_SET_NAME     = 'MODEL_NAME'
           and      JRN.WINDOW_ID     > SUB.MIN_WINDOW_ID
           and       JRN.WINDOW_ID     <= SUB.MAX_WINDOW_ID_DEL
           group by     JRN.COLUMN1,SUB.CDC_SUBSCRIBER, SUB.MAX_WINDOW_ID_INS) JRN,
           SCHEMA.SOURCE_CDC TARG
      where JRN.COLUMN1     = TARG.COLUMN1(+)
      and not      (
                     TARG.ROWID is not null
                  and     JRN.WINDOW_ID > JRN.MAX_WINDOW_ID_INS
                  ); 
      I can tell the record is not being picked up because of the condition, JRN.WINDOW_ID <= SUB.MAX_WINDOW_ID_DEL. I'm not sure what this condition is doing but the JRN.WINDOW_ID=28, SUB.MIN_WINDOW_ID=26, and SUB.MAX_WINDOW_ID_DEL=27.

      Any ideas on how to get this working?
        • 1. Re: JKM Oracle Consistent (Update Date) CDC
          PeakIndicators_Alastair
          Hi,
          After your journal is started you need to set up a packge (or manually perform these steps on the Model in ODI) to do the following using ODI Tools :


          Extend Window (this resets the SCN numbers in the subscriber table you have found) ---> Lock Subscriber ---> (< Run interfaces>) ----> Unlock Subscriber ----> Purge Journal



          Its hidden in the docs here :
          http://docs.oracle.com/cd/E14571_01/integrate.1111/e12643/data_capture.htm#CFHIHJEE

          here is great guide I always refer people to which shows exactly how :

          http://soainfrastructure.blogspot.co.uk/2009/02/setting-up-oracle-data-integrator-odi_15.html


          The guide explains how to set up ODI to loop around and wait for more change data to occur (using ODIWaitForLogData) .
          Hope this helps
          Alastair
          • 2. Re: JKM Oracle Consistent (Update Date) CDC
            knayam247
            Thanks Alastair. It's showing up now.

            In the guide, it's showing how to have the process continuously running as you mentioned, and this is ultimately what we want as well. However, they are using JKM Oracle 10g Consistent (LOGMINER) which allows the use of OdiWaitForLogData to set up the loop. I'm curious if this tool would work with JKM using Update Date instead of logs as well?
            • 3. Re: JKM Oracle Consistent (Update Date) CDC
              PeakIndicators_Alastair
              Hello,
              I think it will work, there is still a 'Journal Data Store' in that sense, only the technology is different underneath.
              If that doesnt work, you could try reverse engineering the J$ objects and use ODIWaitForData as this works on views anyway,might need to jig the polling frequency and # rows but it should be OK.
              • 4. Re: JKM Oracle Consistent (Update Date) CDC
                knayam247
                Ok thanks for the tip. Will test both ways and see which one(s) work.