Looking up data by using reference links

You can use the IBM® Db2® connector to look up data directly from a Db2 table by using a reference link to link the Db2 Connector stage to a Lookup stage. The Lookup stage is a processing stage. It is used to perform lookup operations on a data set read into memory from any other Parallel job stage that can output data. You can specify a condition on each of the reference links, such that the stage will only perform a lookup on that reference link if the condition is satisfied.

About this task

A reference link represents a table lookup operation. You can use a reference link as an input link to a Lookup stage and as an output link from other types of stages, such as the Db2 Connector stage.


  1. On the job design canvas, drag a Db2 connector icon and a Lookup stage icon to the job design canvas.
    (The Lookup stage is located in the Processing category of the Palette menu.)
  2. Join the stages by dragging a link from the Db2 Connector stage to the Lookup stage.
  3. Right-click the link, and select Convert to Reference from the menu.
    The line changes to a dashed line to show that the link is a reference link.
  4. Open the Db2 Connector stage editor by double-clicking the Db2 connector icon.
  5. In the stage editor, define the database connection information for the stage, and then define the read operation for the reference link.
    When you are finished, click OK.
  6. Open the Lookup stage editor by double-clicking the Lookup stage icon.
  7. Define the lookup operation for the Lookup stage.
    The left pane of the editor shows the input links, and the right pane shows the output links. The metadata for these links is displayed below these panes. For each record of the source data set from the primary link, the Lookup stage performs a table lookup on each of the lookup tables attached by reference links. The table lookup is based on the values of a set of lookup key columns, one set for each table.
  8. Click OK.