In-depth description of Informatica bulk and normal Mode

Bulk is a high-performance Load data method provided by Informatica. It uses the underlying mechanism of the database to call the Utility provided by the database itself for data loading.

When using Bulk Load, Informatica calls Utility for Load. This method bypasses the database log records to improve the database Load performance. Therefore, the Bulk method cannot perform Rollback operations, it is also impossible to use the database for Recover operations. Therefore, when setting this attribute, You need to balance the importance of performance improvement and system data recovery.

From the implementation of Bulk, we can know that Bulk is mainly used for large data Insert operations, that is, not to Update. After this option is set, Informatica Sever actually calls the database's Bulk Utility and ignores the log for loading. Therefore, you can optimize the Bulk mode here, which is the "Number of transaction commits" that we need to adjust. The default value of Commit Interval is 10000. Therefore, you can increase the value to reduce the number of transactions (Bulk Load Transaction) and improve performance. Note that this adjustment is only useful for Oracle and SQL Server. DB2 and Sybase are not affected by this value. They are only related to the size of the Write Block and are committed once they are fully written.

Because the Bulk method can only be used for Insert operations. We all know that if we need an Update operation, we need to set it to Data Driven in the Treat source rows as settings of the Session. What will happen if we select both settings. If both Data Driven and Bulk modes are set, PowerCenter Sever automatically switches to the Normal mode for Load.

Bulk to Normal by default.Workflow Manager Tools Options Miscellaneous Target Load Type