Change Capture Stage - Lookup Stage

Change Capture Stage is used to capture the changes between two sources based on the
change code.
Change Code is the Code generated when we capture the changes using Change Capture
Stage. They are four change codes available
0,1,2,3
0-------Copy (nessuna variazione)
1-------Insert (inserimento)
2-------Delete (record non presente in after)
3-------Edit (aggiornamento)
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
Lets 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
e_id
e_name
e_add
11
John
Odisha
22
Jack
Melbourne
44
Lampard
canada
55
Pomerbachk
perth
66
Rahul
Mumbai
After Link Data
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.
Lookup Stage
LOOK UP Stage in Datastage

8 months ago by admin Comments Off
Like Join and Merge stage, Look up stage has multiple input links, one is primary and others
are reference, according to which lookup operation takes place.
But it doesnt have condition like Merge stage i.e. Reject Links should be equal to update
input links also it not require data on any of the input links to be sorted. This stage provides
four conditions depend on which future of output data depends. We will see these conditions
in Step 4.
Now, lets try to implement Look up stage with the help of following tables.
Table 1
ID
First
Name
Last Name Location Network

ID
Jach
Simmons
Chicago
JS524
Letsc@gmail.com
Shumas
Jane
LA
Sj145
Jaene@ymail.com
Jonty
Waughn
Sydney
JW927
JontyW@sdbh.com
Suhana
Safar
Maxico
SS99
Sas@gmail.com
Table 2
ID
Dept
Dept Head
Electronics
Paul
EmailID
CS
Jack
TS
Summur
IT
Sean
Table 3
ID
Training Cent
CKG
AMD
WC
Step 1 : Design a job structure like below.
Consider Employee table as Primary link as shown. Depending upon each record in Primary link,
Look Up Stage performs look up operation on Reference Link according with key column.
Consider Employees department detail i.e. table 2 and Employees Training Center detail i.e. Table
3 as data on two reference links.
Step 2 : Now we are going to configure Look Up Stage (Named as lkp_emp_det in design). Double
click on Look Up Stage. Following window will pop up. Left pane is for all inputs and right pane is
for output. First link detail table is for Primary link. Second and third are for reference links. Order
of these reference links can be changed by using
this symbol on Title Bar as shown.
Step 3 : In left pane, map Key column (here ID) by just dragging it to the respective key column
in reference links. Map all remaining required column to right pane as shown.
Step 4 : One of the most important step is to set look up conditions which we can do by using 2nd
option on Title bar. Just click on it, following window will pop up.
There is a list of reference links in Link Name column. In Condition column we can give
conditions for each reference link. If this condition will not meet then what will happen to that data
is decided by Condition Not Met column and if lookup fails it is decided by Lookup Failure
column.
Continue : Data will be sent to the Output link.
Reject : Data will be sent to the Reject link.
Drop : Data will neither go to Output link nor to Reject link.
Fail : Job will fail.

In our case, lets first try for without condition in Condition column and Continue and Reject in
other columns.
Step 5 : Compile and run the JOB. Lets see what the output is.
Output : Stream link
Its showing two records. As we have given Lookup Failure condition as Reject, those records
from primary link which are not matched with reference link data are collected in Reject Link
rjct_primary_rec as shown below.
Output : Reject Link
Step 6 : Lets try to configure for Condition column in Lookup Stage Conditions pane.
Just put condition as ID=3 and Reject under Condition Not Met as shown below.
Except ID=3 all records will get rejected and get stored in Reject link. Here data for ID=2 get
rejected and we will get output for Stream link as shown.
Output for Reject Link
Note : Reject Link shows rejected record from primary input link only.
Practice for Drop and Fail condition.

Change Capture Stage - Lookup Stage

Uploaded by

Copyright:

Available Formats

Change Capture Stage - Lookup Stage

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Change Capture Stage - Lookup Stage

Uploaded by

Copyright:

Available Formats

Change Capture Stage is used to capture the changes between two sources based on the

Before Link Data

After Link Data

Step #1: Design your job structure like below

Step #5: Compile and Run the job.

LOOK UP Stage in Datastage

Last Name Location Network

Step 1 : Design a job structure like below.

this symbol on Title Bar as shown.

Fail : Job will fail.

Output for Reject Link

Practice for Drop and Fail condition.

You might also like