Home > Software > Data-Warehouse > DataStage
Interview Questions   Tutorials   Discussions   Programs   Discussion   

DataStage - Difference in the implementation of lookup and join stages, in joining two tables?

asked mar September 20, 2014 06:32 AM  

Difference in the implementation of lookup and join stages, in joining two tables?


1 Answers

answered By vishnoiprem   0  
Join stage and Look Up stage have some different input requirements.
Based on the Requirement we use these Stages which is good for the performance.
We need to see weather we get good performance by using any stage in datastage.
And the stage supports the required inputs are not.
Lets say Join Stage= J.S And Look Up = M.S
J.S - The input names of the Join Stage are Left tables , Right Tables and Intermediate Tables. That means we call the left one as a Left table and right one as a Right table and remaining tables between these tables are call it as Intermediate tables. ( That can be any number of tables in between )
L.S - The input names of the Look Up stage are Primary Tables and Reference Tables. That means First table will be considered as a Master tables and remaining any number of tables are considered as a Update tables.
J.S - We can perform four types of Joins in Join Stage. That means it supports all the four types of Joins. They are

  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

L.S - We can perform only two types of Joins in Look Stage. That means it supports two types of Joins here. And they are
Inner Join
And Left Outer Join

J.S - The Input requirements of Join stage are

  • There will be a N-Inputs ( In the case of Left, Inner, Right Outer Joins)
  • There will be a 2 Inputs ( In the case of Full Outer Join)

And there will be a 1 Output link and
there will be no reject links in Join stage.
L.S - The input requirements of Look Up Stage are as follows

There will be a N-Inputs ( In the Case of Normal Stage)
2 Inputs (In the Case of Sparse Look Up )
1 Output
And 1 Reject Link.

J.S - And Coming to Memory type. This is light memory Usage
L.S - It is a Heavy Memory Usage
J.S - Key Column Names should be Same. That is Primary record should be same with Secondary Records

  • L.S - Key column names Optional.
  • It should be same in the case of Sparse Look Up.
  • The Inner Join Type are as follows
  • J.S - Primary Records Should match with all secondary


J.S - Primary Records should match with all secondary.
The Input requirements with respect to Sorting are as follows.
J.S - In Join Stage Primary records and Secondary records should be sorted when coming( i.e data sorting is mandatory).
L.S - In Look Up stage it is Optional. That is all the primary and secondary records no need to be sorted.

  • And Treatment of Unmateched Records will be as follows
  • J.S - OK for the Primary and Secondary Records if the data is Unmatched records.
  • L.s - Ok for the Primary and we get warning if secondary records are unmatched.
   add comment

Your answer

Join with account you already have



Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!