Change Data Capture

Change data capture as the name suggests the process to identify the change in source data whether the records inserted / updated or deleted from source systems.

Create a model in designer.

In designer tab, go to model and click on new model.

Create a model for source and target tables of oracle technology.

Save it.

Now reverse engineer for source and target tables.

Now go to journalizing option  as below,

Basically we have two types of journal modes.

1.     Simple

2.     Consistent.

When our tables have parent child relationships, then we choose consistent mode and km as jkm oracle consistent, otherwise simple mode and km as jkm oracle simple as above.

Add subscriber to implement CDC.

With this subscriber only the cdc will going to implement. 

 

Add the source table to CDC.

 

Yes. So, now we can see source table added to CDC

 

 

Start the journalizing  for that source table.

    

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.

 

Go to mappings>>new mapping

Drag the Journalized Table as source and the required target.

    

On the Source Data store, check mark the option “ JOURNALIZED DATA ONLY “ as below..

 

ODI will automatically add a filter with a required date condition and subscriber information.

    

I have run it once to check whether its working fine or not.

Backend process of cdc:  

Once CDC enabled you can see below table and views and trigger will be created in database.

J$ and JV$ and JV$D

Trigger Name : T$EMP__SRC_CDC

In my case tables was created below names.

1.     J$ EMP__SRC_CDC -- Table

2. JV$ EMP__SRC_CDC -- View

3. JV$ EMP__SRC_CDC – view 

The tables are created like below:

 

What does the table j$ do in backend ?

J$ tables contains the PK of the changed records in the table that is monitored for changes, along with the name of the subscriber for whom that change is recorded.

Ex:

We updated sal into sal+100 for empno 7369, now the j$table stores the pk of changed data along with subscriber as below.

 

 

Now we will discuss in detail for all the columns (J$)

JRN_SUBSRIBER :- Subscriber details will be store in this column.

JRN_CONSUMED:-

The JRN_CONSUMED is used to logically lock the records: when the records are inserted in the J$ table, the value is set to 0.

 

Note :- Simple CDC the lock/unlock operations are performed by the IKMs. When the IKMs lock the records, the value is changed to 1. The “unlock” process only purges records with a value equal to 1.

JRN_FLAG:-  The JRN_FLAG column indicates the type of change detected in the source system. Deleted records are

JRN_DATE:- Date and time of the change.

PK Column : Column x of the primary key (each column of the primary key of the source table is represented as a separate column in the J$ table)

What does the view in backend(JV$ view ):-  

The JV$ view is the view that is used in the mappings where you select the option Journalized data only. Records from the J$ tables are filtered so that only the following records are returned:

·   Only Locked records : JRN_CONSUMED=’1’

·   If the same PK appears multiple times, only the last entry for that PK (based on the JRN_DATE) is taken into account. Again the logic here is that we want to replicate values as they are currently in the source database. We are not interested in the history of intermediate values that could have existed.

What does the view in backend(JV$D view ):-

Similarly to the JV$ view, the JV$D view joins the J$ table with the source table on the primary key.  This view shows all changed records, locked or not, but applies the same filter on the JRN_DATE column so that only the last entry is taken into account when the same record has been modified multiple times since the last consumption cycle. It lists the changes for all subscribers.    

What does trigger will do in backend?

Trigger will check what type of changes happen in source table . Insert or update or delete . Based on type it will define JRN_FLAG in J$ table.

For Ex:- For your better understand have provided my trigger script.create or replace trigger SCOTT.T$EMP__SRC_CDC

            after insert or update or delete on SCOTT.EMP__SRC_CDC

            for each row

            declare

                        V_FLAG         VARCHAR(1);

                        V_EMPNO    NUMBER(4);

            begin

                        if inserting then

                                    V_EMPNO := :new.EMPNO;

                                    V_FLAG := 'I';

                        end if;

           

                        if updating then

                                    V_EMPNO := :new.EMPNO;

                                    V_FLAG := 'I';

                        end if;

           

                        if deleting then

                                    V_EMPNO := :old.EMPNO;

                                    V_FLAG := 'D';

                        end if;

           

                        insert into SCOTT.J$EMP__SRC_CDC

                        (

                                    JRN_SUBSCRIBER,

                                    JRN_CONSUMED,

                                    JRN_FLAG,

                                    JRN_DATE,

                                    EMPNO

                        )

                        select JRN_SUBSCRIBER,

                                    '0',

                                    V_FLAG,

                                    sysdate,

                                    V_EMPNO

                        from   SCOTT.SNP_SUBSCRIBERS

                        where            JRN_TNAME = 'SCOTT.EMP__SRC_CDC'

                        /* The following line can be uncommented for symetric replication */

                        /* and            upper(USER) <> upper('scott') */

                        ;

            end;