You can specify different partition types at different points in the pipeline to increase session performance. To optimize session performance, use the database partitioning partition type for source and target databases. You can use database partitioning for Oracle and IBM DB2 sources and IBM DB2 targets. When you use source database partitioning, the Integration Service queries the database system for table partition information and fetches data into the session partitions. When you use target database partitioning, the Integration Service loads data into corresponding database partition nodes.
You can use multiple pipeline partitions and database partitions. To improve performance, ensure the number of pipeline partitions equals the number of database partitions. To improve performance for subpartitioned Oracle sources, ensure the number of pipeline partitions equals the number of database subpartitions.
To increase performance, specify partition types at the following partition points in the pipeline:
Source Qualifier transformation.
To read data from multiple flat files concurrently, specify one partition for each flat file in the Source Qualifier transformation. Accept the default partition type, pass-through.
Since the source files vary in size, each partition processes a different amount of data. Set a partition point at the Filter transformation, and choose round-robin partitioning to balance the load going into the Filter transformation.
To eliminate overlapping groups in the Sorter and Aggregator transformations, use hash auto-keys partitioning at the Sorter transformation. This causes the Integration Service to group all items with the same description into the same partition before the Sorter and Aggregator transformations process the rows. You can delete the default partition point at the Aggregator transformation.
Since the target tables are partitioned by key range, specify key range partitioning at the target to optimize writing data to the target.