What's New in SQL Anywhere Studio
Upgrading Software and Databases
Upgrading Adaptive Server Anywhere
This section describes how to use unload and reload your database to upgrade the file format of a database.
If you are upgrading the file format for a database that is involved in SQL Remote replication or that is a remote database in a MobiLink installation, and if you use the utility, you must be sure to use the -ar or -an option. The option ensures that the transaction log offsets for the new database are set to match those of the old database. In Sybase Central, following the instructions below ensures that the option is used.
When unloading and reloading a database that has proxy tables, you must create an external login to map the local user to the remote user, even if the user has the same password on both the local and remote databases. If you do not have an external login, the reload may fail because you cannot connect to the remote server.
| Caution Unloading and reloading a large database can be both time consuming and require a large amount of disk space. The process requires access to disk space approximately twice the size of your database to hold the unloaded data and the new database file. |
Users of version 5 who wish to unload and reload databases involved in replication should follow the instructions in Unloading and reloading a database participating in replication, rather than those in this section.
To upgrade the database file format (Sybase Central)
Carry out the standard precautions for upgrading software.
Start a version 9 database server on the database you wish to upgrade. For example:
dbeng9 mydatabase.db
Start Sybase Central.
From the Start menu, choose Programs
Connect to the database you wish to upgrade.
When you connect to a version 5.x or version 6.x database, a dialog appears asking you whether you want to use the Unload Database wizard to unload and reload your database. Click Yes. Once the wizard completes, the connection to the database is closed.
Right-click the database and choose Unload Database from the popup menu.
The Unload Database wizard appears.
Read the text on the first page of the wizard and click Next.
Choose to unload the database to which you are connected. Click Next.
Specify the path and location for the unloaded database command file. The command file has the .SQL extension and is necessary to rebuild your database file from the files you unload.
Unless space restrictions require otherwise, this location should be a directory on the same machine as the database server. Choose to unload structure and data.
Click Next.
Specify the number of levels of view dependency.
Specifying levels of view dependency allows you to recreate views based upon other views. For example, if you have one view based upon existing tables, you would enter the number 1 in this field. View one is independent and can be recreated from the tables alone. If, however, you have a second view based upon the first view, you would enter number 2 in this field. View 2 is dependent on view 1, and cannot be created until view 1 is created first.
Click Next.
Specify whether the unloaded data is to be saved on the local machine or the server machine and select a location for the data.
Specify whether you want to order the data. Exporting the data in an ordered format means that the data will be reloaded in an ordered format. This is useful if you want to improve performance of your database, or bypass a corrupted index.
Click Next.
Check the box to create and reload into a new database file. Specify a new filename for the database.
You can encrypt the database file if you wish. You need the encryption key each time you want to start the database.
For more information about database file encryption, see Encrypting a database.
Click Finish to start the process. You will need to examine the new database to confirm that the upgrade is completed properly.
To upgrade the database file format (Command line)
Carry out the standard precautions for upgrading software.
Ensure that you have exclusive access to the database to be unloaded and reloaded. No other users can be connected.
Ensure that the version 9 utilities are ahead of other utilities in your system path.
See Using the utilities.
Execute the Unload [dbunload] utility using the -ar option to create a new database.
dbunload -ar -c "connection-string"
The connection-string must connect to the database to be unloaded with DBA authority. This command replaces the existing database with an upgraded database. To use the -ar option, you must connect to a personal server, or to a network server on the same machine as the Unload [dbunload] utility.
For information on other Unload [dbunload] utility options, see Unloading a database using the dbunload command-line utility.
Shut down the database and archive the transaction log before using the reloaded database.
If you want to change the characteristics of the database during unload and reload (for example, change a case-sensitive database to a case-insensitive database), the procedure is more involved. For more information, see Rebuilding databases.