Pushdown Types
The Data Integration Service applies pushdown optimization to a mapping when you select the pushdown type in the mapping run-time properties.
You can select the following pushdown types:
- •None. Select no pushdown type for the mapping.
- •Source. The Data Integration Service tries to push down as much transformation logic as it can to the source database.
- •Full. The Data Integration Service pushes the full transformation logic to the source database.
You can also create a string parameter for the pushdown type and use the following parameter values:
Full Pushdown Optimization
When the Data Integration Service applies full pushdown optimization, it pushes all the transformation logic in the mapping to the source database. You can configure full pushdown in the mapping run-time properties.
Full pushdown optimization is ideal when the source and target are in the same database or when transformations such as Aggregator and Filter transformations are processed in the source database and reduce the amount of data moved. For example, if a mapping contains a Teradata source and Teradata target, configure full pushdown optimization to push all the transformation logic for processing from a Teradata source database to a Teradata target database.
When you configure a mapping with an Update Strategy transformation for full pushdown, you must determine pushdown compatibility for the mapping.
The Data Integration Service can pushdown a mapping with an Update Strategy transformation in the following scenarios:
- •If the target transformation connected to the Update Strategy transformation receives multiple rows that do not have the same key.
- •If the target transformation connected to the Update Strategy transformation receives multiple rows with the same key that can be reordered.
The Data Integration Service cannot pushdown a mapping with an Update Strategy transformation in the following scenario:
- •If the target transformation connected to the Update Strategy transformation receives multiple rows with the same key that cannot be reordered.
You can also use a pushdown compatibility parameter in the mapping. You can use the following parameter values:
- •noMultipleRowsWithSameKeyOnTarget
- •reorderAllowedForMultipleRowsWithSameKey
- •reorderNotAllowedForRowsWithSameKey
The Data Integration Service can use full pushdown optimization for the following sources:
- •Oracle
- •IBM DB2
- •Microsoft SQL Server
- •Teradata
- •Netezza
- •Greenplum
- •SAP HANA
Rules and Guidelines for Full Pushdown Optimization
Consider the following rules and guidelines when you configure full pushdown optimization:
- •The Data Integration Service can push all transformation logic in the mapping to IBM DB2, Oracle, Microsoft SQL Server, and ODBC sources such as Teradata, Greenplum, Netezza, and SAP HANA.
- •When you configure full pushdown optimization for a mapping with an Update Strategy transformation, you can use the Update else Insert strategy only for Oracle and Teradata.
Source Pushdown
When the Data Integration Service applies source pushdown, it analyzes the mapping from source to target or until it reaches a downstream transformation it cannot push to the source database.
The Data Integration Service generates and executes a SELECT statement based on the transformation logic for each transformation it can push to the database. Then, it reads the results of this SQL query and processes the remaining transformations.
You can configure a mapping to use source pushdown if the source and target reside in different databases. For example, if a mapping contains a Teradata source and an Oracle target, you can configure source pushdown to push some transformation logic for processing to the Teradata source.
Configuring Pushdown
You can configure a mapping for pushdown optimization in the mapping run-time properties.
1. Open a mapping.
2. On the Properties tab, select Run-time.
3. Choose a pushdown type or assign a pushdown parameter:
- - None. The Data Integration Service does not pushdown the mapping logic to the source database.
- - Full. The Data Integration Service pushes down the full mapping logic to the source database.
- - Source. The Data Integration Service pushes down all mapping logic except the target to the source database.
- - Assign Parameter. Select the parameter that you configured for pushdown type or create a new parameter and click OK.
4. Optionally, if you choose full pushdown optimization and the mapping contains an Update Strategy transformation, you can choose a pushdown compatibility option or assign a pushdown compatibility parameter.
- - Multiple rows do not have the same key. The target transformation connected to the Update Strategy transformation receives multiple rows that have the same key. The Data Integration Service can pushdown the Update Strategy transformation.
- - Multiple rows with the same key can be reordered. The target transformation connected to the Update Strategy transformation receives multiple rows with the same key that can be reordered. The Data Integration Service can pushdown the Update Strategy transformation.
- - Multiple rows with the same key cannot be reordered. The target transformation connected to the Update Strategy transformation receives multiple rows with the same key that cannot be reordered. The Data Integration Service cannot pushdown the Update Strategy transformation.
- - Assign Parameter. Select the parameter that you configured for pushdown compatibility or create a parameter and click OK.