Hi all, considering upgrading to SQL Anywhere 16 for our embedded application, and particularly interested in performance improvements. I just have a few questions around database file format.

So it appears as though it is possible to connect to and read a database file created in SQL Anywhere 12 with SQL Anywhere 16. Is it possible to do the opposite? I would guess not. Also are there any implications (particularly from a performance perspective) of keeping data files in Sql Anywhere 12 format but accessing them from SQL Anywhere 16?

Is there an accepted upgrade process for database files to migrate them from 12 to 16?

Thanks

asked 24 Jun '14, 05:16

Scott%20Baldwin's gravatar image

Scott Baldwin
1567914
accept rate: 0%

edited 24 Jun '14, 06:42

Volker%20Barth's gravatar image

Volker Barth
30.0k294447654

> embedded application

Neither Volker's answer nor mine consider the possibility you want to upgrade a database that has already been delivered to a client. Is that what you want to do, or are you talking about upgrading-before-delivering?

Upgrading an embedded database that is already in the hands of of a client is a huge challenge, with dbunload/dbugrad only a very small part of the problem... I can talk about how Foxhound does it (12 to 16) if you want, but trust me, avoid it if you can :)

(24 Jun '14, 07:18) Breck Carter

Thanks all for your responses, most helpful. Indeed I do need to upgrade databases already distributed to clients, but that should be fairly easily achievable from within our application. I will do some performance prototyping to determine the value of upgrading the database for our particular application.

Cheers.

(25 Jun '14, 20:22) Scott Baldwin

Just to answer your question partially:

  • A v16 database engine can run v10 and above databases. That's no problem.
  • A v12 database engine cannot run a v16 database - trying to do so will raise SQLCODE -934 "Server must be upgraded to start database ..." (You can try yourself: Start the v16 demo database with dbeng12...)
  • When using a newer database engine with a currently older database, you have basically three options:
  • Leave the database unchanged (here on v12)
  • Rebuild the database to use the new version's file format (i.e. doing a reload)
  • Upgrade just the system catalog and the database options to the new version via the DBUPGRAD utility or the ALTER DATABASE UPGRADE statement.

The v16 docs give some hints on reasons to do a rebuild or an upgrade here. As to performance implications, I guess that will result in the usually "It depends" response...

permanent link

answered 24 Jun '14, 06:40

Volker%20Barth's gravatar image

Volker Barth
30.0k294447654
accept rate: 32%

edited 24 Jun '14, 06:43

Hm, using a numbered list within a bullet list seems tricky with Markdown, methinks - the last three points should have been numbered and indented... (and the preview has showed them accordingly - WYGIalmostWYS)

(24 Jun '14, 06:44) Volker Barth
Replies hidden

> Markdown

You kids today, you have it easy! When I was your age we didn't have Markdown, we had to use HTML! :)

(24 Jun '14, 07:14) Breck Carter
1

Man, I'd totally overestimated your real age, Breck - I'd thought typewriters were the tools of choice those (ancient) days...

Volker will shut up now:)

(24 Jun '14, 08:55) Volker Barth

alt text

(24 Jun '14, 15:38) Breck Carter

An 029... that WAS a modern keypunch.

(30 Jun '14, 09:54) Bill Aumen
1

Proper computers have paper tape drives! The Elliott 903B had a servo-motor controlled clamp that held the tape between two metal rollers. If you turned it on and off fast enough you could generate 'musical' tones which were handy for enhancing the endless car chase / space war games we used to write for it. Multi-media gaming in the '70s.

The only problem was that the maintenance engineer complained that he had to replace more clamps on our machine than all the other 903s he serviced put together.

(02 Jul '14, 09:10) Justin Willey
Replies hidden

@Breck, @Bill and @Justin: Thank you all for making me feel young:)

Inevitably, my kids will correct that feeling soon...

(02 Jul '14, 09:22) Volker Barth

Watch the TV listings for "Halt and Catch Fire" when it comes your way... although it's set in the 1980s so it might be too modern for your tastes.

(02 Jul '14, 14:04) Breck Carter

The very thing - though later we got fancy and had a VDU (which saved a lot of paper as you didn't have to reprint the map every time the photon torpedo moved a square closer to the enemy).

Also missing from the picture is the 1mx1mx1m wooden bin that the paper tape all ended up in and then had to be untangled and wound up again:)

(02 Jul '14, 17:59) Justin Willey
showing 3 of 10 show all flat view

> Is there an accepted upgrade process for database files to migrate them from 12 to 16?

First, read about the behavior changes and make the necessary changes to your application.

What's new in version 12.0.1 » SQL Anywhere behavior changes if you are using 12.0.0.

What's new in version 16.0 » SQL Anywhere behavior changes

Make sure you apply EBF 1915 because anything earlier is affected by the Heartbleed bug, then read the section "16.0.0 Behavior Changes and Critical Bug Fixes" in the read-me file that comes with EBF 1915...

SQL Anywhere Bug Fix Readme for Version 16.0.0, build 1915.htm

Second, consider doing an "in-place" upgrade via dbunload. Here is a sample batch file...

REM If you're a command-line kinda guy, here is my template Windows 
REM batch file run_dbunload_upgrade_v12_to_v16.bat. It does the 
REM unload-reload-all-in-one-dbunload-step, then starts dbsrv16 
REM and dbisql so you can make sure the new database is up and running.

REM Be sure to stop *all* database engines on the computer you're using, 
REM before starting the upgrade.

REM dbunload...
REM -an ...  where to put new database
REM -ap ...  new database page size
REM -c ...   old database startup connection string
REM -o ...   where to put unload console display text file
REM -v       verbose mode for console display

PAUSE MAKE SURE YOU DO NOT HAVE ANY ENGINES RUNNING.

"%SQLANY16%\bin64\dbunload.exe"^
  -an "C:\$ blogs and websites\blog SQLAnywhere\20130691 PENDING Building a Link Checker Inside SQL Anywhere\testing\catalog.db"^
  -ap 4096^
  -c "DBF=C:\$ blogs and websites\blog SQLAnywhere\20120319 Tales From The Doc Face\catalog.db;UID=dba;PWD=sql"^
  -o "C:\$ blogs and websites\blog SQLAnywhere\20130691 PENDING Building a Link Checker Inside SQL Anywhere\testing\dbunload_log_demo.txt"^
  -v

PAUSE dbunload/reload done...

REM dbsrv16...
REM -c ...     initial RAM cache size
REM -o ...     where to put server console display text file
REM -os ...    when to rename and restart server console display text file
REM demo16.db  new database to start

"%SQLANY16%\bin64\dbspawn.exe"^
  -f "%SQLANY16%\bin64\dbsrv16.exe"^
  -o "C:\$ blogs and websites\blog SQLAnywhere\20130691 PENDING Building a Link Checker Inside SQL Anywhere\testing\dbsrv16_log_demo16.txt"^
  "C:\$ blogs and websites\blog SQLAnywhere\20130691 PENDING Building a Link Checker Inside SQL Anywhere\testing\catalog.db"

PAUSE dbsrv16 started...

REM dbisql...
REM -c ...  new database connection string

"%SQLANY16%\bin64\dbisql.com"^
  -c "ENG=catalog;DBN=catalog;UID=dba;PWD=sql" 

PAUSE All done...
permanent link

answered 24 Jun '14, 07:10

Breck%20Carter's gravatar image

Breck Carter
27.4k424585836
accept rate: 21%

edited 24 Jun '14, 07:11

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:

×409
×197
×53
×20
×14

question asked: 24 Jun '14, 05:16

question was seen: 1,653 times

last updated: 02 Jul '14, 17:59