How do I deal with the situation where a COMMIT raises the following exception?

SQLCODE = -660 "SQLE_OMNI_REMOTE_ERROR"
SQLSTATE = WO005
ERRORMSG() = Server 'p003': [Sybase][ODBC Driver][SQL Anywhere]Database server not found

Presumably, something has gone wrong with a connection to a remote server, but that's not the problem...

In this case, there were no outstanding updates on the remote server, but there WERE outstanding updates on the local database and that's what the COMMIT was intended for. (sadly, I don't know whether the local updates were lost or committed)

How do I prevent a problem with a remote server from adversely affecting a transaction on the local database? Is there some way to execute a local COMMIT that ignores any connections to remote servers?

If it can't be prevented, how should it be dealt with? Is there some way to code an EXCEPTION handler that successfully re-issues the COMMIT? (assume the remote server connection is no longer important)

Does the automatic commit associated with ALTER SERVER CONNECTION CLOSE come before or after the remote connection is closed?

asked 16 Sep '12, 09:35

Breck%20Carter's gravatar image

Breck Carter
32.5k5407241050
accept rate: 20%

edited 16 Sep '12, 11:10

Volker%20Barth's gravatar image

Volker Barth
39.8k358546815

In case the remote access is not really necessary (i.e. , as you state, you would just go on locally), I would wrap all statements related to remote data with exception handlers and catch any errors...

No, that does not answer your questions, it's just a comment:)

(16 Sep '12, 11:09) Volker Barth
Replies hidden

That is exactly the case now: The COMMIT is wrapped in its own BEGIN...EXCEPTION block. The question remains, what the [redacted] can the exception handler do about the problem?

(16 Sep '12, 14:41) Breck Carter

Some guesses: As SQL Anywhere uses a T2C protocoll to handle remote data access, I can't image that ALTER SERVER CONNECTION CLOSE could solve the problem during the transaction: Obviously, the final COMMIT must be committed by the remote server, too, and that seems not possible when the remote connection is already closed. It also would mean that when the remote server is not accessible, the COMMIT MUST FAIL.

The following doc tells that savepoints are not supported remotely, so savepoints (aka nested transactions) won't help either to undo/ignore just the remote work...

If these assumptions are correct, I think there are only two workarounds:

  • Break the transaction into smaller chunks, so the window of opportunity between "getting remote work done" (when the remote server is functional) and "two-phase commit" (when the remote server may be gone out of reach) is shortened.
  • Put your transaction in a loop and repeat it if the COMMIT fails - possibly with ignoring the remote access parts.

(No, not been there, lucky me:)

(17 Sep '12, 03:16) Volker Barth
Be the first one to answer this question!
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:

×106
×56
×31

question asked: 16 Sep '12, 09:35

question was seen: 2,813 times

last updated: 17 Sep '12, 03:18