Why should dbsrv12 -b not be used with LOAD TABLE?

[Breck_Carter]

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?

http://dcx.sybase.com/index.html#1200en/dbadmin/b-database-dbengine.html

-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

[johnsmirnios]

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 🙂

-john.

[Breck_Carter]

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.

[Breck_Carter]

No, not "particularly useful"... always intriguing, however 🙂

[johnsmirnios]

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.

[johnsmirnios]

Also see my comments on your blog about UNLOAD COMPRESSED and named pipes: http://sqlanywhere.blogspot.com/2010/12/unload-compressed-in-action.html

[VolkerBarth]

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