By default, Informatica enables cache while working with Lookup transformation. The lookup first gets the data from Lookup-table to cache for validation.
Lookup cache in informatica
When we Lookup the same table with different Lookups, then get it every time, the cache is time taking since each time service will get table data into it. To avoid this, we can select a persistence cache in informatica, which will not refresh during the session.
The cache can be Persistent or non-persistent and will refresh after every successful session. If we do not select a Persistent-cache for big tables, we will cross SLA loading time.
Properties of Lookup
- Lookup cache persistent: To be checked i.e. a Named Persistent Cache will be used.
- Cache File Name Prefix: user_defined_cache_file_name i.e. the Named Persistent cache file name that will be used in all the other mappings using the same lookup table. Enter the prefix name only. Do not enter .idx or .dat
- Re-cache from lookup source: To be checked i.e. the Named Persistent Cache file will be rebuilt or refreshed with the current data of the lookup table.
Later, in the other Lookups (using the same table), we need to select 2 properties:
- Lookup cache persistent:To be checked i.e. a Named Persistent Cache will be used.
- Cache File Name Prefix:user_defined_cache_file_name i.e. the Named Persistent cache file name that will be used in all the other mappings using the same lookup table. Enter the prefix name only. Do not enter .idx or .dat
- Refer here for more details.
Dynamic Cache:
In the dynamic cache, service will do some changes, based on the source data.
0- Row is updated
1- Row is inserted
2- Row does not changeBy giving router transformation, we can send data to target tables. See here more details on dynamic lookup.
Related articles
- Informatica- Fatal Error Oradriver.cpp at line 416- Resolution (srinimf.com)
- How to: Build Dynamic Lookup (daxbeginners.wordpress.com)
Srinimf 
One thought
Comments are closed.