Overrides the default SQL statement to query the lookup table.

Specifies the SQL statement you want the Integration Service to use for querying lookup values. Use with the lookup cache enabled.

The name of the table or the source qualifier from which the transformation looks up and caches values. When you create the Lookup transformation, choose a source, target, or source qualifier as the lookup source. You can also import a table, view, or synonym from another database when you create the Lookup transformation.

If you enter a lookup SQL override, you do not need to enter the Lookup Table Name.

Restricts the lookups the Integration Service performs based on the value of data in any port in the Lookup transformation. Use with the lookup cache enabled.

Indicates whether the Integration Service caches lookup values during the session.

When you enable lookup caching, the Integration Service queries the lookup source once, caches the values, and looks up values in the cache during the session. Caching the lookup values can improve session performance.

When you disable caching, each time a row passes into the transformation, the Integration Service issues a select statement to the lookup source for lookup values.

Determines which rows to return when the Lookup transformation finds multiple rows that match the lookup condition. Select one of the following values:

Displays the lookup condition you set in the Condition tab.

Specifies the database that contains the lookup table. You can define the database in the mapping, session, or parameter file:

By default, the Designer specifies $Source if you choose a source table and $Target if you choose a target table when you create the Lookup transformation. You can override these values in the session properties.

The Integration Service fails the session if it cannot determine the type of database connection.

Indicates that the Lookup transformation reads values from a relational table, flat file, or source qualifier.

Sets the amount of detail included in the session log.

Specifies the directory used to build the lookup cache files when you configure the Lookup transformation to cache the lookup source. Also saves the persistent lookup cache files when you select the Lookup Persistent option.

By default, the Integration Service uses the $PMCacheDir directory configured for the Integration Service.

Indicates whether the Integration Service uses a persistent lookup cache, which consists of at least two cache files. If a Lookup transformation is configured for a persistent lookup cache and persistent lookup cache files do not exist, the Integration Service creates the files during the session. Use with the lookup cache enabled.

Default is Auto. Indicates the maximum size the Integration Service allocates to the data cache and the index in memory. You can use a numeric value for the cache, you can use a cache value from a parameter file or you can configure the Integration Service to set the cache size by using the Auto setting. If you configure the Integration Service to determine the cache size, you can also configure a maximum amount of memory for the Integration Service to allocate to the cache.

If the Integration Service cannot allocate the configured amount of memory when initializing the session, it fails the session. When the Integration Service cannot store all the data cache data in memory, it pages to disk.

Use with the lookup cache enabled.

Indicates to use a dynamic lookup cache. Inserts or updates rows in the lookup cache as it passes rows to the target table.

Use with the lookup cache enabled.

Use with dynamic caching enabled. When you enable this property, the Integration Service outputs old values out of the lookup/output ports. When the Integration Service updates a row in the cache, it outputs the value that existed in the lookup cache before it updated the row based on the input data. When the Integration Service inserts a row in the cache, it outputs null values.

When you disable this property, the Integration Service outputs the same values out of the lookup/output and input/output ports.

This property is enabled by default.

An expression that indicates whether to update dynamic cache. Create an expression using lookup ports or input ports. The expression can contain input values or values in the lookup cache. The Integration Service updates the cache when the condition is true and the data exists in the cache. Use with dynamic caching enabled. Default is true.

Use with persistent lookup cache. Specifies the file name prefix to use with persistent lookup cache files. The Integration Service uses the file name prefix as the file name for the persistent cache files it saves to disk. Enter the prefix. Do not enter .idx or .dat.

You can enter a parameter or variable for the file name prefix. Use any parameter or variable type that you can define in the parameter file.

If the named persistent cache files exist, the Integration Service builds the memory cache from the files. If the named persistent cache files do not exist, the Integration Service rebuilds the persistent cache files.

Use with persistent lookup cache. If you use a persistent lookup cache and enable this option, the Integration Service rebuilds the persistent lookup cache from the lookup source when it first calls the Lookup transformation instance.

If you do not enable this option, the Integration Service reuses the persisted cache files.

Use with dynamic caching enabled. Applies to rows entering the Lookup transformation with the row type of insert. When enabled, the Integration Service inserts rows in the cache and updates existing rows When disabled, the Integration Service does not update existing rows.

Use with dynamic caching enabled. Applies to rows entering the Lookup transformation with the row type of update.

When enabled, the Integration Service updates existing rows, and inserts a row if it is new. When disabled, the Integration Service does not insert new rows.

Click the Open button to select a datetime format. Define the format and the field width. Milliseconds, microseconds, or nanoseconds formats have a field width of 29.

If you do not select a datetime format for a port, you can enter any datetime format. Default is MM/DD/YYYY HH24:MI:SS. The Datetime format does not change the size of the port.

If you do not define a thousand separator for a port, the Integration Service uses the properties defined here.

You can choose no separator, a comma, or a period. Default is no separator.

If you do not define a decimal separator for a particular field in the lookup definition or on the Ports tab, the Integration Service uses the properties defined here.

You can choose a comma or a period decimal separator. Default is period.

The Integration Service uses case sensitive string comparisons when performing lookups on string columns.

For relational lookups, the case sensitive comparison depends on the database support.

Determines how the Integration Service orders null values. You can choose to sort null values high or low. By default, the Integration Service sorts null values high. This overrides the Integration Service configuration to treat nulls in comparison operators as high, low, or null.

For relational lookups, null ordering depends on the database default value.

Indicates whether or not the lookup file data is in sorted order. This increases lookup performance for file lookups. If you enable sorted input, and the condition columns are not grouped, the Integration Service fails the session. If the condition columns are grouped, but not sorted, the Integration Service processes the lookup as if you did not configure sorted input.

The lookup source does not change in a session.

Allows the Integration Service to build the lookup cache before the Lookup transformation receives the data. The Integration Service can build multiple lookup cache files at the same time to improve performance.

You can configure this option in the mapping or the session. The Integration Service uses the session-level setting if you configure the Lookup transformation option as Auto.

Configure one of the following options:

You must configure the number of pipelines that the Integration Service can build concurrently. Configure the Additional Concurrent Pipelines for Lookup Cache Creation session property. The Integration Service can pre-build lookup cache if this property is greater than zero.

Specifies the subsecond precision for datetime ports.

For relational lookups, you can change the precision for databases that have an editable scale for datetime data. You can change subsecond precision for Oracle Timestamp, Informix Datetime, and Teradata Timestamp datatypes.

Enter a positive integer value from 0 to 9. Default is 6 microseconds. If you enable pushdown optimization, the database returns the complete datetime value, regardless of the subsecond precision setting.