Question Re: Execute SqlAnywhere 11 Command without Rollback on Windows using the UTILITY_DB in Technology Q&A

Execute SqlAnywhere 11 Command without Rollback on Windows using the UTILITY_DB

Former Member — Tue, 04 Nov 2014 16:36:07 GMT

I use an application that connects to a SqlAnywhere 11 server/database by issuing the following command (taken from the ConsoleLogFile):

<,3,EXEC_IMM,START DATABASE 'database-file.DBF' AS
"database-name" USING DISTINCT SQLSTATE
Starting database "database-name" (database-file.DBF) at Tue Nov 04 2014 09:42
Database recovery in progress
    Last checkpoint at Mon Sep 29 2014 13:05
    Checkpoint log...
    Transaction log: database-file.DBF.TLG...
Error: Cannot open transaction log file -- The system cannot find the file specified.
Recovery complete
E,3,-106,Cannot open transaction log file -- database-file.DBF.TLG
>.,3
<,3,DISCONNECT
Database "UTILITY_DB" (UTILITY_DB) stopped at Tue Nov 04 2014 09:42
>,3


I'd like to emulate the way the application issues the START DATABASE command but can't figure out how to execute with EXEC_IMM (I'm assuming this is EXECUTE IMMEDIATE?) when logged into the UTILTIY_DB as DBA; I can only seem to execute with PREPARE (e.g., <,3,EXEC_IMM,START DATABASE...). 
Most importantly I'd like to prevent a ROLLBACK after the recovery. I tried starting the server and database with the -f command, but it then complains about the database needing recovery.
So, how do I execute unprepared statements and/or prevent rollbacks on errors?

Re: Execute SqlAnywhere 11 Command without Rollback on Windows using the UTILITY_DB

VolkerBarth — Wed, 05 Nov 2014 03:22:38 GMT

What exact problem are you facing - what's the link between START DATABASE and a rollback? Are you trying to start a database and that should not have an impact on the current transaction? (I don't know if it does, at least START DATABASE should not do an auto-commit.)

Just as a general hint: There are several ways to prevent an impact of operations on the current transaction, e.g.:

put these operations within a savepoint than can be rollbacked without "disturbing" the surrounding transaction
use an event to execute these operations and start that event from the current transaction: as events run on their own connection, they use a separate transaction by design
put these operations within a separate transaction (though that necessarily means the current transaction must be committed beforehad unless you use a separate connection).

Re: Execute SqlAnywhere 11 Command without Rollback on Windows using the UTILITY_DB

Former Member — Wed, 05 Nov 2014 15:05:47 GMT

I asked a similar question related to this issue on StackOverflow: http://stackoverflow.com/questions/26742883/execute-sqlanywhere-11-command-without-rollback-on-windows-using-the-utility-db. I'll update this question with more details in a moment

Re: Execute SqlAnywhere 11 Command without Rollback on Windows using the UTILITY_DB

Former Member — Wed, 05 Nov 2014 16:05:39 GMT

If the goal is to prevent a ROLLBACK of uncommitted transactions at the last time the database went down, the short answer is that you can't.

The long answer is that even if you could, you'd have to deal with potential database issues like check constraints and referential integrity (specifically if wait_for_commit is used). However, you may be able to regain some of this data by applying translated SQL for those uncommitted transactions against the database manually.

Re: Execute SqlAnywhere 11 Command without Rollback on Windows using the UTILITY_DB

Former Member — Wed, 05 Nov 2014 17:58:12 GMT

If you believe you have a big transaction that didn't complete you could always pull out the details from the transaction log using the dbtran utility. See dbtran -s -a to get at the SQL (and then change the rollback to be a commit and resubmit after the database recovers.