Pushdown Optimization And Transformations 3m1wm

Pushdown Optimization and Transformations By penchalaRaju.Yanamala This chapter includes the following topics: Pushdown Optimization and Transformations Overview Aggregator Transformation Expression Transformation Filter Transformation er Transformation Lookup Transformation Router Transformation Sequence Generator Transformation Sorter Transformation Source Qualifier Transformation Target Union Transformation Update Strategy Transformation Pushdown Optimization and Transformations Overview When you configure pushdown optimization, the Integration Service tries to push each transformation to the database. The following criteria affects whether the Integration Service can push the transformation to the database: Type of transformation Location of the transformation in the mapping Mapping and session configuration for the transformation The expressions contained in the transformation The criteria might also affect the type of pushdown optimization that the Integration Service can perform and the type of database to which the transformation can be pushed. The Integration Service can push logic of the following transformations to the database: Aggregator Expression Filter er Lookup Router Sequence Generator Sorter Source Qualifier Target Union Update Strategy Rules and Guidelines

Use the following rules and guidelines when you configure the Integration Service to push transformation logic to a database. The Integration Service processes the transformation logic if any of the following conditions are true: The transformation logic updates a mapping variable and saves it to the repository database. The transformation contains a variable port. The session is configured to override the default values of input or output ports. The database does not have an equivalent operator, variable, or function that is used in an expression in the transformation. The mapping contains too many branches. When you branch a pipeline, the SQL statement required to represent the mapping logic becomes more complex. The Integration Service cannot generate SQL for a mapping that contains more than 64 two-way branches, 43 three-way branches, or 32 four-way branches. If the mapping branches exceed these limitations, the Integration Service processes the downstream transformations. The Integration Service processes all transformations in the mapping if any of the following conditions are true: The session is a data profiling or debug session. The session is configured to log row errors. Aggregator Transformation The following table shows the pushdown types for each database to which you can push the Aggregator transformation: Database Pushdown Type IBM DB2 Source-side, Full Microsoft SQL Server Source-side, Full Oracle Source-side, Full Sybase ASE Source-side, Full Teradata Source-side, Full ODBC Source-side, Full The Integration Service processes the Aggregator transformation if any of the following conditions are true: The session and mapping is configured for incremental aggregation. The transformation contains a nested aggregate function. The transformation contains a conditional clause in an aggregate expression. The transformation uses a FIRST(), LAST(), MEDIAN(), or PERCENTILE() function in any port expression. An output port is not an aggregate or a part of the group by port. The transformation is downstream from another Aggregator transformation. The Integration Service pushes the first Aggregator transformation to the database and processes all downstream Aggregator transformations. Expression Transformation

The following table shows the pushdown types for each database to which you can push the Expression transformation: Database Pushdown Type IBM DB2 Source-side, Target-side, Full Microsoft SQL Server Source-side, Target-side, Full Oracle Source-side, Target-side, Full Sybase ASE Source-side, Target-side, Full Teradata Source-side, Target-side, Full ODBC Source-side, Target-side, Full The Integration Service processes the Expression transformation if the transformation calls an unconnected Stored Procedure. Filter Transformation The following table shows the pushdown types for each database to which you can push the Filter transformation: Database Pushdown Type IBM DB2 Source-side, Full Microsoft SQL Server Source-side, Full Oracle Source-side, Full Sybase ASE Source-side, Full Teradata Source-side, Full ODBC Source-side, Full The Integration Service processes the Filter transformation if the filter expression cannot be pushed to the database. For example, if the filter expression contains an operator that cannot be pushed to the database, the Integration Service does not push the filter expression to the database. er Transformation The following table shows the pushdown types for each database to which you can push the er transformation: Database Pushdown Type IBM DB2 Source-side, Full Microsoft SQL Server Source-side, Full Oracle Source-side, Full Sybase ASE Source-side, Full Teradata Source-side, Full ODBC Source-side, Full The Integration Service processes the er transformation if any of the following conditions are true: The er transformation is downstream from an Aggregator transformation. The Integration Service cannot push the master and detail pipelines of the

