Developer Mapping Guide > Pushdown Optimization > Pushdown Optimization Expressions
  

Pushdown Optimization Expressions

The Data Integration Service can push transformation logic to the source database when the transformation contains operators and functions that the source supports. The Data Integration Service translates the transformation expression into a query by determining equivalent operators and functions in the database. If there is no equivalent operator or function, the Data Integration Service processes the transformation logic.
If the source uses an ODBC connection and you configure a database-specific ODBC provider in the ODBC connection object, then the Data Integration Service considers the source to be the native source type.

Functions

Informatica functions are not available for nonrelational sources on z/OS. The following table displays the Informatica functions available for pushdown optimization for IBM DB2 sources:
Function
DB2 for i5/OS1
DB2 for LUW
DB2 for z/OS1
ABORT()
No
No
No
ABS()
No
Yes
No
ADD_TO_DATE()
Yes
Yes
Yes
AES_DECRYPT()
No
No
No
AES_ENCRYPT()
No
No
No
ASCII()
Yes
Yes
Yes
AVG()
Yes
Yes
Yes
CEIL()
Yes
Yes
Yes
CHOOSE()
No
No
No
CHR()
No
Yes
No
CHRCODE()
No
Yes
Yes
COMPRESS()
No
No
No
CONCAT()
Yes
Yes
Yes
COS()
Yes
Yes
Yes
COSH()
Yes
Yes
Yes
COUNT()
Yes
Yes
Yes
CRC32()
No
No
No
CREATE_TIMESTAMP_TZ()
No
No
No
CUME()
No
No
No
DATE_COMPARE()
Yes
Yes
Yes
DATE_DIFF()
No
No
No
DECODE()
No
Yes
No
DECODE_BASE64()
No
No
No
DECOMPRESS()
No
No
No
ENCODE_BASE64()
No
No
No
ERROR()
No
No
No
EXP()
No
Yes
No
FIRST()
No
No
No
FLOOR()
No
Yes
No
FV()
No
No
No
GET_DATE_PART()
Yes
Yes
Yes
GET_TIMESTAMP()
No
No
No
GET_TIMEZONE()
No
No
No
GREATEST()
No
No
No
IIF()
No
Yes
No
IN()
No
Yes
No
INDEXOF()
No
No
No
INITCAP()
No
No
No
INSTR()
Yes
Yes
Yes
IS_DATE()
No
No
No
IS_NUMBER()
No
No
No
IS_SPACES()
No
No
No
ISNULL()
Yes
Yes
Yes
LAST()
No
No
No
LAST_DAY()
No
No
No
LEAST()
No
No
No
LENGTH()
Yes
Yes
Yes
LN()
Yes
Yes
Yes
LOG()
Yes
Yes
Yes
LOWER()
Yes
Yes
Yes
LPAD()
No
No
No
LTRIM()
Yes
Yes
Yes
MAKE_DATE_TIME()
No
No
No
MAX()
Yes
Yes
Yes
MD5()
No
No
No
MEDIAN()
No
No
No
METAPHONE()
No
No
No
MIN()
Yes
Yes
Yes
MOD()
Yes
Yes
Yes
MOVINGAVG()
No
No
No
MOVINGSUM()
No
No
No
NPER()
No
No
No
PERCENTILE()
No
No
No
PMT()
No
No
No
POWER()
Yes
Yes
Yes
PV()
No
No
No
RAND()
No
No
No
RATE()
No
No
No
REG_EXTRACT()
No
No
No
REG_MATCH()
No
No
No
REG_REPLACE
No
No
No
REPLACECHR()
No
No
No
REPLACESTR()
No
No
No
REVERSE()
No
No
No
ROUND(DATE)
No
No
Yes
ROUND(NUMBER)
Yes
Yes
Yes
RPAD()
No
No
No
RTRIM()
Yes
Yes
Yes
SET_DATE_PART()
No
No
No
SIGN()
Yes
Yes
Yes
SIN()
Yes
Yes
Yes
SINH()
Yes
Yes
Yes
SOUNDEX()
No
Yes1
No
SQRT()
No
Yes
No
STDDEV()
Yes
Yes
Yes
SUBSTR()
Yes
Yes
Yes
SUM()
Yes
Yes
Yes
SYSTIMESTAMP()
Yes
Yes
Yes
TAN()
Yes
Yes
Yes
TANH()
Yes
Yes
Yes
TO_BIGINT
Yes
Yes
Yes
TO_CHAR(DATE)
Yes
Yes
Yes
TO_CHAR(NUMBER)
Yes
Yes2
Yes
TO_DATE()
Yes
Yes
Yes
TO_DECIMAL()
Yes
Yes3
Yes
TO_DECIMAL38()
No
No
No
TO_FLOAT()
Yes
Yes
Yes
TO_INTEGER()
Yes
Yes
Yes
TO_TIMESTAMP_TZ()
No
No
No
TRUNC(DATE)
No
No
No
TRUNC(NUMBER)
Yes
Yes
Yes
UPPER()
Yes
Yes
Yes
VARIANCE()
Yes
Yes
Yes
1 The Data Integration Service can push these functions to the source only when they are included in Filter transformation logic.

