Data Warehousing Community Forum
February 09, 2012, 01:17:13 pm

Pages: [1]   Go Down
  Print  
Author Topic: Filling DB Transaction Log file in Bulk Mode  (Read 1622 times)
Pink Apple
DW Fresher
*

Reputation: +3/-0
Offline Offline

Posts: 14


View Profile
« on: January 30, 2010, 02:46:57 am »

I am running a workflow that populates a staging area in a DW and therefore is set to Bulk Mode.  Somehow - my run just failed as the transaction log was filled.  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.
Logged
Whoever
Administrator
*****

Reputation: +34/-0
Offline Offline

Posts: 124



View Profile WWW
« Reply #1 on: February 01, 2010, 10:44:34 am »

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

If most people said what’s on their minds, they’d be speechless.
Pink Apple
DW Fresher
*

Reputation: +3/-0
Offline Offline

Posts: 14


View Profile
« Reply #2 on: February 01, 2010, 08:26:45 pm »

That's what I get for listening to the DBAs I suppose.  Thank you so much, very helpful.  I had actually tried using NLI first, but that was still filling the transaction log.  Probably autocommit related - though I cannot figure out how to turn it off.  I set the commit interval to 1000000000 so it would never actually hit and wouldnt commit until end of file, but that didnt work either.  Ugh.
Logged
Pink Apple
DW Fresher
*

Reputation: +3/-0
Offline Offline

Posts: 14


View Profile
« Reply #3 on: February 01, 2010, 09:18:44 pm »

Ok - so after some additional research, I came across this:

Logging is required for fully recoverable databases. The load utility almost completely eliminates the logging associated with the loading of data. In place of logging, you have the option of making a copy of the loaded portion of the table. If you have a database environment that allows for database recovery following a failure, you can do one of the following:
◦Explicitly request that a copy of the loaded portion of the table be made.
◦Take a backup of the table spaces in which the table resides immediately after the completion of the load operation


I am thinking that 'bulk inserts' in the original article you referenced is referring to the import utility, rather than the load utility.  Which puts me back to my original question of how I can be filling the trans log.  Thanks again.
Logged
Pages: [1]   Go Up
  Print  
 
Jump to: