I have no idea how this could possibly have happened when by definition, no logging is taking place when running in bulk mode. Any ideas? I am on DB2.
I'm not sure if that is correct -
By default, each insert is logged for recovery purposes. Log records are written first to the log buffer in memory, and then to the log file, most commonly when the log buffer is full or a commit occurs. Optimizing the logging of bulk inserts is a matter of minimizing the number of log writes and making the writes as fast as possible.
The first thing to consider here is the log buffer size, controlled by the database configuration parameter LOGBUFSZ. It has a default value of 8 pages, or 32K, which is smaller than ideal for most bulk inserts. Let's take an example. For a bulk insert with 200 bytes logged per row, the log buffer will fill after about 160 rows have been inserted. If there are 1000 rows being inserted, there will be about six log writes due to the log buffer being filled, plus one for the commit. By raising the value of LOGBUFSZ to 64 (256K) or more, the log buffer will not fill, and there will only be one log write (at commit) for the bulk insert. Tests 104 and 105 showed about a 13% improvement from using a larger LOGBUFSZ. The downside of a larger log buffer is that crash recovery can take slightly longer.
Another possibility for minimizing log writes is to use "ALTER TABLE <name> ACTIVATE NOT LOGGED INITIALLY" (NLI) for the table being inserted into. When that's done, none of the inserts will be logged for the duration of that same unit of work, but there are two important issues related to NLI:
If there's a statement failure, the table will be marked as inaccessible and must be dropped. This and other recovery issues (see the SQL Reference discussion of Create Table) makes NLI not a feasible approach for many situations.
The commit at the end of the unit of work cannot complete until all of the dirty pages involved in the unit of work have been written to disk. This means the commit can take a substantial amount of time. In fact, as shown by tests 6 and 7, if page cleaning is not done aggressively, the total elapsed time for Iisert plus commit can be longer with NLI. However, as test 8 showed, utilizing NLI with aggressive page cleaning can shorten the elapsed time substantially. If using NLI, keep your eye on the elapsed times of your commits.
As far as speeding up log writes is concerned, some possibilities are:
Place the log on disk(s) that are separate from the tables being inserted into.
Stripe the log across multiple disks at the operating system level.
Consider a raw device for the log, but note that management is more difficult.
Avoid RAID 5 due to its inappropriateness for write-intensive activity.
http://www.ibm.com/developerworks/data/library/tips/dm-0403wilkins/The page also has many
optimization suggestions.