Using 12.0.1.4403 I was looking into the dbRemote options. We have always used the default of 20 for the -g option however I am wondering how dbremote handles an error in one of the transactions when it finally issues a commit.

As I understand it commits are ignored until DBRemote has at least this number of operations (inserts, deletes, updates) (default 20) that are uncommitted. Assuming a grouping of 20 operations If one of the operations fails what happens to the other 19?

Thanks in advance...

Jim

asked 14 Jun '16, 10:47

J%20Diaz's gravatar image

J Diaz
830243044
accept rate: 14%

So according to what I understood the best option is to set a parameter of -g 1 to avoid any further troubles. However, I am not sure if it's applicable in a continuous mode.

Homepage

(12 Oct '16, 05:30) jessRD
Replies hidden

?Troubles?

(12 Oct '16, 09:05) Nick Elson S...

MEA CULPA I deleted my previous answer ... it was actually wrong on a number of points. I've corrected here ...

The -g SQL Remote switch only affects the receive phase of dbremote and will 'group up'/combine small transactions with subsequent operations to make slight bigger transactons. The number is a measure of the number of statements and the setting is used as a threshold on the number of pending operations when dbremote is processing a commit. If the current number of operations is greater or equal to that number dbremote will execute a commit; lower it will forego executing the commit combining the current transaction with the next.

The effect of this can be most dramatic for a situation where there are autocommitted operations or when you have lots of very small transactions.

A setting of -g 1 is guaranteed to turn off all grouping and dbremote will apply complete transactions at the points they were originally committed.

Error handling will roll back and step by step resubmitting single statements (including the commits) when required. This helps to identify the offending statement but can also side-step/correct some conditions.

Another contributing factor here is that SQL Remote (dbremote) defers referential integrity checking until the commit so RI errors will cluster around the commits if/when those occur, so a large -g setting can be a bit more expensive if you have RI errors in your design.

Note: Messages sizes are not affected by this setting. The send phase will fill messages as much as it reasonably can independantly of this switch setting.

Hopefully a better answer.

permanent link

answered 14 Jun '16, 15:06

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

edited 12 Oct '16, 19:19

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659

Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×42

question asked: 14 Jun '16, 10:47

question was seen: 649 times

last updated: 12 Oct '16, 19:19