Use a dynamic lookup cache to keep the lookup cache synchronized with the target.
When you enable lookup caching, a mapping task builds the lookup cache when it processes the first lookup request. The cache can be static or dynamic. If the cache is static, the data in the lookup cache doesn't change as the mapping task runs. If the task uses the cache multiple times, the task uses the same data. If the cache is dynamic, the task updates the cache based on the actions in the task, so if the task uses the lookup multiple times, downstream transformations can use updated data.
You can use a dynamic cache with most types of lookup sources. You cannot use a dynamic cache with flat file or Salesforce lookups. For more information about using a dynamic cache with a specific type of lookup source, see the help for the appropriate connector.
Based on the results of the lookup query, the row type, and the Lookup transformation properties, the mapping task performs one of the following actions on the dynamic lookup cache when it reads a row from the source:
Inserts the row into the cache
The mapping task inserts the row when the row is not in the cache. The mapping task flags the row as insert.
Updates the row in the cache
The mapping task updates the row when the row exists in the cache. The mapping task updates the row in the cache based on the input fields. The mapping task flags the row as an update row.
Makes no change to the cache
The mapping task makes no change when the row is in the cache and nothing changes. The mapping task flags the row as unchanged.
The dynamic Lookup transformation includes the return field, NewLookupRow, which describes the changes the task makes to each row in the cache. Based on the value of the NewLookupRow, you can also configure a Router or Filter transformation with the dynamic Lookup transformation to route insert or update rows to the target table. You can route unchanged rows to another target table or flat file, or you can drop them.
You cannot use a parameterized source, target, or lookup with a Lookup transformation that uses a dynamic cache.
Static and dynamic lookup comparison
You might want to use dynamic cache instead of a static cache if the source might contain duplicate private keys. Or, you might want to use a dynamic cache when the source contains a large table of data to optimize performance.
Data Integration processes lookup conditions differently based on whether you configure the Lookup transformation to use a static or dynamic cache.
The following table compares a Lookup transformation that uses a static cache to a Lookup transformation that uses a dynamic cache:
Static Lookup Cache
Dynamic Lookup Cache
The cache does not change during the task run.
The task inserts or updates rows in the cache as it passes rows to the target.
You can use a flat file, relational database, and other connection types such as Salesforce for lookup.
You cannot use a flat file or Salesforce connection type.
When the lookup condition is true, the task returns a value from the lookup table or cache.
When the condition is not true, the task returns the default value.
When the lookup condition is true, the task either updates the row in the cache and target or leaves the cache unchanged. This indicates that the row is in the cache and target table.
When the lookup condition is not true, the task either inserts the row in the cache and target or leaves the cache unchanged based on the row type. This indicates that the row is not in the cache or target table.
Dynamic cache updates
When the mapping task reads a row, it changes the lookup cache depending on the results of the lookup query and the Lookup transformation properties that you define. The mapping task assigns a value to the NewLookupRow return field that indicates the action it takes.
The following table lists the possible NewLookupRow values:
NewLookupRow Value
Description
0
Mapping task does not update or insert the row in the cache.
1
Mapping task inserts the row into the cache.
2
Mapping task updates the row in the cache.
You can use the NewLookupRow values in downstream transformations.
Inserts and updates for insert rows
You can configure how the mapping task handles inserts and updates to the cache for insert rows. To update existing rows in the dynamic lookup cache when the row type is insert, enable the Insert Else Update advanced property for the transformation.
Note: This property only applies to rows entering the Lookup transformation where the row type is insert. When a row of any other row type, such as update, enters the Lookup transformation, the Insert Else Update property has no effect on how the mapping task handles the row.
When you enable Insert Else Update and the row type entering the Lookup transformation is insert, the mapping task inserts the row into the cache if it is new. If the row exists in the index cache but the data cache is different than the current row, the mapping task updates the row in the data cache.
If you do not enable Insert Else Update and the row type entering the Lookup transformation is insert, the mapping task inserts the row into the cache if it is new, and makes no change to the cache if the row exists.
The following table describes how the mapping task changes the lookup cache when the row type of the rows entering the Lookup transformation is insert:
Insert Else Update Option
Row found in cache?
Data cache is different?
Lookup Cache Result
NewLookupRow Value
Disabled - insert only
Yes
-
No change
0
Disabled - insert only
No
-
Insert
1
Enabled
Yes
Yes
Update
2
Enabled
Yes
No
No change
0
Enabled
No
-
Insert
1
Dynamic cache and lookup source synchronization
The Lookup transformation maintains a dynamic lookup cache to track the rows that it passes to the target. When multiple tasks update the same target, you can configure the Lookup transformation in each task to synchronize the dynamic lookup cache to the same lookup source instead of a target.
To synchronize the cache with the lookup source, enable the Synchronize Dynamic Cache property for the Lookup transformation.
When you configure a Lookup transformation to synchronize the cache with the Lookup source, the Lookup transformation performs a lookup on the lookup source. If the data does not exist in the Lookup source, the Lookup transformation inserts the row into the lookup source before it updates the dynamic lookup cache.
The data might exist in the lookup source if another task inserted the row. To synchronize the lookup cache to the lookup source, the task retrieves the latest values from the lookup source. The Lookup transformation inserts the values from the Lookup source in the dynamic lookup cache.
For example, you have multiple tasks running simultaneously. Each task generates product numbers for new product names. When a task generates a product number, the other tasks must use the same product number to identify the product. The product number is generated once and inserted in the lookup source. If another task processes a row containing the product, it must use the product number that is in the lookup source. Each task performs a lookup on the lookup source to determine which product numbers have already been generated.
When you configure the Lookup transformation to synchronize the cache with the lookup source, the task performs a lookup on the dynamic lookup cache for insert rows. If data does not exist in the dynamic lookup cache, the task performs a lookup on the lookup source. It then completes one of the following tasks:
•If data exists in the lookup source, the task inserts a row in the dynamic lookup cache with the columns from the lookup source. It does not update the cache with the source row.
•If data does not exist in the lookup source, the task inserts the data into the lookup source and inserts the row into the cache.
The lookup source contains the same fields as the lookup cache. The task does not insert a row in the lookup cache unless the column is projected from the Lookup transformation or the field is part of a lookup condition.
Dynamic cache and target synchronization
Configure downstream transformations to ensure that the dynamic lookup cache and target are synchronized.
When you use a dynamic lookup cache, the mapping task writes to the lookup cache before it writes to the target table. The lookup cache and target table can become unsynchronized if the task does not write the data to the target. For example, the target database might reject the data.
Consider the following guidelines to keep the lookup cache synchronized with the lookup table:
•Use the Router transformation to pass rows to the cached target when the NewLookupRow value equals one or two.
•Use the Router transformation to drop rows when the NewLookupRow value equals zero. Or, output the rows to a different target.
Field mapping
When you use a dynamic lookup cache, map incoming fields with lookup cache fields on the Field Mapping tab. The Field Mapping tab is only available when you configure the Lookup transformation to use a dynamic cache.
You must map all of the incoming fields when you use a dynamic cache so that the cache can update as the task runs. Optionally, you can map the Sequence-ID field instead of an incoming field if you want to create a generated key for a field in the target object.
Generated key fields
When you configure a dynamic lookup cache, you can create a generated key for a field in the target object.
To create a generated key for a field in the target object, map the Sequence-ID field to a lookup cache field on the Field Mapping tab. You can map the Sequence-ID field instead of an incoming field to lookup cache fields with the integer or Bigint data type. For integer lookup fields, the generated key maximum value is 2,147,483,647. For Bigint lookup fields, the generated key maximum value is 9,223,372,036,854,775,807.
When you map the Sequence-ID field, Data Integration generates a key when it inserts a row into the lookup cache.
Data Integration uses the following process to generate sequence IDs:
1When Data Integration creates the dynamic lookup cache, it tracks the range of values for each field that has a sequence ID in the dynamic lookup cache.
2When Data Integration inserts a row of data into the cache, it generates a key for a field by incrementing the greatest sequence ID value by one.
3When Data Integration reaches the maximum number for a generated sequence ID, it starts over at one. Data Integration increments each sequence ID by one until it reaches the smallest existing value minus one. If Data Integration runs out of unique sequence ID numbers, the mapping task fails.
Data Integration generates a sequence ID for each row it inserts into the cache.
Ignore fields in comparison
When you use a dynamic lookup cache, you can configure fields to be ignored when Data Integration compares the values in the lookup fields with the values in the associated incoming fields. Ignoring some fields in the comparison can improve mapping performance.
When you run a mapping that uses a dynamic lookup cache, by default, Data Integration compares the values in all lookup fields with the values in the associated incoming fields. Data Integration compares the values to determine whether to update the row in the lookup cache. When a value in an incoming field differs from the value in the lookup field, Data Integration updates the row in the cache.
If you do not want to compare all fields, you can choose the fields that you want Data Integration to ignore when it compares fields. For example, the source data includes a column that indicates whether the row contains data that you need to update. Enable the Ignore in Comparison property for all lookup fields except the field that indicates whether to update the row in the cache and target table.
Configure the fields to be ignored on the Return Fields tab of the Lookup transformation. To ignore a field, enable the Ignore in Comparison property for the field.
You must configure the transformation to compare at least one field unless the lookup condition contains all the lookup fields.
Dynamic lookup query overrides
When you add a WHERE clause in a Lookup transformation that uses a dynamic cache, connect a Filter transformation before the Lookup transformation to filter rows that you do not want to insert into the cache or target table. If you do not include the Filter transformation, you might get inconsistent results between the cache and the target table.
For example, you configure a Lookup transformation to perform a dynamic lookup on the employee table, EMP, matching rows by EMP_ID. You define the following lookup SQL override:
SELECT EMP_ID, EMP_STATUS FROM EMP ORDER BY EMP_ID, EMP_STATUS WHERE EMP_STATUS = 4
When you first run the mapping, the mapping task builds the lookup cache from the target table based on the lookup SQL override. All rows in the cache match the condition in the WHERE clause, EMP_STATUS = 4.
The mapping task reads a source row that meets the lookup condition you specify, but the value of EMP_STATUS is 2. Although the target might have the row where EMP_STATUS is 2, the mapping task does not find the row in the cache because of the SQL override. The mapping task inserts the row into the cache and passes the row to the target table. When the mapping task inserts this row in the target table, you might get inconsistent results when the row already exists. In addition, not all rows in the cache match the condition in the WHERE clause in the SQL override.
To verify that you only insert rows into the cache that match the WHERE clause, you add a Filter transformation before the Lookup transformation and define the filter condition as the condition in the WHERE clause in the lookup SQL override.
You enter the following filter condition in the Filter transformation and the WHERE clause in the SQL override: