ALTER DATABASE

Sets or drops the AUTOUPGRADE attribute for a database.

alter_database.png

 

Usage

Embedded, Interactive, Module, ODBC, JDBC.

Description

When a database is set to attribute AUTOUPGRADE, the database system will keep track of changes to tables, indexes and constraints in the database. The upgrade process is performed by the following steps:

1Initially a database (i.e. system databank and associated databank files) is copied from development environment to a production system.

2Changes are made to the original development database. Changes such as create table and alter table are kept track as the AUTOUPGRADE attribute has been set.

3When all changes are completed, the updated system databank is copied from the development system to the production system. (Note that only sysdb is copied.)

4The auto-upgrade is now performed as tables are accessed in the databanks that use the old table definition. Upgrades may, for example, add new columns to a table. When this is done the table is reloaded with the new column in place. During the upgrade new constraints are not validated. Instead, the constraints are applied when rows are modified in subsequent use of the table.

Note:When adding constraints to a table within a database applying AUTOUPGRADE, the WITHOUT CHECK option must be used. See ALTER TABLE to find out the consequences of this.

When creating a unique index within a database applying AUTOUPGRADE, the WITHOUT CHECK option must be used. See CREATE INDEX to find out the consequences of this.

When a table is found missing in a database with the AUTOUPGRADE attribute, the table will automatically be created. The table is initially empty. In addition, if a databank file is missing the databank is automatically created when AUTOUPGRADE is in effect.

Restrictions

SYSADM is the only ident allowed to execute the ALTER DATABASE statement.

The Mimer SQL shadowing functionality cannot be used together with the automatic upgrade feature. When setting the AUTOUPGRADE attribute on a database, following CREATE SHADOW statements will fail. And vice versa, when having databank shadows in the system it will not be possible to enable the AUTOUPGRADE attribute.

Notes

The AUTOUPGRADE attribute must be set when the database is set into production for the first time. This is essential for the upgrade functionality to work properly, otherwise automatic upgrade cannot be performed.

When dropping the AUTOUPGRADE attribute there is no way to come back to automatic upgrade for the database. All upgrade information gathered will be dropped. If restoring the AUTOUPGRADE attribute after dropping it, a new starting point for automatic upgrade is created. This means that upgrade information is gathered again, but upgrade cannot be performed from earlier system databank versions.

The AUTOUPGRADE attribute provides an advanced, but also restricted, method for automatic upgrade which is mainly aimed for mobile devices using remote upgrading. The functionality is not recommended for enterprise application environments.

Using the AUTOUPGRADE attribute gives certain implications when adding table constraints, see Adding a Table Constraint.

Example

Set the database to AUTOUPGRADE.

ALTER DATABASE SET AUTOUPGRADE;

Standard Compliance

Standard

Compliance

Comments

 

Mimer SQL extension

The ALTER DATABASE statement is a Mimer SQL extension.