You can configure a connected Lookup transformation when you use a PostgreSQL connection in a mapping to return data from a PostgreSQL source based on a specified lookup condition.
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:
1The Data Integration Server passes values from another transformation to input ports in the Lookup transformation.
2For 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.
3The Data Integration Server then passes the returned data to the next transformation in the mapping.
Configuring a cached or uncached connected Lookup transformation
To configure a connected Lookup transformation, do not select the Unconnected Lookup checkbox in the general properties of the Lookup object. To configure a cached or uncached lookup in the Lookup transformation, in the advanced properties of the lookup object, enable or disable the Lookup Caching Enabled based on your requirement. The Lookup Caching Enabled checkbox is enabled by default.
The default Lookup Cache Directory Name is $PMCacheDir, which is the directory to store the cached lookup data when you select Lookup Caching Enabled. If you do not want a cached lookup, clear the checkbox.
For more information about configuring the lookup properties in a Lookup transformation, see Transformations.
Overriding the default lookup query in a Lookup transformation
When you configure a lookup, you specify the lookup condition to query the lookup table in the Lookup Condition tab of the Lookup transformation. When you run the mapping, the Data Integration Server finds the data in the lookup source using the query generated from the lookup condition.
If you want to override the lookup query generated from the lookup condition, you can specify an SQL statement to override the lookup query. The Data Integration Server uses the specified lookup SQL override statement to query the lookup table.
To override, you must specify the SQL statement in the SQL Override field in the advanced properties of the Lookup Object tab. Ensure that you configure the SQL override with lookup caching enabled. To do this, select the Lookup Caching Enabled field in the Advanced tab of the Lookup transformation.