You can configure either a cached or uncached lookup to determine whether to cache the lookup data during the runtime session.

When you enable caching, the

Data Integration

Server queries the lookup source once and caches the values for use during the session. Caching the lookup values improves the session performance. When you disable caching, each time a row passes into the transformation, a SELECT statement gets the lookup values.

When you configure a Lookup transformation, you can specify an SQL statement you want to use to override the default SQL statement for querying lookup values. The

Data Integration

Server uses the specified lookup SQL override statement and overrides the default SQL statement to query the lookup table. You must use the SQL override with lookup caching enabled.

The

Data Integration

Server performs the following steps when you run a connected lookup transformation:

The
Data Integration
Server passes values from another transformation to input ports in the Lookup transformation.
For each input row, the
Data Integration
Server queries the lookup source or the cache based on the lookup ports and the lookup condition in the transformation:
If the transformation is uncached, the
Data Integration
Server returns values from the source based on the lookup query.
If the transformation is enabled for caching, the
Data Integration
Server queries the lookup cache during the session and returns the values from the lookup cache.
The
Data Integration
Server then passes the returned data to the next transformation in the mapping.