er transformation to the database. The er transformation is configured with an outer , and the master or detail source is a multi-table . The Integration Service cannot generate SQL to represent an outer combined with a multi-table . The er transformation is configured with a full outer and configured for pushdown optimization to Sybase. The session is configured to mark all source rows as updates and configured for pushdown optimization to Teradata. Lookup Transformation When you configure a Lookup transformation for pushdown optimization, the database performs a lookup on the database lookup table. The following table shows the pushdown types for each database to which you can push the Lookup transformation: Database Pushdown Type IBM DB2 Source-side, Target-side, Full Microsoft SQL Server Source-side, Full Oracle Source-side, Target-side, Full Sybase ASE Source-side, Full Teradata Source-side, Full ODBC Source-side, Full Use the following rules and guidelines when you configure the Integration Service to push Lookup transformation logic to a database: The database does not use PowerCenter caches when processing transformation logic. The Integration Service processes all transformations after a pipeline branch when multiple Lookup transformations are present in different branches of pipeline, and the branches merge downstream. A session configured for target-side pushdown optimization fails if the session requires datatype conversion. The Integration Service processes the Lookup transformation if any of the following conditions are true: The Lookup transformation is a pipeline lookup. The transformation uses a dynamic cache. The transformation is configured to return the first, last, or any matching value. To use pushdown optimization, you must configure the Lookup transformation to report an error on multiple matches. The transformation is downstream from an Aggregator transformation. The session is configured to mark all source rows as updates and configured for pushdown optimization to Teradata. The session is configured for source-side pushdown optimization and the lookup table and source table are on different databases. The session is configured for target-side pushdown optimization and the lookup table and target table are on different databases. The session is configured for full pushdown optimization and the source and target tables are not in the same database as the lookup table.

Unconnected Lookup Transformation Use the following rules and guidelines when you configure the Integration Service to push an unconnected Lookup transformation to a database: If the Lookup transformation contains an SQL override or a filter, configure the session for pushdown optimization with a view. The database might perform slower than the Integration Service if the session contains multiple unconnected lookups. The generated SQL might be complex because the Integration Service creates an outer each time it invokes an unconnected lookup. Test the session with and without pushdown optimization to determine which session has better performance. The Integration Service processes an unconnected Lookup transformation if you configure target-side pushdown optimization for the unconnected lookup. Lookup Transformation with an SQL Override Use the following rules and guidelines when you configure the Integration Service to push a Lookup transformation with an SQL override to a database: Configure the session for pushdown optimization with a view. You cannot append an ORDER BY clause to the SQL statement in the lookup override. The session fails if you append an ORDER BY clause. The order of the columns in the lookup override must match the order of the ports in the Lookup transformation. If you reverse the order of the ports in the lookup override, the query results transpose the values. The session fails if the SELECT statement in the SQL override refers to a database sequence. The Integration Service processes a Lookup transformation with an SQL override if the transformation contains Informatica outer syntax in the SQL override. Use ANSI outer syntax in the SQL override to push the transformation to a database. Router Transformation The following table shows the pushdown types for each database to which you can push the Router transformation: Database Pushdown Type IBM DB2 Source-side, Full Microsoft SQL Server Source-side, Full Oracle Source-side, Full Sybase ASE Source-side, Full Teradata Source-side, Full ODBC Source-side, Full You can use source-side pushdown when all output groups merge into one transformation that can be pushed to the source database. The Integration Service processes the Router transformation if the router expression cannot be pushed to the database. For example, if the expression

contains an operator that cannot be pushed to the database, the Integration Service does not push the expression to the Sequence Generator Transformation The following table shows the pushdown types for each database to which you can push the Sequence Generator transformation: Database Pushdown Type IBM DB2 Source-side, Target-side, Full Oracle Source-side, Target-side, Full The Integration Service processes the Sequence Generator transformation if any of the following conditions are true: The transformation is reusable. The transformation is connected to multiple targets. The transformation connects the CURRVAL port. The Integration Service cannot push all of the logic for the Sequence Generator transformation to the database. For example, a Sequence Generator transformation creates sequence values that are supplied to two branches of a pipeline. When you configure pushdown optimization, the database can create sequence values for only one pipeline branch. When the Integration Service cannot push all of the Sequence Generator logic to the database, the following message appears: Pushdown optimization stops at the transformation because the upstream Sequence Generator <Sequence Generator transformation name> cannot be pushed entirely to the database. The pipeline branches before the Sequence Generator transformation and then s back together after the Sequence Generator transformation. The pipeline branches after the Sequence Generator transformation and does not back together. A sequence value es through an Aggregator, a Filter, a er, a Sorter, or a Union transformation. The Sequence Generator transformation provides sequence values to a transformation downstream from a Source Qualifier transformation that is configured to select distinct rows. The Integration Service processes a transformation downstream from the Sequence Generator transformation if it uses the NEXTVAL port of the Sequence Generator transformation in CASE expressions and is configured for pushdown optimization to IBM DB2. Sorter Transformation The following table shows the pushdown types for each database to which you can push the Sorter transformation: Database Pushdown Type IBM DB2 Source-side, Full Microsoft SQL Server Source-side, Full

Oracle Sybase ASE Teradata ODBC

Source-side, Full Source-side, Full Source-side, Full Source-side, Full

