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

DataStage - What is the main differences between Lookup, Join and Merge stages?

asked mar September 20, 2014 06:33 AM  

What is the main differences between Lookup, Join and Merge stages?


1 Answers

answered By Mswami   0  
All the three concepts are different from each other in the way they use the memory storage, compare input requirements and how they treat various records. Join and Merge needs less memory as compared to the Lookup stage.

Lookup - give an opportunity to refer the data without sorting it. If the reference data is small and manageable in the memory. In this scenario this stage can give boost to the performance of the job. This stage is also to merge the information from two different sources.

Join - The fact is join does support two or more input links (left right and possibly intermediate links). But yes if you are tallking about full outer join then more than two links are not supported.

Merge - As the name suggest the stage is to merge the two or more sources also this stage needs the sorted data input. The benefit of this stage on above join stage is that in this stage we can have the reject links which is not possible in the join stage however it is possible in the lookup.

Coming back to main question of difference between Join and Merge Stage the other significant differences are:

1) Number Of Reject Link:
(Join) does not support reject link.
(Merge) has as many reject link as the update links( if there are n-input links then 1 will be master link and n-1 will be the update link).

2) Data Selection:
(Join) There are various ways in which data is being selected. e.g. we have different types of joins inner outer( left right full) cross join etc. So you have different selection criteria for dropping/selecting a row.
(Merge) Data in Master record and update records are merged only when both have same value for the merge key columns.
   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!