CHANGE CAPTURE Stage in Datastage

In this article we are going to study ‘Change Capture’ stage in Datastage. It is a Processing Stage. As name suggests it captures the change between two input data by comparing them based on key column.
The two input links are linked with Change Capture stage by the two default link names i.e. ‘Before’ and ’After’. This captured change is mentioned in output in the form of code in separate column i.e.
0 = If the data is Copied as it is from ‘Before’ Link to ‘After’ Link.
1 = If the data is newly Inserted in ‘After’ link
2 = If the data is Deleted from ‘Before’ link
3 = If the data is Edited in ‘After’ link from ‘Before’ link
Let’s see working of this stage with the help of following example.
Consider following two tables which we are going to use as ‘Before’ and ‘After’ .
Before Link Data

e_id	e_name	e_add
11	John	Singapore
22	Jack	Melbourne
33	Jessy	Sydney
44	Lampard	canada
55	Pomerbachk	perth

After Link Data

e_id	e_name	e_add
11	John	Odisha
22	Jack	Melbourne
44	Lampard	canada
55	Pomerbachk	perth
66	Rahul	Mumbai

Step #1: Design your job structure like below

Configure above tables with respective input sequential files i.e. ‘After’ table with sf_after_data and ‘Before’ table with sf_before_data as shown.
Step #2: Double click on Change Capture stage, following window will pop up.

Here select ‘key =e_id’ for sorting purpose under ‘Change Keys’ as shown.
Now, next is the most important point in the discussion.
‘Change Values’ is the column name which is taken into the consideration for capturing the change. Here, Select ‘Value =e_add ’. According to the values in column ‘e_add’ , Change Capture stage decides particular data is copied, edited or inserted or not.
‘Change Mode’ is the ‘Option’ which helps you to define keys & Values explicitly or implicitly.
Step #3: For simplicity purpose I have mentioned Before and After links with respective Before and After data so that it will be easy to recognize.
Mention this links under Link Ordering tab as shown below.

Step #4: Do the output mapping under Mapping tab. The new column named change_code() is available for mapping which will be show the data is edited, copied, deleted or inserted.

Step #5: Compile and Run the job.  
Double click on Dataset output file ds_capture_change and click on View Output. Following window will pop up.

It shows output with additional column named Change_Code which shows values 0,1,2 and 3 depending on data deleted, inserted, copied or edited.
For e_id  22 is copied as it is from Before to After so its code is 0.
For e_id  66, new record is inserted in After so its code is 1.
For e_id 33, record is deleted from Before so its code is 2.
For e_id  11,44 and 55, e_add values are edited so its code is 3.

In this way we can use Change Capture stage for analysis purpose. Its more usefull when tjere is big amount of input data.