The main difference between normal and bulk load is, in normal load Informatica repository service create logs and in bulk load log is not being created.
That is the reason bulk load loads the data fast and if anything goes wrong the data cannot be recovered. But in normal load since the log is created the lost data can be recovered.
By configuring session to Bulk loading we cannot do ‘session recovery‘ when it get failed whereas in normal loading we can easily do session recovery.
Note: In normal load Informatica creates logs record by record in database level and in bulk mode it will not create detail log. By avoiding that detail writing in bulk mode improve performance.
If there are indexes and constraints in the table, you cannot use Bulk Load
You can drop/disable those indexes/constraints (pre session ) , then load the data (use Bulk Load) , and then create/enable the indexes/constraints (post session)
Pre-Session Database statements:
drop index schema_name.index_name1;
drop index schema_name.index_name2;
Post-Session Database statements:
create unique index schema_name.index_name1
on schema_name.table_name ( index_name1 asc )
not partitioned in tablespace_name allow reverse scans compress no;
We use this strategy mostly while loading huge number of records into big tables, we compare the run time of normal load and bulk load (drop indexes and then create) and decide which one to use.
You can choose Bulk mode when you load data into DB2, Sybase, Oracle, or Microsoft SQL Server databases. If you specify Bulk for other database types, the Integration Service reverts to a normal load.
