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

Job Design

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.

Configure Change Capture Stage 1


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 Modeis 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.

Link Ordering 1

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.

Output Maping 1

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.

Output

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.


11 Responses to “CHANGE CAPTURE Stage in Datastage”

  1. Tamara says:

    thanks you, information is very easy to understand

  2. Surendra says:

    check again for e_dd 44,55 and they should come out as copy records rather than Edited.

  3. siva says:

    good explanation of the concept.

  4. Sam says:

    Does the CHANGE CAPTURE Stage exist in Datastage 8.5?

    I have Datastage 8.5 and I don’t see the CHANGE CAPTURE Stage under “Processing”. Please advise. Thanks!

  5. praveen says:

    can any one explain the change mode option

  6. Mohammed Samy Hajji says:

    Helpfull. Thank you.

  7. Girish says:

    Hi,
    Can anyone explain about change apply stage with an example?

Leave a Reply

© 2017 Database ETL. All rights reserved.