Pushdown Optimization to Sources
The Data Integration Service can push transformation logic to different sources such as relational sources and sources that use database-specific ODBC drivers. The type of transformation logic that the Data Integration Service pushes depends on the source type.
The Data Integration Service can push transformation logic to the following types of sources:
- •Relational sources
- •Sources that use native database drivers
- •PowerExchange® nonrelational sources
- •Sources that use database-specific ODBC drivers
- •SAP 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:
- •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 Greenplum, Hive, MSSQL, Oracle, Sybase, and Teradata relational sources:
Aggregate Functions | Greenplum | Hive | MSSQL | Oracle | Sybase | Teradata |
|---|
AVG | Yes | Yes | Yes | Yes | Yes | Yes |
COUNT | Yes | Yes | Yes | Yes | Yes | Yes |
FIRST | No | No | No | No | No | No |
LAST | No | No | No | No | No | No |
MAX | Yes | Yes | Yes | Yes | Yes | Yes |
MEDIAN | No | No | No | Yes | No | No |
MIN | Yes | Yes | Yes | Yes | Yes | Yes |
PERCENTILE | No | No | No | No | No | No |
STDDEV | Yes | Yes | Yes | Yes | No | Yes |
SUM | Yes | Yes | Yes | Yes | Yes | Yes |
VARIANCE | 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.
Pushdown Optimization to Native Sources
When the Data Integration Service pushes transformation logic to relational sources using the native drivers, the Data Integration Service generates SQL statements that use the native database SQL.
The Data Integration Service can push Aggregator, Expression, Filter, Joiner, Sorter, and Union transformation logic to the following native sources:
- •IBM DB2 for Linux, UNIX, and Windows ("DB2 for LUW")
- •Microsoft SQL Server. The Data Integration Service can use a native connection to Microsoft SQL Server when the Data Integration Service runs on Windows.
- •Oracle
The Data Integration Service can push Filter transformation logic to the following native sources:
- •IBM DB2 for i5/OS
- •IBM DB2 for z/OS
Pushdown Optimization to PowerExchange Nonrelational Sources
For PowerExchange nonrelational data sources on z/OS systems, the Data Integration Service pushes Filter transformation logic to PowerExchange. PowerExchange translates the logic into a query that the source can process.
The Data Integration Service can push Filter transformation logic for the following types of nonrelational sources:
- •IBM IMS
- •Sequential data sets
- •VSAM
Pushdown Optimization to ODBC Sources
The Data Integration Service can push transformation logic to databases that use database-specific ODBC drivers. If you select the ODBC provider as Other, the Data Integration Service cannot push transformation logic to the source.
When you use a database-specific ODBC driver to connect to a source, the Data Integration Service uses the native database SQL to generate SQL statements.
You can specify the ODBC provider in the ODBC connection object.
You can configure a specific ODBC provider for the following ODBC connection types:
- •Greenplum
- •Microsoft SQL Server
- •Netezza
- •SAP HANA
- •Sybase ASE
- •Teradata
Pushdown Optimization to SAP Sources
The Data Integration Service can push Filter transformation logic to SAP sources for expressions that contain a column name, an operator, and a literal string. When the Data Integration Service pushes transformation logic to SAP, the Data Integration Service converts the literal string in the expressions to an SAP datatype.
The Data Integration Service can push Filter transformation logic that contains the TO_DATE function when TO_DATE converts a DATS, TIMS, or ACCP datatype character string to one of the following date formats:
- •'MM/DD/YYYY'
- •'YYYY/MM/DD'
- •'YYYY-MM-DD HH24:MI:SS'
- •'YYYY/MM/DD HH24:MI:SS'
- •'MM/DD/YYYY HH24:MI:SS'
The Data Integration Service processes the transformation logic if you apply the TO_DATE function to a datatype other than DATS, TIMS, or ACCP or if TO_DATE converts a character string to a format that the Data Integration Services cannot push to SAP. The Data Integration Service processes transformation logic that contains other Informatica functions. The Data Integration Service processes transformation logic that contains other Informatica functions.
Filter transformation expressions can include multiple conditions separated by AND or OR. If conditions apply to multiple SAP tables, the Data Integration Service can push transformation logic to SAP when the SAP data object uses the Open SQL ABAP join syntax. Configure the Select syntax mode in the read operation of the SAP data object.
SAP Data Type Exceptions
The Data Integration Service processes the Filter transformation logic when the source cannot process the transformation logic and the transformation expression includes the following data types: