Change Data Capture stage

rajkraj · Post by **rajkraj** » Thu May 01, 2008 1:36 pm

Hi,
I have requirement where i have to compare a
dataset data with data in DB2 table .

I am using a CDC stage to do this,I have the
following settings

Change Mode:Explicit Keys,All Values

DropOutput for copy=True
DropOutput for Delete=False
DropOutput for Edit=False
DropOutput for Insert=False

I have 3 keys ,i am sorting and hash partitioning them in the CDC stage.
I have checked the datatypes from both the before link(DB2) and after link(Dataset).
I have 12 other values which have to be compared.Most of the values are
varchar,(which i have trimmed) and 2 values are decimal.

I am testing this job with just one record which are same in both before and
after links.As the record is same i should not get any output from the
CDC stage,but i get one record with Change code 3(which means Edit).
keys are as below

key1 key2 Key3

A 1 .

I have put peek stages before the data is passed into CDC stage and see that the
data is same in both before and after links.The record is not supposed to come out of the
CDC stage.

Can some one guide me in this matter if i am missing some thing.

Thanks

rajkraj · Post by **rajkraj** » Fri May 02, 2008 6:40 am

Can some one shed some light on this.

Thanks

OddJob · Post by **OddJob** » Fri May 02, 2008 6:57 am

Can you post the director log entry for the peeks, before and after?

rajkraj · Post by **rajkraj** » Fri May 02, 2008 7:12 am

Peek1
Col1:X Col2:1 Col3:. Col4:ABC Col5:Address1 Col6: Col7: Col8: Col9: 0000000.00 Col10: 00000000.

Peek2
Col1:X Col2:1 Col3:. Col4:ABC Col5:Address1 Col6: Col7: Col8: Col9: 0000000.00 Col10: 00000000.

Col3 is of type Char(1) and the value in it is '.'
The Col6 ,Col7 and Col8 are empty strings('') of type Varchar.Col9(9,2) and Col10(8,0) are decimals.

I thought as Col6,Col7 and Col8 are '',it is not able to compare and tried replacing empty strings with some identical data on both before and after stages.
But no luck even then the record is coming out of CDC stage with Change code 3.

Thanks

rajkraj · Post by **rajkraj** » Fri May 02, 2008 9:39 am

Can some one give their expert views on this issue.

Thanks

keshav0307 · Post by **keshav0307** » Sat May 03, 2008 8:08 pm

never used DB2, but i was having this problem with teradata tables with varchar columns.
try pad char as ' '.
or add a transformer stage between the DB2 and CDC stage.

you can also try to extract the DB2 data into a flat file and then use the flat file in CDC.

rajkraj · Post by **rajkraj** » Sun May 04, 2008 6:31 pm

I already have a transformer between Db2 stage and a CDC stage,where in i am trimming the varchar fields.
But still there is problem.

Thanks

bikan · Post by **bikan** » Mon May 05, 2008 12:27 am

are you doing hash partitioning and sorting on boyh the links? Is the number of records on both link are same?

Minhajuddin · Post by **Minhajuddin** » Mon May 05, 2008 1:55 am

Dump the 'before' and 'after' data into two sequential files. Use a Hex-editor to compare the values of these, There has to be something different. Sometimes, things look the same to the naked eye but are not.

vkhandel · Post by **vkhandel** » Mon May 05, 2008 3:02 am

rajkraj wrote:I already have a transformer between Db2 stage and a CDC stage,where in i am trimming the varchar fields.
But still there is problem.

Thanks

Try to trim with the following function -
Left(Trim(col_name),Len(Trim(col_name)))

I have faced the problem, wherein trim was not working properly for the fields fetched from database.

rajkraj · Post by **rajkraj** » Tue May 06, 2008 1:22 pm

vkhandel,

The trim function which you have posted worked.
But i was wondering why isn't the normal Trim not working .

Thanks!

ray.wurlod · Post by **ray.wurlod** » Tue May 06, 2008 4:26 pm

What is the value of APT_STRING_PADCHAR environment variable?

rajkraj · Post by **rajkraj** » Wed May 07, 2008 7:51 am

I am not using that Environment variable in my job parameter though,Do you think still that might be the problem.

Thanks

ray.wurlod · Post by **ray.wurlod** » Wed May 07, 2008 3:42 pm

If it exists for your project then it may be.

vkhandel · Post by **vkhandel** » Thu May 08, 2008 12:09 am

rajkraj wrote:vkhandel,

The trim function which you have posted worked.
But i was wondering why isn't the normal Trim not working .

Thanks!

The reason which i understood is that the field is appended with a null character at the end '\0' instead of spaces... thats why the trim function is not able to truncate the spaces..
try using the select dump(column_name) from table_name command for the specific column, and check if it retruns the ASCII Value 32 in the last......