Informatica- Lookup Caches

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

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.

In the first Lookup we need to select 3 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
  • 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.

Advertisements

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.