Rebuilding version 9 and earlier databases for version 12

This section describes how to unload and reload your database into a new version 12 database.

For information about upgrading Windows Mobile databases, see Rebuilding databases on Windows Mobile.

Rebuilding Mac OS X databases

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 12 is installed, and then unload the database using the version 12 software.

Once the database is unloaded, you can perform the reload on Mac OS X using the version 12 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 approximately 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 12 tools:

You must disconnect the database from any earlier versions of the database server, and you must shut down any earlier database servers running on the computer. You must also shut down any version 12 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.
You must 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).
You cannot run a database server named dbunload_support_engine on the computer where the rebuild is taking place.
You cannot unload a version 9 or earlier database when the database file requires recovery. Recovery might be required, for example, if the database file was created using the Backup utility (dbbackup) or a BACKUP DATABASE statement. 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.

Special considerations

Password case sensitivity In newly-created SQL Anywhere 12 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 12 databases is 4096 bytes. The supported page sizes in version 12 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 Supported and 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.

Rebuild a version 9 or earlier database from Sybase Central

You can use the Unload Database Wizard to rebuild an old database. Use the wizard to unload into a reload file and data files, unload and reload into a new database, or unload and reload into an existing database. It is strongly recommended that you back up your database before rebuilding it.

Sybase Central upgrade notes

The database file must be located on the same computer as the SQL Anywhere 12 installation.
You cannot unload a subset of tables from a database. You must use the dbunload utility to do this.
If the Unload Database Wizard determines that the database file is already running, then the database is stopped before the unload proceeds.

To rebuild a database (Sybase Central)

Carry out the standard precautions for upgrading software. See Important upgrade precautions.
If possible, defragment the drive where the new database will be stored because a fragmented drive can decrease database performance.
Ensure that you have exclusive access to the database to be unloaded and reloaded. No other users can be connected.
Choose Start » Programs » SQL Anywhere 12 » Administration Tools » Sybase Central.
Choose Tools » SQL Anywhere 12 » Unload Database.
Read the introductory page of the Unload Database Wizard, and click Next.
Select Unload A Database Running On An Earlier Version Of The Server, Or A Database That Is Not Running. Enter the connection information for the database. Click Next.
Select Unload And Reload Into A New Database. Click Next.
Specify a new file name for the database. Click Next.

You can specify the page size for the new database. In version 12, the default (and recommended) 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. See Encrypting and decrypting a database.
Choose to unload structure and data. Click Next.
Specify whether you want to connect to the new database when the rebuild is complete.
Click Finish. Examine the new database to confirm that the rebuild completed properly.

Rebuild a version 9 or earlier database using the Unload utility

You can use the Unload utility (dbunload) -an or -ar option to rebuild an old database:

The -an option is recommended because it creates a new database leaving the original database intact.
The -ar option replaces your old database with a new version 12 database.

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.

To rebuild a database (command line)

Carry out the standard precautions for upgrading software. See Important upgrade precautions.
Ensure that the version 12 utilities are ahead of other utilities in your system path. See Using the utilities.
Shut down all SQL Anywhere and Adaptive Server Anywhere database servers because the version 12 dbunload utility cannot be used against a database that is running on a previous version of the database server. For example:
dbstop -c "DBF=mydb.db;UID=DBA;PWD=sql"
If possible, defragment the drive where the new database will be stored because a fragmented drive can decrease database performance.

Back up the database. For example:

dbbackup -c "DBF=mydb.db;UID=DBA;PWD=sql" old-db-backup-dir

See Backup quick start.

Run the Unload utility (dbunload) using the -an or -ar option to create a new database.
dbunload -c "connection-string" -an database-filename
For example:
dbunload -c "DBF=mydb.db;UID=DBA;PWD=sql" -an mydb12.db
The database user specified in the connection-string must connect to the database to be unloaded with DBA authority. This command creates a new database (by specifying -an). If you specify the -ar option, the existing database is replaced with a rebuilt database. 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 the other options available for the Unload utility (dbunload), see Unload utility (dbunload).

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.

To diagnose a rebuild failure

Run dbunload -n on your old database. Data is not unloaded because -n is specified.
dbunload -c "connection-string" -n directory-name
Create a new, empty version 12 database.
dbinit test.db

Apply the reload.sql file to the empty database.

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

Make changes to 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 12 database.

Unload the database with the version 12 dbunload utility.

For information about ensuring you are using the correct version of the database utilities, see Using the utilities.

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.

Discuss this page in DocCommentXchange.