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

56 Comments

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

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

Leave a Reply

Required fields are marked *.


This site uses Akismet to reduce spam. Learn how your comment data is processed.