Using 18.104.22.16803 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...
asked 14 Jun '16, 10:47
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.