Purpose

This page is intended to explain how to rebuild a SQL Anywhere database using the command-line utility dbunload.

Overview

In order to run a database created with SQL Anywhere 10 or earlier on version 17, it needs to be rebuilt. A database may also need to be rebuilt to correct certain forms of corruption, to reduce fragmentation and unused space, or to change initialization parameters such as page size or collation sequence. For a more complete list of reasons, see Rebuilding a SQL Anywhere Database.

Assumptions

For the purposes of this document, it is assumed that you will be rebuilding the demo.db database that ships with the SQL Anywhere software. The default user name for this database is DBA, and the password is sql. It is assumed that the databse will be rebuilt either replacing the existing file or creating a new one called new_demo.db, with the same user name and password.

SAP does not recommend using the default password, nor overly simple passwords, in production databases.

Creating a new database

This step can be skipped if you intend to automatically create a new database using dbunload (-ar or -an switches). Create a new database if you need to change any of the following:

• • Collation sequence
  • Database encryption
  • Table encryption
  • Treatment of trailing blanks in comparisons
  • Accent sensitivity or insensitivity
  • Case sensitivity or insensitivity

Other reasons for building a new database separately might include the need to do the rebuild on a separate machine, or errors that occur while using the -ar or -an switches.

 dbinit -dba dba,sql -mpl 3 -p 8192 new_demo.db

Type dbinit /? or go to the SQL Anywhere help file for additional initialization options.

Note that the -dba and -mpl switches were added in SQL Anywhere 17. The -dba switch is mandatory for setting the username and password of the default DBA user.

Rebuilding the database

1. Shut the database down if it is currently running.
2. Make an safe copy of the database and log file. This is done by doing a file copy of the database and log file and storing the copies in a safe location.
3. Start the database using the either the personal or network server, making sure that there are no other connections to it. You can use the -gm option to limit connections to the network server. This ensures that no other users are connected to the database when it is being rebuilt.
        dbsrv10 -gm 2 -cl 80P demo.db
     dbeng10 -cl 80P demo.db
   The -gm option specifies two users. It is only essential to set -gm to 2 if you are using the -ar, -an, or -ac options with dbunload. If you are unloading into a reload.sql file, set -gm to 1. -cl 80P allocates 80 percent of the available physical RAM to the server, to improve efficiency.
4. Unload the database using the dbunload utility. This utility supports several options:
   • Rebuild the database and replace the existing one (-ar [ directory ] switch). If the database is involved in replication or synchronization, specify the directory for off-line logs. Example:
          dbunload -c “UID=DBA;PWD=sql;DBF=c:\demo.db” -ar
   • Unload the data into an automatically created new database (-an switch). The new database will have all the same initialization settings as the old one, but a different name. Example:
          dbunload -c “UID=DBA;PWD=sql;DBF=c:\demo.db” -an c:\new_demo.db
   • Unload the data into an existing database (-ac switch). This is useful for changing initialization settings of the database, such as page size or collation sequence. Example:
          dbunload -c “UID=DBA;PWD=sql;DBF=c:\demo.db” -an c:\new_demo.db
   • Unload the database into a SQL file (default reload.sql) and .dat files (default). You must specify a directory where the .dat files should be stored. The reload.sql file is stored in the directory where you execute the statement. Example:
          dbunload -c “UID=DBA;PWD=sql;DBF=c:\demo.db” c:\unload.
     Type "dbunload /?" to get a list of additional options.

Reload the schema and data

This step is only necessary if you did not use -ac, -an or -ar during the Rebuilding the database phase. You may need to reload the schema and data as a separate step if you need to perform the rebuild in a different location than the database, or if you receive errors when using dbunload with -ac.

• Start dbisql.
  Execute the following command: READ [ path\ ] reload.sql

or

• Run the following command from a command line or batch file:
  dbisql -c "uid=DBA;pwd=SQL;dbf=[ path\ ]new_demo.db

Finalizing the rebuild

Shut down

Archive log

Restart

Related Content

Related Documents

Rebuilding a SQL Anywhere Database

How to upgrade to the latest version of SQL Anywhere

Database rebuilds

Related SAP Notes/KBAs