Table of Contents

Search

  1. Preface
  2. Understanding Pipeline Partitioning
  3. Partition Points
  4. Partition Types
  5. Pushdown Optimization
  6. Pushdown Optimization and Transformations
  7. Real-time Processing
  8. Commit Points
  9. Row Error Logging
  10. Workflow Recovery
  11. Stopping and Aborting
  12. Concurrent Workflows
  13. Grid Processing
  14. Load Balancer
  15. Workflow Variables
  16. Parameters and Variables in Sessions
  17. Parameter Files
  18. FastExport
  19. External Loading
  20. FTP
  21. Session Caches
  22. Incremental Aggregation
  23. Session Log Interface
  24. Understanding Buffer Memory
  25. High Precision Data

Advanced Workflow Guide

Advanced Workflow Guide

Database Partitioning with One Source

Database Partitioning with One Source

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


Updated November 14, 2019