Working with
Databases
You can configure
pushdown optimization for the following databases:
IBM
DB2
|
|
Microsoft
SQL Server
|
Netezza
|
|
Oracle
|
Sybase
ASE
|
|
Teradata
|
Databases
that use ODBC drivers
|
When you push
transformation logic to a database, the database may produce different output
than the Integration Service. In addition, the Integration Service can usually
push more transformation logic to a database if you use a native driver,
instead of an ODBC driver.
Comparing the Output of the Integration Service and Databases
The Integration Service and databases can produce different results when processing the same transformation logic. The Integration Service sometimes converts data to a different format when it reads data. The Integration Service and database may also handle null values, case sensitivity, and sort order differently.
The database and
Integration Service produce different output when the following settings and
conversions are different:
Nulls treated as the highest or lowest value. The Integration Service and a database can treat null values differently. For example, you want to push a Sorter transformation to an Oracle database. In the session, you configure nulls as the lowest value in the sort order. Oracle treats null values as the highest value in the sort order. |
|
Sort
order.
The Integration Service and a database can use different sort orders. For
example, you want to push the transformations in a session to a Microsoft SQL
Server database, which is configured to use a sort order that is not case
sensitive. You configure the session properties to use the binary sort order,
which is case sensitive. The results differ based on whether the Integration
Service or Microsoft SQL Server database process the transformation logic.
|
Case
sensitivity.
The Integration Service and a database can treat case sensitivity
differently. For example, the Integration Service uses case sensitive queries
and the database does not. A Filter transformation uses the following filter
condition: IIF(col_varchar2 = ‘CA’, TRUE, FALSE). You need the database to
return rows that match ‘CA.’ However, if you push this transformation logic
to a Microsoft SQL Server database that is not case sensitive, it returns
rows that match the values ‘Ca,’ ‘ca,’ ‘cA,’ and ‘CA.’
|
|
Numeric
values converted to character values. The Integration Service and a
database can convert the same numeric value to a character value in different
formats. The database can convert numeric values to an unacceptable character
format. For example, a table contains the number 1234567890. When the
Integration Service converts the number to a character value, it inserts the
characters ‘1234567890.’ However, a database might convert the number to
‘1.2E9.’ The two sets of characters represent the same value. However, if you
require the characters in the format ‘1234567890,’ you can disable pushdown
optimization.
|
Precision.
The Integration Service and a database can have different precision for particular datatypes. Transformation datatypes use a default numeric precision that can vary from the native datatypes. For example, a transformation Decimal datatype has a precision of 1-28. The corresponding Teradata Decimal datatype has a precision of 1-18. The results can vary if the database uses a different precision than the Integration Service. |
Using ODBC Drivers
When you use native drivers for all databases, except Netezza, the Integration Service generates SQL statements using native database SQL. When you use ODBC drivers, the Integration Service usually cannot detect the database type. As a result, it generates SQL statements using ANSI SQL. The Integration Service can generate more functions when it generates SQL statements using the native language than ANSI SQL.
Note: Although the
Integration Service uses an ODBC driver for the Netezza database, the
Integration Service detects that the database is Netezza and generates native
database SQL when pushing the transformation logic to the Netezza database.
In some cases, ANSI
SQL is not compatible with the database syntax. The following sections describe
problems that you can encounter when you use ODBC drivers. When possible, use
native drivers to prevent these problems.
Working with Dates
The Integration Service and database can process dates differently. When you configure the session to push date conversion to the database, you can receive unexpected results or the session can fail.
The database can
produce different output than the Integration Service when the following date
settings and conversions are different:
Date values converted to character values. The Integration Service converts the transformation Date/Time datatype to the native datatype that supports subsecond precision in the database. The session fails if you configure the datetime format in the session to a format that the database does not support. For example, when the Integration Service performs the ROUND function on a date, it stores the date value in a character column, using the format MM/DD/YYYY HH:MI:SS.US. When the database performs this function, it stores the date in the default date format for the database. If the database is Oracle, it stores the date as the default DD-MON-YY. If you require the date to be in the format MM/DD/YYYY HH:MI:SS.US, you can disable pushdown optimization. |
|
Date
formats for TO_CHAR and TO_DATE functions. The Integration Service uses the
date format in the TO_CHAR or TO_DATE function when the Integration Service
pushes the function to the database. The database converts each date string
to a datetime value supported by the database.
|
For example, the
Integration Service pushes the following expression to the database:
TO_DATE( DATE_PROMISED,
'MM/DD/YY' )
The database
interprets the date string in the DATE_PROMISED port based on the specified
date format string MM/DD/YY. The database converts each date string, such as
01/22/98, to the supported date value, such as Jan 22 1998 00:00:00.
If the Integration
Service pushes a date format to an IBM DB2, a Microsoft SQL Server, or a Sybase
database that the database does not support, the Integration Service stops
pushdown optimization and processes the transformation.
The Integration
Service converts all dates before pushing transformations to an Oracle or
Teradata database. If the database does not support the date format after the
date conversion, the session fails.
HH24
date format.
You cannot use the HH24 format in the date format string for Teradata. When
the Integration Service generates SQL for a Teradata database, it uses the HH
format string instead.
|
|
Blank
spaces in date format strings. You cannot use blank spaces in the
date format string in Teradata. When the Integration Service generates SQL
for a Teradata database, it substitutes the space with ‘B.’
|
Handling
subsecond precision for a Lookup transformation. If you enable
subsecond precision for a Lookup transformation, the database and Integration
Service perform the lookup comparison using the subsecond precision, but
return different results. Unlike the Integration Service, the database does
not truncate the lookup results based on subsecond precision. For example,
you configure the Lookup transformation to show subsecond precision to the
millisecond. If the lookup result is 8:20:35.123456, a database returns
8:20:35.123456, but the Integration Service returns 8:20:35.123.
|
|
SYSDATE
built-in variable.
When you use the SYSDATE built-in variable, the Integration Service returns
the current date and time for the node running the service process. However,
when you push the transformation logic to the database, the SYSDATE variable
returns the current date and time for the machine hosting the database. If
the time zone of the machine hosting the database is not the same as the time
zone of the machine running the Integration Service process, the results can
vary.
|
No comments:
Post a Comment