Informatica Basic Concepts: SQ, Lookup

SQL override:

The Source Qualifier provides the SQL Query option to override the default query. You can enter any SQL statement supported by your source database. You might enter your own SELECT statement, or have the database perform aggregate calculations, or call a stored procedure or stored function to read the data and perform some tasks.

Connected & Unconnected look-up:

An unconnected Lookup transformation exists separate from the pipeline in the mapping. You write an expression using the :LKP reference qualifier to call the lookup within another transformation. While the connected lookup forms a part of the whole flow of mapping.

Various Caches:

1. Persistent cache (we can save the lookup cache files and reuse them the next time process the lookup transformation)

2. Re-cache from database (If the persistent cache not synchronized with lookup table you can configure the lookup transformation to rebuild the lookup cache)

3. Static cache (When the lookup condition is true, Informatica server return a value from lookup cache and it’s does not update the cache while it processes the lookup transformation)

4. Dynamic cache (Informatica server dynamically inserts new rows or update existing rows in the cache and the target. Suppose if we want lookup a target table we can use dynamic cache)

5. Shared cache (we can share lookup transformation between multiple transformations in a mapping. 2 lookup in a mapping can share single lookup cache)

What is dynamic lookup?

When we use target lookup table, Informatica server dynamically insert new values or it updates if the values exist and passes to target table.