The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

Why does the V12 Help say "The -b option should not be used if you are using LOAD TABLE"?

I agree that -b might not help a lot if you are using LOAD TABLE, but "not help a lot" is quite different from "should not be used".

For example, consider an application that uses both INSERT and LOAD TABLE to load masses of data... the INSERT operations would benefit greatly from dbsrv12 -b (no giant transaction log).

Why should that application eschew dbsrv12 -b simply because it also uses LOAD TABLE?

-b dbeng12/dbsrv12 server option

Uses bulk operation mode.

This option is useful for using the Interactive SQL INPUT statement to load large quantities of data into a database. See INPUT statement [Interactive SQL].

The -b option should not be used if you are using LOAD TABLE to bulk load data. See LOAD TABLE statement.

When you use this option, the database server allows only one connection by one application. It keeps a rollback log, but it doesn't keep a transaction log. The multi-user locking mechanism is turned off.

When you first start the database server after loading data with the -b option, you should use a new transaction log file.

Bulk operation mode doesn't disable the firing of triggers

asked 24 Dec '10, 11:30

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

I can't think of any technical reason not to use LOAD TABLE at the same time as -b. Again, I think the documentation lost the spirit of the original sentiment. It probably was meant to suggest that you could have the benefit of no logging by using LOAD TABLE rather than resorting to -b which has lots of other consequences too.

Do you find -b particularly useful? Personally, I'd love to see it disappear. Writing to the log certainly doesn't come for free but many performance improvements were made in the logging code. If necessary (and if possible) maybe a method of inserting without logging could be added at the statement level (with obvious disastrous implications for recovery). Oh, and please don't take that last statement and turn it into "John suggests..." -- I don't suggest it but maybe we could be bullied into it :)


permanent link

answered 24 Dec '10, 12:51

John%20Smirnios's gravatar image

John Smirnios
accept rate: 40%

What are the "other consequences"? I have just added dbsrv11 -b to the Foxhound upgrade process, but I can just as quickly rip it out since UNLOAD COMPRESSED is being used on the biggest tables.

(24 Dec '10, 12:57) Breck Carter

No, not "particularly useful"... always intriguing, however :)

(24 Dec '10, 12:58) Breck Carter

Other consequences include but may not be limited to the following:

  1. Making changes for which no record is made to a transaction log. That can make recovering from a backup impossible.

  2. Every commit performs a checkpoint which makes executing a bunch of DDL very, very slow.

  3. The need to restart a server to do unlogged operations then restart it again to do logged operations.

  4. A limit of only one connection.

Some or all of these consequences may not matter to you in the case of your upgrade process since you are essentially doing a reload and starting from scratch.

(24 Dec '10, 15:49) John Smirnios

Also see my comments on your blog about UNLOAD COMPRESSED and named pipes:

(24 Dec '10, 16:02) John Smirnios

@Breck, @John: Lots of useful stuff in these blog comments. Could we have them here, too? - Say, UNLOAD blog to SQLA :)

(27 Dec '10, 09:21) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 24 Dec '10, 11:30

question was seen: 677 times

last updated: 24 Dec '10, 12:51