2 When this function takes a decimal or float argument, the Data Integration Service can push the function only when it is included in Filter transformation logic.

3 When this function takes a string argument, the Data Integration Service can push the function only when it is included in Filter transformation logic.

The following table displays the Informatica functions available for pushdown optimization for Greenplum, Hive, Microsoft SQL Server, Netezza, Oracle, SAP, SAP HANA, Sybase ASE, and Teradata sources:
Function
Greenplum
Hive
Microsoft SQL Server
Netezza
Oracle
SAP 1
SAP HANA
Sybase ASE
Teradata
ABORT()
No
No
No
No
No
No
No
No
No
ABS()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
ADD_TO_DATE()
Yes
No
Yes
Yes
Yes
No
No
Yes
Yes
AES_DECRYPT()
No
No
No
No
No
No
No
No
No
AES_ENCRYPT()
No
No
No
No
No
No
No
No
No
ASCII()
Yes
No
Yes
Yes
Yes
No
No
Yes
No
AVG()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
CEIL()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
CHOOSE()
No
No
No
No
No
No
No
No
No
CHR()
Yes
No
Yes
Yes
Yes
No
Yes
Yes
No
CHRCODE()
Yes
No
Yes
Yes
Yes
No
Yes
Yes
No
COMPRESS()
No
No
No
No
No
No
No
No
No
CONCAT()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
COS()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
COSH()
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
COUNT()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
CRC32()
No
No
No
No
No
No
No
No
No
CREATE_TIMESTAMP_TZ()
No
No
No
No
Yes
No
No
No
No
CUME()
No
No
Yes
No
No
No
No
No
No
DATE_COMPARE()
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
DATE_DIFF()
No
No
No
No
No
No
Yes
No
No
DECODE()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
DECODE_BASE64()
No
No
No
No
No
No
No
No
No
DECOMPRESS()
No
No
No
No
No
No
No
No
No
ENCODE_BASE64()
No
No
No
No
No
No
No
No
No
ERROR()
No
No
No
No
No
No
No
No
No
EXP()
Yes
Yes
Yes
Yes
Yes
No
No
Yes
Yes
FIRST()
No
No
No
No
No
No
No
No
No
FLOOR()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
FV()
No
No
No
No
No
No
No
No
No
GET_DATE_PART()
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
GET_TIMESTAMP()
No
No
No
No
Yes
No
No
No
No
GET_TIMEZONE()
No
No
No
No
No
No
No
No
No
GREATEST()
No
No
No
No
Yes
No
No
No
No
IIF()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
IN()
No
No
Yes
No
Yes
No
No
Yes
Yes
INDEXOF()
No
No
No
No
No
No
No
No
No
INITCAP()
Yes
No
No
Yes
Yes
No
No
No
No
INSTR()
No
No
Yes
Yes
Yes
No
No
Yes
Yes
IS_DATE()
No
No
No
No
No
No
No
No
No
IS_NUMBER()
No
No
No
No
No
No
No
No
No
IS_SPACES()
No
No
No
No
No
No
No
No
No
ISNULL()
No
Yes
Yes
No
Yes
No
Yes
Yes
Yes
LAST()
No
No
No
No
No
No
No
No
No
LAST_DAY()
No
No
No
Yes
Yes
No
Yes
No
No
LEAST()
No
No
No
No
Yes
No
No
No
No
LENGTH()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
LN()
Yes
Yes
Yes
No
Yes
No
Yes
Yes
Yes
LOG()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
LOWER()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
LPAD()
Yes
Yes
No
Yes
Yes
No
Yes
No
No
LTRIM()
Yes
Yes
Yes
Yes
Yes
No
Yes
No
Yes
MAKE_DATE_TIME()
No
No
No
No
No
No
No
No
No
MAX()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
MD5()
No
No
No
No
No
No
No
No
No
MEDIAN()
No
No
No
No
Yes
No
No
No
No
METAPHONE()
No
No
No
No
No
No
No
No
No
MIN()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
MOD()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
MOVINGAVG()
No
No
No
No
No
No
No
No
No
MOVINGSUM()
No
No
No
No
No
No
No
No
No
NPER()
No
No
No
No
No
No
No
No
No
PERCENTILE()
No
No
No
No
No
No
No
No
No
PMT()
No
No
No
No
No
No
No
No
No
POWER()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
PV()
No
No
No
No
No
No
No
No
No
RAND()
No
No
No
No
No
No
No
No
No
RATE()
No
No
No
No
No
No
No
No
No
REG_EXTRACT()
No
No
No
No
No
No
No
No
No
REG_MATCH()
No
No
No
No
No
No
No
No
No
REG_REPLACE
No
No
No
No
No
No
No
No
No
REPLACECHR()
No
No
No
No
No
No
No
No
No
REPLACESTR()
No
No
No
No
No
No
No
No
No
REVERSE()
No
No
No
No
No
No
No
No
No
ROUND(DATE)
No
No
No
No
Yes
No
No
No
No
ROUND(NUMBER)
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
RPAD()
Yes
Yes
No
Yes
Yes
No
Yes
No
No
RTRIM()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
SET_DATE_PART()
No
No
No
No
No
No
No
No
No
SIGN()
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
SIN()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
SINH()
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
SOUNDEX()
No
No
Yes
No
Yes
No
No
Yes
No
SQRT()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
STDDEV()
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
SUBSTR()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
SUM()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
SYSTIMESTAMP()
Yes
No
Yes
Yes
Yes
No
Yes 2
Yes
No
TAN()
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
TANH()
Yes
No
Yes
Yes
Yes
No
No
Yes
Yes
TO_BIGINT
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
TO_CHAR(DATE)
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
TO_CHAR(NUMBER)
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
TO_DATE()
Yes
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
TO_DECIMAL()
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
TO_DECIMAL38()
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
TO_FLOAT()
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
TO_INTEGER()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
TO_TIMESTAMP_TZ()
No
No
No
No
Yes
No
No
No
No
TRUNC(DATE)
Yes
No
No
Yes
Yes
No
Yes
No
No
TRUNC(NUMBER)
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
UPPER()
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
VARIANCE()
Yes
No
Yes
Yes
Yes
No
No
Yes
Yes
1 The Data Integration Service can push these functions to the source only when they are included in Filter transformation logic.

2 SYSTIMESTAMP() supports only the SS argument.

The following table summarizes the availability of Informatica functions for pushdown optimization.
Function
DB2 for LUW
Microsoft SQL Server
ODBC
Oracle
Sybase ASE
ABS()
Yes
Yes
Yes
Yes
Yes
ADD_TO_DATE()
Yes
Yes
No
Yes
Yes
ASCII()
Yes
Yes
No
Yes
Yes
CEIL()
Yes
Yes
No
Yes
Yes
CHR()
Yes
Yes
No
Yes
Yes
CONCAT()
Yes
Yes
No
Yes
Yes
COS()
Yes
Yes
Yes
Yes
Yes
COSH()
Yes
Yes
No
Yes
Yes
DATE_COMPARE()
Yes
Yes
Yes
Yes
Yes
DECODE()
Yes
Yes
Yes
Yes
Yes
EXP()
Yes
Yes
Yes
n/a
Yes
FLOOR()
No
Yes
No
Yes
Yes
GET_DATE_PART()
Yes
Yes
No
Yes
Yes
IIF()
Yes
Yes
Yes
No
Yes
IN()
No
Yes
Yes
No
Yes
INITCAP()
No
No
No
Yes
No
INSTR()
Yes
Yes
No
Yes
Yes
ISNULL()
Yes
Yes
Yes
Yes
Yes
LAST_DAY()
No
No
No
Yes
n/a
LENGTH()
Yes
Yes
No
Yes
Yes
LN()
Yes
No
No
Yes
Yes
LOG()
Yes
Yes
No
Yes
Yes
LOOKUP()
No
No
Yes
No
No
LOWER()
Yes
Yes
Yes
Yes
Yes
LPAD()
No
No
No
Yes
n/a
LTRIM()
X
Yes
No
Yes
Yes
MOD()
X
Yes
No
Yes
Yes
POWER()
X
Yes
No
Yes
Yes
ROUND(DATE)
No
No
No
Yes
n/a
ROUND(NUMBER)
Yes
Yes
No
Yes
Yes
RPAD()
No
No
No
Yes
No
RTRIM()
Yes
Yes
No
Yes
Yes
SIGN()
Yes
Yes
No
Yes
Yes
SIN()
Yes
Yes
Yes
Yes
Yes
SINH()
Yes
Yes
No
Yes
Yes
SOUNDEX()
Yes1
Yes
No
Yes
Yes
SQRT()
Yes
Yes
Yes
Yes
Yes
SUBSTR()
Yes
Yes
No
Yes
Yes
SYSDATE()
Yes
Yes
No
Yes
Yes
SYSTIMESTAMP()
Yes
Yes
No
Yes
Yes
TAN()
Yes
Yes
Yes
Yes
Yes
TANH()
Yes
Yes
No
Yes
Yes
TO_BIGINT
Yes
Yes
No
Yes
Yes
TO_CHAR(DATE)
Yes
Yes
No
Yes
Yes
TO_CHAR(NUMBER)
Yes2
Yes
No
Yes
Yes
TO_DATE()
Yes
Yes
No
Yes
Yes
TO_DECIMAL()
Yes3
Yes
No
Yes
Yes
TO_FLOAT()
Yes
Yes
No
Yes
Yes
TO_INTEGER()
Yes
Yes
No
Yes
Yes
TRUNC(DATE)
No
No
No
Yes
No
TRUNC(NUMBER)
Yes
Yes
No
Yes
Yes
UPPER()
Yes
Yes
Yes
Yes
Yes
1 The Data Integration Service can push these functions to the source only when they are included in Filter transformation logic.

