How do I deal with COMMIT failing on SQLCODE -660?

[Breck_Carter]

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?

[VolkerBarth]

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:)

[Breck_Carter]

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?

[VolkerBarth]

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:)