An in depth and detailed explanation of Informatica bulk and normal modes

Bulk is a high-performance Load data mode provided by Informatica. It uses the underlying mechanism of the database and relies on calling the Utility provided by the database itself to load the data.

When using Bulk Load, Informatica calls Utility for Load, which will bypass the log record of database, so as to improve the performance of database Load, so it is impossible to operate Rollback in Bulk and Recover in database. Therefore, when setting this property, it is necessary to balance the importance of performance improvement and system data recovery under 1.

From the implementation of Bulk, we can know that Bulk is mainly selected when operating Insert with large data volume, in other words, Update is not used. When this option is set, Informatica Sever actually calls Bulk Utility of the database and ignores log for loading. Therefore, the Bulk mode can also be tuned here, which is the "transaction commit number" we need to adjust. The default value for Commit Interval is 10000. So you can increase this value to reduce the number of transactions (Bulk Load Transaction) and improve performance. It should be noted that this adjustment is only useful for Oracle and SQL Sever. DB2 and Sybase are not affected by this value, only related to the size of Write and Block, and are submitted once they are full.

Because Bulk mode can only be used to do Insert operation. As we all know, if we need Update operation, we need to set Data Driven on the setting of Treat source rows as of Session. What will happen when we choose two settings at the same time? If you set both Data, Driven and Bulk modes, PowerCenter and Sever will automatically switch to Normal for Load.

Default Bulk to Normal settings. Workflow Manager Tools Options Miscellaneous Target Load Type