iAnywhere.com - Rebuilding version 9 and earlier databases for version 10.0.0

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

SQL Anywhere 10.0.1 » SQL Anywhere 10 - Changes and Upgrading » Upgrading to SQL Anywhere 10 » Upgrading SQL Anywhere

Rebuilding version 9 and earlier databases for version 10.0.0

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

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

Rebuilding Mac OS X databases

You must use SQL Anywhere 9.0.2 or earlier software to unload a version 9.0.2 or earlier database on Mac OS X. If you want to unload a Mac OS X database using SQL Anywhere 10 software, you must unload the database on a different platform. The reload can be performed on Mac OS X using the version 10 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.0.2 or earlier databases using the 10.0.0 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 10 database servers that are running on the computer. If dbunload 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 dialog, do not enter values in the Server Name and Start Line fields.
You must run dbunload on the computer where the old database is located (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.
If you are using NetWare, you must rebuild the database on a Windows or Unix computer. You can then connect to the new version 10.0.0 database on a database server running on the NetWare computer.

Special considerations

Password case sensitivity In newly-created SQL Anywhere 10 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 10 databases has been changed to 4096 bytes from 2048 bytes. The supported page sizes in version 10 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 to specify a different page size. See Initialization utility (dbinit).
Collations Unless you specify a new or different collation for the rebuilt database, the 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 if you rebuild in a single step. 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.

Rebuilding a version 9 or earlier database from Sybase Central

You can use the Unload Database wizard to rebuild an old database. The wizard lets you choose whether you want to unloading 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 10 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 will be stopped before the unload proceeds.

To upgrade the database file format (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.

For an alternative procedure to defragmenting, see Rebuilding a version 9 or earlier database without defragmenting.
Back up the database. For example:
```
dbbackup -c "DBF=mydb.db;UID=DBA;PWD=sql" old-db-backup-dir
```
Note

Make sure you use the correct version of dbbackup to back up your database. See Using the utilities.
Ensure that you have exclusive access to the database to be unloaded and reloaded. No other users can be connected.
Start Sybase Central.

From the Start menu, choose Programs > SQL Anywhere 10 > Sybase Central.

The Sybase Central dialog listing SQL Anywhere tasks appears. (If this dialog does not appear, use the alternative method described below using the Tools menu.)
Click Prepare a Version 9 or Earlier Database for SQL Anywhere 10. Or, from the Tools menu, choose SQL Anywhere 10 > Unload Database.

The Unload Database wizard appears.
Read the introductory page of the wizard, and then click Next.
Select Unload a Non-running Database and 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 10, the default (and recommended) page size is 4096 bytes.

You can encrypt the database file if you want. You need the encryption key each time you want to start the database. See Encrypting a database.
Choose to unload structure and data. Click Next.
Specify whether you want to connect to the new database when the unload/reload is complete.
Click Finish to start the process. Examine the new database to confirm that the upgrade completed properly.

Rebuilding 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.
The -ar option replaces your old database with a new version 10 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 upgrade the database file format (command line)

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.

For an alternative procedure to defragmenting, see Rebuilding a version 9 or earlier database without defragmenting.
Back up the database. For example:
```
dbbackup -c "DBF=mydb.db;UID=DBA;PWD=sql" old-db-backup-dir
```
Note

Make sure you use the correct version of dbbackup to back up your database. See Using the utilities.
Ensure that you have exclusive access to the database to be unloaded and reloaded. No other users can be connected.
Ensure that the version 10 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 10 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"
```
Execute 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" -o dbunload_log_mydb.txt -an mydb10.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 an upgraded database. 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 on other Unload utility (dbunload) options, see Unload utility (dbunload).
Shut down the database and back up the transaction log before using the reloaded database.

Rebuilding a version 9 or earlier database without defragmenting

As an alternative to defragmenting the drive where the new database will be located, you can use the following procedure.

To rebuild the database without defragmenting

Shut down all SQL Anywhere and Adaptive Server Anywhere database servers because the version 10 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"
```
Ensure that the version 10 utilities are ahead of other utilities in your system path. See Using the utilities.

Back up the database. For example:

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

Use dbunload to create a reload.sql file. For example:
```
dbunload -c "connection-string" directory-name
```
Create a new database file using the Initialization utility (dbinit) or the Create Database wizard in Sybase Central. For example:
```
dbinit new.db
```
Connect to the new database from Interactive SQL
```
dbisql -c "DBF=new.db;UID=DBA;pwd=sql"
```
Execute the following statement to add disk space to the database that can be used when loading the data into the database. Add enough space to accommodate the size of your database file. This disk space should be contiguous, which can improve the performance of the reload. For example:
```
ALTER DBSPACE system
ADD 200MB
```
Apply the reload.sql file to the database from Interactive SQL.
```
dbisql -c "DBF=new.db;UID=DBA;pwd=sql" reload.sql
```

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.

dbunload -c "connection-string" -n directory-name

Create a new, empty version 10 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 with a `Table 'table-name' not found` error.	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 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 10 database.	Unload the database with the version 10 dbunload utility. For information about ensuring you are using the correct verison 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.