SQL Anywhere 16/12 file formats

[Former Member]

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

[Breck_Carter]

> 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 🙂

[Former Member]

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.

[VolkerBarth · Accepted solution]

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...

[VolkerBarth · Accepted solution]

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)

[Breck_Carter · Accepted solution]

> Markdown

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

[VolkerBarth · Accepted solution]

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

[Breck_Carter · Accepted solution]

[Former Member · Accepted solution]

An 029... that WAS a modern keypunch.

[justin_willey · Accepted solution]

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.

[VolkerBarth · Accepted solution]

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

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

[Breck_Carter · Accepted solution]

http://www.tnmoc.org/news/notes-museum/elliott-903-takes-step-closer-restoration

[Breck_Carter · Accepted solution]

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.

[justin_willey · Accepted solution]

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

[Breck_Carter]

> 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...