Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

The following Help topic discusses the dbsrv12 -b bulk loading option.

It says "You can also use some of the logging options available for the LOAD TABLE statement that allow bulk-loaded data to be recorded in the transaction log."

Does that mean LOAD TABLE WITH ROW LOGGING will actually override dbsrv12 -b and cause data to be written to the transaction log?

Data recovery issues for bulk operations

You can run the database server in bulk operations mode (the -b server option). When you use this option, the database server does not perform certain important functions. Specifically:

Function Implication
Maintain a transaction log There is no record of the changes. Each COMMIT causes a checkpoint.
Lock any records There are no serious implications.

Alternatively, you may also need to ensure that data from bulk loading is still available in the event of recovery. You can do so by keeping the original data sources intact, and in their original location. You can also use some of the logging options available for the LOAD TABLE statement that allow bulk-loaded data to be recorded in the transaction log. See LOAD TABLE statement.

Caution You should back up the database before and after using bulk operations mode because your database is not protected against media failure in this mode.

asked 24 Dec '10, 11:41

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

I can only imagine that the documentation is suggesting that you can use -b bulk loading mode or, alternatively, LOAD TABLE without -b if you are trying to avoid writing operations to the transaction log. Internally, I would assume that LOAD TABLE WITH ROW LOGGING when in -b mode will try to write the rows to the log but the lower levels will just ignore those attempts because -b is enabled.

Bulk operations mode should soooo have been removed long ago.


permanent link

answered 24 Dec '10, 12:33

John%20Smirnios's gravatar image

John Smirnios
accept rate: 37%

Why should it have been removed? What if you are FORCED to use INSERT to bulk load millions of rows?

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

If we had the (dangerous) ability to do unlogged operations, I don't think bulk mode would be needed at all. I think the user can already avoid the overhead of per-row locks if you get an exclusive lock on the table too. If the cost of logging the row data is acceptable but the space overhead is not, the app doing the massive inserts could truncate the log periodically -- possibly even triggered by an event.

(24 Dec '10, 15:58) John Smirnios
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:41

question was seen: 1,422 times

last updated: 24 Dec '10, 12:33