Upgrading SQL Anywhere

Before using existing applications with this version of the software, be sure to review the list of behavior changes to determine whether your application is affected. See SQL Anywhere 11 - Changes and Upgrading.

Note

Adaptive Server Anywhere has been renamed SQL Anywhere. In this chapter, SQL Anywhere is used to refer to all versions.

Upgrading version 10.0.0 and later databases

If you are upgrading from version 10.0.0 or later, you can either use the Upgrade utility or rebuild your database. Upgrading or rebuilding is an optional step because the version 11 software can be used with a version 10 database. However, if you want to take advantage of all of the new features in version 11, you must rebuild your database. See Upgrading version 10.0.0 and later databases and Rebuilding version 10.0.0 and later databases.

Databases with materialized views

It is recommended that you refresh the materialized views in your database after upgrading your database server, or after rebuilding or upgrading your database to work with an upgraded database server. See Refreshing manual views.

Upgrading version 9 and earlier databases

If you are upgrading to SQL Anywhere version 11.0.0 from version 9 or earlier, you must rebuild the database, which consists of unloading the old database, and reloading it into a new version 11 database. Attempting to start version 9 or earlier databases results in an error on database startup. There are four approaches for rebuilding existing databases:

  • Use the version 11.0.0 Unload utility (dbunload) with the -an (create a new database) or -ar (replace the old database) option. See Rebuilding a version 9 or earlier database using the Unload utility.
    Note

    The Unload utility (dbunload) has the same file name in all versions of SQL Anywhere. You must make sure you are using the correct version. See Using the utilities.

    As well, 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.

  • Use the Unload Database Wizard in Sybase Central. You can choose to create a new database, replace an existing database with the new database, or unload the database to a file. See Rebuilding a version 9 or earlier database from Sybase Central.
  • Unload the database using the version 11.0.0 Unload utility, and then reload the database using the reload.sql file on the version 11.0.0 database server.

    If you need to make schema changes, this is the recommended way of upgrading. After you make the schema changes, you can initialize a new database, and then apply the reload script to it.

  • Unload the database using an older version of dbunload, and then reload the database using the reload.sql file and the version 11.0.0 database server. You should only use this approach if the other methods fail because deprecated or unsupported database option settings, objects, or SQL syntax could be unloaded into the reload.sql file. You then have to edit the file manually if problems occur during the reload. The internal reload capabilities of version 11.0.0 take care of many of these problems.
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 11 is installed, and then unload the database using the version 11 software.

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

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. For more information, see Rebuilding databases.

Compatibility with existing software
  • SQL Anywhere 11 database servers support connections from client applications using software from version 6.0.0 or later. Version 5 and earlier clients cannot connect to a version 11 database server. When version 9 and earlier clients connect to a version 11 database server, they cannot use the following features:
    • Kerberos logins
    • The embedded SQL NCHAR type
    • Improved support for Unicode data from ODBC, OLE DB, and ADO.NET (for example, describing NCHAR as WCHAR columns)
    • BLOB performance enhancements for Unicode applications using ODBC, and all applications using OLE DB and ADO.NET
    • Enhancements that primarily improve WAN performance, but that also provide some improvements to LAN performance
    Setting SATMP for shared memory connections

    A different search order is used to locate the temporary file in version 9 and earlier than is used in version 11. If you are connecting a version 9 or earlier client to a version 11 database server over shared memory, you must set the SATMP (version 11) and ASTMP (version 9 and earlier) environment variables to specify the location of the temporary file. If you do not set these environment variables, the shared memory connection attempt will fail.

  • Management of old databases and old database servers from the current version of Sybase Central is provided as follows:
    • You can connect to and administer version 9 and later databases running on version 9 and later servers.
    • You can connect to a version 5 or later database running on the same machine as Sybase Central to rebuild the database using the Unload Database Wizard from Sybase Central. The database is stopped before it is unloaded.
    • There is no support for version 7 and earlier databases running on version 7 and older database servers.

Using the utilities
Important upgrade precautions
Upgrade quick start
Rebuilding version 10.0.0 and later databases
Rebuilding version 9 and earlier databases for version 11.0.0
Upgrading version 10.0.0 and later databases

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