Joiner transformation is an active and connected transformation. It provides the option of creating joins in the Informatica. By using the joiner transformation, the created joins are similar to the joins in databases.
The joiner transformation is used to join two heterogeneous sources. The joiner transformation joins sources on the basis of 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. We need to join the output of the joiner transformation with another source to join more than two sources. And to join n number of sources in mapping, we need n-1 joiner transformations.
In joiner transformation, there are two sources which we are using for joins, such as:
In the properties of joiner transformation, we can select which data source can be a Master source and which source can be a detail source.
During execution, the master source is cached into the memory for joining purpose. So it is necessary to select the source with less number of records as the master source.
Configuring Joiner Transformation
In Informatica, we configure the following properties of joiner transformation, such as:
Types of Joins
In Informatica, the following joins can be created using joiner transformation, such as:
In the following example, we will join emp and dept tables using joiner transformation in the following steps:
Step 1: Create a new target table EMP_DEPTNAME in the database using the below script and import the table in Informatica targets.
Step 2: Create a new mapping and import source tables "EMP" and "DEPT" and target table, which we created in the previous step.
Step 3: From the transformation menu, select create option and,
Step 4: Drag and drop all the columns from both the source qualifiers to the joiner transformation.
Step 5: Double click on the joiner transformation, then in the edit transformation window:
Step 6: Then, in the same window:
For performance optimization, we assign the master source to the source table pipeline, which is having less number of records. To perform this task:
Step 7: Double click on the joiner transformation to open the edit properties window, and then
Step 8: Link the relevant columns from the joiner transformation to the target table.
Now save the mapping and execute it after creating a session and workflow for it. The join will be created using Informatica joiner, and relevant details will be fetched from both the tables.
When both the Master and detail source are sorted on the ports specified in the join condition, then use the sorted input option in the joiner properties tab.
We can improve the performance by using the sorted input option as the integration service performs the join by minimizing the number of disk IOs. It gives excellent performance when we are working with large data sets.
The joiner Transformation is called as the blocking transformation. The integration service blocks and unblocks the source data depending on whether the joiner transformation is configured for sorted input or not.
Unsorted Joiner Transformation
In the case of unsorted joiner transformation, the integration service first reads all the master rows before it reads the detail rows.
The integration service blocks the detail source while it caches all the master rows. Once it reads all the master rows, then it unblocks the detail source and understands the details rows.
Sorted Joiner Transformation
The blocking logic may or may not possible in case of sorted joiner transformation. The integration service uses blocking logic if it can do so without blocking all sources in the target load order group. Otherwise, it does not use blocking logic.
How to Improve Joiner Transformation Performance?
Below are some important points to improve the performance of a joiner transformation, such as:
Limitations of Joiner Transformation
Here are the following limitations of joiner transformation, such as: