Configuring normal lookup operations

Data that is read by a database stage can serve as reference data to a Lookup stage. By default, this reference data is loaded into memory like any other reference link. When you perform a normal lookup operation, the Db2 connector retrieves all of the records and allows the Lookup stage to process the records.

Before you begin

You must create a lookup operation job first. You also must define your columns in the input stage for the Lookup stage and the output stage for the Lookup stage.

Procedure

  1. Double-click on the Db2 Connector stage to open the link properties window.
  2. From Lookup Type drop-down list, select Normal.
  3. Click the Columns tab and define the columns that you want to use from the database to which the connector is connected.
  4. Configure the properties on the Properties tab.
    1. Define and test your connection properties in the Connection section.
    2. In the Usage section, you can specify if you want auto-generated SQL statements or user-defined SQL statements. Specify Key as Yes or No.
      • If you specify Generate SQL as Yes, specify the Table name and then select the Key columns in the Columns tab in the lookup stage. To specify the Key columns drag the required columns from the primary link to the reference link. Note that this syntax means that many records are retrieved as opposed to the records that are retrieved in a sparse lookup operation.
      • If you specify Generate SQL as No, select the Key columns in the Columns tab in the lookup stage. To specify the Key columns drag the required columns from the primary link to the reference link. Specify the Select statement property. Type your SELECT statement in the Select statement property using the following format: select * from table_name. Note that this syntax means that many records are retrieved as opposed to the records that are retrieved in a sparse lookup operation.
    3. Optional: Configure any other properties on the Properties tab.
  5. Click OK to save the changes.
  6. To map the input links to the output link, double-click the Lookup stage to open the stage editor.
    1. Drag or copy the columns from your input link to your output link to add the columns to the output link
    2. Define any conditions for a lookup failure by clicking the Constraint icon in the menu.
    3. Select the appropriate value for the Lookup Failure column and click OK. If you select Reject, you must have a reject link and target stage in your job configuration to capture these records.
  7. Click OK.
  8. Save, compile, and run the job.