In this blog we will see step by step how we can setup the change data capture (CDC)/journalizing in ODI 12c.
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’.
Step 1. 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 some key.
Step 2. 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.
Step 3. 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.
Step 4. 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 4 records in my staging table ACCOUNTS_STAGE manually. After commit I have noticed that 4 records also inserted in my J$ Tabe i.e. J$ACCOUNTS_STAGE with consume flag as 0 i.e. records not yet consumed. J$ Table J$ACCOUNTS_STAGE has only 5 columns, last one is the key of table.
- JRN_SUBSCRIBER
- JRN_CONSUMED
- JRN_FLAG
- JRN_DATE
- ACCOUNT_ID
Above records are inserted by the trigger T$ACCOUNTS_STAGE on ACCOUNTS_STAGE.
Step 5. Journalizing Filter in Mapping :
You can set your subscriber here in filter.
Step 6. Journalizing Data Only:
Tick the ‘Check box’ for ‘Journalizing Data Only‘.
JV$ view:
JV$ACCOUNTS_STAGE 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$ACCOUNTS_STAGE) 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.
select L.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, L.ACCOUNT_ID AS ACCOUNT_ID, max(L.JRN_DATE) AS JRN_DATE from DEV_SCHEMA.J$ACCOUNTS_STAGE L where L.JRN_CONSUMED = '1' group by L.JRN_SUBSCRIBER,L.ACCOUNT_ID
Locking of records in J$ table using below SQL update statements. When you start execution of mappings this is the first step.
update DEV_SCHEMA.J$ACCOUNTS_STAGE set JRN_CONSUMED = '1' where (1=1) AND JRN_SUBSCRIBER = 'SUNOPSIS'
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 ACCOUNTS_STAGE 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 (JV$DACCOUNTS_STAGE)
At the end of mapping steps there is one step which cleans journalized table.
delete from J$ACCOUNTS_STAGE where JRN_CONSUMED = '1' AND JRN_SUBSCRIBER = 'SUNOPSIS' ;
Thanks!
Happy Learning! Your feedback would be appreciated!
Follow @shobhitsinghIN
