I have become a huge fan of dbunload -an for upgrading a database in one fell swoop... the days of reloads collapsing in a heap seem to be behind us. It would be nice to say "This upgrade process leaves the existing database untouched" and I'm sure it does... except the file timestamp changes for the old.db and log files. There doesn't seem to be any dbunload equivalent for the dbeng.exe -r option... what am I missing? I realize that a database in need of recovery can't be unloaded in read-only mode, that's outside the scope of this question. And yes, the old-school three-step-process dbunload-dbinit-reload could make use of dbeng -r, but that's not as much fun as dbunload -an. |
As part of the connection string you can pass database switches (DBS) that apply to an autostarted database. UID=dba;PWD=sql;DBF=mydb;DBS=-r;... Since the -r switch can be either a server switch or database switch, it can be passed to start that database as read-only even if other aspects of the server are not. 1
Ooops, no cigar! :) Using DBS=-r in the -c string on a SQL Anywhere 16 dbunload -an command DOES work to treat the old database as read-only (the .db and .log file timestamps don't change)... BUT, it has an unintended consequence: It creates the new database without a log file (as if dbinit -n was run). That's a showstopper; what else do I need to do? Running dblog to fix the condition might work, but it detracts from the coolness factor of runnning a one-step upgrade. Here is the dbunload command... "%SQLANY16%\bin64\dbunload.exe"^ -an "C:\RuralFinds\ruralfinds16.db"^ -ap 4096^ -et^ -c "DBF=C:\RuralFinds\ruralfinds.db;UID=dba;PWD=sql;DBS=-r"^ -o "C:\RuralFinds\dbunload_upgrade_log_ruralfinds16.txt"^ -v Here is the output from dbinfo... SQL Anywhere Information Utility Version 16.0.0.1512 Database : C:\RuralFinds\ruralfinds16.db Log file : none Log mirror: none Page size : 4096 Encrypted : No Strings padded with blanks for comparisons: No CHAR collation sequence: 1252LATIN1(CaseSensitivity=Ignore) CHAR character set encoding: windows-1252 NCHAR collation sequence: UCA(CaseSensitivity=Ignore;AccentSensitivity=Ignore;Pu nctuationSensitivity=Primary) NCHAR character set encoding: UTF-8 Database checksums enabled: Yes Encrypted tables supported: Yes
(09 Aug '13, 08:29)
Breck Carter
Replies hidden
2
Ah, that is an interesting side effect. Because the database is being started as read-only, the server isn't loading the transaction log (no changes are allowed in read-only, so there is no need to load the transaction log). dbunload sees the database running without a log, and generates a similar database without a log file.
(09 Aug '13, 12:53)
Tyson Lewis
FWIW a simple "dblog -t xxx.log xxx.db" command immediately after the dbunload rectifies the problem... I guess that's not too big a price to pay to be able to say to management "No, sir, the database upgrade process does not in any way modify the existing database, so yes, sir, the fallback process is protected! Sir! [saluting]" :)
(09 Aug '13, 14:25)
Breck Carter
Is that "an interesting side effect" or a bug? IMHO, dbunload surely could access the old database's properties, and that should still show the existance of a loog file here...
(09 Aug '13, 16:19)
Volker Barth
What property would that be? When an otherwise ordinary V16 database is started with -r, here's what you see: SELECT DB_PROPERTY ( 'LogName' ); DB_PROPERTY('LogName') ---------------------- (NULL) When you stop and restart without the -r, the log file spec is magically available: DB_PROPERTY('LogName') ----------------------------------- C:\projects\$SA_templates\ddd16.log As bugs go, it may be a particularly skanky bug (unwilling to change its bad habits, hard to reform, etc) so maybe we're better off calling it a "side effect" :) FWIW Foxhound suffers from the same bug: It interprets "NULL LogName" as "no log file" and calls it a curiosity, not unlike a character from Cops or Jail or Honey Boo Boo. ...and for bonus grins and giggles, here's what the Help says about dbsrv16 -r: "Opens all database files (the main database file, dbspaces, transaction log, and transaction log mirrors) as read-only." Seriously? I don't think so :)
(09 Aug '13, 16:42)
Breck Carter
Ah, I see - that would explain the behaviour. Well, I had thought of a DBLogFileInfo() call - that should return the "real" log information but would have to be called before starting the old database... Apparently, the current behaviour has enough simple workarounds:
(09 Aug '13, 17:20)
Volker Barth
|