A Joiner transformation in IICS-IDMC can join data from two related Heterogeneous sources. Also, we can join homogeneous source tables.
Usage:
▹ You can use the Joiner transformation to join a flat file with a Relational table.
▹ To Join two heterogeneous sources using Joiner transformation, at least one common with common data required between two sources.
▹ You can create multiple join conditions.
▹ Joiner will support only equality operator (=) in join condition.
▹ When you link a transformation to the Joiner transformation, you connect it to the Master or Detail group. To improve job performance, connect the transformation that represents the smaller data set to the Master group.
▹ You can use the following join types in Joiner Transformation as shown below.
- Normal Join – Matching Records between Master and Detail source
- Master Outer Join – All Records from Detail source, Matching records from Master source
- Detail Outer Join – All records from Master source, Matching records from Detail source
- Full Outer Join – All records from Master source and Detail source.
▹ Master outer join in Joiner transformation is Equivalent to Right outer join in oracle.
▹ Detail outer join in Joiner transformation is Equivalent to Left outer join in oracle.
▹ Field name conflicts can occur when you join sources with matching field names. You can resolve the conflict in one of the following ways:
- Create a field name conflict resolution.
- Rename matching fields in an upstream transformation.
- Pass data through an Expression transformation to rename fields.
Improve the performance of joiner Transformation:
▹ Take the source which has less numbers of records as master source in joiner TR in the mapping.
▹ Use sorter Input option and send the Sorted data to joiner to improve the performance.
▹ Join based on numerical columns
▹ Use Normal Join whenever possible.
▹ When sources are homogeneous tables, instead of using joiner, Use Source Transformation will improve the performance.
Join more than 2 sources:
▹ To join more than two sources in a mapping, you can use multiple Joiner transformations. You can join the output from the Joiner transformation with another source pipeline.
▹ To join N heterogeneous sources, N-1 Joiner Transformations are required.
Joiner transformation Cache:
There are two types of cache in Joiner Transformation
JOINER INDEX CACHE — it will store the ports in the joining condition
JOINER DATA CACHE — it will store the ports other than joining condition
Difference between Joiner transformation and Source Transformation in IICS -IMDC
| Source Transformation | Joiner Transformation |
|---|---|
| To join homogeneous tables only | To join heterogeneous sources |
| Source transformation will support all types of joins which supported by source database | Joiner will support only four type of joins |
| To join “n” source tables, only one source transformation is required | To join “n” sources, “n-1” joiner transformations required |
| We can write user defined SQL queries | There is no SQL query option in joiner |
| Source transformation have filter, sort and select distinct properties | None of the properties available |
| Source query can support any operator | Joiner will support only equality operator |
| Use source transformation to join whenever possible | Join only when sources are heterogeneous |
| Source transformation is not having any cache | Joiner is having index and data cache |
| Source transformation does not have sorter input option | Joiner is having sorted input option |
Leave a comment