Pushdown Optimization in Informatica

PUSHDOWN Optimization: pushdown optimization technique in informatica pushes transformation logic to source or target databases. When you run a session configured for pushdown optimization, the Integration Service analyzes the mapping and transformations to determine the transformation logic it can push to the database.

Integration service reads transformation logic and convert it into SQL queries and sends those converted SQL queries to DB. Source or Target database runs those queries to process transformation logic.

We can push transformation logic to database it's purely depend upon database, T/R logic, mappings and session configuration.

We can preview SQL queries and mapping logic which integration service uses to push in pushdown optimization viewer.

Pushdown optimization enables data processing through informatica transformation can be pushed to RDBMS to get better performance, this minimize data moving between servers.

Types of pushdown optimization

1. Source pushdown optimization: When session configured for source side pushdown optimization Integration service analyzes mapping from Source definition to target definition to determine it cannot push to database or not then it generate SQL "select" statement based on the transformation logic for each transformation it can push to the database. When you run the session, the Integration Service pushes all transformation logic that is valid to push to the database by executing the generated SQL statement. Then, it reads the results of this SQL statement and continues to run the session.

If a session contains an SQL override or lookup override, the Integration Service generates a view based on the SQL override. Then it generates a SELECT statement and runs the SELECT statement against this view. Once session completes, the Integration Service drops the view from the database.

2. Target pushdown optimization: When we run session which is configured for target side pushdown optimization then integration service analyze mapping from target to source definition that it cannot push to target database. then integration service generate SQL insert, update or delete statements based on the transformation logic for each transformation it can push to db, starting with the first transformation in the pipeline it can push to the database. The Integration Service processes the transformation logic up to the point that it can push the transformation logic to the target database. Then, it executes the generated SQL.

3. Full pushdown optimization: The Integration Service pushes as much transformation logic as possible to both source and target databases. If we configuring session for full pushdown optimization, and if Integration Service cannot push all the transformation logic to the database, it will perform partial pushdown optimization instead of full pushdown optimization.

If you want to implement full pushdown optimization, the source and target must be on the same database. When you run a session which is configured for full pushdown optimization, the Integration Service analyzes the mapping starting with the source and analyzes each transformation logic in the pipeline until it analyzes the target. It generates SQL statements that are executed against the source and target database based on the transformation logic it can push to the database. If the session contains an SQL override or lookup override, the Integration Service generates a view and runs a SELECT statement against this view.

Session configuration for pushdown optimization:

session can be configured for pushdown optimization by editing session properties with option available "To Source", "To Target", "Full" & "$$PushdownConfig" once you choose any of these other options get enabled

• Allow temporary view for pushdown- Allows the Integration Service to create temporary view objects in the database when it pushes the session to the database.
• Allow Temporary Sequence for Pushdown- Allows the Integration Service to create temporary sequence objects in the database.
• Allow Pushdown for User Incompatible Connections- Indicates that the database user of the active database has read permission on the idle databases.

 Pushdown Optimization Viewer: Use the Pushdown Optimization Viewer to examine the transformations that can be pushed to the database. Select a pushdown option or pushdown group in the Pushdown Optimization Viewer to view the corresponding SQL statement that is generated for the specified selections.

Pushdown optimizer viewer pops up in a new window and it shows how integration service converts the data transformation logic into SQL statement for a particular mapping. When you select a pushdown option or pushdown group in the viewer, you do not change the pushdown configuration. To change the configuration, we must update the pushdown option in the session properties.

Pushdown Optimization and SCD: Slowly changing dimensions type 1 and type 3 logic can be pushed to the database using pushdown 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.

Important points on pushdown optimization:

• Use pushdown optimization technique when the source or target database gives better performance when compared to the informatica.
• When you run a session for full pushdown optimization, the database must run a long transaction, if the session contains a large quantity of data. Consider the following database performance issues when you generate a long transaction.
•  A long transaction uses more database resources.
•  A long transaction locks the database for longer periods of time, and thereby reduces the database concurrency and increases the likelihood of deadlock.
• A long transaction can increase the likelihood that an unexpected event may occur.
• Some cases where integration service and pushdown optimization can produce different results even though same transformation logic is there. This can happen while data conversion, Case sensitivity, sequence generation and sorting data.

Nulls treated as the highest or lowest value: While sorting the data, the Integration Service can treat null values as lowest, but database treats null values as the highest value in the sort order.

SYSDATE built-in variable: Built-in Variable SYSDATE in the Integration Service returns the current date and time for the node running the service process. However, in the database, the SYSDATE returns the current date and time for the machine hosting the database. If the time zone of the machine hosting the database is not the same as the time zone of the machine running the Integration Service process, the results can vary.

Date Conversion: The Integration Service converts all dates before pushing transformations to the database and if the format is not supported by the database, the session fails.

Logging: When the Integration Service pushes transformation logic to the database, it cannot trace all the events that occur inside the database server. The statistics the Integration Service can trace depend on the type of pushdown optimization. When the Integration Service runs a session configured for full pushdown optimization and an error occurs, the database handles the errors. When the database handles errors, the Integration Service does not write reject rows to the reject file.