Delete Change Code value while using Change Capture Stage.

[navistar]

Greetings Friends,

I am using a Change Capture stage to figure the changes between a Before dataset and After dataset.
For clarity purpose, let me show you the layout of the before and after datasets. (Both of them have the same column list, as they are extracts of the same table from different environments)

Before Dataset
Key-ColValue-Col1Value-Col2
K1; 1; A
K2; 2; B
K3 ;3; C



After Dataset
Key-Col Value-Col1 Value-Col2
K1;1;A
K2;2;F
K5;5;E

Say that I gave change codes for copy (Change-Code = 0) and inserts (Change-Code = 1) and deletes (Change Code = 2) and updates (Change-Code =3).

As you can see above, record with key K1 did not change, K2 had a change in Value-Col2, K3 got deleted and K5 is a new insert in the 'After' dataset. So I expected my change dataset to be as follows:

Change Dataset (Expected)
Key-Col:Value-Col1:Value-Col2:Change_Code
K1;1;A;0
K2;2;F;3
K3;NULL;NULL;2
K5;5;E;1

Change Dataset (Actual)
Key-Col:Value-Col1:Value-Col2:Change_Code
K1;1;A;0
K2;2;F;3
K3;3;C;2
K5;5;E;1


The problem I notice is that the records with a Delete change-code (2) have the values from 'Before' dataset. Since change capture stage outputs the 'After' data set values (I say this because in the in the Output tab I see the columns prefixed as 'After.'), I expected it to carry no information for the value columns 1 & 2 and just have the Key colum as 'K3' (See Highlighted row on Change Dataset (Expected) table.

Why would this happen? Can I get a change dataset as I exepcted above?

Additional Notes:
1. I have removed RPC from all links (set at a job level)
2. The inputs ot the Change Capture stage are Hash partitioned and sorted on the Key column
3. I specified Change Mode as'Explicit Keys & Values'
4. I set the Drop Output for Copy, Insert, Delete and Edit as False

I am trying to use the change dataset and merge it with the before dataset to create a change detect file which carries the accurate before and after information for archival and audit purposes. But because the records with Change-Code = 2 (Delete) retain the before column values, the change detect file would have the same information in 'Before' and 'After' columns. I hope I made my question clear!

Apologies for the formatting...I don't seem to get it right..

Thank you folks!
N

[kumar_s]

If you dont find the input in After (Delete) you get the data from Before Dataset.
Why dont u need that values? If required, use Transformer / Modify to Nullify by checking the code value for 2.

[kumar_s]

If you dont find the input in After (Delete) you get the data from Before Dataset.
Why dont u need that values? If required, use Transformer / Modify to Nullify by checking the code value for 2.

[navistar]

kumar_s: Thank you for the reply.

Why don't I need the values? - Because, as I mentioned in my original post, I am trying to create a Change Detect dataset by merging the Change Dataset from Change Capture Stage and the Before dataset records.

Can I make those Delete record columns NULL or empty? - Yes I can do that and I will go that route in case that is the only option.

Interestingly, say I exclude 'Value-Col2' from the list of values and just have the 'Value-Col1' as a value column, I get NULL for Value-COl2 in the Change Dataset record with the Delete Change-Code.

How could that be?

Thanks,
N

[kumar_s]

If you exclude the column, you wont get the value to be populated, and hence you get NULL.
After Datastage wont get the Key value as K3 unless it fetches that particular record set from After Dataset.
If you just need to identify the changed Key columns, you can do that using the Change code and Key column itself.