Use the following rules and guidelines when you configure the Integration Service to push Sorter transformation logic to a database: The Integration Service pushes the Sorter transformation to the database and processes downstream transformations when the Sorter transformation is configured for a distinct sort. The Integration Service processes the Sorter transformation when the Sorter transformation is downstream from a Union transformation and the port used as a sort key in the Sorter transformation is not projected from the Union transformation to the Sorter transformation. Source Qualifier Transformation The following table shows the pushdown types for each database to which you can push the Source Qualifier transformation: Database Pushdown Type IBM DB2 Source-side, Full Microsoft SQL Server Source-side, Full Oracle Source-side, Full Sybase ASE Source-side, Full Teradata Source-side, Full ODBC Source-side, Full The Integration Service processes the Source Qualifier transformation logic when any of the following conditions are true: The transformation contains Informatica outer syntax in the SQL override or a -defined . Use ANSI outer syntax in the SQL override to enable the Integration Service to push the Source Qualifier transformation to the database. The source is configured for database partitioning. The source is an Oracle source that uses an XMLType datatype. Source Qualifier Transformation with an SQL Override Use the following rules and guidelines when you configure pushdown optimization for a session containing a Source Qualifier transformation with an SQL override: The SELECT statement in a custom SQL query must list the port names in the order in which they appear in the transformation. If the ports are not in the correct order, the session can fail or output unexpected results. Configure the session for pushdown optimization with a view. The session fails if the SELECT statement in the SQL override refers to a database sequence. The session fails if the SQL override contains an ORDER BY clause and you

push the Source Qualifier transformation logic to an IBM DB2, a Microsoft SQL Server, a Sybase ASE, or a Teradata database. If a Source Qualifier transformation is configured to select distinct values and contains an SQL override, the Integration Service ignores the distinct configuration. If the session contains multiple partitions, specify the SQL override for all partitions. Test the SQL override query on the source database before you push it to the database because PowerCenter does not validate the override SQL syntax. The session fails if the SQL syntax is not compatible with the source database. Target The following table shows the pushdown types for each database to which you can push the target logic: Database Pushdown Type IBM DB2 Target-side, Full Microsoft SQL Server Target-side, Full Oracle Target-side, Full Sybase ASE Target-side, Full Teradata Target-side, Full ODBC Target-side, Full The Integration Service processes the target logic when you configure the session for full pushdown optimization and any of the following conditions are true: The target includes a target update override. The session is configured for constraint-based loading, and the target load order group contains more than one target. The session uses an external loader. If you configure full pushdown optimization and the target uses a different connection than the source, the Integration Service cannot push the all transformation logic to one database. Instead, it pushes as much transformation logic as possible to the source database and pushes any remaining transformation logic to the target database if it is possible. The Integration Service processes the target logic when you configure the session for target-side pushdown optimization and any of the following conditions are true: The target includes a target update override. The target is configured for database partitioning. The session is configured for bulk loading. The session uses an external loader. Use source-side pushdown optimization with an external loader to enable the Integration Service to push the transformation logic to the source database. Union Transformation

The following table shows the pushdown types for each database to which you can push the Union transformation: Database Pushdown Type IBM DB2 Source-side, Full Microsoft SQL Server Source-side, Full Oracle Source-side, Full Sybase ASE Source-side, Full Teradata Source-side, Full ODBC Source-side, Full The Integration Service processes the Union transformation logic when any of the following conditions are true: The Integration Service cannot push all input groups to the source database. The input groups do not originate from the same source. Some ports in the Union transformation are not connected to the upstream transformation that outputs distinct rows. Update Strategy Transformation The following table shows the pushdown types for each database to which you can push the Update Strategy transformation: Database Pushdown Type IBM DB2 Full Microsoft SQL Server Full Oracle Full Sybase ASE Full Teradata Full ODBC Full Use the following rules and guidelines when you configure the Integration Service to push Update Strategy transformation logic to a database: The generated SQL for an Update Strategy transformation with an update operation can be complex. Run the session with and without pushdown optimization to determine which configuration is faster. If there are multiple operations to the same row, the Integration Service and database can process the operations differently. To ensure that new rows are not deleted or updated when pushed to a database, source rows are processed in the following order: delete transactions, update transactions, and then insert transactions. If the Update Strategy transformation contains more than one insert, update, or delete operation, the Integration Service generates and runs the insert, update, and delete SQL statements serially. The Integration Service runs the three statements even if they are not required. This might decrease performance. The Integration Service ignores rejected rows when using full pushdown optimization. It does not write reject rows to a reject file.

The Integration Service processes the Update Strategy transformation if the update strategy expression cannot be pushed to the database. For example, if the expression contains an operator that cannot be pushed to the database, the Integration Service does not push the expression to the database. The Integration Service processes the Update Strategy transformation if any of the following conditions are true The transformation is upstream from an Aggregator, a er, or a distinct Sorter transformation and the session is configured to treat the source rows as data driven. The transformation uses operations other than the insert operation and the Integration Service cannot push all transformation logic to the database. The update strategy expression returns a value that is not numeric and not Boolean.