Contents Index ALLOCATE DESCRIPTOR statement [ESQL] ALTER DBSPACE statement

ASA SQL Reference
  SQL Statements

ALTER DATABASE statement

Description 

Use this statement to upgrade a database created with previous versions of the software; or to add Java or JConnect support to any database.

Syntax 1 

ALTER DATABASE
[ UPGRADE [ JAVA { ON | OFF | JDK { '1.1.8' | '1.3' } } ]
    [ JCONNECT { ON | OFF } ]
| REMOVE JAVA ]

Syntax 2 

ALTER DATABASE
{    CALIBRATE [ SERVER ]
 | CALIBRATE DBSPACE dbspace-name
 | CALIBRATE DBSPACE TEMPORARY
 | RESTORE DEFAULT CALIBRATION
}

Syntax 3 

ALTER DATABASE dbfile
MODIFY [ TRANSACTION ] LOG
{ { OFF | ON } { log-name | log-name  MIRROR mirror-name | MIRROR mirror-name } }
[ KEY key [ ALGORITHM algorithm ] ]

Parameters 

JAVA clause    Controls support for Java in the upgraded database.

The default behavior is JAVA OFF.

If you add Java in the database, you must restart the database before it can be used.

Java in the database is a separately licensable component. For more information, see Introduction to Java in the Database.

JCONNECT clause    If you wish to use the Sybase jConnect JDBC driver to access system catalog information, you need to specify JCONNECT ON. If you wish to exclude the jConnect system objects, specify JCONNECT OFF. You can still use JDBC, as long as you do not access system catalog information. The default is to include jConnect support (JCONNECT ON).

Setting JCONNECT OFF does not remove jConnect support from a database.

REMOVE JAVA clause    Removes Java in the database from a database. The operation leaves the database as if it were created with JAVA OFF. Java in the database must not be in use when the statement is issued. You must remove all Java classes from the database before executing this statement. The statement does not remove stored procedures and triggers that reference Java objects, and the presence of these objects does not trigger an error in the ALTER DATABASE statement.

Usage 

Syntax 1    You can use the ALTER DATABASE statement as an alternative to the Upgrade utility to upgrade a database. After using ALTER DATABASE UPGRADE, you should shut down the database. (The Upgrade utility does this for you automatically.)

Backup before upgrading 
As with any software, it is recommended that you make a backup of your database before upgrading.

ALTER DATABASE can be used to upgrade databases created with earlier versions of the software. This applies to maintenance releases as well, so you can upgrade a database created with, for example, version 7.0.2 to 7.0.3 standards using the ALTER DATABASE statement in version 7.0.3 of the software.

In general, changes in databases between minor versions are limited to additional database options and minor system table changes.

When used to upgrade a database, ALTER DATABASE makes the following changes:

You can also use ALTER DATABASE to just add Java in the database or jConnect features if the database was created with the current version of the software.

For more information on adding Java support, see Java-enabling a database. For more information on adding jConnect support to a Version 6 database, see Installing jConnect system objects into a database.

Not all features made available 
Features that require a physical reorganization of the database file are not made available by ALTER DATABASE. Such features include index enhancements and changes in data storage. To obtain the benefits of these enhancements, you must unload and reload your database.

For more information, see Rebuilding databases.

Syntax 2    You can also use ALTER DATABASE to perform recalibration of the I/O cost model used by the optimizer. This updates the Disk Transfer Time (DTT) model, which is a mathematical model of the disk I/O used by the cost model.

In normal operation, the cost model uses a built-in default DTT model. This default model was designed based on typical hardware and configuration. In rare cases when you are using specialized hardware such as non-standard disk drives, and when you are having performance problems, it may be useful to overwrite the default model with one based on your particular setup. However, it is generally recommended to leave the default in place.

When you recalibrate the I/O cost model, the server is unavailable for other use. In addition, it is essential that all other activities on the computer are idle. Recalibrating the server is an expensive operation and may take some time to complete.

When you use the CALIBRATE [SERVER] argument, all dbspaces are calibrated except for the temporary dbspace. Use CALIBRATE TEMPORARY DBSPACE to calibrate it. Use CALIBRATE DBSPACE dbspace-name to calibrate a single dbspace. Use RESTORE DEFAULT CALIBRATION to restore the default DTT model.

Syntax 3    You can use the ALTER DATABASE statement to change the transaction log and mirror names associated with a database file. These changes are the same as those made by the Transaction Log (dblog) utility. You can execute this statement while connected to the utility database or another database, depending on the setting of the -gu option. If you are changing the transaction or mirror log of an encrypted database, you must specify a key and the encryption algorithm.

Permissions 

Must have DBA authority, and must be the only connection to the database.

For REMOVE JAVA, Java in the database must not be in use when the statement is issued.

Not supported on Windows CE.

Java in the database is a separately licensable component.

Side effects 

Automatic commit

See also 

CREATE DATABASE statement

The Upgrade utility

CREATE STATISTICS statement

The Transaction Log utility

Standards and compatibility 
Example 

The following example upgrades a database to enable Java operations.

ALTER DATABASE UPGRADE
JAVA ON

The following example sets the transaction log filename associated with asademo.db to newdemo.log.

ALTER DATABASE 'asademo.db'
MODIFY LOG ON 'newdemo.log'

Contents Index ALLOCATE DESCRIPTOR statement [ESQL] ALTER DBSPACE statement