Rebuilding a database (file) to a new SQL Anywhere version

[huber1]

Hi

I would like to upgrade an SQL Anywhere 11 database to version 12. I started the version 11 database file with a version 12 database server. I then used Sybase Central to unload the database. I used Tools > SQL Anywhere 12 > Unload Database... I then followed the screen messages, starting with the first choice: Unload a database running on a curent version of the server. Then I chose Unload and reload into a new database. I saved it into a new database file (with Unload structure and data etc. set). As a result I got a database file and the log file. I assume this is now a real version 12 database and log file?

Best regards, Robert

PS: By the way, how can I verify the db file version it is/was built?

[Former Member]

Yes this should be a version 12 database.

You can verify that by looking at the SYS.SYSHISTORY system view.

[huber1]

Thank you. As the table sys.syshistory doesn't have a database (file) column, it can't show the database file version, can it?

Best regards, Robert

[Chris_Kleisath]

If you look in the SYSHISTORY table, you should see a line with operation='INIT'. The value of the version column will tell you which server version executed the operation. As long as the version is 12.0.0 or higher, then you have a version 12 database file.

[Chris_Kleisath]

See this page on DCX for more info on the SYSHISTORY view.

[huber1]

Thanks for detailing it. I am still a bit uncertain about the UPGRADE info. Concerning a database file, Sybase differentiates between upgrade and rebuild (unload/reload) in their docu, saying only rebuild gives access to all new features and speed enhancements. In what sense is the UPGRADE info in the sys.syshistory table meant? Does it mean the file is "only" upgraded, not rebuilt or does it mean it was either upgraded or rebuilt? I am of course also wondering why upgrade exists. Shouldn't we rebuild a database file in any case for a new version, as we would like to get all new features, not only some of them?

[VolkerBarth]

I'll try to answer these comment questions in my answer below.

[huber1]

Glenn

I am trying to leave a comment on your article http://iablog.sybase.com/paulley/2010/04/keywords-and-upgrades/

but I always get get an error message: Invalid Data: Please go back and try again.

Please help, best regards, Robert

[VolkerBarth]

Robert, I can confirm the behaviour.

I guess that blog comments are disabled for older blog articles - but don't know the valid timespan. As said, I guess...

[VolkerBarth]

I would generally recommend to rebuild a database for a new version if that is possible. It's the only way to get access to all new features of the new version.

However, it makes it necessary to put down the database for a certain time, and rebuilding a huge database will need some time. Therefore that may be not easily doable for a database used 24x7. If you can't afford the time needed for a complete reload, you can alternatively

1. just run the database unchanged on a new database engine (as long as that combination is supported - e.g. a v12 engine can run v10-v12 databases, but not older ones, whereas a v9 engine can run v5-v9 databases) - or

2. upgrade the database to the current version. That will make some of the new features available but not all - usually not those ones that require a different physical data storage.

As you can see, these alternatives have different trade-offs to give you several choices.

---

As to SYSHISTORY, the "INIT row" will name the version the database will be built with - for a reloaded database that's the version it was rebuilt with.

The "UPDGRADE row" will tell to which version(s) the database was upgraded to.

If you are not sure whether your database was successfully rebuilt with v12 (though the Sybase Central Wizard should tell you a failure), the "INIT row" should assure you that your version is build with v12.

[huber1]

The picture is getting clearer and clearer - thanks! So the UPGRADE row in sys.syshistory table is really (only) an upgraded database file, whereas the INIT row shows a rebuilt database file. Is my assumption then correct, as there is only one INIT row in the syshistory table (the first one), after rebuilding a database file, the syshistory is reset and starts with the INIT row showing the rebuild version?

[MarkCulp]

A database will only ever have one INIT root - the "INIT" comes from "dbinit" which is the tool to "initialize" (aka create) a new database. When you "rebuild" a database, a new database is "initialized" and then all of the data from your original (old) database is unloaded and reloaded into the new database.

So the INIT row in the syshistory table shows you the version of the software that was used to created the database and other other rows (START, UPGRADE, etc) show you how the database has been used and what modifications (UPGRADEs) have been made to it since it was created.