In what scenarios we can use dynamic cache . Please explain me with some real time scenario
I assume you refer to dynamic cache for lookups in PowerCenter. if that is the case then this post is not on the right forum...
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.
So, How does dynamic lookup work?
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 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.
If the source and target contains the columns like ITEM_ID,ITEM_NAME,ITEM_DESC,PRICE,MANF_ID
In the mapping i use the lookup table by looking the target table. In the lookup take all the columns from source as input. And give the condition as ITEM_ID=IN_ITEM_ID. And select the property Dynamic Lookup Cache, when u select this property the other 3 properties will be enable, click on Insert Else Update property also. By this u can see a new column in the lookup table by NewLookupRow which is in disabled state. And give all the output associated ports by their respective input ports which r taken from source.
Take Router t/r and connect with the lookup o/p rows with the NewLookupRow also. Now, apply the conditions like NewLookupRow=1 and
Then connect this router to an 2 Update Strategies, by all the columns except that NewLookupRow. and give the condition as DD_INSERT and in the second update strategy give the condition as DD_UPDATE.
Then connect this update with Targets.
I hope this scenario will give u an idea about the dynamic cache.
If you are using Dynamic cache in lookup transformation. That will automatically comes with Associated port and New lookup row port(Dynamjcally insert or updates the data into target table) to connect the ports from source database.
it will caches the data automatically from sources.
0---- No inserts or No updates in cache
1---- Insert data
Mostly we use in ------Scd2 type.
We use normally dynamic lookup when there is a continuous changes in source system.
The default cache property of the lookup table is static.
When we do lookup of target table using static cache then when we run the integeration service it caches the target table only once through out the session. It will not upate any cache.
Let say we have to load type 2 in target table.
A B C
1 Tony Alive
A B C D E F
1 Tony Alive 2005-12-12 10:10:00 9999-12-12 00:00:00 I
When we run again the mapping then
A B C D E F
1 Tony Alive 2005-12-12 10:10:00 9999-12-12 00:00:00 U
Now the scenaio where dynamic vs static
Let say we are running above session with the above target record and we have new records related to same id or name
A B C
1 Tony Dead
1 Tony Again Alive
We need to maintain type 2 for above records sequencially
If we use static cache then we will get only one record with Alive..
then while type 2 for above new 2 records the E columns will be same so if many cases the start date (E) will be key and throws duplicate rows. It really does't know which record to pickup we normally take 9999 reord to update with current time.
In this case if we use dynamic cache then the cache is updated along with the target table.
so the above 2 records are sequencially inserted with correct end dates(999)..
note: some databases like Oralce doesn't support milliseconds. In that case there might be errors while loading.
Real scenario means i can tell banking but i didn't work on this ..
Let say you went to atm and with drawn some money with in 5 seconds you did another transaction.
so these transactions has to be captured and the money has to be subtracted when you did second transaction.
In this case a dynamic cache will work.
But normally for transactions most of companies uses webservices. i just given example ;-)