Simple Change Data Capture from DB2 Table to Oracle Table

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:

image 

The primary key of DB2_DEPT is column DEPTNO.

And the given Oracle table DEPT looks like below, whose primary key is column DEPT.

image

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.

image

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.

image

And then, specify the code template JCT_DB2_UDB_SIMPLE to capture the change.

image

3. Start the CDC and subscribe the changes from the source table.

We can start the CDC by right-clicking the DB2 module.

image

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.

image

image

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:

image

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$.

image

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.

image

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'

image

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.

image

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.

image

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.

Comments:

Nice article. Could you elaborate a bit on JCT_DB2_UDB_SIMPLE and the journal table J$_DB2_DEPT? Are journal tables a feature of DB2 to facilitate auditing? (I'm not familiar with DB2). What if the source db has no such feature?

Posted by Ilias on August 19, 2010 at 04:47 AM PDT #

JCT_DB2_UDB_SIMPLE is a built-in Code Template(CT) of OWB, which is used to create the infrastructure required for simple Change Data Capture on IBM DB2 UDB tables using triggers. Journal table J$_DB2_DEPT is one of tables/views created by OWB when deploying the JCT_DB2_UDB_SIMPLE, not a feature of DB2.

Posted by Alex on August 19, 2010 at 11:52 AM PDT #

Thanks, I guess I was totally off the mark. In future articles can you make the screen shots bigger because they are very hard to read...

Posted by Ilias on August 19, 2010 at 03:18 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today