CDC Stage

kab123 · Post by **kab123** » Thu Nov 03, 2005 2:52 pm

Hi,

I am trying to compare history file to current file using CDC stage...
The jobs runs fine and could do CDC correctly..but i get some warnings on the other columns which are not keys..and change values
Warning: "CDC: When checking operator: Defaulting "P_PHONE" in transfer from "beforeRec" to "outputRec".

any clue what needs to be done..

Thanks in advance...

kab123 · Post by **kab123** » Fri Nov 04, 2005 10:01 am

I guess there are not many people using CDC stage...

ArndW · Post by **ArndW** » Fri Nov 04, 2005 11:04 am

Kab123,

there are a few who use CDC. Yours would be the first post that I see where no answer at all is forthcoming.

I get the same warning for all columns that are neither key nor used for CDC and think that this is just the way that things work, I can only get use a handler to demote the message to informational.

kab123 · Post by **kab123** » Fri Nov 04, 2005 12:15 pm

exactly..I supressed this message using message handler in dev ...
but this message handler I dont think will get migrated as well with code to production and I dont even have access to add that rule...I dont want to confuse them about this warning..so I thought I would get some suggestions from the forum...
looks like I have to follow the same in PROD too

ArndW · Post by **ArndW** » Fri Nov 04, 2005 12:42 pm

You could submit this to IBM/Ascential support and see if there is an alternative. We'd be curious to hear what the response it.

ameyvaidya · Post by **ameyvaidya** » Sun Nov 06, 2005 8:33 pm

Hi Kab123,
I think this is why one gets a warning when running CDC with columns other than:keys and values:

* The Target dataset is constructed with columns that are a union of the before and after columns in the dataset. Now there may be columns ini the after dataset that are not present in the Before dataset. There may also be non-key and non-value columns with the same name in both before and after datasets.

What happens in this scenario is that, When the CDC stage marks a row as deleted, the target dataset is populated with the before row.

1. Now columns exclusively present in the after row (which are a part of the union of the two) do not have corresponding before values. These are defaulted to the proper value for the data type of the column(null for nullable; "" for String, 0 for numerics) Hence the "Defaulting" type of warnings.

2. When a non-key and non-value column that is present in both the before and after datasets is encountered, the Before value is considered(row is deleted), the after value cannot be mapped to any target column and there is no way RCP can pass the column forward coz a column with the same name has already been mapped. So the "After" column is dropped. Hence the "Dropping" type of warnings.

Resolution:
Do not pass any non-key or non-value columns to the CDC. instead, use something like this:

Code: Select all

After------> Copy-------------------------->Join---|
                |        (Insert,Change,Copy)|     |->
                |                    |--------
                ---->CDC----->Switch--                Funnel--->
                |                    |--------
                |                  (Delete)   |    |-> 
Before-----> Copy-------------------------->Join---|

It sucks and destroys all performance but there are no warnings.

IHTH

vmcburney · Post by **vmcburney** » Sun Nov 06, 2005 9:40 pm

I would just ignore the warning and handle it is the message handler. Much easier then trying to split and merge the record down two paths. These warnings happen even if there is no defaulting going on. For example if you drop deletes you still get these warnings, even though the rows with defaulting values are not being written out. Also happens if no deletes are found. So it is largely meaningless unless you have designed your job poorly and try to send delete records down an update or insert path without realising they are there.

To migrate a message handler just copy the file from the MsgHandlers directory in the DataStage home folder to the target server. The details are stored in text files with an msh extension.

jamshid_kunhalu · Post by **jamshid_kunhalu** » Mon Nov 07, 2005 4:22 am

hi Kab

Even i faced some what similar problem...what i did is In CDC properties option

i changed Change Mode= Explicit Keys and All Values

before it was

Change Mode= Explicit Keys & Values..

it worked fine for me...i didnt need to handle the warnings after that....

Jamshid

ray.wurlod · Post by **ray.wurlod** » Mon Nov 07, 2005 6:35 am

kab123 wrote:I guess there are not many people using CDC stage...

Some of us sleep at night, or take the occasional weekend off! The downside is that now I've got nothing to add to what's already been posted on this thread!

chulett · Post by **chulett** » Mon Nov 07, 2005 7:19 am

ray.wurlod wrote:The downside is that now I've got nothing to add to what's already been posted on this thread!

Other than the fact that you've got nothing to add, that is.

kab123 · Post by **kab123** » Mon Nov 07, 2005 11:05 am

Thank you all for ur suggestions..
From the opinions above, I feel that message handler is the best option to stick too..