The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.

asked 05 Aug '13, 16:40

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%


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.

permanent link

answered 05 Aug '13, 16:54

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

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:

  • Use DBLOG -t afterwards for the new database (as you have suggested)
  • Don't use a read-only database but let the unload run against a copy of the original database so the original is still left unchanged.
(09 Aug '13, 17:20) Volker Barth
showing 1 of 6 show all flat view
Your answer
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:

×51

question asked: 05 Aug '13, 16:40

question was seen: 1,691 times

last updated: 09 Aug '13, 17:21