Tutorial corner


Lookup transformation properties in Informatica

Spread the love

Lookup transformation properties in Informatica: Last time, we started with the introduction of lookup transformation in Informatica. We also discussed different type of lookup transformation in Informatica as connected  lookup vs unconnected lookup transformation. In this Informatica tutorial, we will talk about the Lookup transformation properties in Informatica. We will also see how to enable /disable lookup cache by Lookup transformation properties.

Lookup transformation properties

Once you create a lookup transformation, you can update the Lookup transformation properties by going to the Properties tab of lookup transformation

Check here for Normalizer transformation in informatica

Lookup Transformation Properties

Lookup Transformation Properties

Configure Lookup Transformation Properties

Modify the Lookup Transformation properties by clicking on the Properties tab.

Property Lookup Type Description
Lookup SQL Override Relational 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 only with the lookup cache enabled.
Lookup Table Name Pipeline/ Relational The name of the table or 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.
Lookup Source Filter  Relational Restricts the lookups the Integration Service performs based on the value of data in Lookup transformation input ports.
Lookup Caching Enabled Flat File/Pipeline/Relational 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.

Note: The Integration Service always caches flat file and pipeline lookups.

Lookup Policy onMultiple Match Flat FilePipelineRelational Determines which rows that the Lookup transformation returns when it finds multiple rows that match the lookup condition. You can select the first or last row returned from the cache or lookup source, or report an error. Or, you can allow the Lookup transformation to use any value. When you configure the Lookup transformation to return any matching value, the transformation returns the first value that matches the lookup condition. It creates an index based on the key  ports instead of all Lookup transformation ports. If you do not enable the Output Old Value On Update option, the Lookup Policy On Multiple Match option is set to Report Error for dynamic lookups.
Lookup Condition Flat FilePipelineRelational Displays the lookup condition you set in the Condition tab.
Connection Information  Relational Specifies the database containing the lookup table. You can define the database in the mapping, session, or parameter file:

Mapping level. Select the connection object. You can also specify the database connection type.

Type Relational:  before the connection name if it is a relational connection.

Type Application: before the connection name if it is an application connection.

Session level:  Use the $Source or $Target connection variable. If you use one of these variables, the lookup table must reside in the source or target database. Specify the database connection in the session properties for each variable.

Parameter file. Use the session parameter $DBConnectionName or $AppConnectionName, and define it in the parameter file.

By default, the Designer specifies $Source if you choose an existing source table and $Target if you choose an existing 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.

Source Type Flat FilePipelineRelational Indicates that the Lookup transformation reads values from a relational table, flat file, or source qualifier.
Tracing Level Flat FilePipelineRelational Sets the amount of detail included in the session log.
Lookup Cache Directory Name Flat FilePipelineRelational Specifies the directory used to build the lookup cache files when you configure the Lookup transformation to cache the lookup source. Also used to save the persistent lookup cache files when you selectthe Lookup Persistent option.By default, the Integration Service uses the $PMCacheDir directory configured for the Integration Service.
 Lookup Cache Persistent Flat FilePipelineRelational 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 only with the lookup cache enabled.
Lookup Data Cache SizeLookup Index Cache Size Flat FilePipelineRelational Default is Auto. Indicates the maximum size the Integration Service allocates to the data cache and the index in memory. You can configure a numeric value, or you can configure the Integration Service to determine the cache size at run-time. 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.
 Dynamic Lookup Cache Flat FilePipelineRelational Indicates to use a dynamic lookup cache. Inserts or updates rows in the lookup cache as it passes rows to the target table. Use only with the lookup cache enabled.
Output Old Value On Update Flat FilePipelineRelational 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 new 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.
Cache File Name Prefix Flat FilePipelineRelational 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. Only 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.
Recache From Lookup Source Flat FilePipelineRelational Use with the lookup cache enabled. When selected, the Integration Service rebuilds the lookup cache from the lookup source when it first calls the Lookup transformation instance. If you use a persistent lookup cache, it rebuilds the persistent cache files before using the cache. If you do not use a persistent lookup cache, it rebuilds the lookup cache in memory before using the cache.
Insert Else Update Flat FilePipelineRelational Use with dynamic caching enabled. Applies to rows entering the Lookup transformation with the row type of insert. When enabled, the Integration Service inserts new rows in the cache and updates existing rows When disabled, the Integration Service does not update existing rows.
Update Else Insert Flat FilePipelineRelational 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 new row if it is new. When disabled, the Integration Service does not insert new rows
Datetime Format  Flat File Click the Open button to select a datetime format. Define the formatand field width. Milliseconds, microseconds, or nanosecondsformats have a field width of 29.If you do not select a datetime format for a port, you can enter anydatetime format. Default is “MM/DD/YYYY HH24:MI:SS”. The Date time format does not change the size of the port.
Thousand Separator  Flat File 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 noseparator.
Decimal Separator  Flat File 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.
Case-Sensitive StringComparison Flat FilePipeline The Integration Service uses case-sensitive string comparisons when performing lookups on string columns.For relational lookups, the case-sensitive comparison is based onthe database support.
Null Ordering Flat FilePipeline Determines how the Integration Service orders null values. You canchoose 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 is based on the database default value.
Sorted Input  Flat FilePipeline Indicates whether or not the lookup file data is sorted. Thisincreases lookup performance for file lookups. If you enable sortedinput, and the condition columns are not grouped, the IntegrationService fails the session. If the condition columns are grouped, butnot sorted, the Integration Service processes the lookup as if youdid not configure sorted input.
Lookup Source is Static  Flat FilePipelineRelational The lookup source does not change in a session.
Pre-build Lookup Cache Allow the Integration Service to build the lookup cache before theLookup transformation requires the data.


Always allowed. The Integration Service builds the lookup cache before the Lookup transformation receives the first source row.

Always disallowed. The Integration Service builds the lookup cache when the Lookup transformation receives the source row.

Subsecond Precision  Relational 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.

Check here for Aggregator transformation in informatica.

So, in this informatica tutorial, we have gone through Lookup transformation properties in Informatica.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme