Informatica

Pushdown Optimization in Informatica

In order to achieve the optimal performance in informatica, we are using pushdown optimization. It is a way of load balance among the servers. The Informatica PowerCenter Pushdown Optimization Option enables data transformation processing, where appropriate, to be pushed down into any relational database to make the best use of database assets. Using pushdown optimization in Informatica transformation processing logic can be pushed 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.
 Informatica supports below database types for Pushdown Optimization.
  • Oracle
  • IBM DB2
  • Teradata
  • Sybase
  • Netezza
  • MS SQL Server

Types of Pushdown Optimization
Informatica sessions can be configured three types of pushdown optimization techniques.
  • Source-side pushdown optimization
  • Target-side pushdown optimization
  • Full pushdown optimization
Source-side Pushdown Optimization
When a session is configured for Source-side pushdown optimization, Integration Service analyzes the mapping from source to target for any transformation logic and it tries to push it to source database and executes the corresponding SELECT statement.
Target-side Pushdown Optimization
When a session is configured for Source-side pushdown optimization, Integration Service analyzes the mapping from target to source for any tranformation logic or until it reaches an upstream transformation Integration Service can’t push to target database. It generates an INSERT, DELETE, or UPDATE statement based on the transformation logic for each transformation it can push to the database and executes the SQLs.
Full Pushdown Optimization
We can use full pushdown optimization only when the source and target databases are in the same relational database management system. 
                   The Integration Service pushes as much transformation logic as possible to both source and target databases. If you configure a session for full pushdown optimization, and the Integration Service cannot push all the transformation logic to the database, it performs source-side or target-side pushdown optimization instead.The Integration Service analyzes the mapping starting with the source and analyzes each transformation in the pipeline until it analyzes the target.
                 When it can push all transformation logic to the database, it generates an INSERT, SELECT statement to run on the database. The statement incorporates transformation logic from all the transformations in the mapping. If the Integration Service can push only part of the transformation logic to the database, it does not fail the session, it pushes as much transformation logic to the source and target database as possible and then processes the remaining transformation logic.







Powered by Blogger.