Consider the following rules and guidelines when you configure
SQL ELT optimization
for a Microsoft SQL Server mapping:
When you push transformation logic to the database,
ensure that the database has enough resources to process the queries faster.
Otherwise, there could be a performance degradation.
You can't use
Generate SQL
Query
in the
SQL ELT Query
panel to generate
and preview the SQL queries that
Data Integration
pushes to
the database.
When you use multiple objects as the
source type in a mapping, the mapping might fail with the following
error:
FnName: Execute Direct -- [Informatica][ODBC SQL Server
Wire Protocol driver][Microsoft SQL Server]The multi-part identifier
"<schema_name>.<table_name>.<column_name>" could not be
bound.
Workaround: You can configure a join relationship in a
Joiner transformation with multiple source instances in the
mapping.
If the following transformation or mapping conditions
is true, the Secure Agent processes a logic instead of pushing it to the
database:
The transformation contains a
variable port.
The transformation is not a Sorter
transformation, Union transformation, or target in a mapping.
The transformation downstream from
a Sorter or Union transformation, or contains a distinct sort.
A configured
mapping
task to override the default values of input or output
ports.
The database does not have an
equivalent operator, variable, or function to use in an expression in the
transformation.
The mapping contains too many
branches. The Secure Agent can't generate an SQL query for a mapping that
contains more than 64 two-way branches, 43 three-way branches, or 32
four-way branches. If the number of branches exceeds these limitations, the
Integration Service processes the downstream transformations.
A
mapping
task to log
row errors.
Consider the following rules and guidelines when you configure full
SQL ELT optimization
for a Microsoft SQL Server mapping:
You can't push the LTRIM(), RTRIM(), or
MOD() function that contains more than one argument.
To push the MOD() function to the
Microsoft SQL Server database, the argument that you pass must be of the Integer
date type.
When you push the INSTR() function, you
can only define string, search_value, and start arguments.
Consider the following rules and guidelines when you use an Expression transformation in a
Microsoft SQL Server mapping enabled with full
SQL ELT optimization
:
When you push the IS_DATE() function
that contains the Text or Ntext data type, the IS_DATE() function returns
0.
When you push the IS_DATE() and
IS_NUMBER() functions and the values in the argument contain NULL, the functions
return 0.
You can't get a case-sensitive return
value for the REPLACECHR() or REPLACESTR() functions.
When you push the MD5() function that
contains the nchar data type, the function returns a different value for the
nchar data type as compared to a mapping that runs without
SQL ELT optimization
.
To get the same return value for the
nchar and char data types when you push the MD5() function, enter the
ConvertToVarcharForMD5InPDO=Yes
property in the mapping
task.
When you push the LPAD() function that
contains the second_string argument, the function truncates the second string
from left to right.
For example, for the LPAD('Infa',9,'RELATIONAL
CONNECTIVITY') expression, the function returns the following value:
IVITYInfa
When you push the DATE_DIFF() function
that contains the date1 and date2 arguments, the function returns the following
different values as compared to a mapping that runs without
SQL ELT optimization
:
The function returns a negative
number when the value of date1 is later than the value of date2.
The function returns a positive
number when the value of date1 is earlier than the value of date2.
ASK INFAPreview