2 When this function takes a decimal or float argument, the Data Integration Service can push the function only when it is included in Filter transformation logic.

3 When this function takes a string argument, the Data Integration Service can push the function only when it is included in Filter transformation logic.

Hive Function Exceptions

The Data Integration Service cannot push supported functions to Hive sources under certain conditions.
The Data Integration Service processes transformation logic for Hive sources when expressions contain supported functions with the following logic:
The Data Integration service cannot process the transformation logic for Hive sources when you use the following functions with the date datatype:

IBM DB2 Function Exceptions

The Data Integration Service cannot push supported functions to IBM DB2 for i5/OS, DB2 for LUW, and DB2 for z/OS sources under certain conditions. The Data Integration Service cannot push supported functions to IBM DB2 for LUW sources under certain conditions.
The Data Integration Service processes transformation logic for IBM DB2 sources when expressions contain supported functions with the following logic:

Microsoft SQL Server Function Exceptions

The Data Integration Service cannot push supported functions to Microsoft SQL Server sources under certain conditions.
The Data Integration Service processes transformation logic for Microsoft SQL Server sources when expressions contain supported functions with the following logic:

Netezza Function Exceptions

The Data Integration Service cannot push supported functions to Netezza sources under certain conditions.
The Data Integration Service processes transformation logic for Netezza sources when expressions contain supported functions with the following logic:

