|
Upgrades the database, turns jConnect support for a database on or off, calibrates the database, changes the transaction log and transaction log mirror file names, or forces a mirror server to take ownership of a database.
ALTER DATABASE UPGRADE [ PROCEDURE ON ] [ JCONNECT { ON | OFF } ] [ RESTART { ON | OFF } ] [ SYSTEM PROCEDURE AS DEFINER { ON | OFF } ]
ALTER DATABASE UPGRADE SCRIPT FILE sql_script_path [ RESTART 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 }
ALTER DATABASE SET CHECKSUM OFF
ALTER DATABASE { SAVE CACHE | RESTORE CACHE | DROP CACHE }
The database file. You can also specify a variable name.
Drop and recreate all dbo- and SYS-owned procedures in the database.
To allow the jConnect JDBC driver access to system catalog information, specify JCONNECT ON. This clause installs the system objects that provide jConnect support. Specify JCONNECT OFF 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.
RESTART is ON by default. When RESTART ON is specified and the AutoStop (ASTOP) connection parameter is set to No, the database restarts after it is upgraded. Otherwise, the database is stopped after an upgrade.
The SYSTEM PROCEDURE AS DEFINER clause specifies whether to execute pre-16.0 system procedures that perform privileged tasks with the privileges of the invoker or the definer (owner). ON means these system procedures are executed with the privileges of the definer (owner). OFF means these system procedures are executed with the privileges of the invoker.
If this clause is not specified, the default is to maintain the current behavior of the database being upgraded. When upgrading a pre-16.0 database, this means running the procedures as definer.
This setting does not impact user-defined procedures, or any procedures introduced in version 16.0 or later.
Use this clause to specify the location of the user-defined upgrade script file. sql_script_path is the location and name of a .sql script file containing the DML and DDL statements to perform.
If the script execution is not successful, and RESTART ON is specified (the default), the database is rolled back to the checkpoint that was automatically performed before the upgrade, and restarted. If RESTART OFF is specified, the database is stopped without a checkpoint, and is rolled back to the last checkpoint the next time the database is started.
Calibrate all dbspaces except for the temporary dbspace. This clause also performs the work done by CALIBRATE PARALLEL READ.
Calibrate the specified dbspace.
Calibrate the temporary dbspace.
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 the parallel I/O capabilities of devices for all dbspace files. The CALIBRATE [ SERVER ] clause also performs this calibration.
Restore the Disk Transfer Time (DTT) model to the built-in default values that are based on typical hardware and configuration settings.
Change the file name of the transaction log or transaction log mirror file. 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 starts 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 starts using the new file as its transaction log mirror.
You can also use this clause to turn off the transaction log or transaction log mirror. For example, ALTER DATABASE ALTER LOG OFF.
Specify the encryption key to use for the transaction log or transaction log mirror. The encryption key can be either a string or a variable name. When using the ALTER [ TRANSACTION ] LOG clause on a strongly encrypted database, you must specify the encryption key.
Force a database server that is currently acting as the mirror server to take ownership of the database.
Using the FORCE START clause can result in the loss of transactions if the primary server contains transactions that the mirror server does not have.
It is recommended that you restart the primary and execute ALTER DATABASE with the SET PARTNER FAILOVER clause to force a failure without lost transactions. The FORCE START clause should only be used as a last resort when the primary cannot be restarted.
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.
Initiate a database mirroring failover from the primary server to the mirror server without stopping the server. This statement must be executed while you are connected to the database on the primary server, and can be executed from within a procedure or event. When this statement is executed:
The database server closes all connections to the database, including the connection that executed the statement
The database stops, and then restarts in the mirror role.
If the statement is contained in a procedure or event, then the other statements that follow it may not be executed
Disable global checksums for the database. By default, new databases have global checksums enabled, while version 11 and earlier databases do not have global checksums enabled.
Regardless of the setting of this clause, the database server always enables write checksums for databases running on storage devices such as removable drives, to help provide early detection if the database file becomes corrupt. The database server also calculates checksums for critical pages during validation activities.
For databases that do not have global checksums enabled, you can enable write checksums by using the -wc options.
Record the current cache contents so that you can restore the database to this state when necessary to improve performance.
This statement is not logged to the transaction log and cannot be executed on read-only databases, including databases involved in high availability and read-only scale-out configurations.
After the statement executes, the prefetch_pages column of the SYSDBSPACE system view is updated with one bit in the page for each page currently in cache. The bitmap that is used is the same one as that returned from the sp_db_cache_contents system procedure.
Improve performance by restoring the current database to the state it was in when the ALTER DATABASE SAVE CACHE statement was executed.
This statement is not logged to the transaction log.
This statement reads the pages identified by the prefetch_pages column of the SYSDBSPACE system view. The effect of the statement is the same as that of calling the sp_read_db_pages system procedure for each dbspace with the bitmap stored in the prefetch_column of the SYSDBSPACE system view.
Clear any saved cache pages.
This clause clears the pages stored in the saved_cache_pages column of the SYSDBSPACE system view and sets the column value to NULL. After executing the ALTER DATABASE DROP CACHE statement, subsequent ALTER DATABASE RESTORE CACHE statements return an error unless the ALTER DATABASE SAVE CACHE statement is executed first.
Use the ALTER DATABASE UPGRADE statement as an alternative to the Upgrade utility (dbupgrad) to upgrade or update a database. By default, the database is stopped and restarted after the upgrade. The transaction log is archived during the upgrade and a new transaction log is created before the database is stopped or restarted.
After executing an ALTER DATABASE UPGRADE statement, you should shut down the database and archive the transaction log.
In general, changes in databases between minor versions are limited to additional database options and minor system table and system 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.
An error message is returned if you execute an ALTER DATABASE UPGRADE statement on a database that is currently being mirrored.
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.
Back up your database files before attempting to upgrade your database.
To use the jConnect JDBC driver to access system catalog information, specify JCONNECT ON (the default). To exclude the jConnect system objects, specify JCONNECT OFF. Setting JCONNECT OFF does not remove jConnect support from a database. 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.
If sql_script_path is not valid, an error is returned and the database is not stopped or restarted.
The script must be stored on, and run from, the database server computer.
If the script execution is successful, the database continues running.
You can perform recalibration of the I/O cost model used by the optimizer. This operation 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. 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 by using the IOParallelism extended database property.
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 by using the sa_unload_cost_model and sa_load_cost_model system procedures, respectively.
Use the ALTER DATABASE statement to change the transaction log and transaction log mirror names associated with a database file. The database must not be running to make these changes. 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 log or transaction log mirror 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.
Use the BACKUP DATABASE statement to rename the transaction log for a running database. For example:
BACKUP DATABASE DIRECTORY 'directory-name' TRANSACTION LOG ONLY TRANSACTION LOG RENAME;
ALTER DATABASE...FORCE START must be executed from the mirror server, not the primary server.
This clause can only be used to disable checksums for a database.
Use the ALTER DATABASE statement to record the cache contents at a steady state and restore this state when necessary.
Unless otherwise specified in the following list, only the ALTER DATABASE system privilege is required to upgrade the database.
Upgrading components or restoring objects: you must have the ALTER DATABASE system privilege, and must be the only connection to the database.
Performing a user-defined upgrade: While only the ALTER DATABASE system privilege is required to execute the ALTER DATABASE statement, the user performing the upgrade must have all the privileges required to perform the actions in the specified .sql file. If the user does not have the proper privileges, the database upgrade fails and the database is rolled back to its state prior to executing the ALTER DATABASE statement.
Performing calibration: you must have the SERVER OPERATOR system privilege, you must have file permissions on the directories where the transaction log is located, and the database must not be running.
Your ability to execute the ALTER DATABASE dbfile ALTER TRANSACTION LOG statement depends on the setting for the -gu database option, and whether you have the SERVER OPERATOR system privilege.
Changing ownership of a database: you must have the SERVER OPERATOR system privilege.
The privileges required to execute an ALTER DATABASE dbname FORCE START statement can be changed by the -gd database server option.
Turning off checksum: you must have the ALTER DATABASE system privilege.
Cache warming to a steady state: you must have the SERVER OPERATOR system privilege.
Automatic commit
When executing an ALTER DATABASE UPGRADE statement, the transaction log is archived during the upgrade, and a new transaction log is created when the database restarts after the upgrade. Also, by default the database is stopped and restarted after the upgrade.
Not in the standard.
The ALTER DATABASE statement is supported by Adaptive Server Enterprise. However, the statement's clauses supported by Adaptive Server Enterprise are disjoint from those clauses supported by SQL Anywhere.
The following statement disables jConnect support:
ALTER DATABASE UPGRADE JCONNECT OFF;
The following statement sets the transaction log file name associated with demo.db to mynewdemo.log:
ALTER DATABASE 'demo.db\\demo.db'
ALTER LOG ON 'mynewdemo.log';The following statement performs a user-defined upgrade by running a fictitious myUpgrade.sql script file. If the upgrade is successful, the database continues to run. If the upgrade is not successful, the database is rolled back to the checkpoint that was automatically performed before the upgrade, and restarted:
ALTER DATABASE UPGRADE SCRIPT FILE 'C:\\Users\\Public\\Documents\\myUpgrade.sql' RESTART ON;
The following statement creates a variable for the database asatest.db:
CREATE VARIABLE @db1 LONG VARCHAR ; SET @db1 = 'asatest' ;
The following statement uses the variable @db1 to alter the database asatest.db:
ALTER DATABASE @db1 ALTER TRANSACTION LOG ON 'vis_tmp2.log' MIRROR 'vis_tmp2.mlg' ;
The following example shows you how to save the contents of the cache when the database is running in a steady state and then restore the cache to that state when necessary.
Create a dbspace by executing the following statement:
CREATE DBSPACE mydbs
AS 'C:\\mydb\\mydbs.db';Create a table in the new dbspace and add some data by executing the following statements:
CREATE TABLE mytable( col1 INT, col2 CHAR(128) ) IN mydbs;
INSERT INTO mytable SELECT column_id, column_name FROM sys.syscolumn;
COMMIT;
Execute the following statement to determine the ID of the new dbspace:
SELECT * FROM SYS.SYSDBSPACE WHERE dbspace_name = 'mydbs';
Note the ID of the new dbspace and that the saved_cache_pages column is NULL.
Verify which pages from the new dbspace are currently in the cache by executing the following statement:
SELECT * FROM dbo.sp_db_cache_contents( );
The statement above returns information for all dbspaces in the database. To restrict the results to the new mydbs dbspace, execute the following statement, where mydbs-ID is the ID of the new mydbs dbspace that was obtained from SYS. SYSDBSPACE:
SELECT * FROM dbo.sp_db_cache_contents( mydbs-ID );
Save the current steady state of the database by executing the following statement:
ALTER DATABASE SAVE CACHE;
Later, after numerous other transactions have been executed and the cache has undergone significant change, you want to return to the saved steady state. You can either read all the steady state pages for all dbspaces into the cache or read all the steady state pages for a specific dbspace into the cache.
To read all the steady state pages for all dbspaces into the cache, execute the following statement:
ALTER DATABASE RESTORE CACHE;
If you would rather read all the steady state pages for one dbspace (for example, the new dbspace mydbs), then execute a series of statements similar to the following:
CREATE VARIABLE cache_pages LONG VARBIT;
SELECT saved_cache_pages INTO cache_pages FROM SYS.SYSDBSPACE WHERE dbspace_name='mydbs';
CALL dbo.sp_read_db_pages( mydbs-ID, cache_pages );