Mapping Configuration for a Dynamic Lookup Cache
If you use a Lookup with a dynamic cache, you must configure the mapping to update the dynamic lookup cache and write the changed rows to the target.
Complete the following steps to configure a mapping with a dynamic lookup cache:
- Flag the input rows of the Lookup transformation for insert or update.
- By default, the row type of all input rows is insert. Add an Update Strategy transformation before the Lookup transformation to specify different row types for the input rows.
- Specify how the Integration Service handles the input rows for the dynamic cache.
- Select the Insert Else Update or Update Else Insert options to process rows flagged for insert or update.
- Create separate mapping pipelines for rows to be inserted into the target and updated in the target.
- Add a Filter or Router transformation after the Lookup transformation to route insert and update rows into separate mapping branches. Use the value of the NewLookupRow to determine the appropriate branch for each row.
- Configure the row type for output rows of the Lookup transformation.
- Add an Update Strategy transformation to flag rows for insert or update.
Insert Else Update
Use the Insert Else Update property to update existing rows in the dynamic lookup cache when the row type is insert.
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 Integration Service handles the row.
When you select Insert Else Update and the row type entering the Lookup transformation is insert, the Integration Service 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 Integration Service updates the row in the data cache.
If you do not select Insert Else Update and the row type entering the Lookup transformation is insert, the Integration Service 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 Integration Service 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 |
|---|
Cleared - insert only | Yes | - | No change | 0 |
Cleared - insert only | No | - | Insert | 1 |
Selected | Yes | Yes | Update | 2 |
Selected | Yes | No | No change | 0 |
Selected | No | - | Insert | 1 |
Update Else Insert
Use the Update Else Insert property to insert new rows in the dynamic lookup cache when the row type is update.
You can select the Update Else Insert property in the Lookup transformation. This property only applies to rows entering the Lookup transformation where the row type is update. When a row of any other row type, such as insert, enters the Lookup transformation, this property has no effect on how the Integration Service handles the row.
When you select this property and the row type entering the Lookup transformation is update, the Integration Service updates the row in the cache if the row exists in the index cache and the cache data is different than the existing row. The Integration Service inserts the row in the cache if it is new.
If you do not select this property and the row type entering the Lookup transformation is update, the Integration Service updates the row in the cache if it exists, and makes no change to the cache if the row is new.
If you select Ignore Null for all lookup ports not in the lookup condition and if all those ports contain null values, the Integration Service does not change the cache and the NewLookupRow value equals 0.
The following table describes how the Integration Service changes the lookup cache when the row type of the rows entering the Lookup transformation is update:
Update Else Insert Option | Row Found in Cache | Data Cache is Different | Lookup Cache Result | NewLookupRow Value |
|---|
Cleared (update only) | Yes | Yes | Update | 2 |
Cleared (update only) | Yes | No | No change | 0 |
Cleared (update only) | No | - | No change | 0 |
Selected | Yes | Yes | Update | 2 |
Selected | Yes | No | No change | 0 |
Selected | No | - | Insert | 1 |
Dynamic Lookup Cache and Target Synchronization
Configure downstream transformations to ensure that the dynamic lookup cache and the target are synchronized.
When you use a dynamic lookup cache, the Integration Service writes to the lookup cache before it writes to the target table. The lookup cache and target table can become unsynchronized if the Integration Service 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 a 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.
- •Use Update Strategy transformations after the Lookup transformation to flag rows for insert or update into the target.
- •Verify that the Lookup transformation outputs the same values to the target that the Integration Service writes to the lookup cache. When you choose to output new values on update, only connect lookup/output ports to the target table instead of output ports. When you choose to output old values on update, add an Expression transformation after the Lookup transformation and before the Router transformation. Add output ports in the Expression transformation for each port in the target table and create expressions to ensure you do not output null input values to the target.
- •Select Insert and Update as Update when you define the update strategy target table options. This ensures that the Integration Service updates rows marked for update and inserts rows marked for insert.