Difference between static cache and dynamic cache

Difference between static cache and dynamic cache?

Property	Static Cache Lookup	Dynamic Cache Lookup
How it work	In Static Cache the Integration Service does not update the  cache while it processes the transformation. Cache is created, when lookup is called for the first time. After that it is never updated.	In Dynamic Cache ,the Integration Service dynamically inserts or updates data in the lookup cache and passes the data to the target
Scenarios	Static Cache is normally used while referring to reference Data or when Data source is not a target for the mapping.Example : Referring to Dept table to get Dept Name based on the DEPT no for Each employee.	Dynamic Cache is normally used when we refer to Target as the lookup source,  which actually change while running the mapping.Example : If there are 2 or more entries of the same customer on the same day in Source systems then while loading to the target we want the Lookup Cache to be refreshed dynamically(Insert/Update) and see the latest data in Cache
How to Enable	Lookup Can be marked as static, by unchecking the “Dynamic lookup  Cache” in Properties Tab of Lookup Transformation.Note: “Lookup caching Enabled” should be checked first to enable the cache.	Lookup Can be marked as dynamic , by checking the “Dynamic lookup  Cache” in Properties Tab of Lookup Transformation.Note: “Lookup caching Enabled” should be checked first to enable the cache.
Default Cache	By Default Cache lookup Transformation is static.	By Default Cache lookup Transformation is not dynamic .
Condition	In Statuc cache Lookup , we can use all type of operator like =. >, >=  etc while adding the condition in condition tab	In Dynamic Cache lookup , we can use only equal operator (=) while adding the condition in condition tab of lookup Transformation
Cache Type	Static Cache is read only	Dynamic Cache is Read/Write
Return Value	Informatica returns value when condition is true and if it is false it will return default value  in connected look up and Null value in unconnected look up	When condition is false , then we insert the new record in Target as well as in Lookup Cache.
ConnectedUnconnected	Can be configured as connected as well as unconnected lookup	Can be configured as connected only
Support	Supports lookup on flatfile	It does not support lookup on flat file

Why lookup is Active in informatica 9.x?

Why lookup is Active in informatica 9.x?

Active Transformation:
An active Transformation can change the number of rows that pass through it form source to target i.e it eliminates rows that do not meet the condition in transformation.

Passive Transformation:
A passive transformation does not change the number of rows that pass through it i.e it passes all rows through the transformation.

In Informatica 9.x Lookup can return multiple rows on Successful match.

Properties > Lookup Policy on Multiple match

deleted record from Target table

Same as previous scenario, here target records has to be deleted if corresponding record gets deleted in source

mapping:

Joiner Transformation:
1. Target table as Master
2. Join Type : Detail Outer Join
3. Condition : EMPNO_SRC = EMPNO_TGT

Update Strategy:
Update Strategy Expression:         IIF(ISNULL(EMPNO),DD_DELETE,DD_REJECT)

Delete Flag

Lets say a record is deleted in source which is already available in target,Requirement is to have a column DELETE_FLAG in target and stores 'Y' for the records
deleted in source else 'N'

Source table: SRC_EMP

Target Table: TGT_EMP

Add D_FLAG column to TGT_EMP

Import Source table to Informatica as Source.
Import Target table to Informatica as both Source and Target.

Use Joiner Transformations as below

Use Expression Transformation as below

Use Update Strategy as below

 Final mapping looks like below

Workflow Designer:
session >
Properties >Target Source Row as - Update
Mapping > SQ_SRC_EMP should be Source
Mapping > SQ_TGT_EMP should be Target
Mapping > TGT_EMP should be  Target

Result:

Please post your comments.