Simple Change Data Capture from DB2 Table to Oracle Table
By Alex Wu on Aug 18, 2010
In 11.2 releases, Change Data Capture (CDC) can be done by code template mapping. This allows users to capture the data changes from heterogeneous data source, and load into the target across different platforms.
Here is an example that captures the data change in DB2 table DB2_DEPT, and then loads it into Oracle table DEPT. It would have expected that the deletion change can also be captured when the data source is from heterogeneous platform, then all insert/update/delete changes can be done in one simple mapping. However, due to some issue, the deletion change may not work as expected. And this example provides a solution to the CDC from DB2 table to Oracle table.
Assuming the table DB2 table DB2_DEPT is as follows:
The primary key of DB2_DEPT is column DEPTNO.
And the given Oracle table DEPT looks like below, whose primary key is column DEPT.
And now, let’s set up code template mappings to load the CDC from DB2_DEPT to Oracle table DEPT.
1. Create source and target modules respectively, and import source and target tables into OWB. After importing the table DEPT, let’s copy the table DEPT and paste it under the same Oracle module, and then rename it to DEPT$. This is to create a DEPT-like table DEPT$, which will be used to save the temporary deletion records from the source.
2. Set up the CDC for source table.
To capture the data change in DB2 database, we can use the built-in code template JCT_DB2_UDB_SIMPLE. And this can be configured from the DB2 module editor as follows:
First, select the table that will be captured. In this example, I choose the table DB2_DEPT, and shuttle it from the left to the right side.
And then, specify the code template JCT_DB2_UDB_SIMPLE to capture the change.
3. Start the CDC and subscribe the changes from the source table.
We can start the CDC by right-clicking the DB2 module.
When it’s done, we can begin to subscribe the changes from table DB2_DEPT. It will ask for the subscriber name before subscribing, and you can input any name as you like. I input the subscribe name as “SUNOPSIS” here.
4. Import the Journal table from source database
Now, we need to import the journal table. The journal table contains the changes for the source table, and is created automatically when starting the change data capture. By default, the journal table’s name begins with J$_, and then follows with the source table name. Thus, the journal table is J$_DB2_DEPT in this case. Let’s get table J$_DB2_DEPT into OWB now.
5. Create a procedure in the target module to clean up the temporary table DEPT$.
We also need to create a procedure to clean up the deletion records in the temporary table DEPT$ after CDC loading is executed. It can be created as simple as this:
6. Create code template mappings to load changed data
There are 3 mappings required to perform the loading of all kind of data changes.
a) Mapping to load deletion records to Oracle temporary table DEPT$.
Note that the filter condition above must look like:
INOUTGRP1.JRN_FLAG = 'D' And INOUTGRP1.JRN_SUBSCRIBER = 'SUNOPSIS'
This is to only select the deletion records with the subscribe name we defined previously.
b) Mapping to load insert/update records to Oracle target table DEPT.
This mapping is to load the insert/update change in the source to the target table directly. It’s required to enable the CDC and configure the CDC filter condition for the source table in the mapping. Since the subscribe name has been configured as “SUNOPSIS” in step 3, the CDC filter condition looks like:
INOUTGRP1.JRN_SUBSCRIBER = 'SUNOPSIS'
And for the target execution unit, we need to choose the insert/update code template to handle the insert/update manipulation, for example, ICT_ORACLE_INCR_UPD.
c) Mapping to synchronize the deletion records in the target table DEPT.
This mapping is to synchronize the deletion changes in the temporary table DEPT$ with the target table DEPT. After that, it also cleans up the records in DEPT$ through the post mapping operator CLEANUP_DEPT$, which is associated with the procedure CLEANUP_DEPT$. Note that the loading type for the target table DEPT is “DELETE” here.
Since it involves the Oracle-specified operator post mapping operator, the available code template for the execution unit is only DEFAULT_ORACLE_TARGET_CT.
7. Create a process flow to load the data automatically.
Of course, we can run above mappings following the sequence: MAP1 -> MAP2 -> MAP3. However, an alternative is to create a process flow to run above mappings automatically.
And now, all are set. Let’s deploy the procedure, mappings and process flow package. Then, any change in the source table will be captured and stored in the journal table. And when above process flow is executed, the change will be loaded into the target automatically.