Dynamic Lookup Transformation:
Use a dynamic lookup cache when you need to insert and update rows in the target. When you use a dynamic lookup cache, you can insert and update the cache with the same data you pass to the target to insert and update.
Dynamic lookup transformation allows for the synchronization of the target lookup table image in the memory with its physical table in a database.
Once you have configured your lookup to use dynamic cache (we will see below how to do that), when Integration Service reads a row from the source, it updates the lookup cache by performing one of the following actions
Inserts the row into the cache:
If the incoming row is not in the cache, the Integration Service inserts the row in the cache based on input ports or generated Sequence-ID. The Integration Service flags the row as insert.
Updates the row in the cache:
If the row exists in the cache, the Integration Service updates the row in the cache based on the input ports. The Integration Service flags the row as update makes no change to the cache: This happens when the row exists in the cache and the lookup is configured or specified To Insert New Rows only or, the row is not in the cache and lookup is configured to update existing rows only or, the row is in the cache, but based on the lookup condition, nothing changes. The Integration Service flags the row as unchanged
Fortunately, as soon as you create a dynamic lookup Informatica adds one extra port to the lookup. This new port is called:
NewLookupRow
Using the value of this port, the rows can be routed for insert, update or to do nothing. You just need to use a Router
or Filter transformation followed by an Update Strategy.
Oh, forgot to tell you the actual values that you can expect in NewLookupRow port are:
0 = Integration Service does not update or insert the row in the cache.
1 = Integration Service inserts the row into the cache.
2 = Integration Service updates the row in the cache.
When the Integration Service reads a row, it changes the lookup cache depending on the results of the lookup query and the Lookup transformation properties you define. It assigns the value 0, 1, or 2 to the NewLookupRow port to indicate if it inserts or updates the row in the cache, or makes no change.
UPDATE STRATEGY:
0 --> insert DD_INSERT
1 --> update DD_UPDATE
2 --> delete DD_DELETE
3 --> reject DD_REJECT
