Rebuilding version 10.0.0 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 of 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.0.0 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

Use the following procedure to retain the next available value for autoincrement columns in the rebuilt database.

To reload tables with autoincrement columns

  1. In Interactive SQL, connect to the database as a user with DBA authority and execute the following statements:

    CHECKPOINT;
UNLOAD 
    SELECT 'CALL dbo.sa_reset_identity(''' || table_name || ''',''' 
        || user_name || ''',' || max_identity || ');'
    FROM SYS.SYSTABLE t
        JOIN SYS.SYSCOLUMN c ON (t.table_id = c.table_id)
        JOIN SYS.SYSUSERPERM u ON (t.creator = u.user_id)
    WHERE user_name NOT IN ('SYS','dbo')
    AND "default" LIKE '%autoincrement%'
TO 'c:\reset.sql' FORMAT TEXT ESCAPES OFF QUOTES OFF;

  2. Use the Unload Database Wizard or dbunload utility to rebuild the database.

  3. Execute the following statement:

    READ c:\reset.sql;
Rebuilding the database

To rebuild a database (Sybase Central)

  1. Carry out the standard precautions for upgrading software. See Important upgrade precautions.

  2. From the Start menu, choose 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. From the Tools menu, choose 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 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 Using 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 connect to the database to be unloaded with DBA authority.

    This 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 server, or to a network 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.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0