Partitioned Transformations
When a mapping enabled for partitioning contains a transformation that supports partitioning, the Data Integration Service can use multiple threads to run the transformation.
The Data Integration Service determines whether it needs to add an additional partition point at the transformation, and then determines the optimal number of threads for that transformation pipeline stage. The Data Integration Service also determines whether it needs to redistribute data at the partition point. For example, the Data Integration Service might redistribute data at an Aggregator transformation to group rows for an aggregate expression.
Some transformations do not support partitioning. When a mapping enabled for partitioning contains a transformation that does not support partitioning, the Data Integration Service uses one thread to run the transformation. The Data Integration Service can use multiple threads to run the remaining mapping pipeline stages.
The following transformations do not support partitioning:
- •Association
- •Consolidation
- •Exception
- •Match, when configured for field match analysis
- •REST Web Service Consumer
- •Unconnected Lookup
- •Web Service Consumer
Restrictions for Partitioned Transformations
Some transformations that support partitioning require specific configurations. If a mapping enabled for partitioning contains a transformation with an unsupported configuration, the Data Integration Service uses one thread to run the transformation. The Data Integration Service can use multiple threads to process the remaining mapping pipeline stages.
The following transformations require specific configurations to support partitioning:
- •Aggregator transformations must include a group by port. Aggregator transformations must not include a pass-through port. Aggregator transformations must not include numeric functions that calculate running totals and averages on a row-by-row basis.
- •Expression transformations must not include the following types of functions or variables:
- - Numeric functions that calculate running totals and averages on a row-by-row basis.
- - Special functions that might return different results when multiple threads process the transformation.
- - Local variables that depend on the value of a previous row.
- •Decision, Java, and SQL transformations must have the Partitionable property enabled.
- •Joiner transformations must include a join condition that uses an equality operator. If the join condition includes multiple equality conditions, the conditions must be combined using the AND operator.
- •Rank transformations must include a group by port.
Cache Partitioning for Transformations
Cache partitioning creates a separate cache for each partition that processes an Aggregator, Joiner, Rank, Lookup, or Sorter transformation. During cache partitioning, each partition stores different data in a separate cache. Each cache contains the rows needed by that partition.
Cache partitioning optimizes mapping performance because each thread queries a separate cache in parallel. When the Data Integration Service creates partitions for a mapping, the Data Integration Service always uses cache partitioning for partitioned Aggregator, Joiner, Rank, and Sorter transformations. The Data Integration Service might use cache partitioning for partitioned Lookup transformations.
The Data Integration Service uses cache partitioning for connected Lookup transformations under the following conditions:
- •The lookup condition contains only equality operators.
- •When the connected Lookup transformation looks up data in a relational table, the database is configured for case-sensitive comparison.
For example, if the lookup condition contains a string port and the database is not configured for case-sensitive comparison, the Data Integration Service does not use cache partitioning.
When the Data Integration Service does not use cache partitioning for a Lookup transformation, all threads that run the Lookup transformation share the same cache. Each thread queries the same cache serially.
Note: The Data Integration Service does not use cache partitioning for unconnected Lookup transformations because the service uses one thread to run unconnected Lookup transformations.
Cache Size for Partitioned Caches
When the Data Integration Service uses cache partitioning for Aggregator, Joiner, Rank, Lookup, and Sorter transformations, the service divides the cache size across the partitions.
You configure the cache size in the transformation advanced properties. You can enter a numeric value in bytes, or you can select Auto to have the Data Integration Service determine the cache size at run time.
If you enter a numeric value, the Data Integration Service divides the cache size across the number of transformation threads at run time. For example, you configure the transformation cache size to be 2,000,000 bytes. The Data Integration Service uses four threads to run the transformation. The service divides the cache size value so that each thread uses a maximum of 500,000 bytes for the cache size.
If you select Auto, the Data Integration Service determines the cache size for the transformation at run time. The service then divides the cache size across the number of transformation threads.
Optimize Cache Directories for Partitioning
For optimal performance during cache partitioning for Aggregator, Joiner, Rank, and Sorter transformations, configure multiple cache directories.
Transformation threads write to the cache directory when the Data Integration Service uses cache partitioning and must store overflow values in cache files. When multiple threads write to a single directory, the mapping might encounter a bottleneck due to input/output (I/O) contention. An I/O contention can occur when threads write data to the file system at the same time.
When you configure multiple cache directories, the Data Integration Service determines the cache directory for each transformation thread in a round-robin fashion. For example, you configure an Aggregator transformation to use directoryA and directoryB as cache directories. If the Data Integration Service uses four threads to run the Aggregator transformation, the first and third transformation threads store overflow values in cache files in directoryA. The second and fourth transformation threads store overflow values in cache files in directoryB.
If the Data Integration Service does not use cache partitioning for the Aggregator, Joiner, Rank, or Sorter transformation, the service stores overflow values in cache files in the first listed directory.
Note: A Lookup transformation can only use a single cache directory.
Configure the cache directories in the Cache Directory property for the Aggregator, Joiner, or Rank transformation advanced properties. Configure the cache directories in the Work Directory property for the Sorter transformation advanced properties. By default, the Cache Directory and Work Directory properties are configured to use the system parameter values defined for the Data Integration Service. Use the default CacheDir or TempDir system parameter value if an administrator entered multiple directories separated by semicolons for the Cache Directory or Temporary Directories property for the Data Integration Service.
You can enter a different value to configure multiple cache directories specific to the transformation. Enter multiple directories separated by semicolons for the property or for the user-defined parameter assigned to the property.
Disable Partitioning for a Transformation
A partitioned Decision, Java, or SQL transformation might not return the same result for each mapping run. You can disable partitioning for these transformations so that the Data Integration Service uses one thread to process the transformation. The Data Integration Service can use multiple threads to process the remaining mapping pipeline stages.
In a Java or SQL transformation, the Partitionable advanced property is selected by default. Clear the advanced property to disable partitioning for the transformation.
In a Decision transformation, the Partitionable advanced property is cleared by default. Select the advanced property to enable partitioning for the transformation.
The reason that you might want to disable partitioning for a transformation depends on the transformation type.
Decision Transformation
You might want to disable partitioning for a Decision transformation that uses a numeric function. The numeric functions CUME, MOVINGSUM, and MOVINGAVG calculate running totals and averages on a row-by-row basis. If a partitioned Decision transformation includes one of these functions, each thread processes the function separately. Each function calculates the result using a subset of the data instead of all of the data. Therefore, a partitioned transformation that uses CUME, MOVINGSUM, or MOVINGAVG functions might not return the same calculated result with each mapping run.
Java Transformation
Disable partitioning for a Java transformation when the Java code requires that the transformation be processed with one thread.
SQL Transformation
Disable partitioning for an SQL transformation when the SQL queries require that the transformation be processed with one thread. Or, you might want to disable partitioning for an SQL transformation so that only one connection is made to the database.