Use this statement to upgrade the database, turn jConnect support for a database on or off, calibrate the database, change the transaction and mirror log file names, or force a mirror server to take ownership of a database.
ALTER DATABASE UPGRADE [ PROCEDURE ON ] [ JCONNECT { ON | OFF } ]
ALTER DATABASE { CALIBRATE [ SERVER ] | CALIBRATE DBSPACE dbspace-name | CALIBRATE DBSPACE TEMPORARY | CALIBRATE GROUP READ | CALIBRATE PARALLEL READ | RESTORE DEFAULT CALIBRATION }
ALTER DATABASE dbfile ALTER [ TRANSACTION ] LOG { { ON [ log-name ] [ MIRROR mirror-name ] | OFF } [ KEY key ]
ALTER DATABASE { dbname FORCE START | SET PARTNER FAILOVER }
PROCEDURE clause Drop and re-create all dbo- and sys-owned procedures in the database.
JCONNECT clause To allow the jConnect JDBC driver access to system catalog information, specify JCONNECT ON. This installs the system objects that provide jConnect support. Specify JCONNECT OFF if you want to exclude the jConnect system objects. You can still use JDBC, as long as you do not access system information. JCONNECT is ON by default.
If you are altering a database for use on Windows Mobile, see Using jConnect on Windows Mobile.
CALIBRATE [ SERVER ] clause Calibrate all dbspaces except for the temporary dbspace. This clause also performs the work done by CALIBRATE PARALLEL READ.
CALIBRATE DBSPACE TEMPORARY clause Calibrate the temporary dbspace.
CALIBRATE GROUP READ clause Perform group read calibration on the temporary dbspace. Writes large work tables to the temporary dbspace and uses different group read sizes to time the reading of the files. If adding space to the temporary table exceeds the limit for the connection, or if the cache is not large enough to allow calibration with the largest memory size, calibration fails and an error message is returned.
CALIBRATE PARALLEL READ clause Calibrate the parallel I/O capabilities of devices for all dbspace files. The CALIBRATE [ SERVER ] clause also performs this calibration.
RESTORE DEFAULT CALIBRATION clause Restore the Disk Transfer Time (DTT) model to the built-in default values that are based on typical hardware and configuration settings.
ALTER [TRANSACTION] LOG clause Change the name of the transaction or mirror log file name. If MIRROR mirror-name is not specified, the clause sets a file name for a new transaction log. If the database is not currently using a transaction log, it starts using one. If the database is already using a transaction log, it changes to using the new file as its transaction log.
If MIRROR mirror-name is specified, the clause sets a file name for a new transaction log mirror. If the database is not currently using a transaction log mirror, it starts using one. If the database is already using a transaction log mirror, it changes to using the new file as its transaction log mirror.
You can also use this clause to turn off the transaction or mirror log. For example, ALTER DATABASE LOG OFF.
KEY clause Specifies the encryption key to use for the transaction or mirror log. When using the ALTER [TRANSACTION] clause on a strongly encrypted database, you must specify the encryption key.
dbname FORCE START clause Forces a database server that is currently acting as the mirror server to take ownership of the database. This clause can be executed from within a procedure or event, and must be executed while connected to the utility database on the mirror server. See Forcing a database server to become the primary server.
SET PARTNER FAILOVER clause Initiate a database mirroring failover from the primary server to the mirror server. This statement must be executed while connected to the database on the primary server, and can be executed from within a procedure or event. When executed, any existing connections to the database are closed, including the connection that executed the statement; consequently, if the statement is contained in a procedure or event, other statements that follow it may not be executed. See Initiating failover on the primary server.
Syntax 1 You can use the ALTER DATABASE UPGRADE statement as an alternative to the Upgrade utility to upgrade or update a database. This applies to maintenance releases as well. After running this statement, you should restart the database. In general, changes in databases between minor versions are limited to additional database options and minor system table and procedure changes. The ALTER DATABASE UPGRADE statement upgrades the system tables to the current version and adds any new database options. If necessary, it also drops and recreates all system procedures. You can force a rebuild of the system procedures by specifying the PROCEDURE ON clause.
You can also use the ALTER DATABASE UPGRADE statement to restore settings and system objects to their original installed state.
Features that require a physical reorganization of the database file are not made available by executing an ALTER DATABASE UPGRADE statement. Such features include index enhancements and changes in data storage. To obtain the benefits of these enhancements, you must unload and reload your database. See Rebuilding databases.
You should always back up your database files before upgrading. If you apply the upgrade to the existing files, then these files become unusable if the upgrade fails. For information about backing up your database, see Backup and data recovery.
To use the jConnect JDBC driver to access system catalog information, specify JCONNECT ON (the default). If you want to exclude the jConnect system objects, specify JCONNECT OFF. Setting JCONNECT OFF does not remove jConnect support from a database. Also, you can still use JDBC, as long as you do not access system catalog information. If you subsequently download a more recent version of jConnect, you can upgrade the version in the database by (re)executing the ALTER DATABASE UPGRADE JCONNECT ON statement. See Installing jConnect system objects into a database.
Syntax 2 Use Syntax 2 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. When you recalibrate the I/O cost model, the database server is unavailable for other use. In addition, it is essential that all other activities on the computer are idle. Recalibrating the database server is an expensive operation and may take some time to complete. It is recommended that you leave the default in place.
When using the CALIBRATE PARALLEL READ clause, parallel calibration is not performed on dbspace files with fewer than 10000 pages. Even though the database server automatically suspends all of its activity during calibration operations, parallel calibration should be done when there are no processes consuming significant resources on the same computer. After calibration, you can retrieve the maximum estimated number of parallel I/O operations allowed on a dbspace file using the IOParallelism extended database property. See DB_EXTENDED_PROPERTY function [System].
To eliminate repetitive, time-consuming recalibration activities when there is a large number of similar hardware installations, you can re-use a calibration by unloading it and then applying it (loading it) into another database using the sa_unload_cost_model and sa_load_cost_model system procedures, respectively. See sa_unload_cost_model system procedure, and sa_load_cost_model system procedure.
Syntax 3 You can use the ALTER DATABASE statement to change the transaction and mirror log 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. You cannot stop using the transaction log if the database is using auditing. Once you turn off auditing, you can stop using the transaction log. This syntax is not supported in procedures, triggers, events, or batches.
Syntax 4 Attempting to execute an ALTER DATABASE dbname FORCE START statement for a database that is not being mirrored or is currently active and owned by this server results in an error. Also, if the primary server is still connected to the mirror server, an error is given. See Introduction to database mirroring.
For Syntax 1 and 2, must have DBA authority, and must be the only connection to the database. ALTER DATABASE UPGRADE is not supported on Windows Mobile.
For Syntax 3, you must have file permissions on the directories where the transaction log is located, and the database must not be running.
For Syntax 4, you must have the permissions specified by the -gk server option.
Automatic commit
SQL/2003 Vendor extension.
The following example disables jConnect support:
ALTER DATABASE UPGRADE JCONNECT OFF; |
The following example sets the transaction log file name associated with demo.db to newdemo.log:
ALTER DATABASE 'demo.db' ALTER LOG ON 'newdemo.log'; |
| Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |