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

Pushdown Optimization to Relational Sources

Pushdown Optimization to Relational Sources

The Data Integration Service can push transformation logic to relational sources using the native drivers or database-specific ODBC drivers.
The Data Integration Service can push Aggregator, Expression, Filter, Joiner, Sorter, and Union transformation logic to the following relational sources:
  • Amazon Redshift
  • Greenplum
  • Hive
  • IBM DB2
  • Microsoft SQL Server
  • Oracle
  • SAP HANA
  • Sybase
  • Teradata
When you push Aggregator transformation logic to a relational source, pass-through ports are valid if they are group-by ports. The transformation language includes aggregate functions that you can use in an Aggregator transformation.
The following table displays the aggregate functions that are valid in an IBM DB2 relational source:
Aggregate Functions
DB2-LUW
DB2i
DB2z/os
AVG
Yes
Yes
Yes
COUNT
Yes
Yes
Yes
FIRST
No
No
No
LAST
No
No
No
MAX
Yes
Yes
Yes
MEDIAN
No
No
No
MIN
Yes
Yes
Yes
PERCENTILE
No
No
No
STDDEV
Yes
Yes
Yes
SUM
Yes
Yes
Yes
VARIANCE
Yes
Yes
Yes
The following table displays the aggregate functions that are valid in Amazon Redshift, Greenplum, Hive, MSSQL, Oracle, Sybase, and Teradata relational sources:
Aggregate Functions
Amazon Redshift
Greenplum
Hive
MSSQL
Oracle
Sybase
Teradata
AVG
Yes
Yes
Yes
Yes
Yes
Yes
Yes
COUNT
Yes
Yes
Yes
Yes
Yes
Yes
Yes
FIRST
No
No
No
No
No
No
No
LAST
No
No
No
No
No
No
No
MAX
Yes
Yes
Yes
Yes
Yes
Yes
Yes
MEDIAN
No
No
No
No
Yes
No
No
MIN
Yes
Yes
Yes
Yes
Yes
Yes
Yes
PERCENTILE
No
No
No
No
No
No
No
STDDEV
Yes
Yes
Yes
Yes
Yes
No
Yes
SUM
Yes
Yes
Yes
Yes
Yes
Yes
Yes
VARIANCE
Yes
Yes
Yes
Yes
Yes
No
Yes
The following table displays the aggregate functions that are valid in MSSQL, Oracle, and Sybase relational sources:
Aggregate Functions
MSSQL
Oracle
Sybase
AVG
Yes
Yes
Yes
COUNT
Yes
Yes
Yes
FIRST
No
No
No
LAST
No
No
No
MAX
Yes
Yes
Yes
MEDIAN
No
Yes
No
MIN
Yes
Yes
Yes
PERCENTILE
No
No
No
STDDEV
Yes
Yes
No
SUM
Yes
Yes
Yes
VARIANCE
Yes
Yes
No
A relational source has a default configuration for treating null values. By default, some databases treat null values lower than any other value and some databases treat null values higher than any other value. You can push the Sorter transformation logic to the relational source and get accurate results when the source has the default null ordering.
If you configure a Sorter transformation for distinct output rows, you must enable case sensitive sorting to push transformation logic to source for DB2, Sybase, and Oracle.
The Data Integration Service cannot push any function that contains the Decimal data type to a Hive source.

0 COMMENTS

We’d like to hear from you!