IBM Infosphere Change Data Capture

synsog · Post by **synsog** » Fri Sep 21, 2012 11:46 am

How to build "User exits for InfoSphere CDC" ? My requirement is I have source table which is having 100 columns, I dont not want all the columns to be mirrored and more over I want a user-defined SQL select query to define the columns to get mirrored.

ArndW · Post by **ArndW** » Sun Sep 23, 2012 10:24 am

What do you mean by "mirrored" in this context with CDC?

vmcburney · Post by **vmcburney** » Sun Sep 23, 2012 11:17 pm

You do not need a user exit for this. CDC has a GUI interface for adding column filters. When you set up a table subscription from SQL Server you can use the "Filter Columns" option to prevent some columns from being replicated. You just check the boxes of the columns you want to hide from replication. If you want to remove columns from an existing subscription just open up the column mapping and click on the filter tab.

It's hard to include user-defined SQL in InfoSphere CDC since it does not use a SQL interface - it mirrors data from the logs.

If you are using InfoSphere CDC with DataStage you follow the same steps - set up the subscription with the columns filtered and then generate a DataStage table definition for that subscription.

chulett · Post by **chulett** » Mon Sep 24, 2012 6:56 am

Isn't that basically how any subscription based CDC process works? I haven't been on the receiving end of every one on the planet but that's what I recall from the ones I've seen.

synsog · Post by **synsog** » Mon Sep 24, 2012 7:32 am

ArndW wrote:What do you mean by "mirrored" in this context with CDC?

Mirrored - Setting up/ Identifying the column for CDC to capture the changes on this column.

By default, if we create a subscription, we need to sepcify in CDC that , if any change is occured on a specific column then create a record for that change. This process is called Mirroring in CDC methodology.

Thanks,
Sripathy

synsog · Post by **synsog** » Mon Sep 24, 2012 7:39 am

I appreciate your reply. Yes, be default, we are processing as per the guidelines/blueprint of the IBM CDC. We are setting up the subscription and doing the filter options. And more over, I create a user exit function in java to handle special characters in source column as well. Now the requrierment is that, if suppose, in a table, i have 100 columns, i created the subscription, then CDC will genereate rows if any change is happened in any column and CDC runs 24/7 ( on Mirror Continious Mode ). But if at Source Data base, if any CR is happened like Addition of Column/ Deletion of Column etc, then CDC fails, since there is change in Meta-data. Now, I dont bother if any new additions are happen, since i dont want those columns to be mirrored. There is no option in CDC to handle this situation, we opend a PMR with IBM, their response is use UserExit function, but without any given example. This means, we can include a "Select" query choosing our own columns for Mirroring. Hope it clears my requirement.

chulett · Post by **chulett** » Mon Sep 24, 2012 8:02 am

Ask for an example.

vmcburney · Post by **vmcburney** » Mon Sep 24, 2012 5:01 pm

I would think that the removal of renaming of columns in a source database would be rare and that adding columns would be more common. In a major application upgrade there could be any number of changes to a source schema which you could track if you using Metadata Asset Manager and Metadata Workbench, assuming you have an Information Server 8.7 installed.

1) Baseline your source system schema by importing the database catalog via Metadata Asset Manager.
2) Import your InfoSphere CDC subscriptions via Metadata Asset Manager.
3) Run metadata stitching in Metadata Workbench to ensure CDC subscriptions are bound to database tables and columns.
4) Import new source system database schemas in Metadata Asset Manager to report on table and column changes the impact they have on CDC subscriptions.

If source system changes come in through a dev, test and prod deployment approach there should be plenty of advance warning of database changes. If this were Oracle to Oracle mirroring you could use DDL replication but I don't think they have that for SQL Server yet.