What is Lookup stage? Uses of Look up stage?

It performs on dataset read into memory from any other parallel job stage that can 

output data. 

The main uses of the lookup stage is to map short codes in the input dataset onto 

expanded information from a look up table which is then joined to the data coming from 

input. For example, some we get the data with customers name and address. Here the 

data identifies state as a two letters or three letters like mel for melbourne or 

syd for sydney. But you want the data to carry the full name of the state by 

defining the code as the key column. In this case lookup stage used very much. 

It will reads each line, it uses the key to look up the stage in the lookup table. 

It adds the state to the new column defined for the output link. 

So that full state name is added to the each row based on codes given. 

If the code not found in the lookup table, record will be rejected. 

Lookup stage also performs to validate the row. 



Look Up stage is a processing stage which performs horizontal combining. 

Lookup stage Supports 

N-Inputs ( For Norman Lookup ) 

2 Inputs ( For Sparse Lookup) 

1 output 

And 1 Reject link 



Up to Datastage 7 Version We have only 2 Types of LookUps 

a) Normal Lookup and b) Sparse Lookup 

But in Datastage 8 Version, enhancements has been take place. They are 


c) Range Look Up And d) Case less Look up 




Normal Lookup:-- In Normal Look, all the reference records are copied to the memory and the primary records are cross verified with the reference records. 


Sparse Lookup:--In Sparse lookup stage, each primary records are sent to the Source and cross verified with the reference records. 


Here , we use sparse lookup when the data coming have memory sufficiency 
and the primary records is relatively smaller than reference date we go for this sparse lookup. 

Range LookUp:--- Range Lookup is going to perform the range checking on selected columns. 


For Example: -- If we want to check the range of salary, in order to find the grades of the employee than we can use the range lookup.