Change Data Capture stage
Moderators: chulett, rschirm, roy
Change Data Capture stage
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
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
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
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
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
Try to trim with the following function -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
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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..rajkraj wrote:vkhandel,
The trim function which you have posted worked.
But i was wondering why isn't the normal Trim not working .
Thanks!
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......