The rebuild process for version 9 and earlier databases

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

Note

The page size for a database can be (in bytes) 2048, 4096, 8192, 16384, or 32768, with the default being the page size of the original database.

For information about upgrading Windows Mobile databases, see Database rebuilding on Windows Mobile.

Note

SQL Anywhere 9.0.2 for Mac OS X was supported on PPC, while SQL Anywhere 10.0.0 and later for Mac OS X are supported on Intel. If you have a version 9.0.2 or earlier database on Mac OS X, you have two options for unloading the database:

Unload the database using the version 9.0.2 software.
Copy the database to a different platform where SQL Anywhere 16 is installed, and then unload the database using the version 16 software.

Once the database is unloaded, you can perform the reload on Mac OS X using the version 16 software.

Caution

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

Upgrade restrictions

There are some restrictions to note when rebuilding version 9 or earlier databases using the version 16 tools:

Disconnect the database from any earlier versions of the database server, and shut down any earlier database servers running on the computer. You must also shut down any version 16 database servers that are running on the computer. If dbunload cannot proceed because it detects any of these cases, it issues an error and fails.
Do not include the ENG, START, or LINKS connection parameters in the dbunload connection string for the old database (specified in the -c option). If you specify these parameters, they are ignored and a warning appears. In the Sybase Central Connect window, do not enter values in the Server name or Start line fields.
Run dbunload on a computer with direct file system access to the old database (dbunload must be able to connect to the database using shared memory).
Do not run a database server named dbunload_support_engine on the computer where the rebuild is taking place.
Do not unload a version 9 or earlier database when the database file requires recovery. When you use the Unload utility (dbunload) on a database file that requires recovery, a message is returned indicating that the database could not be started. Use a version 9 database server to start the database and then stop the database before retrying dbunload. To unload a version 9 or later database, you must be able to start the database in read-only mode. See -r database option.

Special considerations

Password case sensitivity In newly-created SQL Anywhere 16 databases, all passwords are case sensitive, regardless of the case-sensitivity of the database. The default DBA password for new databases is sql.
When you rebuild an existing database, SQL Anywhere determines the case sensitivity of the password as follows:
- If the password was originally entered in a case-insensitive database, the password remains case-insensitive.
- If the password was originally entered in a case-sensitive database, uppercase and mixed case passwords remain case sensitive. However, if the password was entered in all lowercase, then the password becomes case-insensitive.
- Changes to both existing passwords and new passwords are case sensitive.
Page sizes The default database page size for SQL Anywhere 16 databases is 4096 bytes. The supported page sizes in version 16 are 2048 bytes, 4096 bytes, 8192 bytes, 16384 bytes, and 32768 bytes. If your old database uses an unsupported page size, the new database has a page size of 4096 bytes by default. You can use the dbinit -p option or the dbunload -ap option to specify a different page size. See Initialization utility (dbinit) and Unload utility (dbunload).
Collations In version 9 and earlier, SQL Anywhere supported one collation used with CHAR data types. This collation used the SQL Anywhere Collation Algorithm (SACA). In version 10 and later, SQL Anywhere supports two collation algorithms, SACA and UCA (Unicode Collation Algorithm). Unless you specify a new or different collation for the rebuilt database, the SACA collation from the old database is unloaded and reused in the rebuilt database.
If you are rebuilding a database with a custom collation, the collation is preserved only if you rebuild in a single step (internal unload). If you choose to unload the database, and then load the schema and data into a database that you create, then you must use one of the supplied collations. See Alternate collations.
Database file size 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.

Known issues

If the rebuild process fails when you run dbunload or the Unload Database Wizard, you can use the following steps to help diagnose the reason for the failure.

Create a new, empty version 16 database.
dbinit -dba DBA,sql test.db

Apply the reload.sql file to the empty database.

dbisql -c "DBF=test.db;UID=DBA;pwd=sql" reload.sql

Change the reload.sql file or the original database based on the messages you receive when applying the reload.sql file to the new database.

The following table lists issues that are known to cause a rebuild to fail, as well as their solutions.

Known problem

Solution

A DECLARE LOCAL TEMPORARY TABLE statement in a procedure or trigger causes a syntax error if the table name is prefixed with an owner name.

Remove the owner name.

If a CREATE TRIGGER statement does not include an owner name for the table on which the trigger is defined, and the table must be qualified with an owner when referenced by the user executing the reload.sql file, the statement fails and an error is returned indicating that the table could not be found.

Prefix the table name with the owner name.

If an object name (such as a table, column, variable, or parameter name) corresponds to a reserved word introduced in a later version of SQL Anywhere (such as NCHAR), then the reload fails. For example:

CREATE PROCEDURE p( )
BEGIN
 DECLARE NCHAR INT;
 SET NCHAR = 1;
END;

Change all references to the reserved word to use a different name. For variable names, prefixing the name with @ is a common convention that prevents naming conflicts.

For a complete list of reserved words, see Reserved words.

If a database is unloaded with a version 9 or earlier copy of dbunload, the reload.sql file can contain calls to the ml_add_property system procedure, but this procedure is not present in a new version 16 database.

Unload the database with the version 16 dbunload utility.

For information about ensuring you are using the correct version of the database utilities, see How to ensure that you are running the correct version of the utilities when you have multiple versions installed.

If you unload a database using a version 9 or earlier version of dbunload, views that use Transact-SQL outer joins (by specifying *= or =*) may not be created properly when they are reloaded.

Add the following line to the reload script:

SET TEMPORARY OPTION tsql_outer_joins='on';

You should later rewrite any views that use Transact-SQL outer joins.

The [NOT] DETERMINISTIC clause is not supported in the CREATE PROCEDURE and ALTER PROCEDURE statements. If the clause is present, the reload fails and a syntax error is returned.

If you are upgrading a database that contains user-defined procedures that include the [NOT] DETERMINISTIC clause, you must remove the clause before you unload and reload the database.

Rebuilding a version 9 or earlier database (Sybase Central)
Rebuilding a version 9 or earlier database using the Unload utility (command line)

Discuss this page in DocCommentXchange.