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
|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
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.