Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere 11 - Changes and Upgrading » Upgrading to SQL Anywhere 11 » Upgrading SQL Anywhere

 

Rebuilding version 10 and later databases

Rebuilding a database consists of unloading and reloading the database to upgrade its file format. When you upgrade the file format, it changes the format used to store and access data on disk, letting you use all the new features and performance enhancements in the latest version of the software.

Caution

Unloading and reloading a large database can be time consuming and can require a large amount of disk space. The process may require disk space approximately twice the size of your database to hold the unloaded data and the new database file.

If you are rebuilding a database that is involved in SQL Remote replication or that is a remote database in a MobiLink installation, and if you use the dbunload 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.

When using dbunload with a version 10 or later database, the version of dbunload used must match the version of the database server used to access the database. If an older version of dbunload is used with a newer database server, or vice versa, an error is reported.

Because of index changes in SQL Anywhere, when you rebuild a database by unloading and reloading it, the rebuilt database may be smaller than the original database. This decrease in database size does not indicate a problem or a loss of data.

Note

It is recommended that you back up your database before you rebuild it.

Reloading tables with autoincrement columns

You can retain the next available value for autoincrement columns in the rebuilt database by specifying the dbunload -l option. This option adds calls to the sa_reset_identity system procedure to the generated reload.sql script for each table that contains an autoincrement value, preserving the current value of SYSTABCOL.max_identity.

Rebuilding the database
To rebuild a database (Sybase Central)
  1. Carry out the standard precautions for upgrading software. See Important upgrade precautions.

  2. Choose Start » Programs » SQL Anywhere 11 » Sybase Central.

  3. Start a version 11 database server running the database you want to upgrade, and then connect to the database from Sybase Central.

  4. Choose Tools » SQL Anywhere 11 » Unload Database.

  5. Read the text on the first page of the Unload Database Wizard and then click Next.

  6. Select Unload A Database Running On A Current Version Of The Server, and then select the database from the list. Click Next.

  7. Choose to unload and reload into a new database. Click Next.

  8. Specify a new file name for the database.

  9. You can also specify the page size for the new database, but the page size you specify cannot be larger than the database server page size. The default page size is 4096 bytes. You can encrypt the database file if you want. If you choose strong encryption, you need the encryption key each time you want to start the database. Click Next.

    For more information about database file encryption, see Encrypting and decrypting a database.

  10. Choose Unload Structure And Data. You can also select any other options you want for your database. Click Next.

  11. Choose Unload All Database Objects. Click Next.

  12. Specify whether you want to connect to the new database when the unload/reload is complete.

  13. Click Finish to start the process. You should examine the new database to confirm that the rebuild completed properly.

    For more information about using the Unload Database Wizard, see Export data with the Unload Database Wizard.

To rebuild a database (command line)
  1. Carry out the standard precautions for upgrading software. See Important upgrade precautions.

  2. Ensure that you have exclusive access to the database to be upgraded and ensure that the path of the version 11 utilities is ahead of the path of the other utilities in your system path. See Using the utilities.

  3. Run the Unload utility (dbunload) using the -an option to create a new database.

    dbunload -c "connection-string" -an new-db-file

    The database user specified in the connection-string must have DBA authority on the database to be rebuilt.

    This command creates a new database. If you want to replace the existing database with an upgraded database, use the -ar option instead of -an. To use the -ar option, you must connect to a personal database server, or to a network database server on the same computer as the Unload utility (dbunload).

    For information about other Unload utility (dbunload) options, see Unload utility (dbunload).

  4. 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. See Rebuilding databases.