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
- Double-click on the Db2 Connector stage to open the link
properties window.
- From Lookup Type drop-down list,
select Normal.
- Click the Columns tab and define
the columns that you want to use from the database to which the connector
is connected.
- Configure the properties on the Properties tab.
- Define and test your connection properties in the Connection section.
- 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.
- Optional: Configure any other properties
on the Properties tab.
- Click OK to save the changes.
- To map the input links to the output link, double-click
the Lookup stage to open the stage editor.
- Drag or copy the columns from your input link to your
output link to add the columns to the output link
- Define any conditions for a lookup failure by clicking
the Constraint icon in the menu.
- 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.
- Click OK.
- Save, compile, and run the job.