Informatica Development Best Practice

Workflow Manager default properties can be modified to improve the overall performance and few of them are listed below. This properties can impact the ETL runtime directly and needs to configured based on :

i) Source Database
ii) Target Database
iii) Data Volume

Category

Technique

Session Properties

While loading Staging Tables for FULL LOADS, Truncate target table option should be checked. Based on the Target database and the primary key defined, Integration Service fires TRUNCATE or DELETE statement.Database                  Primary Key Defined                   No Primary KeyDB2                             TRUNCATE                                       TRUNCATE
INFORMIX                 DELETE                                              DELETE
ODBC                         DELETE                                                DELETE
ORACLE                    DELETE UNRECOVERABLE            TRUNCATE
MSSQL                       DELETE                                               TRUNCATE
SYBASE                     TRUNCATE                                        TRUNCATE

Workflow Property “Commit interval” (Default value : 10,000) should be increased for increased for Volumes more than 1 million records. Database Rollback Segment size should also be updated, while increasing “Commit Interval”.

Insert/Update/Delete options should be set as determined by the target population method.

Target Option                                   Integration Service
Insert                                                   Uses Target update Option
Update as Update
Update as Insert
Update else Insert
Update as update                             Updates all rows as Update
Update as Insert                               Inserts all rows
Update else Insert                            Updates existing rows else Insert

Partition

Maximum number of partitions for a session should be 1.5 times the number of processes in the Informatica server. i.e. 1.5 X 4 Processors = 6 partitions.

Key Value partitions should be used only when an even Distribution of data can be obtained. In other cases, Pass Through partitions should be used.

A Source filter should be added to evenly distribute the data between Pass through Partitions. Key Value should have ONLY numeric values. MOD(NVL(<Numeric Key Value>,0),# No of Partitions defined) Ex: MOD(NVL(product_sys_no,0),6)

If a session contains “N” partition, increase the DTM Buffer Size to at least “N” times the value for the session with One partition

If the Source or Target database is of MPP( Massively Parallel Processing ), enable Pushdown Optimization. By enabling this, Integration Service will push as much Transformation Logic to Source database or Target database or FULL ( both ) , based on the settings. This property can be ignored for Conventional databases.

Tagged as: Informatica, Informatica PowerCenter 8x Key Concepts, Informatica workflow process control