Pushdown optimization technique in informatica pushes the part or complete transformation logic to the source or target database. The integration service translates the transformation logic into SQL queries and sends those SQL statements to the database. Then the source or target database runs those SQL queries to process the transformation logic.
The amount of transformation logic you can push to the database depends on the database, transformation logic, mapping and session configuration. The Integration Service processes all transformation logic that it cannot push to a database.
To preview the SQL statements and mapping logic that the integration service pushes to the source or target database, use the pushdown optimization viewer.
Pushdown Optimization Types:
There are three types of pushdown optimization techniques that you can configure in a session. They are:
Source-Side Pushdown Optimization:
The Integration Service first analyses the mapping from the source to the target or until it reaches a downstream transformation that it cannot push to the source database. Then the integration service generates the SELECT statement and executes the SQL in the source database. Then it reads the result rows and process the remaining transformations.
Target-Side Pushdown Optimization:
The Integration Service first analyses the mapping from the target to the source or until it reaches an upstream transformation that it cannot push to the target database. Then the integration service generates the Insert, Update or Delete statements. The integration service processes the transformation logic up to the point that it can push the transformation logic to the database. Then, it executes the generated SQLs on the target database.
Full Pushdown Optimization:
You can use full pushdown optimization only when the source and target databases are in the same relational database management system.
The Integration Service analyzes the mapping from the source to the target or until it reaches a downstream transformation it cannot push to the target database. If the Integration Service cannot push all transformation logic to the target database, it tries to push all transformation logic to the source database.
If it cannot push all transformation logic to the source or target, the Integration Service pushes as much transformation logic to the source database, processes intermediate transformations that it cannot push to any database, and then pushes the remaining transformation logic to the target database. The Integration Service generates and executes an INSERT SELECT, DELETE, or UPDATE statement for each database to which it pushes transformation logic.
Pushdown Optimization and Slowly Changing Dimensions:
Slowly changing dimensions type 1 and type 3 logic can be pushed to the database using push down optimization technique. Use the following rules and guidelines for pushing SCD logic to database.
When to Use Pushdown Optimization:
Use pushdown optimization technique when the source or target database gives better performance when compared to the informatica.
The amount of transformation logic you can push to the database depends on the database, transformation logic, mapping and session configuration. The Integration Service processes all transformation logic that it cannot push to a database.
To preview the SQL statements and mapping logic that the integration service pushes to the source or target database, use the pushdown optimization viewer.
Pushdown Optimization Types:
There are three types of pushdown optimization techniques that you can configure in a session. They are:
- Source-side Pushdown Optimization
- Target-side Pushdown Optimization
- Full Pushdown Optimization
Source-Side Pushdown Optimization:
The Integration Service first analyses the mapping from the source to the target or until it reaches a downstream transformation that it cannot push to the source database. Then the integration service generates the SELECT statement and executes the SQL in the source database. Then it reads the result rows and process the remaining transformations.
Target-Side Pushdown Optimization:
The Integration Service first analyses the mapping from the target to the source or until it reaches an upstream transformation that it cannot push to the target database. Then the integration service generates the Insert, Update or Delete statements. The integration service processes the transformation logic up to the point that it can push the transformation logic to the database. Then, it executes the generated SQLs on the target database.
Full Pushdown Optimization:
You can use full pushdown optimization only when the source and target databases are in the same relational database management system.
The Integration Service analyzes the mapping from the source to the target or until it reaches a downstream transformation it cannot push to the target database. If the Integration Service cannot push all transformation logic to the target database, it tries to push all transformation logic to the source database.
If it cannot push all transformation logic to the source or target, the Integration Service pushes as much transformation logic to the source database, processes intermediate transformations that it cannot push to any database, and then pushes the remaining transformation logic to the target database. The Integration Service generates and executes an INSERT SELECT, DELETE, or UPDATE statement for each database to which it pushes transformation logic.
Pushdown Optimization and Slowly Changing Dimensions:
Slowly changing dimensions type 1 and type 3 logic can be pushed to the database using push down optimization technique. Use the following rules and guidelines for pushing SCD logic to database.
- Type 1 and type 3 SCDs can be pushed to oracle and IBM DB2 databases.
- Source data must not have duplicate rows. The database can become deadlocked if it makes multiple updates to the same row.
- You must create SCDs using wizard in version 8.5 or higher. SCDs created in lower version cannot be pushed to database.
When to Use Pushdown Optimization:
Use pushdown optimization technique when the source or target database gives better performance when compared to the informatica.
No comments:
Post a Comment