Table of Contents

Search

  1. Preface
  2. Transformations
  3. Source transformation
  4. Target transformation
  5. Access Policy transformation
  6. Aggregator transformation
  7. B2B 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 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

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.
The properties that are available vary based on the mapping mode.
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.
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!