When you use database partitioning with a source qualifier with one source, the Integration Service generates SQL queries for each database partition and distributes the data from the database partitions among the session partitions equally.
For example, when a session has three partitions, and the database has five partitions, the Integration Service executes SQL queries in the session partitions against the database partitions. The first and second session partitions receive data from two database partitions. The third session partition receives data from one database partition.
When you use an Oracle database, the Integration Service generates SQL statements similar to the following statements for partition 1:
SELECT <column list> FROM <table name> PARTITION <database_partition
1
name> UNION ALL
SELECT <column list> FROM <table name> PARTITION <database_partition
4
name> UNION ALL
When you use an IBM DB2 database, the Integration Service creates SQL statements similar to the following for partition 1:
SELECT <column list> FROM <table name>
WHERE (nodenumber(<column 1>)=
0
OR nodenumber(<column 1>) =
3
)
If an Oracle source has five partitions, 1–5, and two subpartitions,
a
and
b
, in each partition, and a session has three partitions, the Integration Service executes SQL queries in the session partitions against the database subpartitions. The first and second session partitions receive data from four database subpartitions. The third session partition receives data from two database subpartitions.
The Integration Service generates SQL statements similar to the following statements for partition 1:
SELECT <column list> FROM <table name> SUBPARTITION <database_subpartition
1_a
name> UNION ALL
SELECT <column list> FROM <table name> SUBPARTITION <database_subpartition
1_b
name> UNION ALL
SELECT <column list> FROM <table name> SUBPARTITION <database_subpartition
4_a
name> UNION ALL
SELECT <column list> FROM <table name> SUBPARTITION <database_subpartition
4_b
name> UNION ALL