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