Lookup Caches

We can configure a Lookup transformation to cache the lookup table. The Integration Service (IS) builds a cache in memory when it processes the first row of data in a cached Lookup transformation. The Integration Service also creates cache files by default in the $PMCacheDir. If the data does not fit in the memory cache, the IS stores the overflow values in the cache files. When session completes, IS releases cache memory and deletes the cache files.

Note:

• If we use a flat file lookup, the IS always caches the lookup source.
• We set the Cache type in Lookup Properties.

Lookup Cache Files:

1. Lookup Index Cache: Stores data for the columns used in the lookup condition.
2. Lookup Data Cache: For a connected Lookup transformation, stores data for the connected output ports, not including ports used in the lookup condition. For an unconnected Lookup transformation, stores data from the return port.

Types of Lookup Caches:

1. Static Cache:
By default, the IS creates a static cache. It caches the lookup file or table and looks up values in the cache for each row that comes into the transformation. The IS does not update the cache while it processes the Lookup transformation.
2. Dynamic Cache:
To cache a target table or flat file source and insert new rows or update existing rows in the cache, use a Lookup transformation with a dynamic cache. The IS dynamically inserts or updates data in the lookup cache and passes data to the target. Target table is also our lookup table. No good for performance if table is huge.
3. Persistent Cache:
If the lookup table does not change between sessions, we can configure the Lookup transformation to use a persistent lookup cache. The IS saves and reuses cache files from session to session, eliminating the time required to read the lookup table.
4. Recache from Source:
If the persistent cache is not synchronized with the lookup table, we can configure the Lookup transformation to rebuild the lookup cache. If Lookup table has changed, we can use this to rebuild the lookup cache.
5. Shared Cache:
There are two types of Shared Cache. They are...
1. Unnamed cache: When Lookup transformations in a mapping have compatible caching structures, the IS shares the cache by default. You can only share static unnamed caches.
2. Named cache: Use a persistent named cache when we want to share a cache file across mappings or share a dynamic and a static cache. The caching structures must match or be compatible with a named cache. You can share static and dynamic named caches.