Pushdown Optimization and Types in Informatica

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
• Target-side Pushdown Optimization
• Full Pushdown Optimization

Source-Side Pushdown Optimization: 

The Integration Service first analyzes 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 analyzes 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 pushdown optmization 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 verion 8.5 or higher. SCDs created in lower version cannot be pushed to database.

When to Use Pushdown Optimization: 

Use pushdown optmization technique when the source or target dabase gives better performance when compared to the informatica.

Reverse the Contents of Flat File – Informatica

Q1) I have a flat file, want to reverse the contents of the flat file which means the first record should come as last record and last record should come as first record and load into the target file. 

As an example consider the source flat file data as

Informatica Enterprise Solution
Informatica Power center
Informatica Power exchange
Informatica Data quality

The target flat file data should look as

Informatica Data quality
Informatica Power exchange
Informatica Power center
Informatica Enterprise Solution

Solution:

Follow the below steps for creating the mapping logic

• Create a new mapping.
• Drag the flat file source into the mapping.
• Create an expression transformation and drag the ports of source qualifier transformation into the expression transformation.
• Create the below additional ports in the expression transformation and assign the corresponding expressions

Variable port: v_count = v_count+1
Output port o_count = v_count

• Now create a sorter transformation and drag the ports of expression transformation into it.
• In the sorter transformation specify the sort key as o_count and sort order as DESCENDING.
• Drag the target definition into the mapping and connect the ports of sorter transformation to the target.

Q2) Load the header record of the flat file into first target, footer record into second target and the remaining records into the third target.

The solution to this problem I have already posted by using aggregator and joiner. Now we will see how to implement this by reversing the contents of the file.

Solution:

• Connect the source qualifier transformation to the expression transformation. In the expression transformation create the additional ports as mentioned above.
• Connect the expression transformation to a router. In the router transformation create an output group and specify the group condition as o_count=1. Connect this output group to a target and the default group to sorter transformation.
• Sort the data in descending order on o_count port.
• Connect the output of sorter transformation to expression transformation (don’t connect o_count port).
• Again in the expression transformation create the same additional ports mentioned above.
• Connect this expression transformation to router and create an output group. In the output group specify the condition as o_count=1 and connect this group to second target. Connect the default group to the third group.