Maintain Order in a Partitioned Mapping
You can establish order in a mapping with a sorted flat file source, a sorted relational source, or a Sorter transformation. When the Data Integration Service adds a partition point to a mapping, it might redistribute data and lose an order established earlier in the mapping. To maintain order in a partitioned mapping, you must specify that some transformations and Write transformations maintain the row order.
You can specify that the following mapping objects maintain the row order of the input data:
- •Expression transformation
- •Java transformation
- •Sequence Generator transformation
- •SQL transformation
- •Write transformation
For example, if a relational target has a database trigger that depends on the data being written in the sorted order, configure the Write transformation to maintain the row order.
When you configure Write transformations to maintain row order, the Data Integration Service uses a single thread to write to the target. If an Aggregator transformation that uses sorted input precedes the Write transformation, the Data Integration Service uses a single thread to process both the Aggregator transformation and the target.
When you configure all other transformations to maintain row order, the Data Integration Service determines the optimal number of threads for the transformation pipeline stage while maintaining the order.
The method that you use to configure transformations to maintain row order depends on the following object types:
- Expression, Sequence Generator, or SQL transformation
- Select the Maintain Row Order property in the Advanced properties of an Expression, Sequence Generator, or SQL transformation.
- Java transformation
- Select the Stateless property in the Advanced properties of a Java transformation.
- Write transformation
- Select the Maintain Row Order property in the Advanced properties of the Write transformation.
Maintain a Stable Sort
When you maintain order in a partitioned mapping, the Data Integration Service does not perform a stable sort. The Data Integration Service maintains the order of the rows based on the sort key. However, if multiple rows have equal values for the sort key, the rows might not appear in the same relative order in the output as they appear in the input.
For example, a mapping enabled for partitioning reads from a sorted flat file source that contains the following data:
Order_ID,Item_ID,Item,Quantity,Price
45,000468,ItemD,5,0.56
45,123456,ItemA,5,3.04
41,456789,ItemB,2,12.02
43,123456,ItemA,3,3.04
The mapping includes a Sorter transformation that specifies Order_ID as the sort key with the direction as descending. When the Data Integration Service uses multiple threads to run the Sorter transformation, it might not maintain the relative order of the rows with the same value for Order_ID. For example, the service might write the rows to a merged target file in the following order:
Order_ID,Item_ID,Item,Quantity,Price
45,123456,ItemA,5,3.04
45,000468,ItemD,5,0.56
43,123456,ItemA,3,3.04
41,456789,ItemB,2,12.02
To maintain a stable sort, disable partitioning for the mapping by setting the Maximum Parallelism run-time property for the mapping to 1.