Transformations > Lookup transformation > Advanced properties
  

Advanced properties

You can configure advanced properties for a Lookup transformation. The connection type and the mapping type determine which advanced properties are available for the Lookup transformation.
You can set the following properties:
Property
Description
Tracing Level
Detail level of error and status messages that Data Integration writes in the session log. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
Lookup SQL Override
Overrides the default SQL statement to query the lookup table. Specifies the SQL statement you want to use for querying lookup values.
Use with lookup cache enabled.
Lookup Source Filter
Restricts the lookups based on the value of data in any field in the Lookup transformation.
Use with lookup cache enabled.
Lookup Caching Enabled
Determines whether to cache lookup data during the runtime session. When you enable caching, Data Integration queries the lookup source once and caches the values for use during the session, which can improve performance. When you disable caching, a SELECT statement gets the lookup values each time a row passes into the transformation.
Caching is enabled and is not editable in the following circumstances:
  • - When the lookup source type does not support uncached lookups.
  • - When you select a multiple match policy, but the lookup source type does not support the policy in uncached lookups. For example, you cannot disable caching when you select Return first row or Return last row as the multiple match policy for a lookup against an Amazon Redshift V2 source.
Default is enabled.
This property is not displayed for flat file lookups because flat file lookups are always cached.
Lookup Cache Directory Name
Specifies the directory to store cached lookup data when you select Lookup Caching Enabled.
The directory name can be an environment variable.
Lookup Cache Persistent
Specifies whether to save the lookup cache file to reuse it the next time Data Integration processes a Lookup transformation configured to use the cache.
Cache File Name Prefix
Use with persistent lookup cache. Specifies the file name prefix to use with persistent lookup cache files. Data Integration uses the file name prefix as the file name for the persistent cache files that it saves to disk.
If the named persistent cache files exist, Data Integration builds the memory cache from the files. If the named persistent cache files do not exist, Data Integration rebuilds the persistent cache files.
Enter the prefix. Do not include a file extension such as .idx or .dat.
Re-cache from Lookup Source
Use with persistent lookup cache. When selected, Data Integration rebuilds the persistent lookup cache from the lookup source when it first calls the Lookup transformation instance.
Data Cache Size
Data cache size for the transformation. Select one of the following options:
  • - Auto. Data Integration sets the cache size automatically. If you select Auto, you can also configure a maximum amount of memory for Data Integration to allocate to the cache.
  • - Value. Enter the cache size in bytes.
Default is Auto.
Index Cache Size
Index cache size for the transformation. Select one of the following options:
  • - Auto. Data Integration sets the cache size automatically. If you select Auto, you can also configure a maximum amount of memory for Data Integration to allocate to the cache.
  • - Value. Enter the cache size in bytes.
Default is Auto.
Dynamic Lookup Cache
Determines whether to use a dynamic cache instead of a static cache. When you enable dynamic caching, the task updates the cache as it inserts or updates rows in the target so that the cache and target remain in sync.
Use when lookup cache is enabled.
Output Old Value On Update
When you enable this property, when the task updates a row in the cache, it outputs the value that existed in the lookup cache before it updated the row. When it inserts a row, it returns a null value.
Use when dynamic lookup cache is enabled.
Synchronize dynamic cache
When you enable this property, the task retrieves the latest values from the lookup source and updates the dynamic cache. This is helpful when multiple tasks that use the same lookup source are running simultaneously.
Use when dynamic lookup cache is enabled.
Cache synchronization is not available for some connection types. For more information, see the help for the appropriate connector.
Insert Else Update
Applies to rows entering the Lookup transformation with the row type of insert. When enabled, the mapping task inserts rows in the cache and updates existing rows. When disabled, the mapping task does not update existing rows.
Use when dynamic lookup cache is enabled.
Lookup Source is Static
When you enable this property, the lookup source does not change when the task runs.
Datetime Format
Sets the datetime format and field width. Milliseconds, microseconds, or nanoseconds formats have a field width of 29. If you do not specify a datetime format here, you can enter any datetime format for fields. Default is YYYY-MM-DD HH24:MI:SS. The format does not change the size of the field.
Default is YYYY-MM-DD HH24:MI:SS. The Datetime format does not change the size of the field.
Thousand Separator
Specifies the thousand separator. Enter a comma (,) a period (.) or None.
Default is None.
Decimal Separator
Specifies the decimal separator. Enter a comma (,) or a period (.).
Default is period.
Case Sensitive String Comparison
Determines whether to enable case-sensitive string comparisons when you perform lookups on string columns in flat files. For relational uncached lookups, the column types that support case-sensitive comparison depend on the database.
Case-sensitivity is automatically enabled for lookups in elastic mappings.
Null Ordering
Determines how the null values are ordered. You can choose to sort null values high or low. By default, null values are sorted high. This overrides configuration to treat nulls in comparison operators as high, low, or null. For relational lookups, null ordering depends on the database default value.
Sorted Input
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 session fails. If the condition columns are grouped but not sorted, the lookup is processed as if you did not configure sorted input.
Pre-build Lookup Cache
Specifies to build the lookup cache before the Lookup transformation receives data. Multiple lookup cache files can be built at the same time to improve performance.
Subsecond Precision
Sets the subsecond precision for datetime fields. For relational lookups, you can change the precision for databases that have an editable scale for datetime data. You can change the subsecond precision for Oracle Timestamp, Informix Datetime, and Teradata Timestamp data types.
Enter a positive integer value from 0 to 9. Default is 6 microseconds.
If you enable pushdown optimization in a task, the database returns the complete datetime value, regardless of the subsecond precision setting.
Optional
Determines whether the transformation is optional. If a transformation is optional and there are no incoming fields, the mapping task can run and the data can go through another branch in the data flow. If a transformation is required and there are no incoming fields, the task fails.
For example, you configure a parameter for the source connection. In one branch of the data flow, you add a transformation with a field rule so that only Date/Time data enters the transformation, and you specify that the transformation is optional. When you configure the mapping task, you select a source that does not have Date/Time data. The mapping task ignores the branch with the optional transformation, and the data flow continues through another branch of the mapping.