Oracle Function Exceptions

The Data Integration Service cannot push supported functions to Oracle sources under certain conditions.
The Data Integration Service processes transformation logic for Oracle sources when expressions contain supported functions with the following logic:

ODBC Function Exception

The Data Integration Service processes transformation logic for ODBC when the CaseFlag argument for the IN function is a number other than zero.
Note: When the ODBC connection object properties include a database-specific ODBC provider, the Data Integration Service considers the source to be the native source type.
The Data Integration Service cannot push the EXP() function to Teradata sources when you specify the ODBC provider in the connection object as Other. Set the ODBC provider to Teradata to push the EXP() function.

Sybase ASE Function Exceptions

The Data Integration Service cannot push supported functions to Sybase ASE sources under certain conditions.
The Data Integration Service processes transformation logic for Sybase ASE sources when expressions contain supported functions with the following logic:

Teradata Function Exceptions

The Data Integration Service cannot push supported functions to Teradata sources under certain conditions.
The Data Integration Service processes transformation logic for Teradata sources when expressions contain supported functions with the following logic:

Operators

The following table summarizes the availability of Informatica operators by source type. Each column displays whether the Data Integration Service can push the operator to the source.
Note: Nonrelational sources are IMS, VSAM, and sequential data sets on z/OS.
Operator
DB2 for LUW
DB2 for i5/OS or z/OS1
Greenplum
Hive
Microsoft SQL Server
Nonrelational*
Oracle
SAP*
SAP HANA
Sybase ASE
Teradata
+
-
*
Yes
Yes
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
/
Yes
Yes
Yes
Yes
Yes
No
Yes
No
Yes
Yes
Yes
%
Yes
Yes
Yes
Yes
Yes
No
Yes
No
Yes
Yes
Yes
||
Yes
Yes
Yes
Yes
Yes
No
Yes
No
Yes
Yes
Yes
=
>
<
>=
<=
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
<>
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
Yes
Yes
!=
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
^=
Yes
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
Yes
Yes
AND
OR
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
NOT
Yes
Yes
Yes
Yes
Yes
No
Yes
No
Yes
Yes
Yes
1 The Data Integration Service can push these operators to the source only when they are included in Filter transformation logic.

Operator
DB2 for LUW
Microsoft SQL Server
ODBC
Oracle
Sybase ASE
+
-
*
Yes
Yes
Yes
Yes
Yes
/
Yes
Yes
Yes
Yes
Yes
%
Yes
Yes
No
Yes
Yes
||
Yes
Yes
No
Yes
Yes
=
>
<
>=
<=
Yes
Yes
Yes
Yes
Yes
<>
Yes
Yes
Yes
Yes
Yes
!=
Yes
Yes
Yes
Yes
Yes
^=
Yes
Yes
Yes
Yes
Yes
AND
OR
Yes
Yes
Yes
Yes
Yes
NOT
Yes
Yes
Yes
Yes
Yes