Issue with Change Capture Stage

poorna_76 · Post by **poorna_76** » Tue Feb 20, 2007 8:41 am

I have data coming from Teradata table as Before DataSet.
I have data in a DataSet as After DataSet.
I am trying to do a change capture.

When I run the job for the first time, Before DataSet is empty, and the change_code is 1 and job gives me the records to be inserted.

The issue is, when I rerun the same job immediately without any changes, the change_code is 3 for all the records,indicating a change in the records.

What am I missing here?

I have specified change mode as Explicit Keys -All Values.
There is only 1 key column in my data and the rest of them are value columns.

Thanks in Advance.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 20, 2007 1:48 pm

What are you doing with the records downstream of the Change Capture stage? In particular, are any records loaded into the Teradata table (thereby to become a populated "before" data set)?

kumar_s · Post by **kumar_s** » Tue Feb 20, 2007 5:35 pm

I guess, its your output dataset points to the After Dataset as input, and hence it might be showing as Copy.

poorna_76 · Post by **poorna_76** » Wed Feb 21, 2007 9:22 pm

ray.wurlod wrote:What are you doing with the records downstream of the Change Capture stage? In particular, are any records loaded into the Teradata table (thereby to become a populated "before" data set)?

Ray,

I have a transformer after Change Capture Stage.
From the transformer i 2 links going out.
The first link with constraint "change_code = 1" for new records.
The second link with constraint "change_code = 3" for modified/update records.

I am merging these 2 links using a funnel stage and loading back(doing Upsert) to the same Teradata table.

The issue still remains the same.

Thanks in advance.

poorna_76 · Post by **poorna_76** » Wed Feb 21, 2007 9:26 pm

kumar_s wrote:I guess, its your output dataset points to the After Dataset as input, and hence it might be showing as Copy.

Kumar,

In my change Capture Change stage, i had specified the drop option to True for the copy.

So i should not be getting any records out of the Change Capture stage,
when the records are exact copies.

But still i am getting all the records back , with change_code = 3, indicating change in the records.

The issue still remains the same.

Thanks in Advance

balajisr · Post by **balajisr** » Wed Feb 21, 2007 11:40 pm

Is your metadata for the input of before and after datasets in the change capture stage same?
Are you doing any transformation in the transformer after the change capture stage?

kumar_s · Post by **kumar_s** » Wed Feb 21, 2007 11:49 pm

Hi Poorna,
What is the data in After DataSet? Is it the updated output or copy of merged output from funnel?
Do you have any date field from table getting generated at runtime during load or something??So that every time you get updated time, and the data found to be changed?

pavan007 · Post by **pavan007** » Mon Feb 26, 2007 3:33 pm

There must be some transformation happening after the output from transformer,check for the NULL values and how you are handling in before and after datasets.

vmcburney · Post by **vmcburney** » Mon Feb 26, 2007 4:46 pm

First isolate the problem. Remove a couple columns at a time and rerun the job until you identify the column or columns that is triggering the change. Examine the contents of that column via a DataStage View editor and, if you can, via a different viewer such as a SQL window. DataStage can add some funny null characters to fields, especially when you change CHAR to VARCHAR.

kumar_s · Post by **kumar_s** » Mon Feb 26, 2007 4:53 pm

Probably the other way, Varchar to Char.