Informatica Lookup Cache Why you need it

By default, Informatica enables cache while working with Lookup transformation. Since Lookup will first get data from the Lookup table into the cache for validation.

  • Cache in Lookup-Transformation

When we are Lookup the same table with different Lookups, then getting every time, cache is time taking, since each time service will get table data into cache. To avoid this, we can select Persistent cache. So that caches will not be refreshed during the session.

The cache (Persistent and non-persistent) will be refreshed after every successful session. If we do not select Persistent cache for big tables, we will cross SLA loading time.

Properties to Select When You Use 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 change

By giving router transformation, we can send data to target tables. See here more details on dynamic lookup.

Related articles

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.