Introduction

Often, when a new version of Mimer SQL is released, you need to upgrade existing databases to the new version. Usually, this is because of updates made in the data dictionary structure.

The upgrade procedure can be divided into two parts: upgrading the database and upgrading the application database access. When upgrading to Mimer SQL 10 the applications do not need any specific adjustments.

Once you have upgraded your database to Mimer SQL 10.1, you can use all the new features that comes with this version.

For more information on news in this version, please see the Mimer SQL Release Notes delivered with the product package. The Release Notes document will also have more detailed information covering the upgrade procedure.

Description

The upgrade program will create an entirely new system databank, SYSDB. The file name will be sysdb101.dbf.

The process of upgrading a database will include the following actions:

  • Convert the old transaction handling databank (TRANSDB), transaction logging databank (LOGDB) and SQL work databank (SQLDB). The old content in these databanks is deleted, and the entire log is dropped.
  • Transfer all information from the old SYSDB databank to the new, i.e. move and convert data according to the new layouts for the system base tables.
  • Convert and rebuild bitmap and root pages for all user databanks and shadows. If a databank (or shadow) cannot be opened, for example because the file can not be found at location stored in the data dictionary, an error message is displayed. In this case the databank can later be dropped, or the upgrade program can be executed again after correcting the error situation.

If any statement (view, procedure) stored in the dictionary uses a newly reserved word, the word will be quoted.

Note! If you are creating a new database, upgrading is not an issue.

Note! Before starting the upgrade operation it may be a good idea to take a backup of the databank files since the upgrade procedure is irreversible.

Upgrade on Windows

In Mimer SQL for Windows, an Upgrade Wizard is available. This wizard is used to convert database files from an old internal database format to the format of the current version.

The Upgrade Wizard is automatically invoked when a local database definition, pointing out older version databank files, has been added as a local database in the Mimer Administrator. The system immediately verifies the databank files and asks whether to perform an upgrade or not. The other way to start the wizard is to right-click on the local database that is marked with the icon symbolizing that an upgrade is required. In the pop-up menu an Upgrade menu option is available.

Upgrade on Linux, Unix or OpenVMS

Using the upgrade program, sdbgen –u, database upgrade to Mimer SQL 10 can be performed from older versions ranging from 8.2 to 10.0.

When the sdbgen program is executed, the database name should be given as a parameter to the program (or be defined in MIMER_DATABASE).

sdbgen -u database_name

The database name is presumed to be registered on the system, e.g. found in the sqlhosts file.

Upgrade Notes

If the old database is from version 9.1

As the implementation of collations has been changed since 9.1 the following are done by the upgrade program:

  • If a collation was specified for any column participating in a primary key clause, the collation specification is removed. To introduce it again you need to do the following after the upgrade operation: 
     ALTER TABLE DROP CONSTRAINT pk_constraint_name

 ALTER TABLE ALTER column_name data_type COLLATE collation_name

 ALTER TABLE ADD CONSTRAINT constraint_name PRIMARY KEY (...)
  • If any of the system-defined collations (ENGLISH, SWEDISH or UNICODE_DEFAULT) was specified for other columns, they will be changed to use the new definitions.
  • If any user-defined collations was specified for other columns, they will be removed.
  • All indexes including any column using a collation or specified by collate clause in the index are dropped. Use CREATE INDEX after upgrade to recreate wanted indexes.
  • All user-defined collations are removed.

If the old database is from version 8.2

To go from version 8 the following must be carried out:

  • Eliminate any duplicate access privileges given.
  • Transform the programs MIMER_BR, MIMER_SC, and MIMER_SW to the equivalent privileges BACKUP, STATISTICS and SHADOW.
  • Give generated names to all constraints such as primary keys, foreign keys, check clauses and so on.
  • Give privileges so that primary key updates will be allowed.
  • Mark all indexes as inconsistent (see Update Statistics).
  • If any create statement (domain, table, view, procedure) stored in the dictionary uses a newly reserved word, the word will be quoted.
  • If there were synonyms or views defined on any of the old system base tables or views, they are dropped and cannot be used by any application or script. The reason is that the old base tables no longer exist and the views have changed.
  • Any privileges granted on old system base tables or views are also removed. SYSADM has SELECT privilege on the new base tables. PUBLIC has SELECT privilege (with grant option) on all system views, so no further granting is required.
  • For each user databank, it will rebuild any index table with one or more VARCHAR columns.

Links

For further details on issues mentioned in this article, please see the Mimer SQL Documentation Set located at our documentation site (PDF-file or html navigation).

Graphic Element - Cube