New to SQL anywhere - db rebuilding question

[Former Member]

Hi there,

I have inherited a small project involving a SQL Anywhere database that I have a question about. I have a database file that was created years ago and is hosted on a network server that a small number of client applications access. Over the years, the version of the db engine has been upgraded (currently running on SQL Anywhere 16), but the database file itself has not. I am currently archiving and cleaning out a bunch of data, and I wanted to run a rebuild on the database. During my crash course of SQL Anywhere, I read that the only way to rebuild the db is to unload and then reload it, which I did using Sybase Central. However, it appears that this also upgrades the db version, which makes a few of the client applications unhappy when they try to connect (these apps are not currently under my control). In corresponding with the person who previously maintained this db, it seems that the db file was originally created on version 12 of SQL anywhere, though I don't have an exact version number. Is it possible to reload the database into an 'old version' (for lack of a better term) using the existing engine, or do I need to try and find a copy of 12 to do this with?

[jose_at_sap]

Hi Matt,

You can open a v12 database with the v16 SQLA database engine, so need to unload/reload.

Note: the situation where you'd want unload/reload is when you want to change the file format of the database so that you can take advantage of the new features.

[Former Member]

Hi Jose,

I have been, and I've been able to manage some of the data this way. I was hoping to try and rebuild the file itself though so that the size would shrink. It would hopefully make our weekly backups take a bit less time. What I tried to do was follow the Unload Database wizard and had it unload/reload into a newly created database. The new database has all the right data and the file size itself is smaller, but I see that it added new features (I'm not sure if that's the right terminology. In the newly-created db for example, I see things like 'Sequence Generators', and 'Spacial Reference Systems' in the Sybase Central browsing tree, which are not present in the old version of the db).

I guess what I'm asking basically is, can the v16 SQLA db engine create or reload into a new database that has the old legacy version 12 features? And if that's not the case, would any minor version of the v12 engine work (I only know the file was originally created with v12, not the minor version). For whatever reason, the client applications are expecting a v12 database. I could go without rebuilding to shrink the db if necessary, I was just hoping to do so and save some time/storage space for our backups since I archived and purged a few GB of data out of the file.

[jose_at_sap]

Hi Matt,

Thanks for the extra details.  The v16 reload will not create a script with the "old legacy" v12 features.  What I suggest is the following:

• You'll need to get your hands on the v12 software.  I don't think the exact build number will matter here, so if you don't have that version, you can download the Developer Edition from: http://scn.sap.com/docs/DOC-31795.
• When you unload the database with v16, you'll get the SQL script for the schema.  You'll have to manually edit that file to remove everything that's not related to your schema (e.g. remove references to sequences and SRS).  The SQL script will work even if you only have statements to create objects.
• Once you've done that, just create a new v12 database and reload your SQL script.  You'll now have a v12 database.

Hopefully this makes sense.  Please let me know if you have any questions.

[Former Member]

That does make sense, thank you very much!

[reimer_pods]

IMHO the quickest and easiest way would be using V12 to do the rebuild. In Sybase Central connect to the target database, select "Unload Database ..." from the database's context menu to start the wizard and select "Unload and reload into a new database" as unload method.

This will generate a new database without the need to edit a reload skript.