Informatica Interview Question Part -15

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.