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
  26. POWERCENTERHELP

Advanced Workflow Guide

Advanced Workflow Guide

Key Range Partition Type

Key Range Partition Type

With key range partitioning, the Integration Service distributes rows of data based on a port or set of ports that you define as the partition key. For each port, you define a range of values. The Integration Service uses the key and ranges to send rows to the appropriate partition.
For example, if you specify key range partitioning at a Source Qualifier transformation, the Integration Service uses the key and ranges to create the WHERE clause when it selects data from the source. Therefore, you can have the Integration Service pass all rows that contain customer IDs less than 135000 to one partition and all rows that contain customer IDs greater than or equal to 135000 to another partition.
If you specify hash user keys partitioning at a transformation, the Integration Service uses the key to group data based on the ports you select as the key. For example, if you specify ITEM_DESC as the hash key, the Integration Service distributes data so that all rows that contain items with the same description go to the same partition.
Use key range partitioning in mappings where the source and target tables are partitioned by key range.
The following figure shows a mapping where key range partitioning can optimize writing to the target table:
This mapping includes a flat file source, a Source Qualifier transformation, a Filter transformation, a Sorter transformation, an Aggregator transformation, and an Orcle target.
The target table in the database is partitioned by ITEM_ID as follows:
  • Partition 1: 0001–2999
  • Partition 2: 3000–5999
  • Partition 3: 6000–9999
To optimize writing to the target table, complete the following tasks:
  1. Set the partition type at the target instance to key range.
  2. Create three partitions.
  3. Choose ITEM_ID as the partition key.
    The Integration Service uses this key to pass data to the appropriate partition.
  4. Set the key ranges as follows:
    ITEM_ID
    Start Range
    End Range
    Partition #1
    -
    3000
    Partition #2
    3000
    6000
    Partition #3
    6000
    -
When you set the key range, the Integration Service sends all items with IDs less than 3000 to the first partition. It sends all items with IDs between 3000 and 5999 to the second partition. Items with IDs greater than or equal to 6000 go to the third partition.


Updated July 04, 2018