The joiner transformation is an active and connected transformation used to join two heterogeneous sources. The joiner transformation joins sources based on a condition that matches one or more pairs of columns between the two sources. The two input pipelines include a master and a detail pipeline or branch. To join more than two sources, you need to join the output of the joiner transformation with another source. To join n number of sources in a mapping, you need n-1 joiner transformations.
Creating Joiner Transformation
Follow the below 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.
Configuring Joiner Transformation
Configure the following properties of joiner transformation:
- Case-Sensitive String Comparison: When performing joins on string columns, the integration service uses this option. By default the case sensitive string comparison option is checked.
- Cache Directory: Directory used to cache the master or detail rows. The default directory path is $PMCacheDir. You can override this value.
- Join Type: The type of join to be performed. Normal Join, Master Outer Join, Detail Outer Join or Full Outer Join.
- Tracing Level: Level of tracing to be tracked in the session log file.
- Joiner Data Cache Size: Size of the data cache. The default value is Auto.
- Joiner Index Cache Size: Size of the index cache. The default value is Auto.
- Sorted Input: If the input data is in sorted order, then check this option for better performance.
- Master Sort Order: Sort order of the master source data. Choose Ascending if the master source data is sorted in ascending order. You have to enable Sorted Input option if you choose Ascending. The default value for this option is Auto.
- Transformation Scope: You can choose the transformation scope as All Input or Row.
Join Condition
The integration service joins both the input sources based on the join condition. The join condition contains ports from both the input sources that must match. You can specify only the equal (=) operator between the join columns. Other operators are not allowed in the join condition. As an example, if you want to join the employees and departments table then you have to specify the join condition as department_id1= department_id. Here department_id1 is the port of departments source and department_id is the port of employees source.
Join Type
The joiner transformation supports the following four types of joins.
- Normal Join
- Master Outer Join
- Details Outer Join
- Full Outer Join
We will learn about each join type with an example. Let say i have the following students and subjects tables as the source.
Table Name: Subjects
Subject_Id subject_Name
-----------------------
1 Maths
2 Chemistry
3 Physics
Table Name: Students
Student_Id Subject_Id
---------------------
10 1
20 2
30 NULL
Assume that subjects source is the master and students source is the detail and we will join these sources on the subject_id port.
Normal Join:
The joiner transformation outputs only the records that match the join condition and discards all the rows that do not match the join condition.
The output of the normal join is
Master Ports | Detail Ports
---------------------------------------------
Subject_Id Subject_Name Student_Id Subject_Id
---------------------------------------------
1 Maths 10 1
2 Chemistry 20 2
Master Outer Join:
In a master outer join, the joiner transformation keeps all the records from the detail source and only the matching rows from the master source. It discards the unmatched rows from the master source. The output of master outer join is
Master Ports | Detail Ports
---------------------------------------------
Subject_Id Subject_Name Student_Id Subject_Id
---------------------------------------------
1 Maths 10 1
2 Chemistry 20 2
NULL NULL 30 NULL
Detail Outer Join:
In a detail outer join, the joiner transformation keeps all the records from the master source and only the matching rows from the detail source. It discards the unmatched rows from the detail source. The output of detail outer join is
Master Ports | Detail Ports
---------------------------------------------
Subject_Id Subject_Name Student_Id Subject_Id
-------------------------------------------------------------------
1 Maths 10 1
2 Chemistry 20 2
3 Physics NULL NULL
Full Outer Join:
The full outer join first brings the matching rows from both the sources and then it also keeps the non-matched records from both the master and detail sources. The output of full outer join is
Master Ports | Detail Ports
---------------------------------------------
Subject_Id Subject_Name Student_Id Subject_Id
---------------------------------------------
1 Maths 10 1
2 Chemistry 20 2
3 Physics NULL NULL
NULL NULL 30 NULL
Limitations of Joiner Transformation
The limitations of joiner transformation are
- You cannot use joiner transformation when the input pipeline contains an update strategy transformation.
- You cannot connect a sequence generator transformation directly to the joiner transformation.