Table of Contents

Search

  1. Preface
  2. Mappings
  3. Mapplets
  4. Mapping Parameters
  5. Where to Assign Parameters
  6. Mapping Outputs
  7. Generate a Mapping from an SQL Query
  8. Dynamic Mappings
  9. How to Develop and Run a Dynamic Mapping
  10. Dynamic Mapping Use Cases
  11. Mapping Administration
  12. Export to PowerCenter
  13. Import From PowerCenter
  14. Performance Tuning
  15. Pushdown Optimization
  16. Partitioned Mappings
  17. Developer Tool Naming Conventions

Developer Mapping Guide

Developer Mapping Guide

SQL Queries for Partitioned Relational Sources

SQL Queries for Partitioned Relational Sources

When the Data Integration Service uses multiple threads to read a relational source, it generates an SQL query for each reader thread.
If the database source has more database partitions than the maximum parallelism value, the Data Integration Service distributes the data across the reader threads. The Data Integration Service can generate SQL queries that read from multiple database partitions. When an Oracle source contains subpartitions, the Data Integration Service can generate SQL queries that read from multiple database subpartitions.

DB2 for LUW or Oracle Source Example

The maximum parallelism value is three, and the relational source has five database partitions. When the Data Integration Service runs SQL queries against the database partitions, the first and second reader threads receive data from two database partitions. The third reader thread receives data from one database partition. In this example, the simply query in the Read transformation does not have the select distinct option enabled.
When you use a DB2 for LUW source, the Data Integration Service generates SQL statements similar to the following statements for the first reader thread:
SELECT <column list> FROM <table name> WHERE (nodenumber(<column 1>)=
0
OR nodenumber(<column 1>) =
3
)
When you use an Oracle source, the Data Integration Service generates SQL statements similar to the following statements for the first reader thread:
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

Oracle Source with Subpartitions Example

An Oracle source has five partitions, 1–5, and two subpartitions, a and b, in each partition. The maximum parallelism value is three. The first reader thread receives data from four database subpartitions. The second and third reader threads receive data from three database subpartitions. In this example, the simple query in the Read transformation does not have the select distinct option enabled.
The Data Integration Service generates SQL statements similar to the following statements for the first reader thread:
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

0 COMMENTS

We’d like to hear from you!