Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Integration
  • All Products

Table of Contents

Search

  1. Preface
  2. Transformations
  3. Source transformation
  4. Target transformation
  5. Access Policy transformation
  6. B2B transformation
  7. Aggregator transformation
  8. Cleanse transformation
  9. Data Masking transformation
  10. Data Services transformation
  11. Deduplicate transformation
  12. Expression transformation
  13. Filter transformation
  14. Hierarchy Builder transformation
  15. Hierarchy Parser transformation
  16. Hierarchy Processor transformation
  17. Input transformation
  18. Java transformation
  19. Java transformation API reference
  20. Joiner transformation
  21. Labeler transformation
  22. Lookup transformation
  23. Machine Learning transformation
  24. Mapplet transformation
  25. Normalizer transformation
  26. Output transformation
  27. Parse transformation
  28. Python transformation
  29. Rank transformation
  30. Router transformation
  31. Rule Specification transformation
  32. Sequence Generator transformation
  33. Sorter transformation
  34. SQL transformation
  35. Structure Parser transformation
  36. Transaction Control transformation
  37. Union transformation
  38. Velocity transformation
  39. Verifier transformation
  40. Web Services transformation

Transformations

Transformations

Back Next

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 Source File Directory
Name of the directory for a flat file lookup source. By default,
Data Integration
 reads files from the lookup source connection directory.
You can also use an input parameter to specify the source file directory.
If you use the service process variable directory $PMLookupFileDir, the task writes target files to the configured path for the system variable. To find the configured path of a system variable, see the pmrdtm.cfg file located at the following directory: 
<Secure Agent installation directory>\apps\Data_Integration_Server\<Data Integration Server version>\ICS\main\bin\rdtm
You can also find the configured path for the $PMLookupFileDir variable in the Data Integration Server system configuration details in Administrator.
Lookup Source File Name
File name, or file name and path of the lookup source file.
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.
Use Lookup Field Default Value
Determines whether to return the default value when there's no match in the lookup source. By default, the transformation returns NULL when there's no match in the lookup source.
Doesn't apply when you parameterize the lookup object, perform an unconnected lookup, or disable lookup caching.
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
Determines 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.
Update Dynamic Cache Condition
When the lookup uses a dynamic lookup cache,
Data Integration
 uses the expression condition to determine whether to update the dynamic cache.
Data Integration
 updates the cache when the condition is true and the data exists in the cache.
Enter a boolean expression. You can include field names and values, and in-out parameters in the expression.
Doesn't apply to mappings in advanced mode.
Default is TRUE.
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 advanced mode.
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 SQL ELT 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.
0 COMMENTS
We’d like to hear from you!