The major consideration is the volume of the data. One of the most important mistakes that developers make is to not have volumetric analyses done before deciding to use Join or Lookup stages.
There are two data sets being combined. One is the primary or driving data set, sometimes called the left of the join. The other dataset are the reference data set or the right of the join.
|If the reference datasets take up a large amount of memory relative to the physical RAM memory size of the computer DataStage is running on, then a Lookup stage might crash because the reference datasets might not fit in RAM along with everything else that has to be in RAM. In this case, Join is most preferred.||So, if the reference datasets are not big enough to cause trouble, use a Lookup. Large reference datasets can cause slow performance since each lookup operation can, and typically will, cause a page fault and an I/O operation.|