Joiner Transformation In Informatica

What does Joiner Transformation do?

• Joins source data from two related heterogeneous sources or data from the same source
• There should be at least one matching column and a matching condition
• Contains Master Pipeline and Detail Pipeline
• To join more than two sources in a mapping, join the output from the Joiner transformation with another source Pipeline

How to Configure Joiner Transformation?

• Configure the Joiner transformation properties
• Configure the join condition
• Configure the join type
• Configure the session for sorted or unsorted input
• Configure the transaction scope

Limitations of Joiner Transformations

• 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 Condition in Joiner Transformation

• Based on one or more join condition between Master and Detail the rows are added or discarded.

• During a session, the Integration Service compares each row of the master source against the detail source. 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.

• The data types in join condition should be the same

Types of Joins In Joiner Transformation

• Normal: Equi Join
• Master Outer: Right Outer Join
• Detail Outer: Left Outer Join
• Full Outer: Full Outer Join

 

Using Sorted Input in Joiner Transformation

• When we use Sorted Input in Joiner it improves the performance of the Joiner and helps mainly when using larger data.

• For using sorted Input

1. First sort the incoming data using a Sorter or Source Qualifier
2. Use transformations that maintain the order of the sorted data.
3. Configure the Joiner transformation to use sorted data and configure the join condition to use the sort origin ports. The sort origin represents the source of the sorted data.
4. Define the join condition to receive sorted data in the same order as the sort origin.

• If you pass unsorted or incorrectly sorted data to a Joiner transformation configured to use sorted data, the session fails and the Integration Service logs the error in the session log file.

Example of a Join Condition

For example, you configure Sorter transformations in the master and detail pipelines with the following sorted ports:

1. EMPLOYEE_NUM

2. EMPLOYEE_NAME

3. AGE

When you configure the join condition, use the following guidelines to maintain sort order:

• You must use EMPLOYEE_NUM in the first join condition.

• If you add a second join condition, you must use EMPLOYEE_NAME.

• If you want to use AGE in a join condition, you must also use EMPLOYEE_NAME in the second join condition.

If you skip EMPLOYEE_NAME and join on EMPLOYEE_NUM and AGE, you lose the sort order and the Integration Service fails the session. When you use the Joiner transformation to join the master and detail pipelines, you can configure any one of the following join conditions:

EMPLOYEE_NUM = EMPLOYEE_NUM

Or

EMPLOYEE_NUM = EMPLOYEE_NUM1

EMPLOYEE_NAME = EMPLOYEE_NAME1

Or

EMPLOYEE_NUM = EMPLOYEE_NUM1

EMPLOYEE_NAME = EMPLOYEE_NAME1

AGE = AGE1