Information Stored in Mimer SQL System Databanks

Introduction

The Mimer SQL system databanks, SYSDB, LOGDB, TRANSDB and SQLDB, are fundamental to the functioning of a Mimer SQL database. These databanks are generated automatically when you create an initial database.

System databanks are not used for storing user-defined information and cannot be updated directly by users. Databanks holding user data are usually referred to as user databanks.

If any of the system databanks are damaged or missing, attempts to log on to Mimer SQL will fail.

Description

Below are detailed descriptions on each Mimer SQL system databank.

What information is stored in the system databank SYSDB?

SYSDB is the most important system databank. It contains the meta data, referred to as the Data Dictionary, for a Mimer SQL database. The Data Dictionary holds information about all other databanks in the database. For example, all the table definitions, the users (idents) and their access rights for a specific Mimer SQL database system are stored in the Data Dictionary.

Each Mimer SQL database system has its own SYSDB, which is tightly coupled to all the other databanks defined in the database. Because of this, you cannot simply copy a databank file from one Mimer SQL database to another. In order to move a databank to another Mimer SQL database you have to use the MIMLOAD utility, see the the Mimer SQL Documentation Set for details.

Note! If you lose your SYSDB databank, the database definitions cannot be recreated from separate objects. You have to take backup of the SYSDB databank regularly. It is very important to have an updated copy of the SYSDB, especially if you have made modifications on any object definition (i.e. definition for databank, table, ident, view and so on). See the article Backup Mimer SQL Online for details on backup.

What is the purpose of the system databank LOGDB?

If a user databank is defined with the LOG option, all write operations on this databank are recorded in LOGDB. Operations performed within transactions on SYSDB are also recorded here.

The records in LOGDB should be cleared after a complete backup in order to maintain consistency between the backup and LOGDB. This ensures that LOGDB only contains information about databank changes made from the timestamp when the last backup was taken. If you use the Online Backup in Mimer SQL, LOGDB is automatically cleared at the COMMIT BACKUP statement.

In order to restore a corrupt databank you need a valid backup copy of the databank file and a LOGDB file (or a LOGDB file chain) containing all the operations made to the databank since the backup was taken. See the article on how to Restore Data for details on the recovery process.

It is a recommended practice to backup all the databanks of the database at the same time, and to ensure that LOGDB is always backed up whenever other databanks are backed up (and then it should be cleared).

Another important recommendation is to place the LOGDB databank file on a different physical device than all the other databanks. Otherwise, if a disk crash occurs, both the databank file(s) and the LOGDB file are lost, meaning that the data cannot be fully recovered. A backup in combination with the current LOGDB, valid from the backup in question, is the key to a successful recovery. Note! If LOGDB is lost, a new backup should be taken immediately.

What is the purpose of the system databank TRANSDB?

The TRANSDB databank is used for handling transaction control in Mimer SQL. Transaction control provides a means of protecting the database from corruption which might arise from two users attempting to change the same information at the same time, and also provides the basis for ensuring logical database consistency. I.e that the database contains complete and valid data.

If a databank is defined with either the LOG or TRANSACTION options, all write operations (performed with transaction control) against tables that are held in that databank will be stored in TRANSDB. On a successful transaction commit, the transactions are secured in TRANSDB and they are then flushed to the user databanks as a background activity.

After shutting down the Mimer SQL database server, there may be some pending transactions in TRANSDB. These will be written to the user databank the next time the databank is accessed. Pending transactions are also flushed to the user databank when the databank is set offline or backed up using the SQL statement CREATE BACKUP. If TRANSDB is lost, these pending transactions will not be recoverable.

TRANSDB stores information that allows the database server to bring the database into a logically consistent state after a system failure. It is recommended to have the TRANSDB on another disk than used for the user databanks – mainly for performance reasons. But, it should not be kept together with LOGDB. For detailed documentation on how to distribute databank files, please see the Mimer SQL Documentation set at our Documentation page (look for the section Organizing Databank Files in the System Management Handbook part).

What is the purpose of the system databank SQLDB?

SQLDB is a system databank used for internal temporary work storage and as swap area when the allocated memory is not enough.

If a result set from a query is too big (possibly caused by a badly planned query), or if the result of a query is to be sorted or grouped and no suitable index is available, a temporary table is created in SQLDB. This means that SQLDB can grow quite large since the file size will grow as needed. The used file space will be deallocated when not needed anymore by the database system, but the file size will remain extended. The recommendation is that a GOALSIZE is set for the SQLDB databank, see the the Mimer SQL Documentation Set for details. Having a GOALSIZE set for a databank means that the file size is automatically shrinked to the predefined limit, or as close as possible, whenever an opportunity to do so comes.

Since the SQLDB databank is used for temporary operations, it can be safely removed when the database server is stopped. The Mimer BSQL program is then used to recreate it. When executed, BSQL will automatically find that SQLDB is missing and suggest that it should be created.

The SQLDB databank does not need any backup protection, but if you are backing up a complete database, we recommend that you include SQLDB in the backup as well.