Joiner transformation

Joiner transformation is Connected and Active transformation. It is used to join the two tables or more tables from heterogeneous sources or multiple sources.
We join the both the tables by specifying a condition in the joiner transformation. If we want to join 'n' number of tables we connect 'n-1' joiner transformations. By default joiner transformation considers one of the tables as master and other as detail tables.

Cache files created:-

----------------------- 

There are 2 different Cache files created with Joiner transformation 

- Index Cache : Index cache stores all the port values which are participated in the join condition.

- Data Cache : Data cache have stored all ports which are not participated in the join condition.

Steps to create a joiner transformation in Informatica :-
------------------------------------------------------------
- Go to the mapping designer, click on the Transformation->Create.
- Select the joiner transformation, enter a name and click on OK.
- Drag the ports from the first source into the joiner transformation. By default   the designer creates the input/output ports for the source fields in the joiner   transformation as detail fields.
- Now drag the ports from the second source into the joiner transformation. By   default the designer configures the second source ports as master fields.
- Edit the joiner transformation, go the ports tab and check on any box in the M   column to switch the master/detail relationship for the sources.
- Go to the condition tab, click on the Add button to add a condition. You can   add multiple conditions.
- Go to the properties tab and configure the properties of the joiner     transformation.

There are 4 types of joins in joiner transformation :-
- Normal join
- Master outer join
- Detail outer join
- Full outer join

Normal join : It returns only the records that match the join condition given in the joiner transformation. The Normal Join in Informatica is exactly similar to SQL Inner Join.

Master outer join : It returns only the matching rows from the master table and all the rows from detail table. The Master Join in Informatica is exactly similar to SQL Left outer Join.

Detail outer join : It returns only the matching rows from the details table and all the rows from master table.The Detail Join in Informatica is exactly similar to SQL Right outer Join.

Full outer join : It first returns matching rows from both the table and also returns unmatched rows from both the tables.The Full Join in Informatica is exactly similar to SQL Full outer Join.


Joiner is a blocking transformation :-
---------------------------------------
Integration service blocks and unblocks the source data depending upon whether joiner is sorted or unsorted.

Unsorted Joiner : Here integration service first reads master rows by blocking detail rows, after competing caching of master data it unblocks the detail rows and reads the detail rows.
Sorted Joiner : It may not be possible here