0 / 0
Join versus lookup (DataStage)

Join versus lookup (DataStage)

IBM® DataStage® does not know how large your data is, so cannot make an informed choice whether to combine data using a join stage or a lookup stage. Here's how to decide which to use:

There are two data sets being combined. One is the primary or driving dataset, sometimes called the left of the join. The other data set(s) are the reference data sets, or the right of the join.

In all cases you are concerned with the size of the reference data sets. If these take up a large amount of memory relative to the physical RAM memory size of the computer you are running on, then a lookup stage might thrash because the reference data sets might not fit in RAM along with everything else that has to be in RAM. This results in very slow performance since each lookup operation can, and typically does, cause a page fault and an I/O operation.

So, if the reference data sets are big enough to cause trouble, use a join. A join does a high-speed sort on the driving and reference data sets. This can involve I/O if the data is big enough, but the I/O is all highly optimized and sequential. Once the sort is over the join processing is very fast and never involves paging or other I/O.

Generative AI search and answer
These answers are generated by a large language model in watsonx.ai based on content from the product documentation. Learn more