Pushdown Optimization is a unique way of sending informatica transformation logic to either Source or Target Databases and execute queries there .
Mostly the Transformations which is having logic we can be pushed down to DB are good candidates for it and helped to increase the performance of the overall pipeline .
we can use Pushdown Optimization Viewer to preview the SQL statements and mapping logic that the Integration Service can push to the source or target database.
Two-Pass Pushdown Processing
Pushdown processing is based on a two-pass scan of the mapping metadata. In the first pass,
PowerCenter starts scanning the mapping objects starting with source definition object, moving
towards the target definition object. When the scan encounters an object containing data
transformation logic that cannot be represented in SQL, the scanning process stops, and all
transformation upstream of this transformation are grouped together with equivalent SQL for
execution inside the source system.
In the second pass, PowerCenter scans in the opposite direction (i.e., from the target definitions
towards the source definitions). When the scan encounters an object containing data
transformation logic that cannot be represented in SQL, the scanning process stops, and all
transformation objects downstream of this transformation are grouped together with equivalent
SQL for execution inside the target system. PowerCenter executes any remaining data
transformation logic.
When you configure PowerCenter to use pushdown optimization, it can process the data
transformation logic using full or partial pushdown optimization.
Pushdown
There are mainly 3 types of PDOs available
Source-side optimization. The Integration Service pushes as much transformation logic as possible to the source database.
Target-side optimization. The Integration Service pushes as much transformation logic as possible to the target database.
Full optimization. The Integration Service attempts to push all transformation logic to the target database. If the Integration Service cannot push all transformation logic to the database, it performs both source-side and target-side pushdown optimization.
Following is the list of the transformations which can be pushed down
The Integration Service can push logic of the following transformations to the database:
1. Aggregator
2. Expression
3. Filter
4. Joiner
5. Lookup
6. Router
7. Sequence Generator
8. Sorter
9. Source Qualifier
10. Target
11. Union
12. Update Strategy