Pushdown Optimization (PDO)
What Is Pushdown Optimization?
Pushdown Optimization, as the name indicates, is an approach to push the transformation logic down to the data source/target using corresponding SQL query to leverage power of compute engine of underlying source/target infrastructure. Performance is enhanced by multiple folds and less stress on data integration platform.
Why PDO Is Important?
Informatica has been leveraging Pushdown Optimization technique for quite many years and continuously evolving to increase mapping performance. Since processing data at the database level is extremely accelerated, Pushdown Optimization not only impacts the speed but also makes the jobs simple and efficient. Below are benefits of PDO :
- Boost Performance: Pushdown optimization accelerates data processing speed and hence reduces the processing time thus improving performance of the IDMC jobs.
- Enhanced Productivity & Reduced Cost: PDO is performance and resource driven (lower core usage, faster processing). In cloud driven architecture, cost is the key factor. Advanced PDO can effectively reduce cost overruns.
- No coding: Eliminates or minimizes the need to write complex SQL statements or code. The implementation can be done through simple steps as the Data Integration Service translates transformation logic into SQL statements in the background.
- Scale: Implementing PDO in IDMC effectively leverages high performance data warehouse engine.
- Backward compatible and future proof: Pushdown optimization is backward and forward compatible with several databases/storage platforms.
- OLTP acid properties. The ODBC pushdown optimization combines all the statements into a single BEGIN … COMMIT; statement that ensures transaction control and data integrity.
How Does PDO Work?
We can push the transformation logic to either source or target database using pushdown optimization. The Data Integration Service translates the transformation logic into SQL queries and sends the SQL queries to the source or the target database for execution of the SQL queries to implement the transformation logic.
The database type, transformation logic, and mapping task configuration determines how much transformation logic we can push to the database. The Data Integration Service analyses the mapping from end to end to reach the transformation logic that it cannot push down. It then generates and executes SELECT/DML queries based on the type of push down. Finally, it processes the rest of the transformation logic after reading the results from the SQL queries.
Types Of Pushdown Optimization In IDMC :
Informatica Intelligent Data Management Cloud supports the following types
- Source-side Pushdown Optimization
Using this technique, the Data Integration service pushes maximum possible transformation logic to the Source database. It then generates and executes a SELECT statement for each transformation that it can push to the source database and executes the rest of the logic in Informatica.
- Target-side Pushdown Optimization
Using Target-side pushdown, the Data Integration service pushes maximum possible transformation logic to the Target database. It then generates and executes INSERT, DELETE or UPDATE statements for each transformation that it can push to the target database.
- Full Pushdown Optimization
Using the Full pushdown optimization method, Informatica pushes maximum transformation logic to the target database. In case it cannot push the entire logic to target database, the Data Integration Service performs a source-side pushdown and processes the rest of the intermediate transformation logic which cannot be pushed to any database at the Informatica level.
To utilize the Full pushdown optimization optimally the source and target database connections should be the same.
- User Defined Pushdown Optimization
We can also configure Pushdown Optimization using a user-defined parameter value that is defined in the parameter file. We can use a user-defined parameter such as $$PushdownConfig in the Pushdown Optimization Type on the scheduled Task of the Mapping Task. Then define the parameter in the parameter file as $$PushdownConfig=<Pushdown optimization type>
How To Implement Pushdown In IDMC?
To enable Pushdown Optimization go to
Mapping task 🡪 Schedule Tab 🡪 Select the type of Pushdown Optimization 🡪 Finish the Task
Pushdown Optimization Guidelines:
To implement Pushdown optimization in our mappings we must keep in mind the following guidelines:
- To use Full Pushdown Optimization, the Source, Target, and Lookup objects must be in the same relational database management system.
- Lookup transformation must use Report Error for Multiple Match Policy. Using any other option for Multiple Matches makes the PDO stop at Lookup transformation.
- If an Informatica function is not supported by the database, Pushdown Optimization cannot be applied. Thus, the use of a custom query in Source transformation is recommended.
- Using variable ports in Expression transformation is not supported by Pushdown Optimization. This logic can also be handled in the source query.
- In order to use SQL override query in source, in the Pushdown Optimization section on the Schedule tab, select Create Temporary View. If this option is not selected the mapping will run without the Pushdown optimization.
- In order to use SQL override query in source, in the Pushdown Optimization section on the Schedule tab, select Create Temporary Sequence. If this option is not selected the mapping will run without the Pushdown optimization.
- When the source and target tables belong to two different schemas within the same database, enable cross-schema pushdown optimization. To implement this, create a separate connection for each schema but keep the username, password, and rest of the details same. Select the Pushdown Optimization type as Full and Enable cross-schema pushdown optimization check box in Schedule tab of mapping configuration task.
- When the source and target tables belong to two different databases and run queries on data spread across multiple databases we need to configure cross-database pushdown optimization. To implement this, ensure that the source and target transformations in the mapping must use two different ODBC connections that point to different database DSN entries. Select Full Pushdown Optimization type, add a new session property “Allow Pushdown across Databases” and set the Session Property Value to Yes. Finally select the Enable cross-schema pushdown optimization check box in Schedule tab of mapping configuration task.
Supported Transformations:
The following list shows the supported transformations which can push logic to the database:
- Aggregator
- Expression
- Filter
- Joiner
- Lookup
- Router
- Sequence Generator
- Sorter
- Source Qualifier
- Target
- Union
- Update Strategy