ODI - Change Data Capture and Journal Tables

Today we will look at usage/preference of Change Data Capture and Journal tables in ODI 

Journal Tables : Creating a journal tables have a copy/track  a table whether you insert the records or update the records the journal table itself have the copy of updated/inserted record.

Change Data Capture : Oracle Changed Data Capture Adapters offer log-based change data capture (CDC) for enterprise data sources such as CICS(Customer Information Control System -  Transactional server)  Captured changes are stored in a storage called Staging Area (which is different from the Oracle Data Integrator interfaces' staging areas).

Oracle Data Integrator supports two journalizing modes:

1. Simple Journalizing tracks changes in individual datastores in a model.
   
2. Consistent Set Journalizing tracks changes to a group of the model's datastores, taking into account the referential integrity between these datastores. The group of datastores journalized in this mode is called a Consistent Set.

Adding a table to CDC :

Open Designer Goto Model->Data Store -->Table -->Right click Change Data Capture .In the below scree i added employee table to CDC

Subscribing a table :

In the below you need to enter your subscriber name "Employee_Details" and click on Down arrow button click ok.

Session creation for the Subscriber :

Once you click on OK it will start a session you can refer whether your session is executed or not.Goto Operator there you can find log.

Details will be stored in SNP_CDC_SUBS table

Two triggers (DT$ & UT$) are created on the table that is going to be monitored

UT$Employee_Details & DT$Employee, one trigger will monitor for any updates or inserts and the other monitors deletions. Once a change occurs a table named J$ (J$Employee_details) table will be updated.


Inserted a record in  Employee you can see the J$DEmployee_Detail view has been updated.

Where JRN_Flag : I - Insert/Update.
                          D - Delete.

• JRN_FLAG: Flag indicating the type of change. It takes the value I for an inserted/updated record and D for a deleted record.
• JRN_SUBSCRIBER: Name of the Subscriber.
• JRN_DATE: Timestamp of the change

We can view the journalized data from the desinger itself Right click on table-->CDC-->Journal Data it will show all journalized data.


Note: Without having primary key you can't subscriber a table


Reference : 
http://john-goodwin.blogspot.com/2009/02/odi-series-essbaseplanning-automating.html
http://download.oracle.com/docs/cd/B10500_01/server.920/a96520/cdc.htm
http://gerardnico.com/wiki/dit/odi/cdc