Change Data Capture

 Change data capture as the name suggest the process to identify the change in source data whether the records inserted/updated or deleted from the source systems. After identifying the change data & applying the same in targets.  We will explore here the complete process & objects required to setup the CDC in ODI 12c.

ODI Journalizing Modes:

Simple – Capture changes for individual data-stores in a model, does not support referential integrity between the data-store.

Consistent Set – It also support the referential integrity between data-stores. Group of datastores journalized in this mode is called ‘Consistent Set’.

Import JKM:

Import the Journalizing Knowledge Model – JKM Oracle Simple

Create the Model for your source data-store. Select the ‘Journalizing Mode‘ as simple & select the correct JKM from drop down. Refer below screenshot.

Reverse engineer your tables,

Make sure it has “PRIMARY KEY”.

Add Subscriber:

Subscriber: It is a application or process which consume the captured change data. When subscribers have consumed the captured changes, later on changes were discarded from the journals.

If you do not want to add any subscriber to your data-store, by default ‘SUNOPSIS‘ will be there.

 

Add to CDC:

You are adding your data-store to CDC process.

 

If you open your data-store, go to Journalizing tab. You will see a message that table is included in CDC. Also the clock sign will come on  your data-store.

 

Start Journal:

Start the journal, select the subscriber.  Select context & run. It will create all the objects using which change data can be captured in J$ tables.

Below objects created in database. Two views are created: the JV$ view and the JV$D view.

TABLES: J$ACCOUNTS_STAGE & SNP_SUBSCRIBERS

TRIGGER – T$ACCOUNTS_STAGE

VIEWS: JV$ACCOUNTS_STAGE & JV$DACCOUNTS_STAGE

Subscriber Table:

ACCOUNTS_STAGE Table registered as subscriber in SNP_SUBSCRIBERS.

 

J$ Table:

I have inserted 1 records & updated a record in my staging table EMP_SOURCE manually.

JRN_SUBSCRIBER

JRN_CONSUMED

JRN_FLAG

JRN_DATE

ACCOUNT_ID

Above records are inserted by the trigger T$EMP_SOURCE on EMP_SOURCE. 

Journalizing Filter in Mapping:

You can set your subscriber here in filter.

Journalizing Data Only:

Tick the ‘Check box’ for ‘Journalizing Data Only ‘.

JV$ view:

JV$EMP_SOURCE is used in the mappings where you select the option Journalized data only. This view is used to insert the records in I$ flow table.

Records from the J$ table (J$EMP_SOURCE) are filtered using condition JRN_CONSUMED=1. If you have multiple records for 1 key, then it will pick the latest entry of the that key using JRN_DATE.

JV$D View:

Data view is used to show the change data available for all subscribe in the J$ table without any consume filter, when you select the menu Journal Data from data-store.

I have inserted new 1 record & 1 updated in my staging table EMP_SOURCE manually. If you notice the JRN_FLAG is coming as I for update case also, code was present in trigger like that.

 

List of changed data if available: It is coming from JV$D view