Informatica - Joiner Transformation

Joiner

Use the Joiner transformation to join source data from two related heterogeneous sources residing in different locations or file systems.


The two input pipelines include a master pipeline and a detail pipeline or a master and a detail branch. The master pipeline ends at the Joiner transformation, while the detail pipeline continues to the target.

limitations on the pipelines you connect to the Joiner transformation:

-->You cannot use a Joiner transformation when either input pipeline contains an Update Strategy transformation.

--> You cannot use a Joiner transformation if you connect a Sequence Generator transformation directly before the Joiner transformation.

Join Types

The Joiner transformation supports the following types of joins:
--> Normal
--> Master Outer
--> Detail Outer
--> Full Outer

Note: A normal or master outer join performs faster than a full outer or detail outer join.

Normal Join

With a normal join, the Integration Service discards all rows of data from the master and detail source that do not match, based on the condition.

Master Outer Join

A master outer join keeps all rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source.

Detail Outer Join

A detail outer join keeps all rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.

Full Outer Join

A full outer join keeps all rows of data from both the master and detail sources.

Caching Master Rows

When the Integration Service processes a Joiner transformation, it reads rows from both sources concurrently and builds the index and data cache based on the master rows. The Integration Service then performs the join based on the detail source data and the cache data. The number of rows the Integration Service stores in the cache depends on the partitioning scheme, the source data, and whether you configure the Joiner transformation for sorted input. To improve performance for an unsorted Joiner transformation, use the source with fewer rows as the master source. To improve performance for a sorted Joiner transformation, use the source with fewer duplicate key values as the master.

Note:

The Joiner transformation does not match null values. For example, if both EMP_ID1 and EMP_ID2 contain a row with a null value, the Integration Service does not consider them a match and does not join the two rows. To join rows with null values, replace null input with default values, and then join on the default values.