Lookup stage in Datastage : Quick Example

Lookup stage in Datastage : Quick Example

The Lookup stage in DataStage is one of the most used stages in DataStage. It lets you merge columns from different data source’s based on the key column. The Lookup stage could perform inner join as well as left/right outer join.The lookup stage uses memory for processing the records.

Types of Lookup operations

In Memory Lookup:

The Lookup stage performs in memory Lookup by default. It uses memory(RAM) for intermediate results and operations.

Sparse Lookup:

When there is great set records to be processed, and these records exist in oracle or db2 table then In memory Lookup will not be the best choice. So to avoid this, set the Lookup Type to sparse in oracle or db2 connector stage.

Types of Lookup based on Key Expression.

There are three relational operators available for key expression.

Equality (==):

lookup is to be done when the key is matched.

Range lookup (<= ,>=):

The range lookup in Datastage is done by defining the upper and lower bound columns with relation operator >= and <=.

Trans_Date <= End_Date

Caseless lookup

The caseless Lookup in DataStage is done by ticking the option caseless while defining the key expression.

Lookup vs. Join:

Lookup stage performs in memory lookup and works pretty well with small reference data. When the data is huge the physical RAM may not be enough for records therefore in such cases Join stage is preferred.

Lookup stage conditions

You can define lookup conditions for each link in lookup condition window.

To specify Lookup stage condition window, click twice on reference link.

You can specify the following conditions:

which reference link should return multiple rows.

  • A condition to be checked before a lookup is done on a reference link.
  • Action to be taken if the condition fails.
  • Action to be taken if the lookup fails.

Condition Not Met

Possible actions are:

  • Continue:: Fields will be set with Null values if the condition fails.
  • Drop: Drops the row and continues with the next lookup.
  • Fail: the fatal error will be raised.
  • Reject: route the row to the reject link.

Lookup Failure

Possible actions are:

  • Continue:: Fields will be set with Null values if the condition fails.
  • Drop: Drops the row and continues with the next lookup.
  • Fail: the fatal error will be raised.
  • Reject: route the row to the reject link.

What is the difference between Condition not met and look up failure?

If you specify the condition check in lookup then the condition not met option will be evaluated else it will not be applicable. Remember Condition not met is optional.

Example:

Consider dataset 1 has following records

dataset1:

EMPID SALARY DEPT
1 100 IT
2 200 IT
3 300 HR
4 500 CA

Consider dataset 2 has following records

dataset2:

EMPID MOBILE NUMBER
1 9871792665
2 9953836254
6 8859695421
4 NULL

Let us assume Lookup being done by key column ID and Fetch the corresponding Mobile number column

The Output will be:

Output dataset:

EMPID SALARY DEPT MOBILE_NUMBER
1 100 IT 9871792665
2 200 IT 9953836254
3 300 HR NULL
4 500 CA NULL

 How?

For the first and second record, the mobile number will be fetched, but for 3rd and 4th the record mobile number doesn’t exist in dataset2. Therefore Null will be populated

Defining lookup key columns:

Lookup stage Properties
Lookup stage Properties
  1. The lookup being done on Account_TYPE column ON DSLink 2
  2. Interest rates column values are being passed to output field DSLink 6

    Quick Example :

    Job design:

    Sample job with lookup
    Job design

    Source Dataset : Emp

    Source dataset
    Employee dataset

    Source Dataset: Dept

    Source dataset
    Department dataset

    Properties tab :

    Properties tab of lookup stage
    Setup lookup stage properties

    Target dataset :

    Lookup stage :Target dataset
    Target dataset

    Share this post

    One thought on “Lookup stage in Datastage : Quick Example

    1. thank you for this article. what if i have a situation where i want to drop if condition is met (conversely, continue if condition is not met)?

      Reply

    Leave a Reply