Joiner Transformation

Explain about Joiner Transformation

Joiner Transformation is an active transformation which allows you to combine the data records horizontally based on join condition i.e.., you need a matching column to join the data.
Joiner transformation combines the data from two different sources having different metadata
A Joiner transformation is created with following types of ports
1. Input port (I)
2. Output port (O)
3. Master port (M)

A Joiner transformation can only join two input data stream per joiner
The sources to the Joiner transformation designated as "Master source" and "Detailed source"
If you have more than one source, you need to connected output of a joiner to another input pipeline.

A source which is having the lesser number of records is designated as "master source" and the other source is designated as "detailed source"
A Master source is the source which occupies least amount of space or memory in cache

Joiner transformation supports homogeneous and heterogeneous joins

A join which is made on same data source is known as homogeneous join
Ex: Oracle + Oracle  or    SQL server + SQL server


A join which is made on different data sources is known as heterogeneous join
Ex: Oracle + SQL server

The Joiner transformation is created with the following types of joins
1.  Normal Join (equi join or inner join)
2.  Master outer join
3.  Detail outer join
4.  Full outer join

The default join type is Normal join

Consider the following data

EMP1 - MASTER SOURCE

NAME                     DEPTNO                CITY                      
Mark                             10                     London   
Antony                          20                     Chicago
Ram                               30                     Hyderabad


EMP2 - DETAIL SOURCE

NAME                     DEPTNO                AGE
Mark                            10                         42
Ram                              30                        28
Garry                           40                         39


NORMAL JOIN 
NAME                     DEPTNO                CITY                  AGE                 
Mark                             10                     London                    42
Ram                               30                  Hyderabad                28

MASTER OUTER JOIN
NAME                     DEPTNO                CITY                  AGE                 
Mark                             10                     London                    42
Ram                               30                  Hyderabad                28
Garry                           40                         NULL                   39

DETAILED OUTER JOIN
NAME                     DEPTNO                CITY                  AGE                 
Mark                             10                     London                    42
Antony                          20                     Chicago                  NULL
Ram                               30                  Hyderabad                28

FULL OUTER JOIN
NAME                     DEPTNO                CITY                  AGE                 
Mark                             10                     London                    42
Antony                          20                     Chicago                  NULL
Ram                               30                  Hyderabad                28
Garry                           40                         NULL                   39

When you run a session with a Joiner transformation, the Integration Service blocks and unblocks the source data, based on the mapping configuration and whether you configure the Joiner transformation for sorted input.



Unsorted Joiner Transformation
When the Integration Service processes an unsorted Joiner transformation, it reads all master rows before it reads the detail rows. To ensure it reads all master rows before the detail rows, the Integration Service blocks the detail source while it caches rows from the master source. Once the Integration Service reads and caches all master rows, it unblocks the detail source and reads the detail rows. Some mappings with unsorted Joiner transformations violate data flow validation.

Sorted Joiner Transformation
When the Integration Service processes a sorted Joiner transformation, it blocks data based on the mapping configuration. Blocking logic is possible if master and detail input to the Joiner transformation originate from different sources. 

The Integration Service uses blocking logic to process the Joiner transformation if it can do so without blocking all sources in a target load order group simultaneously. Otherwise, it does not use blocking logic. Instead, it stores more rows in the cache.
When the Integration Service can use blocking logic to process the Joiner transformation, it stores fewer rows in the cache, increasing performance.

The Joiner transformation doesn't support non-equijoin
The Joiner transformation supports only "AND" operator
You cannot use a Joiner transformation when both the input pipelines contains an Update Strategy transformation.  
You cannot use a Joiner transformation if you connect a Sequence Generator transformation directly before the Joiner transformation. 


Differences between Sorter and Union Transformation
http://prashanthobiee.blogspot.in/2013/02/difference-between-joiner-and-union.html