Lookup Transformation(3) changes in informatica version 9



Lookup Transformation is Active

One of the changes that made in informatica version 9 was making the lookup transformation as active transformation. The lookup transformation can return all the matching rows.

When creating the lookup transformation itself you have to specify whether the lookup transformation returns multiple rows or not. Once you make the lookup transformation as active transformation, you cannot change it back to passive transformation. The "Lookup Policy on Multiple Match" property value will become "Use All Values". This property becomes read-only and you cannot change this property.

As an example, for each country you can configure the lookup transformation to return all the states in that country. You can cache the lookup table to improve performance. If you configure the lookup transformation for caching, the integration service caches all the rows form the lookup source. The integration service caches all rows for a lookup key by the key index.

Guidelines for Returning Multiple Rows:
配置lookup transformation 返回多行的原则：

Follow the below guidelines when you configure the lookup transformation to return multiple rows:

• You can cache all the rows from the lookup source for cached lookups. 可以缓存所有行
• You can customize the SQL Override for both cached and uncache lookup that return multiple rows. 可自定义缓存或不缓存 lookup 返回的多行记录
• You cannot use dynamic cache for Lookup transformation that returns multiple rows. 不能使用动态缓存
• You cannot return multiple rows from an unconnected Lookup transformation. 不能返回多行记录从一个不连接的lookup transformation
• You can configure multiple Lookup transformations to share a named cache if the Lookup transformations have matching caching lookup on multiple match policies.
• Lookup transformation that returns multiple rows cannot share a cache with a Lookup transformation that returns one matching row for each input row. 返回多行记录的lookup transformation 与一个输入只有一个匹配行的返回值的lookup Transformation  不能 共享一个缓存