Partitioned Relational Targets
When a mapping that is enabled for partitioning writes to an IBM DB2 for LUW or Oracle target, the Data Integration Service can use multiple threads to write to the relational target. The Data Integration Service creates a separate connection to the database for each thread.
Note: If a mapping writes to a relational target other than DB2 for LUW or Oracle, the Data Integration Service uses one thread to write to the target. The Data Integration Service can use multiple threads for the remaining mapping pipeline stages.
When the target is Oracle, the Data Integration Service uses the number of writer threads defined by the maximum parallelism value. If the Oracle relational table has partitions, the database routes the data to the correct partitions.
When the target is DB2 for LUW, the Data Integration Service queries the DB2 for LUW system for partition information. The Data Integration Service loads the partitioned data to the corresponding nodes in the target database.
The number of writer threads that the Data Integration Service uses for a DB2 for LUW target depends on the following situations:
- Number of database partitions is less than or equal to the maximum parallelism value.
- The Data Integration Service uses one writer thread for each database partition. Each writer thread writes to one database partition.
- Number of database partitions is more than the maximum parallelism value.
The Data Integration Service uses the number of writer threads defined by the maximum parallelism value. The Data Integration Service distributes multiple database partitions to some of the writer threads. For example, a DB2 for LUW target has five database partitions, and the maximum parallelism value is three. The Data Integration Service uses three writer threads. The Data Integration Service distributes two database partitions to the first writer thread and the second writer thread. The service distributes one database partition to the third writer thread.
- No database partitions.
The Data Integration Service uses the number of writer threads defined by the maximum parallelism value.
However, you can optimize load performance when the target has database partitions. In this case, each writer thread connects to the DB2 for LUW node that contains the database partition. Because the writer threads connect to different DB2 for LUW nodes instead of all threads connecting to the single master node, performance increases.
Relational Connection Types for Partitioning
The Data Integration Service can use multiple threads to write to a DB2 for LUW or an Oracle relational target based on the connection type used to connect to the database.
You can use any of the following connection types to connect to a DB2 for LUW or Oracle database:
- •DB2 for LUW connection or Oracle connection
- •JDBC connection
- •ODBC connection
To use multiple threads to write to a DB2 for LUW or Oracle relational target, the relational data object must use a DB2 for LUW or Oracle connection.
If the DB2 for LUW or Oracle relational data object uses a JDBC or ODBC connection, the Data Integration Service uses one thread to write to the target. The Data Integration Service can use multiple threads for the remaining mapping pipeline stages.
Rules and Guidelines for Relational Target Partitions
Consider the following rules and guidelines when you enable partitioning for a mapping that writes to a relational target:
- •The Data Integration Service uses one thread to write to the target, but can use multiple threads for the remaining mapping pipeline stages in the following situations:
- - The mapping writes to a relational target other than DB2 for LUW or Oracle.
- - The mapping uses a JDBC or ODBC connection to write to a DB2 for LUW or Oracle target.
- •Enable high precision for the mapping when a DB2 for LUW target table partition key is a decimal column. The Data Integration Service might fail the mapping when a partition key is a decimal column and you do not enable high precision for the mapping.