Mimer SQL Engine System Management Handbook

Introduction

Mimer SQL is an advanced relational database management system (RDBMS) developed by Mimer Information Technology AB.

Mimer SQL has a number of unique technical solutions to handle some of the more complicated functionality that a database management system must provide.

For example, Mimer SQL provides a solution to the problem of allowing simultaneous access to the database without the danger of a deadlock occurring. This greatly simplifies database management and allows truly scalable performance, even during heavy system load.

Another significant technical innovation is the data storage mechanism, which is constantly optimized for the highest possible performance and ensures that no manual reorganization of the database is ever needed.

Mimer SQL offers a uniquely scalable solution, including multi-processor support and, with its availability on all major UNIX (including Linux), Windows VIsta/XP/200x/NT, Mac OS X, and OpenVMS platforms, is ideally suited for open environments where interoperability is important.

The database management language Mimer SQL (Structured Query Language) is compatible in all essential features with the currently accepted SQL standards, see the Mimer SQL Reference Manual, Introduction to SQL Standards, for details.


About this Manual

This manual describes how to establish, manage and maintain a Mimer SQL database. It is a general handbook for system administrators, describing in detail the various areas of responsibility and procedures to use when administering a Mimer SQL database system.

The information contained in this handbook generally applies to all the platforms supported by Mimer SQL.

From time to time platform-specific notes appear in the general description, presented as follows:

UNIX: Denotes information that applies specifically to UNIX/Linux and Mac OS X platforms.

VMS: Denotes information that applies specifically to OpenVMS platforms.

Win: Denotes information that applies specifically to Windows platforms.

There are also some appendices at the end of this handbook which contain information that applies to specific platforms.

Prerequisites

There are no prerequisites for users of this manual. However, it is advisable for the reader to be familiar with, and have a working knowledge of, the host computer operating system.

Related Mimer SQL Publications

· Mimer SQL Engine Reference Manual

contains a complete description of the syntax and usage of all statements in Mimer SQL and is a necessary complement to this manual.

· Mimer SQL Engine User's Manual

contains a description of the BSQL facilities. A user-oriented guide to the SQL statements is also included, which may provide help for less experienced users in formulating statements correctly (particularly the SELECT statement, which can be quite complex).

· Mimer SQL Engine Programmer's Manual

describes the usage of SQL embedded in application programs.

· Mimer SQL Platform-specific Documents

contain platform-specific information. A set of one or more documents is provided, where required, for each platform on which Mimer SQL is supplied.

· Mimer SQL Engine Release Notes

contain general and platform-specific information relating to the Mimer SQL Engine release for which they are supplied.

Acronyms, Terms and Trademarks

Term
Description

API

Application Programming Interface.

BSQL

Facility for using SQL interactively or by running a command file.

Data source

ODBC term for a database.

Databank

A databank corresponds to a physical file in the host file system and is used to store tables. A databank may contain several tables.

Database

A Mimer SQL database consists of the system databanks and a number of user databanks.

Database home directory

The directory where the system databank file containing the data dictionary and some other files that form part of the database are located.

DCL

Digital Command Language.

Embedded SQL

The term used for SQL statements when they are embedded in a traditional host programming language.

JDBC

Database connectivity for Java.

MULTIDEFS

A file, on UNIX and OpenVMS, containing parameters for controlling the database server for a Mimer SQL database.

ODBC

Open Database Connectivity, a specification for a database API in the C language, independent of any specific DBMS or operating system.

PSM

Persistent Stored Modules (i.e. Stored Procedures).

Shadow

A Mimer SQL databank may have one or more shadows. A shadow is a copy of the original (master) databank and is continuously updated by Mimer SQL.

SQL

Structured Query Language.

SQLHOSTS

A file, on UNIX and OpenVMS, containing lookup information for all Mimer SQL databases accessible from the current node.

UNIX

UNIX is a trademark registered by the Open Group.

VMS

VMS is a trademark registered by Hewlett-Packard.

Windows

Windows is a trademark registered by Microsoft.

All other trademarks are the property of their respective holders.


System Management Responsibilities

Installation of a Mimer SQL system and the initial creation of the database environment is performed by a specially privileged operating system user, referred to as the system administrator.

In an established system, the system administrator is also responsible for the maintenance of the installation - system tuning, troubleshooting, backup/restore, and so on. The system administrator must have a good working knowledge of the host computer operating system.

SYSADM

In addition to system administration, there are certain management activities which are performed within the database, i.e. database administration.

A specially privileged Mimer SQL ident called SYSADM is created when a database is installed and this ident should be used whenever database administration activities are to be undertaken.

Note: The ident name SYSADM may not be changed.

SYSADM Privileges and Access Rights

Some of the Mimer SQL functionality requires privileges and access rights which are initially granted only to the SYSADM user, but which may be passed on to other Mimer SQL users.

SYSADM has SELECT access on the internal Mimer SQL data dictionary tables, permitting direct reading of the meta-data describing the system. In examining the contents of the data dictionary with the functionality provided, the user ident SYSADM has, by default, wider access rights than other users.

The user ident SYSADM does not, however, have general access to the contents of the database. Information stored in user-defined tables may only be accessed by other users if the creator of the table explicitly grants permission. In this context, SYSADM is treated just as any other database user.

SQL Statement Execution

At several places in this manual there are guidelines where SQL statements are shown. These statements can be executed from any ad-hoc SQL query tools, such as Mimer BSQL, SQL Query Builder, etc.


The Database Environment

This chapter describes the database environment which is composed of a set of Mimer SQL system databanks, one or more idents authorized to connect to the database and the databanks created by the idents. It also describes database security and data integrity.

The objects that are created in the database are described in the Mimer SQL Reference Manual, Mimer SQL Database Objects.


The Data Dictionary

The database environment is controlled through a central data dictionary, stored in the system databank SYSDB and is automatically maintained by Mimer SQL. The data dictionary contains meta-data describing all the objects in the database. System access to the data dictionary tables is performed by internal routines and is transparent to the user.

Restricted facilities for examining the contents of the data dictionary are available to all users through the LIST and DESCRIBE functions in BSQL, see the Mimer SQL User's Manual, Mimer BSQL for a more complete description of these facilities.

In general, a user may read data dictionary information for database objects to which they have access. The BSQL facilities use pre-defined views on the data dictionary tables to present the information in a structured form, see the Mimer SQL Reference Manual, Data Dictionary Views for documentation on the data dictionary views available to all users.

The SYSADM user ident may read the contents of the data dictionary tables directly, and may grant SELECT access on the tables to any other user ident. The organization of the data dictionary tables is documented in Appendix E Data Dictionary Tables of this manual.

No individual user, including SYSADM, may update data dictionary tables directly. All write operations in the data dictionary are performed by internally controlled routines, to ensure consistency within the dictionary.


Idents

An ident in a Mimer SQL system is an authorized user of the system, or the collective identity of a group of users sharing common privileges.

There are four types of idents: USER, OS_USER, PROGRAM and GROUP idents.

USER Idents

USER idents are authorized to connect to a Mimer SQL database, by using the CONNECT statement in an application program or by entering the correct ident name and password in an interactive environment.

Any privileges a user ident holds may be exercised once the ident has logged on. USER idents are generally associated with specific physical individuals authorized to connect to the database.

OS_USER Idents

OS_USER is an ident type which allows the user currently logged in to the operating system to connect to a Mimer SQL database without providing a username or password.

If an OS_USER ident is defined with a password in Mimer SQL, the ident may connect to Mimer SQL in the same way as any other user ident (i.e. by providing a user name and password). An OS_USER ident is subject to the same access restrictions as any other USER ident.

PROGRAM Idents

PROGRAM idents may not initiate a connection to a Mimer SQL database, but may be entered from within an application program or interactive environment by using the ENTER statement.

A connection to the database should have been established before the ENTER statement is used. The ident using the ENTER statement must hold EXECUTE privilege on the PROGRAM ident.

Entering a PROGRAM ident is analogous to logging on as a USER ident, in that the PROGRAM ident gains access to the system and any privileges the ident holds become applicable.

PROGRAM idents are generally associated with specific functions within the system, not with physical individuals. For an example showing the use of a PROGRAM ident, see the Mimer SQL User's Manual, PROGRAM Idents - ENTER and LEAVE.

GROUP Idents

GROUP idents are collective identities for groups of USER or PROGRAM idents.

Any privileges granted to or revoked from a GROUP ident automatically apply to all members of the group.

Any ident can be a member of as many groups as required, and one group can include any number of members.

GROUP idents provide a facility for organizing the privilege structure in the database system. For examples showing the use of a GROUP ident, see the Mimer SQL User's Manual.

Idents - Access and Authority

USER, OS_USER and PROGRAM idents are authorized users of the system.

Every USER and PROGRAM ident has a unique ident name and a private password which must be correctly supplied to the CONNECT or ENTER statement in application programs.

An OS_USER may access the database without explicitly providing a username or password on condition that the username for the user currently logged in to the operating system correspond to the definition of an OS_USER in the Mimer SQL database.

When Mimer SQL is installed, the user ident SYSADM, used for database administration, is automatically created. The password for SYSADM is defined when the system is installed, see SDBGEN - Generating the System Databanks.

All idents in the system belong to a group which is specified by using the keyword PUBLIC in Mimer SQL statements. Privileges granted to PUBLIC are global to the system.


Schemas

A schema defines a local environment within which private database objects can be created. The ident creating the schema has the right to create objects in it and to drop objects from it.

When a USER, OS_USER or PROGRAM ident is created, a schema with the same name can also be created automatically and the created ident becomes the creator of the schema. This happens by default unless WITHOUT SCHEMA is specified in the CREATE IDENT statement.

When a private database object is created, its name can be specified in a fully qualified form which identifies the schema in which it is to be created. The names of objects must be unique within the schema to which they belong, according to the rules for the particular object-type.

If an unqualified name is specified for a private database object, a schema name equivalent to the name of the current ident is assumed.


Databanks

A Mimer SQL database consists of the Mimer SQL system databanks and a number of user databanks.

Each databank is a single physical file in the host file system.

Mimer SQL System Databanks

The Mimer SQL system databanks are fundamental to the functioning of a Mimer SQL database and they are created during the process of installing a database.

System databanks are not used for storing user-defined information and cannot be updated directly by users.

If any one of the system databanks is damaged or missing, attempts to log on to Mimer SQL will fail. Backup and restore procedures for the system databanks are described in Backing-up and Restoring Data.

The Mimer SQL system databanks are:

  • SYSDB
  • LOGDB
  • TRANSDB
  • SQLDB
SYSDB

SYSDB is the most important system databank as it stores the tables that make up the data dictionary, see The Data Dictionary.

Among other things, the data dictionary holds information about the other databanks that make up the database, the tables each user databank contains, the users (idents) that are known to the Mimer SQL system and the access rights each ident has.

TRANSDB

TRANSDB stores information that is vital for keeping the database in a consistent state.

LOGDB

LOGDB records all write operations performed within transactions on SYSDB and user databanks which have been defined with the LOG option.

The information in this databank is used by the backup and restore facilities, see Backing-up and Restoring Data, to restore the contents of a database in the event of a system failure.

A readlog facility is provided to allow the information in this databank to be examined, see Mimer SQL User's Manual, READLOG.

SQLDB

SQLDB is used by the transaction handling mechanism to store row data read from the database, see Transaction Control, and is used by Mimer SQL for temporary storage of result tables.

User Databanks

User databanks contain the tables in the database created by the users of the system. Typically these databanks are created by the system administrator and TABLE privilege is granted to the users of the system.

The CREATE DATABANK statement is used to create user databanks, see the Mimer SQL Reference Manual, SQL Statement Descriptions.

Except at the point when tables are created, the existence of databanks is transparent to users and application programs. When access is requested to a table, information in the data dictionary is used by Mimer SQL to locate the table and make it available, if permissible.

The division of a database into databanks is made on the basis of file handling considerations from the operating system viewpoint and on the basis of transaction control considerations from the database viewpoint. The use of databanks allows considerable flexibility in the physical placement of data on the computer system.


Databank Options

Databanks may be defined with the LOG, TRANSACTION, WORK, or READ ONLY options which determine transaction handling and logging behavior, as follows:

· LOG

All operations on the databank are performed under transaction control. All transactions are logged.

· TRANSACTION

All operations on the databank are performed under transaction control. No transactions are logged.

· WORK

All operations on the databank are performed without transaction control (even if they are requested within a transaction), and are not logged.

· READ ONLY

Only read only transactions are allowed. No transactions are logged.

· TEMPORARY

This option is SQLDB specific.

Note: Operations performed on databanks with the TRANSACTION or WORK option cannot be restored in the event of system failure, see Backing-up and Restoring Data.

If a databank is dropped from the database, the tables stored in the databank are also dropped and the physical databank file is deleted from disk.

Creating Idents and a Databank - an Example

In the following example, a databank and two users (idents) are created. The idents are then given the authority to create tables within the databank:

SQL> CREATE DATABANK DEVELOP OF 1000 PAGES
            IN 'dev.dbf' WITH LOG OPTION;
SQL> CREATE IDENT DEVUSER AS USER USING 'devuser';
SQL> CREATE IDENT JOE AS OS_USER;
SQL> GRANT TABLE ON DEVELOP TO DEVUSER;
SQL> GRANT TABLE ON DEVELOP TO JOE;


Locating Databank Files

The system databank SYSDB is always stored in a file located in the directory defined as the home directory for the database, see The Local Database. The file locations of all the other system databanks and the user databanks are stored in the data dictionary.

The file specification for the databank file is exactly as specified when the databank was created. If a databank file specification is given in full, it is unambiguously specified and no variable factors are involved in resolving the location of the file.

If a databank file specification appears in the data dictionary without an absolute directory name, the database home directory will be used to complete the file specification.

This substitution is applied whenever the location of the databank file must be determined, (i.e. when the databank is created or altered and whenever tables stored in it are accessed).

Subsequent redefinition of the database home directory or any variables used in the file specification will, therefore, alter the expected location of such databank files.

UNIX: Databases on UNIX platforms may be set up with a directory search path instead of a single home directory, see The Local Database.

The first directory in the search path list must be the database home directory, where SYSDB is located. Other databank files can be located in any of the directories in the search path list.

VMS: Whenever a databank file is specified without a directory name under OpenVMS, it must be located in the database home directory.

If a logical name is included in the file specification, this will be recorded in the data dictionary and will be used whenever the location of the databank file is resolved.

Any logical names used in databank file specifications must be created as GROUP or SYSTEM wide logical names so that the database server process has access to them.

Win: Databases on Windows platforms may be set up with a directory search path instead of a single home directory, see The Local Database.

The first directory in the search path list must be the database home directory, where SYSDB is located. Other databank files can be located in any of the directories in the search path list.

The flexibility achieved by not using full databank file specifications must be weighed against the loss of explicitly specified information from the data dictionary. In addition, the centralized use of mechanisms such as environmental variables or logical names in a complex system requires careful and disciplined management.

In particular, it is necessary for the database server process to have access to all relevant environmental variables and logical names in order to use them when accessing the databanks.


Organizing Databank Files

There are a number of factors involved in the organization of physical databank files that are important to database security and the overall performance of the Mimer SQL system.

Allocating Disk Space

Whenever possible, pre-allocate file space for databanks early in the lifetime of the databank file system.

The databank creation facilities allow the initial size of a new databank file to be specified in terms of the number of Mimer SQL pages. The size of a Mimer SQL page is 2 kilobytes.

The size of the databank file will be extended automatically by the database server during the lifetime of the databank as more space is required for data storage.

UNIX: Under UNIX, the environment variable MIMER_EXTEND can be set to the number of Mimer SQL pages by which all databank files will be extended. The default setting is 128.

VMS: By default, under OpenVMS, databank files will be extended by 1000 OpenVMS blocks at a time. The extend size for a databank file can be altered by using the following DCL command:

$ SET FILE/EXTENSION=extensionsize file.DBF

The databank file must not be in use by the database server or accessed in single user mode when this command is used.

Win: Under Windows, the number of Mimer SQL pages by which all databank files will be extended is determined by the Mimer SQL system and is not configurable.

An attempt to extend a file will fail if the disk is full, the databank attribute MAXZISE is reached, or any imposed disk quota is exceeded.

Having a small file extension size may cause disk fragmentation leading to reduced I/O performance. In addition, if the databank is growing rapidly, the frequently occurring file extension operations may have a negative effect on performance.

A databank file which is created with the size it will actually need in production will be accessed more efficiently than one created with a small initial size and then incrementally extended.

The SQL statement ALTER DATABANK SET FILESIZE can be used to change the size of a databank file to a specified size. ALTER DATABANK DROP FILESIZE is used to shrink the database file as much as possible. The attributes MAXSIZE, MINSIZE and GOALSIZE can also be used to manage the databank file size. Refer to the Mimer SQL Reference Manual, ALTER DATABANK, for details.

Mimer SQL databank files are organized internally into 2, 16 and 64 kilobyte databank blocks.

Accessing an internal databank block which is physically split over two or more distinct areas of allocated disk will require two disk read operations.

To avoid the risk of fragmenting the internal databank blocks, ensure that the number of disk blocks allocated for databank file extensions maps onto a whole number of 64 kilobyte databank blocks.

This will optimize disk I/O efficiency.

VMS: Disk blocks under OpenVMS are 512 bytes in size, therefore a disk cluster size which is a multiple of 4 will avoid fragmenting the 2 kilobyte databank blocks. The cluster size is set when formatting a disk.

Use the following command to check the cluster size of a disk that is already formatted:

$ SHOW DEVICE/FULL

Win: On Windows machines, disk clustering effects are hardware dependent and are not configurable.

Disks are typically configured in terms of an even number of 512 byte or 1024 byte disk blocks and will therefore always work efficiently with Mimer SQL databank files.

Use of disk defragmentation utilities may improve performance for large block
I/Os.

Protecting Data against Loss

For data security reasons, in case of a disk failure, it is strongly recommended that LOGDB is located on a disk unit that is physically separate from that on which the other databanks are located. See Background Information for more information.

Ideally, TRANSDB and LOGDB should always be located on different physical disks which are served by separate disk controllers and no other databank files should be located on either disk.

The ordinary maintenance procedures for any computer system must involve backup and restore. A strategy, structure and procedure must be set up to include the Mimer SQL databases in the system backup routines. See Backing-up and Restoring Data for a detailed discussion of backup and restore.

Note: A system without a complete and valid backup and restore procedure runs the risk of losing valuable data.

Balanced I/O

If several physical disk units are available, the various databanks should be distributed across the available disk units in order to balance the system I/O load.

To optimize the distribution of I/O across disks, place databanks on physical disks in such a way that databanks which are likely to be accessed at the same time are on different disk units.

It is generally the case that TRANSDB will be accessed at the same time as other databanks during a transaction.

Reserved Directories

The structure of the databank file system and procedures such as backup and restore are generally simplified if databank files are placed in directories reserved solely for that purpose. The system administrator should create and maintain a directory structure that best suits the local system.

It is very common practice to reserve entire disks for databanks to allow for the ultimate size of the files.

Other Performance Issues

The placement of databanks on physical disk units will depend on exactly how they will be used when the database system is in operation.

The following issues generally have a more significant effect on database performance than the disk I/O factors relating specifically to physical layout of the Mimer SQL database:

  • the amount of virtual memory paging
  • the speed of the disk
  • the involvement of unnecessary network communication.

For example, to enhance performance, frequently accessed databanks such as TRANSDB may be placed on separate, high performance disks and sufficient memory should be allocated to avoid paging.


Altering Databank Locations

User databanks may be relocated by moving the physical file using operating system commands and then changing the file location stored in the data dictionary by using the ALTER DATABANK statement to specify the new file specification, see the Mimer SQL Reference Manual for the statement syntax.

The ALTER DATABANK statement may only be issued by the owner of the databank.

Example:
  1. Disconnect the databank from the system.
    2. SQL> SET DATABANK databank_name OFFLINE;
  2. Move or copy/delete the databank file to its new location.
  3. Alter the databank file name in the data dictionary and reconnect the databank to the system.
    4. SQL> ALTER DATABANK databank_name SET FILE ´new_filename´;
SQL> SET DATABANK databank_name ONLINE PRESERVE LOG;

Facilities for changing the file specifications stored in the data dictionary for the system databanks, other than SYSDB, are provided by the BSQL program when a system databank is inaccessible, see Re-creating TRANSDB, LOGDB and SQLDB.

SYSDB must always be located in the home directory for the database.

The location of a databank cannot be altered while the database server is accessing it or while it is being accessed in single-user mode.

Note: You cannot move databanks between databases by copying the databank file and using the facilities to alter the databank location recorded in the data dictionary. You must use the LOAD command. See Loading and Unloading Data and Definitions.

UNIX: Databases on UNIX platforms may be set up with a directory search path instead of a single home directory, see Locating Databank Files.

A databank created without specifying the directory in the file specification may be moved between any of the directories in the search path list without the need to alter anything in the data dictionary. Before being moved, the databank should be set offline to ensure that the file is not locked by the database server.


Accessing Databank Files

The databank files in a Mimer SQL database are accessed by the database server regardless of the user running the applications. The operating system privileges that apply to accessing the databank files are associated with the database server.

If a Mimer SQL database is accessed in single-user mode, see Executing in Single-user Mode, the user must have the appropriate operating system level privileges in order to access the databank files.

Ownership of the databank files should not be confused with the creator of the databanks, which is internal to the Mimer SQL data dictionary. It is quite possible that a user who has created databanks is denied direct access at the operating system level to the files for those databanks.


Databank File Deletion

If a databank or shadow is dropped, the corresponding file will also be deleted from disk. Remember that dropping a Mimer SQL ident will also drop all objects, including databanks, that the ident has created.

When a databank is dropped, all shadows of the databank will also be dropped.

Note: If the databank is OFFLINE when it is dropped, the databank file (and any shadow files) will remain on disk in the file system and must be manually deleted.


Transaction Control

Transaction control provides a means of protecting the database from corruption which might arise from two users attempting to the change the same information at the same time and also provides the basis for ensuring database consistency, see Database Consistency.

Optimistic Concurrency Control

Mimer SQL transaction management uses Optimistic Concurrency Control (OCC), which is described in the Mimer SQL Programmer's Manual, Transaction Handling and Database Security.

This type of concurrency control overcomes many of the problems that can occur with conventional locking techniques (e.g. deadlocks and locks being retained by defunct connections). Superior performance is achieved because there is no need for the overhead of a deadlock detection mechanism, since deadlocks cannot occur.

Transaction Phases

A transaction is an atomic operation. Atomic means that all the changes that form the transaction are applied to the database, or none of them are applied.

Three transaction phases exist: build-up, during which the database operations are requested, prepare, during which the transaction is validated, and commitment, during which the operations performed in the transaction are written to disk.

The transaction begins by taking a snapshot of the database in a consistent state.

During build-up, changes requested to the contents of the database are kept in a write-set and are not visible to other users of the system. This allows the database to remain fully accessible to all users. The application program in which build-up occurs sees the database as though the changes had already been applied. Changes requested during transaction build-up become visible to other users when the transaction is successfully committed.

During build-up, a read-set records the state of the database as seen at the time of each operation (including intended changes). If the state of the database at commitment is inconsistent with the read-set, a conflict is reported and the transaction is rolled back (i.e. the write-set is erased and no changes are made to the database). This can happen if, for instance, a transaction asks to update a row which is deleted by another user after build-up has started but before the transaction is committed. The application program is responsible for taking appropriate action if a transaction conflict occurs.

Transaction control behavior in application programs and a number of the system facilities, notably Backup and Restore - see Backing-up and Restoring Data, is controlled at the databank level by setting the option (LOG, TRANSACTION, WORK or READ ONLY) for the databank.

Only operations performed in databanks set up with the LOG option are logged in the Mimer SQL system databank LOGDB. Write operations against tables in LOG and TRANSACTION databanks must be performed under transaction control (i.e. within a transaction).

Refer to the Mimer SQL Programmer's Manual, Transaction Handling and Database Security for more information.


Database Security

Mimer SQL supports a sophisticated system of access rights and privileges, which permit detailed control of database security.

The main components of the database security system are:

  • idents
  • system, object and access privileges
  • restriction views.
  • PSM routines

The Role of Idents in Database Security

Access to the Mimer SQL system as a whole is managed through the use of idents and privileges.

Careful advance planning of the hierarchical structure of idents in the database is vital to the long-term viability of the system. A poorly planned ident structure can easily become impossible to follow and control after a relatively short period of system use.

SYSADM

The Mimer SQL installation process creates one user ident, for use in database administration, with the name SYSADM.

The SYSADM ident has all the system privileges (BACKUP, DATABANK, IDENT, SCHEMA, SHADOW and STATISTICS - see System, Object and Access Privileges, with the ability to grant these privileges to other idents, i.e. the privileges are held with the WITH GRANT OPTION.

The SYSADM ident also has SELECT access on all tables in the data dictionary, again, with the WITH GRANT OPTION. The SYSADM user is ultimately responsible for the structure of the whole system.

Re-creating system databanks can only be done by SYSADM, however, in other respects SYSADM is just an ordinary USER ident in the system.

It is quite possible, and may be advisable, especially in large systems, that SYSADM does not have access to the actual contents of the database; the database administration role should be concerned with objects in the system, not the actual data.

Public Group

All idents created in the system automatically belong to a logical group (specified using the keyword PUBLIC in Mimer SQL statements) which is intended to be used for granting global privileges.

Guidelines for Structuring Idents

The following general recommendations are made for structuring the idents in a system:

  • Create PROGRAM idents for functional roles within the system. These are not coupled to any physical individual or group of individuals and thus have a lifetime independent of the turnover of personnel. (Database administration is an example of a functional role, but it is represented by a user ident rather than a program ident for practical purposes - see Idents for details on idents).
  • Create USER or OS_USER idents for physical users of the system. These may be dropped when the person concerned should no longer have access to the database. Do not grant privileges directly to user idents, other than membership to groups. Administration is much simpler if privileges are granted through groups.
  • Use GROUP idents to represent logical classes of users in the system. Grant privileges to groups rather than to individuals. This makes the granting of access rights to the system easier to organize and a clearer overview of the privilege structure within the system is maintained. It also means that new idents can be granted suitable privileges efficiently through membership in one or more groups.
  • Grant the privilege to create objects (DATABANK, IDENT and TABLE privileges) to program idents only. In this way, individual USER idents may be dropped with no cascade effects (see Cascade Effects Between Privileges). (Creation of domains requires no special privilege and may thus be performed by any ident with a schema. Creation of views requires only SELECT access to the table on which the view is based).
  • Use the WITH GRANT OPTION sparingly and try to minimize the number of levels in the ident hierarchy. This reduces the risk of cascading revocation of privileges, see Cascade Effects Between Privileges.

If these recommendations are followed, the maintenance of the ident structure in the system will be much more straightforward. Access to the contents of the database will be granted to relatively few GROUP idents instead of many individual program or user idents.

When a physical individual should no longer have access to the database, the corresponding USER ident can be dropped with no cascade effects.

System, Object and Access Privileges

Each ident is given privileges within the system which determine the operations the ident is permitted to perform.

Note: In addition to holding any relevant privilege(s), an ident must also be the creator of at least one schema before the ident is able to create private database objects (i.e. domains, functions, indexes, modules, procedures, sequences, synonyms, tables, triggers and views) - see Schemas.

Privileges may be granted either directly or by making the ident a member of a GROUP ident. The privileges are classified as follows:

System Privileges

System privileges give the right to create global objects in the database. There are the following system privileges:

System Privilege
Description

BACKUP

gives the right to perform backup and restore operations

DATABANK

gives the right to create databanks

IDENT

gives the right to create idents and schemas

SCHEMA

gives the right to create schemas

SHADOW

gives the right to create shadows and perform shadow control operations

STATISTICS

gives the right to execute the UPDATE STATISTICS statement.

System privileges are granted to SYSADM at installation time and may be passed on to other idents with or without the WITH GRANT OPTION.

An ident receiving a privilege with the WITH GRANT OPTION may pass the privilege on to another ident.

Object Privileges

Object privileges give rights associated with certain specified objects in the system. There are the following object privileges:

Object Privilege
Description

TABLE

gives the right to create tables in a given databank

SEQUENCE

gives the right to create sequences in a given databank

EXECUTE

gives the right to execute a function, procedure statement, or the right to enter (become) a specified program ident

MEMBER

makes an ident a member in the specified GROUP

USAGE

gives the right to specify the named domain where a data type would normally be specified (in contexts where use of a domain is allowed) or the right to use a specified sequence or collation.

Object privileges are initially, automatically, granted only to the creator of the object (e.g. the creator of a databank automatically has TABLE privilege on the databank).

The privileges may be passed on to other idents with or without the WITH GRANT OPTION.

Access Privileges

Access privileges give rights of access to the contents of a specified table or view. There are the following access privileges:

Access Privilege
Description

SELECT

gives the right to read the table contents

INSERT

gives the right to add new rows to the table (this privilege may be limited to specified columns within the table)

DELETE

gives the right to remove rows from the table

UPDATE

gives the right to change the contents of existing rows in the table (this privilege may be limited to specified columns within the table)

REFERENCES

gives the right to use the primary or alternate keys of the table as a foreign key from another table (this privilege may be limited to specified columns within the table).

In addition to the five access privileges listed above, the keyword ALL may be used as a shorthand method of specifying all the privileges possessed by the granting ident. For example, if an ident has only SELECT and UPDATE privileges on a table and ALL is granted on that table to a new ident, the new ident will only be given SELECT and UPDATE.

Access privileges are initially granted to the creator of the table with the WITH GRANT OPTION. The privileges may be passed on to other idents with or without the WITH GRANT OPTION.

Certain operations are not controlled by explicit privileges, but may only be performed by the creator of the object involved. These operations include ALTER (with the exception of ALTER IDENT, which may be performed by either the ident itself or by the creator of the ident), DROP and COMMENT. Privileges may only be explicitly revoked by their grantor, however cascade effects may go wider.

Cascade Effects Between Privileges

Dropping an object from the database or revoking a privilege from an ident may have cascade effects on other objects and idents, depending on the way the database is organized.

The keywords CASCADE and RESTRICT may be used in the DROP and REVOKE statements.

When using RESTRICT (the default), the operation will fail with no changes being made if any cascade effects result from it.

When using CASCADE, the following operations have the consequences described:

  • If an ident is dropped, all objects created by the ident are dropped and all privileges granted by the ident are revoked.
  • If a databank is dropped, all tables in the databank are also dropped.
  • If a table is dropped, all views and synonyms based on the table are dropped. Also, triggers and routines that references the table are dropped.
  • If a privilege with the WITH GRANT OPTION is revoked from an ident, all instances of that privilege granted to other idents under the authorization of that WITH GRANT OPTION are also revoked. The WITH GRANT OPTION can be revoked separately.
  • If SELECT privilege on a table is revoked from an ident, views created by the ident under the authorization of that SELECT privilege are dropped.

If DATABANK privilege is revoked from an ident, existing databanks created under that privilege are not dropped.

The cascade effects of revoking privileges only occur when the last instance of the privilege is revoked (a new instance of the privilege is created each time the privilege is granted to the same ident on the same object). An ident grants privileges, creates views and so on under the authorization of the most recently received valid instance of the WITH GRANT OPTION, SELECT or other relevant privilege.

The data dictionary keeps a record of the specific instance of an authorization under which an operation was performed. The cascade effects apply only to privileges granted or objects created under the specific instance of the authorization which is being revoked.

This is illustrated in the example cases that follow:

CASE 1
  1. A grants with grant option to M

    2. M grants to X

  2. B grants with grant option to M

    3. M grants to Y

  3. A revokes from M

    4. Both X and Y keep privileges

  4. B revokes from M

    5. Both X and Y lose privileges

CASE 2
  1. A grants with grant option to M
  2. B grants without grant option to M

    3. M grants to X

    M grants to Y

  3. A revokes from M

    4. M loses grant option

    Both X and Y lose privileges

  4. B revokes from M

    5. M loses privilege

Restriction Views

Views are a powerful tool for restricting user access to specific parts of the database and they complement the use of access privileges in maintaining database security.

By defining restriction views (i.e. views based on one table but restricted only to specific rows and/or columns in the table), access may be provided to a subset of the contents of a table without affecting the physical database structure. In this way, the database may be designed optimally according to the relational model, while user access can be defined according to actual data retrieval requirements.


Data Integrity

The following facilities are available for ensuring the integrity of a Mimer SQL database:

  • domains
  • entity integrity (non-NULL primary keys)
  • referential integrity (foreign keys)
  • table integrity
  • view integrity

Domains

Domains define sets of permissible values. By assigning a table column to a domain when the table is created or altered, the values which the column may contain are restricted to those defined in the domain. Any number of columns may use a given domain.

The ident defining a table column must hold USAGE privilege on any domain used.

A default value may also be defined for a domain. The domain default value is inserted into a column defined using the domain when data is inserted without an explicit column value being specified.

If the default value for the domain is defined outside the range of restriction values for the column, attempts to store the default value in a column using the domain will fail. In such a case an explicit value must always be specified when inserting data into the column.

The use of domains in table definitions is recommended, since this can provide an automatic check on the validity of data inserted into the column. However, domain definitions should be carefully planned, since a domain definition cannot currently be altered after it has been defined.

Entity Integrity

Entity integrity refers to the requirement that every row in a table must be uniquely identified and that no row in a table may be identified by NULL (i.e. by an unknown value).

Entity integrity can only be enforced if a primary key constraint or unique constraints are applied.

All primary key columns in tables created by Mimer SQL are defined as NOT NULL, thus ensuring entity integrity. Other (i.e. non-primary key) columns may also be defined as NOT NULL as required.

Referential Integrity

Referential integrity refers to the requirement that data entered into a table in the database must already be present in another table (e.g. a component may not be entered in a parts list if it does not already exist in the set of known components in the database).

Mimer SQL supports referential integrity through the FOREIGN KEY clause in the CREATE TABLE statement.

The properties of a FOREIGN KEY are as follows:

  • The columns defined as a foreign key must correspond exactly in number and data type to the primary key or unique key columns in the referenced table.
  • Data inserted into the foreign key columns (by either INSERT or UPDATE operations) must either already be present in the primary key or a unique key of the reference table or include at least one NULL column.
  • A primary or unique key value that is referenced by a foreign key must not be removed by an update operation. It may be possible to remove such a value with a delete operation provided the ON DELETE rule is used to update the referencing table in a way that preserves the referential integrity.

    • Note: Referential integrity constraints are effectively checked at the end of the INSERT, DELETE or UPDATE statement. Both the table containing the foreign key reference and the referenced table must be stored in a databank with either the TRANSACTION or LOG option.

Table Integrity

Table integrity refers to the facility in Mimer SQL of defining CHECK clauses in a table definition, whereby the contents of one column is checked against the contents of one or more other columns in the same row of the table.

Data may only be entered into the table if the CHECK constraint is not violated.

View Integrity

View integrity refers to the facility in Mimer SQL of including a WITH CHECK OPTION clause in a view definition. If a view is defined with a WITH CHECK OPTION, data which violates the definition of the view may not be entered into the view by INSERT or UPDATE operations.

When a view is defined with a CHECK OPTION, any views defined on that view will inherit the CHECK OPTION.


Creating a Mimer SQL Database

Once the Mimer SQL Engine software is installed, the database environment must be created. This involves the following activities:

  • registering the name of the database on each node in the network from which it is to be accessed.
  • generating the Mimer SQL system databanks SYSDB, TRANSDB, LOGDB and SQLDB as well as the database administration ident called SYSADM.
  • creating idents and data objects in the database using the data definition statements in Mimer SQL.


Registering the Database

In a network environment, the name of a database must be registered on each node from which it is to be accessed.

A database is created as a local database on the node where it resides and it is defined as a remote database on each other node in the network from which access to it is required.

UNIX + VMS: On UNIX and OpenVMS nodes, the name of a database is registered by creating an entry for it in the SQLHOSTS file. See The SQLHOSTS File on VMS and UNIX for details about this file.

All users must have read access to the SQLHOSTS file on the machine they are using in order to run applications and utilities accessing Mimer SQL databases.

Win: On a Windows node, the name of a database is registered by running the Mimer Administrator. The Mimer Administrator adds information about Mimer SQL databases to the Windows registry. Refer to the online Windows help provided with the Mimer Administrator for details on how to use it.


The Local Database

A local database is one that resides on the machine where its database server executes (i.e. the system databank file containing the data dictionary exists on a local disk).

A local database definition registers the database by specifying a name (which is not case sensitive) and a home directory for the database.

It also involves specifying various parameters which configure the database server that is started for the database.

UNIX + VMS: The definition of a local database under UNIX and OpenVMS involves specifying the database name and the database home directory in the SQLHOSTS file, see The SQLHOSTS File on VMS and UNIX.

Parameters that control the database server are specified in the MULTIDEFS file which is located in the database home directory, see The MULTIDEFS File on VMS and UNIX for details about this file and the parameters it contains.

The MULTIDEFS file is automatically created with appropriate default values for all parameters when the database server is first started.

Win: The definition of a local database under Windows is created by running the Mimer Administrator and specifying the required parameters, including those that control the database server, which are stored in the Windows registry.

On-line Windows help is provided with the Mimer Administrator to guide you through the creation of a local database. Default values are supplied for all parameters except the database name and home directory.

A fully created local database, complete with its Mimer SQL license key, see Mimer SQL License Key, Mimer SQL system databanks, see SDBGEN - Generating the System Databanks, user databanks and the ident and data structure contained in them, see Establishing the Ident and Data Structure, constitutes a Mimer SQL database.

A completely created local database can only be accessed from the machine on which it resides. If the database is to be accessed from a remote node, connected to the local machine via a network connection, a remote database definition for the database must be created on the remote node.


Accessing a Database Remotely

In order to access a database that resides on another network node, the database must be created as a local database on the node it resides on and a remote database definition must be set up on the node from which the database is to be accessed.

The purpose of the remote database definition is to define a link with a database that resides elsewhere on the network. The name used for the remote database definition must be the same as that given to the local database it represents.

The definition for the remote database contains the communication parameters required for accessing the database over the network.

UNIX: The definition of a remote database under UNIX involves creating an entry in the /etc/sqlhosts file, see The SQLHOSTS File on VMS and UNIX for information on the parameters involved.

On the database server computer, the mimer service should be defined in
/etc/services and a port dispatcher should be defined in
/etc/inetd.conf.

This is usually done automatically by the dbinstall command. For details, see Appendix D Networking Setup.

VMS: The definition of a remote database under OpenVMS involves creating an entry in the SQLHOSTS file, see The SQLHOSTS File on VMS and UNIX for information on the parameters involved.

Win: The definition of a remote database under Windows is set up by running the Mimer Administrator and specifying the required parameters. On-line Windows help is provided with the Mimer Administrator to guide you through the creation of a remote database.

Client/Server Interface

Once the remote database definition has been set up and provided that the Mimer SQL client/server communications have been established correctly, access to a database that resides on a remote machine is performed transparently.

The Mimer SQL client/server communications interface is integrated into the database server. The database server process manages all connections.

All Mimer SQL applications may use the client/server interface without having to make any special provision in the application code. The client/server interface is automatically activated whenever a remote database is targeted.

The Mimer SQL client/server protocol is identical on all Mimer SQL platforms. This means that a Mimer SQL client on any machine type may access a Mimer SQL server for a remote database on any of the platforms on which Mimer SQL is implemented.


Mimer SQL License Key

To start the database server and to establish connections to the database, a license key is required. (A key valid for development and evaluation only is included in the Mimer SQL distribution.)

Whenever a user connects to a Mimer SQL database, the computer identification and the license key will be checked by the database server to determine access rights. If access is denied, the connect attempt will be aborted and an error message will be shown.

The Mimer SQL license key contains the following (encrypted) information:

  • The maximum number of users that may use the database servers running on the same computer node at any one time.
  • The maximum number of network users that may use the database servers running on the same computer node at any one time.
  • The node name of the computer (in the case of a specific key) or a lifeboat key which is valid for any computer of the platform type for which it was issued (e.g. any UNIX machine).
  • Version number.
  • Expiration date for the key.

The key data is case insensitive and space characters are ignored.

UNIX + VMS: The mimlicense application is used to administrate the license key file. See MIMLICENSE - Managing the license key for information on how to use MIMLICENSE.

Win: The Mimer Administrator is used to enter the Mimer SQL license key. The key is distributed in a .mcfg file.

When you double-click on a .mcfg file, the Mimer Administrator is automatically invoked to install the key.

Refer to the online Windows help provided with the Mimer Administrator for details on how to manually enter the Mimer SQL license key.

The Mimer SQL license key is provided by your Mimer SQL distributor. In order to be able to generate the key, your Mimer SQL distributor must know the node name of the computer on which the database server will run.

UNIX: The host name of a UNIX machine is obtained by using the following UNIX command:

# uname -n

VMS: One of the following methods can be used:

The host name is printed by the MIMSETUP9 command procedure:

$ @disk:[MIMERxxxxx]MIMSETUP9

If the OpenVMS node is part of a cluster, the scsnode parameter describes its name:

$ WRITE SYS$OUTPUT F$GETSYI("SCSNODE")

If the OpenVMS system is not clustered, the node name parameter may be blank. In this case, you should check the SYS$NODE logical name instead:

$ SHOW LOG SYS$NODE

Win: When the dialog box which is used to enter a Mimer SQL license key is opened in the Mimer Administrator, the node name of the computer will be displayed.

Refer to the online Windows help provided with the Mimer Administrator for details on how to open the dialog box.

When the number of Mimer SQL users is increased or new Mimer SQL functionality is added to the site, a new Mimer SQL license key will be provided.

The Mimer SQL license key uses the node name of the computer to link Mimer SQL to the computer it is authorized to run on. This allows for hardware replacement in the event of a failure in the computer system. If a replacement computer is given the same node name as the one it is replacing, the Mimer SQL license key remains valid for the new hardware.


MIMLICENSE - Managing the license key

UNIX + VMS: The mimlicense application is currently available on OpenVMS and UNIX only.

Win: On Windows, the Mimer Administrator is used to administrate the license keys.

The mimlicense application is used to administrate the license key file. Keys may be added, removed or updated by using mimlicense. mimlicense may also be used to list and describe the contents of the key file.

Syntax

The mimlicense program is controlled by flagged information specified on the command-line.

The overall syntax (expressed in long form UNIX-style) is as follows:

mimlicense [--nologo] --file=filename
           [--nologo] --add=hexcode
           [--nologo] --delete=keyid
           --list
           --combined
           --remove

Command-line Arguments

UNIX-style
VMS-style
Function
-a hexcode
--add=hexcode

/ADD=hexcode

Add a license key.

-f filename
--file=filename

/FILE=filename

Add a license key from a .mcfg file.

-d keyid
--delete=keyid

/DELETE=keyid

Delete the specified key.

-r
--remove

/REMOVE

Remove all keys. (Each key must be verified.)

-l
--list

/LIST

List the contents of the key file.

-c
--combined

/COMBINED

Describe what the combined keys permits.

-s
--nologo

/NOLOGO

Silent mode, i.e. execution with no output.

-?
--help

/HELP

Show help text.

UNIX: The UNIX-style command-line flags must be used on a UNIX machine. Both short form arguments (e.g. -r) and long form arguments (e.g. --remove) are supported.

The name for the key file is:

/etc/mimerkey

VMS: Either the UNIX-style or the VMS-style command-line flags may be used on an OpenVMS machine - see the Mimer SQL VMS Guide for more details.

The name for the key file is:

SYS$SPECIFIC:[SYSMGR]MIMERKEY.DAT


SDBGEN - Generating the System Databanks

The Mimer SQL system databanks SYSDB, TRANSDB, LOGDB and SQLDB are generated by the SDBGEN program.

SDBGEN will load the system tables, see Data Dictionary Tables, and defines the data dictionary views detailed in the Mimer SQL Reference Manual.

Note: A databank created for one SYSDB cannot be accessed by using a different SYSDB even if identical data dictionary definitions are created in it.

UNIX: A local database is set up on a UNIX node by running the dbinstall command (see the dbinstall man-page) and SDBGEN is started automatically when required.

The databank files are by default created in the database home directory which is not the ideal arrangement from a security and performance point of view, see Re-creating TRANSDB, LOGDB and SQLDB for guidelines relating to placement and organization.

If there is more than one disk available on the system, it is recommended that directories be created on those disks specifically for locating databanks. When created, the LOCAL definition for the database should be updated in the
/etc/sqlhosts file by changing the single home directory path to a directory path list that includes these directories. The list is colon separated as can be seen in the following example, where the database is called "hotel":

hotel /usr/db/hotel:/extra/db/hotel:/extra2/db/hotel

After this update is made, the database server can be stopped and the selected databank files can be moved from the database home directory to their new locations. When moved, the database server can be started again.

VMS: In order to create the Mimer SQL system databanks for a local database on an OpenVMS node, an entry for the database must be specified in the SQLHOSTS file. SDBGEN should then be executed to create the actual database. See Syntax for information on how to run SDBGEN.

Win: You set up a local database on a Windows node by running the Mimer Administrator. The Mimer Administrator invokes the SDBGEN program in order to create the system databanks when required.

The system databanks are distributed automatically, depending on the number of disks available. On-line Windows help is provided with both the Mimer Administrator and SDBGEN to guide you through setting up a local database.

Setting the Initial Size

The initial size for each of the Mimer SQL system databanks can be specified.

The size for the databanks is specified in Mimer SQL pages. The size of a Mimer SQL page is 2 kilobytes.

Setting Password for System Administrator

The database administration ident SYSADM is also created and a password (passwords are case-sensitive) must be specified for this ident. It should be chosen carefully and changed at appropriate intervals the ALTER IDENT statement.

Caution: Care should be taken to safeguard the SYSADM password, because if it is lost it cannot be retrieved from the system and it is not possible to set a new one.

Syntax

UNIX: On UNIX, the dbinstall utility is used to create the system databanks.

Win: On Windows, the Mimer Administrator is used to create the system databanks.

The SDBGEN command has two purposes. Either to create a new set of system databank files, or to upgrade database files created in an earlier version of Mimer SQL to version 9.3.

The SDBGEN program is controlled by flagged information specified on the command-line.

The syntax (expressed in VMS-style) for creating databank files is as follows:

SDBGEN [/PASSWORD=passw] [dbase] [syssz] [tfn] [tsz] [lfn] [lsz] [sfn] [ssz]

Command-line Arguments

When creating databanks files:

UNIX-style
VMS-style
Function
-p password
--password=password

/PASSWORD=password

Password for SYSADM

dbase

dbase

Database name

syssz

syssz

Size of SYSDB

tfn

tfn

Filename for TRANSDB

tsz

tsz

Size of TRANSDB

lfn

lfn

Filename for LOGDB

lsz

lsz

Size of LOGDB

sfn

sfn

Filename for SQLDB

ssz

ssz

Size of SQLDB

-?
--help

/HELP

Show help text.

If the password parameter is omitted, the SDBGEN command will prompt for all parameters that are missing, including the password for the SYSADM user.

If the password parameter is given, the SDBGEN command will not prompt for any missing parameters, but use default values.

If the dbase parameter is missing, the environment variable MIMER_DATABASE is used to determine which database the databank files should be created for.

The SDBGEN program syntax for upgrading databank files to Mimer SQL version 9.3 is as follows:

SDBGEN /UPGRADE [dbase]
sdbgen --upgrade [dbase]
sdbgen -u [dbase]


Establishing the Ident and Data Structure

Once the local database environment has been created for a Mimer SQL database (database name, server parameters, system databanks, the SYSADM ident plus the system tables and views), the data structure for the database (idents, user databanks, tables, and so on) can be created using Mimer SQL data definition statements.

Mimer BSQL allows the execution of a sequential file which can then be used as a permanent record of the CREATE statements used to create the database objects, see the Mimer SQL User's Manual, Mimer BSQL.

Mimer BSQL also supports the saving of input and/or output to a log file (using the LOG command), so this facility could be used to create a permanent record of an interactive Mimer BSQL session which could be run again at a later date. Mimer BSQL, however, only has limited support for error handling.

An application program using embedded SQL (ESQL), JDBC or ODBC can also be used, but this requires more work on the part of the programmer and it provides a less concise record of the ident and data structure in the database.

Third party SQL tools are also available which may be used to create the database data structure.

Caution: A sequential file intended for non-interactive execution in Mimer BSQL can include user name and password information relating to any CONNECT statements used. For security reasons, such a file should be well protected in the operating system, preferably with any user name and password edited out of any permanent copy of the file.


The Example Database

An example database is delivered with Mimer SQL. It is owned by the ident MIMER_STORE and contains various objects to be able to describe and demonstrate functionality in Mimer SQL. For details about the example database see the feature description `The Example Database' on the Mimer SQL developer site,
http://developer.mimer.com/features/feature_19.htm.

The example database is based on a store that sells music and books, available in various formats, for example a music album may be available in Audio CD, Cassette, DVD Audio, and Vinyl. The store also has a web site, through which users can place orders. SQL stored procedures (SQL/PSM) support various aspects of the business logic.

The example database forms as the basis for examples given in the Mimer SQL documentation Set

UNIX: On UNIX, the dbinstall utility, used to create the system databanks, provides an option to create the example database

Win: On Windows, the Mimer Administrator is used to create the system databanks. The wizard used for this provides an option to create the example database.

.

The example database can be created manually by using the exload program, described in the next section.


EXLOAD - Creating the example database

By using the exload program the example database can be created or removed. (The example database can also be removed by dropping the MIMER_STORE ident using any SQL command tool).

When the example database is installed default values are provided for passwords on idents and for databank file names. These default values can be changed by using the ALTER command after the installation is completed or by explicitly give names as command line arguments to the exload command, see the exload syntax below.

Syntax

The exload syntax (expressed in short form Unix-style) is as follows:

usage:exload [-s|-m] [-f|-r] [-p SYSADM-password] [database]

       -s, --single         Single-user mode database access
       -m, --multi          Multi-user mode database access
       -f, --force          Forced install (overwrites existing environment)
       -r, --remove         Remove the example environment
       -p passw, --password=passw
                            Password for SYSADM
       database   Name of database to access

       When creating the example database it is possible to change the default
       values for passwords and filenames. This is done by adding parameters
       after the 'database' parameter (that must be given in this case).
       These optional parameters must be added in the following order:

       [MIMER_STORE-password
       [MIMER_ADM-password
       [MIMER_USR-password
       [MIMER_WEB-password
       [MIMER_STORE-databank-filename
       [MIMER_ORDERS-databank-filename
       [MIMER_BLOBS-databank-filename]]]]]]]

All parameters to exload are optional.

Command-line Arguments

UNIX-style
VMS-style
Function
-s
--single

/SINGLE

Single-user mode database access

-m
--multi

/MULTI

Multi-user mode database access

-r
--remove

/REMOVE

Remove the example database

-f
--force

/FORCE

Install the example database even if it already exists. The old environment is dropped.

-p SYSADM-pwd
--password=SYSADMpassword

/PASSWORD=SYSADMpassword

Password for SYSADM

database

database

Database name

MIMER_STOREpwd

MIMER_STOREpwd

Password for MIMER_STORE ident

MIMER_ADMpwd

MIMER_ADMpwd

Password for MIMER_ADM ident

MIMER_USRpwd

MIMER_USRpwd

Password for MIMER_USR ident

MIMER_WEBpwd

MIMER_WEBpwd

Password for MIMER_WEB ident

MIMER_STOREdbfilename

MIMER_STOREdbfilename

Filename for MIMER_STORE databank

MIMER_ORDERSdbfilename

MIMER_ORDERSdbfilename

Filename for MIMER_ORDERS databank

MIMER_BLOBSdbfilename

MIMER_BLOBSdbfilename

Filename for MIMER_BLOBS databank

-?
--help

/HELP

Show help text.

If no command line arguments are given when using exload, the following will happen:

  • A SYSADM password is needed and will be prompted for.
  • Access mode and database name will be taken from MIMER_MODE and MIMER_DATABASE, respectively.
  • If the example environment already is installed a question about how to continue will be raised.
  • Default values are used for all example database passwords and filenames. The values used are displayed when the exload program is executed.


Managing Database Connections

This section describes how users connect to a database and how several simultaneous connections from an application can be handled.

The following SQL statements are used for connection management:

  • CONNECT
  • DISCONNECT
  • SET CONNECTION

See the Mimer SQL Reference Manual, SQL Statement Descriptions for details.

Selecting a Database

Applications establish database connections with the CONNECT statement, which specifies the database by name.

An application may connect to any of the databases which have been made accessible from the node where the application is running, see Registering the Database. Some applications which are part of the Mimer SQL distribution allow the database name to be specified as a command-line argument.

The database may be located on the same machine as the application program (a local database), or on a remote machine accessed over a network (a remote database). The network connection is handled by the Mimer SQL software and this is completely transparent to the application program, see Client/Server Interface.

A database is normally accessed by one or more users via the database server. It is also possible for one user to access a local database directly in single-user mode, provided the database server for it is not running and the operating system user has the appropriate access rights to the database files, see Executing in Single-user Mode.

The Default Database

The default database will be used if the CONNECT TO DEFAULT statement is used, or if the database name in the CONNECT statement is specified as an empty string.

The default database can be any of the local or remote databases that are accessible from the node the application program is running on.

The database that is actually selected by a default connection depends on whether a node-specific or user-specific default database is defined at the time the connection is attempted.

Programs supplied as part of the Mimer SQL distribution (e.g. Mimer BSQL) will use the default database when database is not specified on the command line.

Defining a Node-specific Default Database

One default database can be defined for each node in a network.

UNIX + VMS: The default database for UNIX and OpenVMS nodes is defined by specifying the name of the database in the DEFAULT section of the SQLHOSTS file, see The SQLHOSTS File on VMS and UNIX.

Win: The default database for a Windows node is defined by using the Mimer Administrator to create a System Wide Mimer ODBC Data Source with the name default and associating it with the selected database.

Refer to the online Windows help provided with the Mimer Administrator for details on how to create System Wide Mimer ODBC Data Sources.

Defining a User-specific Default Database

There may be times when an individual user may wish to override the default database defined for the local machine. This is done by defining a user-specific default database, which will be chosen in preference to the node-specific one.

UNIX + VMS: A user-specific default database is defined under UNIX and OpenVMS by setting the environment variable or logical name called MIMER_DATABASE to be the name of the required local or remote database, as stated in the SQLHOSTS file.

If the MIMER_DATABASE variable is set, all default connections will be made to the database it identifies.

If the MIMER_DATABASE variable is not set, default connections will be made to the node-specific default database for the local machine.

Win: A user-specific default database is defined under Windows by using the Mimer Administrator to create a User Specific Mimer ODBC Data Source with the name default and associating this with a database selected by the user. Refer to the online Windows help provided with the Mimer Administrator for details on how to create User Specific Mimer ODBC Data Sources.

When a User Specific Mimer ODBC Data Source exists with the same name as a System Wide Mimer ODBC Data Source, the user-specific one takes precedence.

Troubleshooting Remote Database Connect Failures

If an attempt to connect to a remote database fails, the client/server connection can be tested by starting Mimer BSQL on the client node and attempting to connect to the database on the server node.

In the event of a connect failure, the following should be checked:

  • If the connect was attempting to access the default database, check that a user-specific or node-specific default database is correctly defined on the client node, see The Default Database for details on how this is done.
  • Check that the database been correctly set up as a local database on the server node, see The Local Database, and as a remote database on the client node, see Accessing a Database Remotely, and that the name of the remote database is the same as that of the local database.

    • UNIX: Verify that the inetd daemon is listening to the mimer TCP/IP service by using the netstat -a command.

  • Check that the operating system user who is trying to establish the connection can access all required files etc. on the client node.

    • UNIX + VMS: Check that the operating system user has read access to the SQLHOSTS file on the client machine.

  • Check that the operating system user who is trying to establish the connection has all the required operating system privileges

    • VMS: Check that an operating system user who is trying to use DECNET has TMPMBX and NETMBX privileges enabled.

  • If the TCP/IP protocol is being used, check that the server node is reachable from the client node over the network by using the ping command:
    • ping server_node
  • If the TCP/IP protocol is being used, try to telnet to the TCP/IP port. You should get a connection and when <CR> is entered, the connection should be closed by the server:
    • telnet server_node 1360
NAMED PIPES

Win: If using NamedPipes, the operating system user must have an account set up on both the local machine and on the machine where the remote database resides. Both accounts must be set up with the same password.

If using NamedPipes to connect a Mimer SQL version 7.3 client to a Mimer SQL database server version 9 (or 8), it will be necessary to take certain steps to enable network communication.

Under version 7.3 the expected Service name was MIMER, but since version 8 the expected Service name is the name of the database.

Therefore, one of the following must be performed before a version 7.3 client can communicate with a version 8 (or later) remote database server:

1) On each version 7.3 client node, the Service parameter in the remote database definition must be changed to be the name of the database instead of the name MIMER.

Or

2) On the version 9 (or 8) server node, start a NamedPipes server which listens to service MIMER so that it can redirect communications to the correctly named database server.

If using NamedPipes to connect a Mimer SQL client version 9 (or 8) to a Mimer SQL version 7.3 database server, the Service parameter in the remote database definition on the client node must be changed to the name MIMER instead of the name of the database.


Executing SQL Statements

To execute SQL statements you can use DbVisualizer or BSQL, both included in the Mimer distribution.

The Mimer BSQL program supports a number of command-line arguments. See Mimer SQL System Management Handbook, Mimer BSQL for a detailed description.

The syntax for Mimer BSQL (expressed in UNIX-style) is as follows:

bsql [-u[username]] [-p[password]] [-s|-m] [-qquery] [database]

If neither -s nor -m is specified for the optional mode flag, the way the database is accessed will be determined by the setting of the MIMER_MODE variable, see Specifying Single-user Mode Access, or, if this is not set, it will be accessed in multi-user mode.

Arguments containing more than one word should be enclosed in ". Note that VMS translates arguments to lower case when UNIX-style syntax is used.

.

UNIX: The UNIX-style command-line flags must be used on a UNIX machine.

VMS: Either the UNIX-style or the VMS-style command-line flags may be used on an OpenVMS machine - see the Mimer SQL VMS Guide for more details.

Win: The UNIX-style command-line flags can be used from a command prompt window.


Managing a Database Server

The database server enables one or more users to access a database. Each database may have one database server running against it and the server runs on the machine where the database resides.

The parameters which control a database server and which can be tuned to optimize performance are specified as part of the definition of a local database.

The MIMCONTROL functionality provides facilities for managing the operation of a database server (e.g. starting, controlled shutdown, etc.).

The MIMINFO functionality provides facilities for getting system management information from a Mimer SQL database server, such as:

  • listing details for the users on the system
  • monitoring performance parameters
  • dumping data for trouble-shooting analysis by Mimer support personnel.
  • listing SQLPOOL parameters

Operational and error messages generated by a database server are recorded in the database server log, see Database Server Log.

System Performance

In a Mimer SQL system there are a number of system-wide parameters that have a major impact on the overall system performance.

The most important parameters are the bufferpool size and the number of request and background threads.


Database Server Memory Areas

The database server memory requirements include the following components:

  • Code
  • Data and thread stacks
  • Bufferpool
  • Communication buffers
  • SQLPOOL

Code

The server code requires usually a few Mb, but it depends on the platform.

Data and Thread Stacks

As a rough guideline, assume about 500 Kb data plus 400 Kb for each thread started (the total number of threads started is the number of background threads plus the number of request threads), the actual figures, however, depend on the operating system being used.

Bufferpool

The bufferpool is the main primary memory cache used by the basic data access routines in the Mimer SQL database management and contains data pages from the databank files. It is a local memory area in the database server process.

The bufferpool does not grow dynamically, so whenever the bufferpool is full and access to a new page is required, space is released in the bufferpool by swapping out the least-recently-used resident page.

Frequent page replacement operations detract from the overall system performance since access to disk is relatively slow. The best Mimer SQL performance is thus obtained by having as large a bufferpool as possible without exceeding the amount of main memory available. In practice, it is always necessary to find a suitable compromise between allocation of memory to the Mimer SQL bufferpool and keeping memory available for user applications and operating system tasks.

The size of the bufferpool depends on the parameters Pages2K, Pages16K and Pages64K which are specified as part of the local database definition, see The Local Database.

The amount of memory used by the database buffers can be calculated by:

buffer space in kilobytes = Pages2K*2 + Pages16K*16 + Pages64K*64

Note: The bufferpool contains a variety of other data, therefore the total bufferpool size will be at least 10% greater than the space needed for the database buffers.

The default initial bufferpool size for a database server is based on the memory available on the machine.

Fine tuning of the bufferpool is performed manually by adjusting the parameters in the local database definition, see The Local Database, after the Mimer SQL system is fully installed and has been functional for a period of time. The fine tuning should be repeated whenever there is a significant change in the computer workload distribution.

Since the Mimer SQL bufferpool size affects the performance of both Mimer SQL and other applications (because it reserves memory for a Mimer SQL database server), it is advisable to perform regular routine checks on the bufferpool statistics in an operational system by generating a Performance report, see The Performance Report.

Note: The Windows NT performance monitor can also be used to monitor a database server running on any platform. Refer to the documentation supplied by Microsoft for the Windows NT operating system for details.

Bufferpool Tuning Guidelines

Some general guidelines for bufferpool tuning are:

  • Whenever main memory is available, it should be allocated, if possible, to the bufferpool.
  • Ensure that the bufferpool is not subject to system paging or swapping, since the paging algorithms used by Mimer SQL and the operating system usually differ, and forced cooperation between the two will often detract considerably from Mimer SQL's performance.
  • If more than about 2% of all Mimer SQL page requests result in a page fault, the bufferpool is too small. Statistics for page requests and faults are presented in the Performance report, see MIMINFO - System Information.
  • It is important to take note of the page fault statistics for each region in the bufferpool to ensure that the most appropriate allocation has been made in each.

    • The Mimer SQL system decides which page size is most appropriate for each task to be performed. For example, 16K pages are currently used for transaction data (this may change in the future) and therefore allocating too few 16K pages may currently adversely affect performance even though generous allocations have been made in the other bufferpool regions.

Communication Buffers

Each communication buffer is about 70 Kb, it varies slightly depending on platform.

There is one communication buffer for each user as defined by the Users parameter in the local database definition, see The Local Database.

All communication buffers reside in shared memory.

SQLPOOL

The SQLPOOL area contains information about opened tables and databanks, compiled SQL programs, etc.

The initial size (in Kb) of the SQLPOOL is determined by the SQLPool parameter in the local database definition, see The Local Database.

The SQLPOOL area grows dynamically when the database server needs more space. The local database parameter MaxSQLPool controls the maximum size (in Kb) of the SQLPOOL.

The value for MaxSQLPool is 2000*(Users+RequestThreads) by default.

The SQLPOOL area is never locked in physical memory. This allows the SQLPOOL to grow dynamically and it may become larger than the physical memory allocated to the server process. The operating system generally manages this situation by page-faulting. The page-faults will not affect bufferpool performance if that area is locked in physical memory.

If the amount of operating system page-faulting observed in a database server becomes excessive, it is an indication that the memory required by the server process is much greater than the amount of physical memory allocated to it. In this case, either more memory must be installed on the machine or the local database parameters controlling memory allocation must be adjusted to reduce the memory required by the database server process.


Threads

The Mimer SQL database server process supports a number of separate request threads and background threads, running simultaneously under the operating system.

Number of Request Threads

The amount of concurrency that the database server can support is dependent on the number of available request threads. If there are more concurrent requests than threads, the database server will start scheduling requests to improve response times. Increasing the number of request threads in a situation like this may improve performance.

The number of request threads in a database server is defined at system start-up. A change to the number of request threads requires that the system be stopped and re-started.

The maximum number of concurrent request threads is limited by the size of the bufferpool.

Number of Background Threads

The background threads in a Mimer SQL database server perform tasks such as:

  • Recording transactions in LOGDB.
  • Updating master and shadow databanks.
  • Securing data on disk.
  • Online backup.

See Background Threads for information relevant to fine-tuning the number of background threads.


Database Server System Requirements

From the point of view of the operating system, a database server requires the system resources described in the following sections.

Physical Memory

The amount of physical memory used by the database server process is determined by parameters in the local database definition, see The Local Database, whose initial default values are determined by looking at the amount of installed memory.

VMS: For a database server running on an OpenVMS node the amount of physical memory used by the database server process will vary between the OpenVMS process parameters WSQUOTA and WSEXTENT.

The WSQUOTA parameter is calculated by MIMCONTROL and is set large enough to include the bufferpool, initial SQLPOOL, communication buffers, code, and stack data.

The WSEXTENT parameter is set to the SYSGEN parameter WSMAX (the maximum amount of physical memory a single process may have).

Virtual Memory

The amount of virtual memory that the database server process can use is limited by the operating system.

UNIX: The virtual memory handling on UNIX platforms is platform specific - refer to the documentation for the specific UNIX operating system you are using. (Often a paging file used).

VMS: The MIMCONTROL command sets the paging file quota of the database server so that it is large enough to contain all memory areas, including the bufferpool and an SQLPOOL that has grown to MaxSQLPool kilobytes.

It may be appropriate to create larger page files to increase the amount of virtual memory available to the database server.

Win: If you get a message saying the system is running out of virtual memory you may need to increase the size of your paging file. This done by using the Virtual Memory option in the Performance section of the System control panel.

Global Pages

VMS: The database server creates a global section for its communication buffers. This global section resides on the page file. The amount of memory a global section may take from a page file is generally controlled by an operating system parameter. If this limit set by the operating system is exceeded, the MIMCONTROL/START command will fail with the message:

%SYSTEM-E-EXGBLPAGFIL, exceeded global page file limit

If this happens, the OpenVMS SYSGEN parameter called GBLPAGFIL, which limits the amount of memory that global sections may take from the page files, should be increased.


MIMCONTROL - Controlling the Database Server

MIMCONTROL functionality is supplied on all platforms as a complete administration tool for managing database servers.

UNIX: The database servers on a UNIX node can be controlled using the mimadmin command (see the mimadmin man-page). This command invokes the MIMCONTROL program, and other programs, as required. The MIMCONTROL command can also be used directly under UNIX.

A database server on UNIX can be administered by the owner of it or by the superuser root. To change ownership of a database the mimdbfiles command is used (see the mimdbfiles man-page).

When a database server on a UNIX machine is started for the first time, MIMCONTROL will create a default multidefs file containing appropriate default parameter values, based on the amount of memory installed on the machine. Refer to The MULTIDEFS Parameter File for details.

Database server performance can be fine-tuned later by adjusting the parameters as required.

VMS: The database servers for the local databases on an OpenVMS node are controlled by using the MIMCONTROL command directly (as described in this section).

When a database server on an OpenVMS machine is started for the first time, MIMCONTROL will create a default MULTIDEFS file containing appropriate default parameter values, based on the amount of memory installed on the machine. Refer to The MULTIDEFS Parameter File for details.

Database server performance can be fine-tuned later by adjusting the parameters as required.

Win: The Mimer Administrator can be used to control database servers on Windows platforms.

Database servers are also controlled by using the Mimer Controller utility. Refer to the online Windows help provided with the Mimer Controller for further details. You must belong to the administrators group to control database servers.

It is also possible to use the Windows commands NET START, NET STOP, etc. to control database server processes.

Syntax

MIMCONTROL is controlled by flagged information specified on the command-line.

The overall syntax for MIMCONTROL (expressed in short form UNIX-style) is:

mimcontrol [-bcdegkstwA] [-l chan] [database]

If MIMCONTROL is invoked without any options, it displays help options on the command-line.

Command-line Arguments

UNIX-style
VMS-style
Function
-b
--dcl

/STATUS/DCL

Output status information about the specified database server as a single-line list suitable for use in a script.

For details about the output string resulting from this option, see Database Server Status.

-c
--status

/STATUS

Output status information about the specified database server.

This option can be combined with the
-s option, see Examples.

-d
--disable

/DISABLE

Disable new user connections to the database server. Users already connected are not affected.

This option can be combined with the
-s, -t and -w options, see Examples.

-e
--enable

/ENABLE

Enable new user connections to the database server.

-?
--help

/HELP

Show help text.

-k
--kill

/KILL

Kill the database server immediately. This should only be used in emergency situations when a normal stop using the -t option does not work.

The next time the database is started, all databanks that were open at the time the server was killed will be automatically checked. Connected users will receive an error the next time they attempt to access the database.

-l chan
--logout=chan

/LOGOUT=chan

Force logout of the specified channel number.

Use channel numbers displayed by the USERS option of the MIMINFO command, see The Users List.

-g
--generate

/GENERATE

Generate a default multidefs file.

Win: This switch is not available in the Windows environment.

-s [timeout]
--start[=timeout]

/START[=timeout]

Start the database server.

If the server does not become operational within the specified number of seconds, the server will be killed.

Default timeout is 600 seconds.

This option can be combined with the
-c and -d options, see Examples.

-t [timeout]
--stop[=timeout]

/STOP[=timeout]

Stop a database server. Any remaining users will be logged out.

If the server does not stop within the specified number of seconds, the server will be killed.

The default timeout is 120 seconds. This option can be combined with the
-d and -w options, see Examples.

-w [timeout]
--wait[=timeout]

/WAIT[=timeout]

Wait for all connected users to log out.

If there are still users connected after the timeout period expires, the command fails.

The timeout period should be given in seconds. If no timeout period is specified wait will be performed without any timeout.

This option can be combined with the
-d and -t options, see Examples.

-A
--dump

/DUMP

Create a dump directory and produce dumps of all internal database server areas to files in that directory.

The files produced can be examined by using the MIMINFO -f command, see MIMINFO - System Information.

[database]

[database]

Specifies the name of the database to access.

If a database name is not specified, the default database will be controlled.

The default database is determined by the setting of the MIMER_DATABASE environment variable.

The DEFAULT setting in SQLHOSTS is not used for MIMCONTROL.

UNIX: The UNIX-style command-line flags must be used on a UNIX machine. Both short form switches (e.g. -s), and long form switches (e.g. --start) are supported.

VMS: Either the UNIX-style (short or long form) or the VMS-style command-line flags may be used on an OpenVMS machine - see the Mimer SQL VMS Guide for more details.

Win: The UNIX-style command-line flags can be used from a Command Prompt window. Both short form switches (e.g. -s), and long form switches (e.g. --start) are supported.

Database Server Status

The mimcontrol -b (MIMCONTROL/STATUS/DCL) command is a special form of the mimcontrol -c (MIMCONTROL/STATUS) command which returns the database server status information in the form of a single string containing a comma-separated list which is useful when writing scripts.

UNIX: On UNIX, the string returned from mimcontrol -b can be piped and processed as required. The UNIX command cut can be used to extract the list elements, e.g. the following command which will print the second list element:

# mimcontrol -b | cut -f2 -d ','

VMS: On OpenVMS, the MIMCONTROL command is silent and sets the DCL symbol MIMER_STATUS to the value of the status string.

The lexical function F$ELEMENT() can be used to extract the list elements, e.g. the following command will extract the second list element:

$LOGINS=F$ELEMENT(1,",",MIMER_STATUS)

Win: On Windows, the string returned from mimcontrol -b can be piped and processed as required, depending on the script language being used.

Status String Components

The status string has the following components:

server-state,logins,db-directory,connections,server-pid,start-date

Each component is described below:

Component
Description
server-state

The server-state value shows the state of the database server. The value is one of: stopped, starting, running, stopping or crashing.

logins

The logins value is either: enabled, if new logins are permitted, or disabled, if the server has been ordered to reject new logins.

db-directory

The db-directory field shows the home directory for the database.

connections

The connections field shows the number of clients connected to the server.

server-pid

The server-pid field gives the process id of the database server process.

start-date

The start-date field gives the date and time when the database server was started.

Note: If the database server is not operational, the status string will contain only the first three fields.

Examples

The parameter options can be combined in the following ways, examples are given in both VMS-style and UNIX-style:

  • Start a database server, but disallow new logins immediately:
    • MIMCONTROL/START/DISABLE db_server_name
mimcontrol -sd db_server_name
mimcontrol --start --disable db_server_name
  • Start a database server and output a status message for the newly started server:
    • MIMCONTROL/START/STATUS db_server_name
mimcontrol -sc db_server_name
mimcontrol -start --status db_server_name
  • Disable new user connections, then wait for up to three minutes for all users to log out.:
    • MIMCONTROL/DISABLE/WAIT=180 db_server_name
mimcontrol -dw 180 db_server_name
mimcontrol --disable --wait=180 db_server_name

The exit code from the MIMCONTROL command is success if all users logged out within the three minute timeout period.

If the timeout period expires and there are still users logged in on the system, the MIMCONTROL command will exit with a warning status code.
  • The following command will wait for all users to log out of the system:
    • MIMCONTROL/WAIT/STOP db_server_name
mimcontrol -wt db_server_name
mimcontrol --wait --stop db_server_name

When all users are logged out, the system will be stopped. If the wait timeout period expires, the MIMCONTROL command will exit with a warning status code without stopping the system.
  • The following command is similar to the previous one, but will ensure that no new users log in to the system while waiting for all users to log out:
    • MIMCONTROL/DISABLE/WAIT/STOP db_server_name
mimcontrol -dwt db_server_name
mimcontrol --disable --wait --stop db_server_name

Exit Codes

MIMCONTROL returns a status code to the environment executing the command. The status code can be examined by scripts.

VMS: On OpenVMS, the status codes correspond to the OpenVMS condition code severity levels.

Use the $SEVERITY symbol in DCL command procedures.

The following return codes are used:

UNIX/Windows
VMS
Usage

0 (success)

1 (success)

This code is used when the MIMCONTROL command has executed all options with no problems.

1 (warning)

0 (warning)

The warning code is used when there was a timeout in one of the options. The complete sequence of options may not have been executed.

> 1 (error)

2 (error)

The error code is used when the specified command could not be executed at all. For instance, if there was an illegal combination of options, or if the specified database name was not found.

If an error status code is returned, an informational error message will also be produced.


MIMINFO - System Information

MIMINFO is used to obtain information from a Mimer SQL database server which is useful for system control, system tuning and trouble-shooting analysis.

Information can be generated from an active Mimer SQL database server as well as from the SQLPOOL and bufferpool dump files produced by using MIMCONTROL, see MIMCONTROL - Controlling the Database Server.

The output from MIMINFO can be displayed on the screen and may also be directed to a file.

The following reports may be obtained from MIMINFO (further details on each report can be found in the sub-sections that follow):

Report
Description

Users list

this lists details of all the users currently connected.

Performance report

this provides information useful for monitoring performance parameters (MIMSERV).

Bufferpool report

this produces a report which is useful to Mimer SQL support personnel when investigating system problems (MIMDUMP).

SQLPOOL report

displays SQLPOOL parameters

Syntax

The MIMINFO program is controlled by flagged information specified on the command-line.

The overall syntax for MIMINFO (expressed in short form UNIX-style) is:

miminfo [-o file]  -u | -p | -s   [database]
miminfo [-o file]  -u | -p | -s | -m   -f

Command-line Arguments

UNIX-style
VMS-style
Function
-o file
--output=file

/OUTPUT=file

Send output to the specified file instead of to the screen

-u
--users

/USERS

Display users list

-p
--performance

/PERFORMANCE

Produce Performance report (MIMSERV)

-m
--mimdump

/MIMDUMP

Produce Bufferpool report (MIMDUMP)

-s
--sqlpool

/SQLPOOL

Display SQLPOOL parameters

database

database

Take information from the specified database.

If a database name is not specified, the default database will be accessed, see The Default Database.

-f
--file

/FILE

Take information from a dump file (for a users list, a dump file called sqlpool.mdmp is expected to exist otherwise a dump file called bpool.mdmp is expected to exist)

-?
--help

/HELP

Show help text.

UNIX: The UNIX-style command-line flags must be used on a UNIX machine. Both short form arguments (e.g. -u), and long form arguments (e.g. --users) are supported.

VMS: Either the UNIX-style or the VMS-style command-line flags may be used on an OpenVMS machine - see the Mimer SQL VMS Guide for more details.

Win: The UNIX-style command-line flags can be used if the miminfo program is run from a Command Prompt window. Both short form arguments (e.g. -u), and long form arguments (e.g. --users) are supported.

The shortcut Mimer Info can also be used to run the program and interactive selections can then be made in the program.

A detailed description of each of the MIMINFO reports follows.

The Users List

A users list can be generated from an active database or from a dump file produced using MIMCONTROL.

miminfo [--output=file]  --users  [database] | --file

The users list shows the name of each ident connected to the database, the channel number used by the connection, the state of the connection, transaction number, the name of the operating user, the network communication protocol (or `local') and node identification information for connected machine.

The channel number may be used in conjunction with MIMCONTROL to kill a user.

The following is an example of a users list report:

Username        Channel State  Trans. no OS user      Prot   From
============ ========== ===== ========== ============ ====== ====
SYSADM            16387 Busy           3              TCP    204.71.200.67
SYSADM            16388 Busy             STELLA       Local  00019120

Total of 2 users

The Performance Report

The performance report can be used by the system administrator to monitor performance parameters during Mimer SQL use. The Performance report can be generated from an active database or from a dump file produced using MIMCONTROL.

miminfo [--output=file]  --performance  [database] | --file

The performance report presents five kinds of statistical information which may be useful for system tuning (statistics for page management, transactions, background threads, databank and table usage).

Note: When a performance report is used as an aid to system tuning, it is important that the report is generated when the database is in full use. The output from several executions over a period of a few hours or days can provide valuable information on fluctuations in system usage.

The performance report contains the following information:

  • General Statistics
  • Page Management Statistics
  • Transaction Management Statistics
  • Background Threads
  • Databank Statistics
  • Table Statistics
General Statistics

The following table lists the general statistics information available. Where applicable, we have provided a detailed description

Statistics
Description
Current date and time

Current MIMER/DB version

Starting date and time

Current hardware and operating
system

Name of database

System status

If the database server is in an error state, a database dump is usually made automatically. It can be made manually by using the -A option with MIMCONTROL. The dump directory created should be saved for use by Mimer SQL support personnel. The database server can then be restarted.

The database server log, see Database Server Log, should also be inspected to help find the cause of the failure.

Error count

Number of errors that have been written to the database server log. This value should normally be zero.

No. of request threads

Number of request threads started, see Number of Request Threads.

No. of background threads

Number of background threads started, see Number of Background Threads.

No. of I/O threads

Number of I/O threads (typically zero on most machines where separate threads are not needed for I/O processing).

Page Management Statistics

Statistics
Description
No. of pages written to
disk

An indication of the frequency of disk update operations.

No. of file extend
operations

The total number of times databank files have been dynamically extended since the latest startup. The value should preferably be as low as possible for performance reasons.

It is possible to check databank size usage with the DESCRIBE command in Mimer BSQL. A databank can be extended by using the commands:
ALTER DATABANK ADD ... or ALTER SHADOW ADD ....

Buffer size 2K (16K, 64K)

The bufferpool is divided into a region with 2K buffers, one region with 16K buffers, and one region with 64K buffers.

The following information is given for each region:

Statistics
Description
No. of page buffers

This is the number of page buffers allocated to this bufferpool region.

No. of page buffers per
sorter

Total number of Mimer SQL pages that a request thread performing a sort operation may utilize.

No. of remaining sorters

The initial value specifies the number of concurrent sort/merge steps that are allowed.

No. of page partitions

Each region in the bufferpool is divided into separate partitions. Each partition can be accessed concurrently by the Mimer SQL request threads. In tightly coupled multi-processor systems it is desirable, for performance reasons, to have at least as many partitions as there are CPUs. The number of partitions may be increased by increasing the region size.

No. of page requests

Total number of access operations to pages in the buffer region since the latest system start-up.

No. of page faults

Total number of page access requests that resulted in disk read operations. If this value is more than about 2% of the total number of page requests, performance may be improved significantly by increasing the bufferpool size.

No. of pages swapped out

Total number of pages which were written to disk when they were swapped out of the buffer region.

Transaction Management Statistics

Statistics
Description
No. of transaction commits

Total number of successful read/write transaction commits since the latest system start-up.

No. of read commits

Total number of successful read-only transaction commits since the latest system start-up.

No. of transaction checks

A high proportion of transaction checks in relation to the total number of transactions may indicate ill-designed application programs, with long transactions that are more likely to give rise to transaction conflicts.

No. of transaction aborts

Total number of transactions aborted by the optimistic concurrency protocol since the latest system start-up. User requested transaction aborts are not counted.

No. of pending restarts

This is an indication of how much information is stored in TRANSDB. Number of restarts is counted for each databank used in a transaction. This figure grows larger when shadows are set offline. If all databanks have been accessed and there are no offline shadows there should not be any pending restarts.

Background Threads

Statistics
Description
SWA

Background thread identifier.

State

State of the background thread. If the background thread is currently working with a transaction, active is displayed.

If the background thread is not doing anything, inactive is displayed. I/O processing means that the background thread is flushing one or more transactions to disk.

unused means that the background thread is allocated but not currently running (i.e. the thread is not started or closed down).

Trans-no

The number of the transaction currently being processed.

Trans-count

The number of transactions processed by the background thread.

Pending background thread
requests

This indicates how many transactions have not yet been processed by the background threads. If there are too few background threads this value will grow.

Application waiting for
trans-no

For certain operations (SET DATABANK OFFLINE, for example) the application has to wait for the background threads to complete their operations.

If there are too few background threads, it may take some time before this operation is complete. By comparing this trans-no with the trans-no being handled by the background threads it is possible to see how many transactions are left before the operation is completed.

Databank Statistics

Statistics
Description
Name

The name of the databank or shadow.

DBANKID, SEQNO

Databank identification. These two values correspond to the columns DATABANK_SYSID and DATABANK_SEQNO in the data dictionary table SYSTEM.DATABANKS.

Type

The databank option WORK, TRANSACTION, LOG, or READ ONLY. See Re-creating TRANSDB, LOGDB and SQLDB.

The SQLDB databank has the type TEMPORARY, and shadows have the type SHADOW.

Users

Internal user count.

Access

Access mode by which the databank was opened. The possible values are: Read, Write, Shared and Exclusive.

If the databank is open but not referenced by any active statement, None is displayed.

DB-Check

The DB-Check field indicates the progress of a databank check. The possible values are Init, Working (foreground processing, typically index check), Wait B. (foreground ready, waiting for background entrance), Backgr. (background processing), Aborting, Aborted or Complete.

Note: After the DB-Check field, a field for additional information may show up. The values here can be the shadow state: offline, or the online backup states: backup in progress or backup completion.

No. of databanks currently
open

A count of both databanks and shadows opened in the system.

Max number of databanks
open concurrently

This is defined by a parameter in the local database definition, see The Local Database, or possibly by a limit in the operating system.

Databank verification
count

Databank verification is automatically performed when a databank is re-opened without having been correctly closed. Each time this happens a log entry is written to the database server log. When a databank is verified the databank verification count is incremented. The count is cleared when the system is started, and a databank is only verified once per session.

Running background
verifications...

Indicates the number of active databank verifications.

Pending background
verifications...

Indicates the number of active databank verifications.

Databank verification is
only done on index pages...

Indicates the databank verification mode as defined in the local database definition, see The Local Database

Databank verification is
performed on all pages...

Indicates the databank verification mode as defined in the local database definition, see The Local Database

Table Statistics

Statistics
Description
No. of tables currently
open

This shows the number of tables open in both master and shadow databanks. Also included are the read and write sets used by each user.

Max number of tables open
concurrently

This number is set as a parameter in the local database definition, The Local Database.

Bufferpool Report

The Bufferpool report is used by Mimer SQL support personnel for trouble-shooting when database problems are reported by customers.

miminfo [--output=file]  --mimdump  --file

SQLPOOL Report

SQL pool memory allocated is the amount of memory allocated from the operating system for the SQLPOOL. A part of that memory is in use by the server and is displayed on the row SQL pool memory used.

miminfo [--output=file]  --sqlpool  [database] | --file

The following is an example of an SQLPOOL report:

SQLPOOL report
==============
SQL pool memory allocated (KB):        1656
SQL pool memory used      (KB):         554


Database Server Log

The database server log lists startup and shutdown messages for the database server. It may also contain warning and error messages if such situations have been detected by the database server.

UNIX + VMS: A log file called mimer.log is created when the database server is started for the first time. This file is located in the database home directory.

In addition, you can set the Oper parameter in the MULTIDEFS file to send e-mails containing serious database server messages to relevant people. These messages always go to the system log as well.

For more information, see Appendix C The MULTIDEFS Parameter File.

Win: Database server events are logged in the EventLog which may be examined using the Windows event viewer.


Several Installations on One Machine

UNIX: Under UNIX, a host computer may have several Mimer SQL installations, of the same and different versions, installed simultaneously.

If several Mimer SQL version 9 installations are available, only one of them can be linked to /usr/lib and /usr/bin at the same time.

To access an installation that is not linked to these locations, the environment variables PATH and LD_LIBRARY_PATH (or corresponding to located shared libraries) must be used explicitly.

For more information on environment variables, see Environment Variables.

VMS: Under OpenVMS, a host computer may have several Mimer SQL installations, of the same and different versions, installed simultaneously.

Win: Only one Mimer SQL installation can exist on a computer running Windows.


Backing-up and Restoring Data

This chapter discusses backup and restore of Mimer SQL databanks. Two types of backup procedures are described:

  • System Backup, i.e. backing up the databank files from the host operating system. When using host operating system tools for doing databank file backup, the database server must be stopped in order to keep the database consistent.
  • Online Backup, i.e. using the SQL system management statements. The main advantage of online backup is that the database server can continue to operate (backup operations are performed in the background).

Some of the discussion in this chapter refers to shadowing databases, see Mimer SQL Shadowing, which is an optional Mimer SQL module that allows one or more copies of a databank to exist on different disks. Shadowing provides a high level of protection from disk failure because the system will automatically use a databank shadow if the master databank is lost, thus allowing normal database activity to continue without interruption. Databank shadows also allow a copy of a databank to be temporarily set offline (e.g. to be backed-up) without interrupting normal system use.

Several references to transaction handling are made in this chapter. If you are not familiar with transaction handling in Mimer SQL see the Mimer SQL Programmer's Manual, Transaction Handling and Database Security.

Background Information

A Mimer SQL database consists of a collection of databanks (each in a separate operating system file) containing tables with data used by the applications. The SYSDB system databank contains a data dictionary describing the different objects in the database.

Note: Backup protection for SYSDB is particularly important for protecting the database, since SYSDB contains all information describing the database structure. If SYSDB is lost, the system must be rebuilt from scratch.


Database Consistency

Database consistency is handled on two levels: physical and logical.

Physical consistency means that the tables are readable by Mimer SQL. This is ensured as long as the databank file is not physically damaged.

Logical consistency means that the tables contain valid data. This is ensured by Mimer SQL's transaction handling. All transactions are saved in the TRANSDB databank during build-up and are applied to the databanks when they are committed. To use transaction handling, the databank must be created with the TRANSACTION or LOG option.

Transaction handling makes it possible to ensure that a user cannot commit a read write transaction which has read data that is being concurrently updated by another user. If a transaction is successfully committed then all operations in the transaction are performed. If the transaction is aborted due to a conflict, none of the operations in the transaction are performed.

The tables may be logically inconsistent if Mimer SQL is stopped before all operations in a committed transaction have been performed. At some time after the system is restarted, all uncompleted transactions will be read from TRANSDB for automatic completion. This happens in the background on a per-databank basis, after a databank is first accessed following the restart. Transactions that were not committed before the stop are aborted.

The DBOPEN facility, see Databank Open Function, can be used to open all databanks in one operation and thus achieve transaction consistency quickly.

LOGDB and TRANSDB Importance

Important: The information stored in TRANSDB is vital to keep the database consistent in all circumstances, not only in case of failure.

The LOGDB information will contain data on all the changes made to the databank from the time the backup copy of the databank was taken until the time of the disk crash. This information is used if a backup copy of a databank file is to be restored.

Note: Data changes that are not logged cannot be restored by this process, therefore it is important to consider the issue of transaction logging carefully.

If a databank becomes unavailable (because the Mimer SQL system is stopped deliberately or by a system failure) during the commitment of a transaction, information is retained in the TRANSDB system databank and used to complete the transaction when the databank becomes available again.

This is only true for databanks with the TRANSACTION or LOG option. Once information has been successfully written to both LOGDB and the databank file, it is removed from TRANSDB.

It is recommended practice to back up 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, because the LOGDB information provides the transaction data which links the previous backup copy of a databank with the databank as it exists at the current point in time.

Thus, when restoring a databank it should be brought to a state consistent with the latest backup. This is done either by using the latest backup copy of the databank or by using backed up LOGDB information with an older backup copy of the databank.

The current LOGDB system databank is then used to restore the final changes made between the time of the latest backup and the time the databank was lost.

Example

The graphic above describes a scenario which ends up in a system crash.

To recover from this situation the common operation is to start from the most recent backup (T2) and then use the current LOGDB to recover data up to the state at T3.

When the system is restarted, the current TRANSDB is used to automatically recover up to the moment of the crash.

If the most recent backup cannot be used, an older backup has to be brought in (T1). This backup is restored up to the consistent state at T2 by using the LOGDB stored in the backup at T2.

Then the current LOGDB and TRANSDB are used to restore the transactions committed after the backup at T2.

Note: Wherever possible, LOGDB should be stored on a different disk unit, with a separate disk controller, from the other databanks in order to minimize the risk that a disk crash or damaged disk controller destroys both the log and the other databanks.
LOGDB and TRANSDB should always be located on different physical disks which are ideally served by separate disk controllers and no other databank files should be located on either disk, since data may be lost if both TRANSDB and LOGDB are destroyed.
Refer to Organizing Databank Files, for more details on data security and databank files.

Updates Recorded in LOGDB

The LOGDB system databank contains logged update information for each databank with LOG option.

It is recommended that all databanks, including LOGDB, are backed up at the same time and that LOGDB is cleared after backup by resetting the log. Thus, the backed up LOGDB will contain the information required to make databanks from the preceding backup consistent with the current backup.

This will provide double backup protection by allowing a lost databank to be recovered in one of the two ways listed below:

  • restore the databank from the most recent backup and apply the updates currently held in LOGDB, or
  • restore the databank from an earlier backup, then sequentially use the LOGDB files from each subsequent backup to make the databank consistent with the most recent backup, and finally apply the updates currently held in LOGDB.

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 changes made to a databank since the last backup of it was taken. (It is possible to backup databanks without clearing LOGDB records, although care must be taken as this leaves the backup and LOGDB in an inconsistent state).

The ability to restore databank updates from a backup copy of LOGDB replaces the databank incremental backups which were supported in previous versions of Mimer SQL. These are still supported for backward compatibility but it is now recommended that LOGDB backups always be taken to offer the same double protection.

Caution: If, for any reason, the LOGDB databank is lost, no problems will be encountered immediately. All changes will have been properly recorded in the application databanks. A new, empty, LOGDB can simply replace the log that was lost.

However, a backup of the entire database must be taken immediately. The new LOGDB will be empty, and therefore in a state consistent with a backup of all databanks having just been taken and all LOGDB records cleared.

If a backup is not taken immediately, a later attempt to restore a databank is likely to fail because the restore operation will expect to find information in LOGDB that was lost when LOGDB was destroyed.

TRANSDB Considerations

TRANSDB requires backup protection since it nearly always contains unfinished transactions. If TRANSDB is lost before the Mimer SQL system is restarted, the database will be left in a logically inconsistent state.

Possible effects of losing TRANSDB before the database server is restarted are described in the following scenarios:

  • If TRANSDB is lost, some of the databank updates that apply to the most recently committed transactions may have been made while others remain unfinished.
    The only safe operation to do to avoid a logically inconsistent database is to bring up the most recent backup copy and restore from LOGDB. In this case, the only loss is those transactions that were not completely written to LOGDB.
  • If both TRANSDB and LOGDB are lost, the restoration, as described in the previous bullet, cannot be accomplished.

In the case where a restore is not possible, the best solution is to repair the inconsistency immediately after restarting the database server. This is done by using a tool such as BSQL for manual verification and update of data. This is usually possible if the user who initiated the interrupted transaction can be identified and contacted. (Many applications maintain a parallel audit log file for tracking purposes which can be used as a basis for repair work).

An alternative solution if both LOGDB and TRANSDB are lost, is to start over from the most recent backup of your databanks and reprocess all transactions since that time. This may be a costly operation.

Keeping TRANSDB and LOGDB on separate disks under separate disk controllers will minimize the risk that both databanks are lost at the same time.

A TRANSDB shadow is another possible security enhancement, see Mimer SQL Shadowing.

Note: The TRANSDB system databank must never be deliberately deleted, because uncompleted transactions nearly always remain saved in the databank even if the database server is currently stopped.
If a TRANSDB file containing uncompleted transactions is deleted, inconsistency will occur because the information required to complete those transactions when the database server is re-started will have been lost.

SQLDB Considerations

The contents of SQLDB is transient, so this databank does not need backup protection.

However, it may be convenient to have SQLDB included in the backup so that a complete system can be restored easily, without any additional operations to recreate an empty SQLDB.

Some data retrieval requests in Mimer SQL may require large work areas or transaction handling areas for intermediate processing of the data (for instance, requests to sort or group large result sets will require large work tables in SQLDB). This is particularly relevant when ad-hoc queries may be submitted with little thought for the processing requirements or performance of the query. In systems where files expand automatically, the file for SQLDB can become very large as the result of one badly-planned query.

The databank attributes GOALSIZE and MAXSIZE are to manage databank file sizes. See Mimer SQL Reference Manual, CREATE DATABANK.


Databank Backups

A databank backup is a copy of the databank file.

A databank backup is the starting point for any restore operation, and should be stored in a safe place separate from the working databank files (copied to a different disk or preferably written to backup media and removed from the machine).

The backup can be taken either by using the Mimer SQL system administration statements for online backup, see Online Backups Using the SQL Statements, or by using the host file system utilities in a system backup, see System Backups Using the Host File System.

After a backup is taken, the updates logged for the databank in question should be cleared from LOGDB. This will be done automatically when the SQL system management statements for online backup are used.

The DBC program, see Databank Check Functionality, should be executed for each databank in the backup operation in order to validate the physical consistency of the databank.

For a system backup, the backup copies of the databanks should always be taken when the system is closed and the databanks are in a logically consistent state. That is, no uncompleted transactions should exist and all databanks should be backed up at the same time to safeguard database consistency.

System vs. Online Backups

The main advantage of online backup is that all databanks, including the system databanks, can be backed up while the system can remain operational. The backup is initiated and executed by use of SQL statements only. The disadvantage can be that there must be enough disk space available to copy the complete database.

If the disk space is limited, a system backup can be preferable. For a system backup, the database server must be stopped. A system backup needs certain SQL statements (such as set online/offline) to be used together with operating system commands for file copying, etc.

SQL Statements for Backing-up Databanks

Refer to the Mimer SQL Reference Manual, SQL Statement Descriptions for a detailed description and syntax definition of the SQL system management functions. A brief description of the purpose of each function appears here.

Online Backup Commands

The SQL system management statements that can be used to take backups are:

Command
Description
START BACKUP

starts a backup transaction.

CREATE BACKUP

creates a backup within a backup transaction. By default an online backup is created, but optionally an exclusive backup can be initiated, which will lock the databank from other users.

COMMIT BACKUP

commits a started backup transaction.

ROLLBACK BACKUP

aborts a backup transaction and ensures that all log records are preserved.

To use these statements to take a databank backup, the user must either be the creator of the databank, or have BACKUP privilege.

When the SQL statements are used to take a backup of a databank, the entire process of taking a databank backup is handled automatically.

The use of a backup transaction ensures that the backups taken within the transaction are consistent with one another, as each backup is effectively taken at the same point in time. Log records are cleared for successfully backed up databanks when the backup transaction is committed. If LOGDB is included in the backup transaction all log records are cleared.

Online/Offline Commands

The SQL system management statements (typically used when taking databank backups using the host file system) that can be used to set a databank, shadow or the whole database online or offline are:

Command
Description
SET DATABASE OFFLINE

sets all non-system databanks offline, and makes the database unavailable.

If one of the databanks cannot be set offline (e.g. because it is being used), the command will fail.

SET DATABASE ONLINE

sets all databanks online, optionally clearing all records from LOGDB and makes the database available.

SET DATABANK OFFLINE

sets a databank offline and the databank pages are updated with all changes made by committed transactions so far.

The databank file is closed (except SYSDB, which always remains open as long as the database server is running) so the file can be copied, and it becomes unavailable to database users.

SET DATABANK ONLINE

sets a databank online, making it available, optionally clearing records from LOGDB.

SET SHADOW OFFLINE

sets a list of shadows offline, making them unavailable.

SET SHADOW ONLINE

sets a list of shadows online, making them available, optionally clearing records from LOGDB.

A user setting the database online/offline, must have BACKUP privilege and must be the only user accessing the database.

A user setting a databank or a shadow online/offline, must either be the creator of the databank or have BACKUP privilege.

Restore Command

The SQL system management statement used to recover a databank in the event of it being damaged or destroyed is:

Command
Description
ALTER DATABANK RESTORE

used to restore a databank from a backup copy by using a LOGDB backup and/or the information currently in the LOGDB system databank.

A user using this function to restore a databank must be the creator of the databank or have BACKUP privilege.


Backing-up Databanks

This section describes procedures for taking databank backups and for restoring a databank in the event of it being damaged or destroyed.

Online Backups Using the SQL Statements

The procedure for taking databank backups using the SQL system management statements is detailed below.

A CREATE BACKUP statement is executed for each databank to be backed up and databank consistency is ensured by starting a backup transaction using the START BACKUP statement.

The backup transaction is committed by using the COMMIT BACKUP statement, which will perform the backup and clear the relevant LOGDB records. The ROLLBACK BACKUP statement can be executed to abort the backup transaction, which will preserve LOGDB.

Note: The databank check functionality (the DBC program) should be run before archiving the backup copies of the databank files (e.g. copying them to CD/RW) to verify the physical integrity of the databank files.

To backup databanks online, do the following:
  1. Perform SQL statements for initiating and executing the backup.
    2. SQL> START BACKUP;
SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK databank-name;
 .
 .   (repeat for each databank to be backed up)
 .
SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK logdb;
SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK sysdb;
SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK transdb;
SQL> CREATE BACKUP IN 'backup-file-name' FOR DATABANK sqldb;
SQL> COMMIT BACKUP;
SQL> EXIT;
  2. Verify the backup copies from the operating system command line using the DBC program.
    3. dbc  backup-file-name  report-filename  sysdb-file-name
.
. (repeat for each backup file created above)
.
  3. Archive the verified backup copies (e.g. copy to DVD/CD).

The START BACKUP statement will start a backup transaction which will ensure that all the backups taken are consistent with one another (they are effectively backed up at the same point in time).

The CREATE BACKUP statement will only create an empty backup copy file. The entire contents of the specified databank file is copied to the specified file by COMMIT BACKUP. (If any of the backups fail, the ROLLBACK BACKUP statement can be executed to ensure that the log records are preserved.)

The COMMIT BACKUP statement will clear all the LOGDB records that apply to the databanks backed up in the backup transaction.

Databank backup file names are subject to the same restrictions that apply to the SQL statement CREATE DATABANK - see the Mimer SQL Reference Manual.

System Backups Using the Host File System

The procedure for taking databank backups using the host file system is detailed below.

We recommend that you always take a backup of all databanks, including SYSDB, LOGDB, SQLDB and TRANSDB.

Note: The database server must be stopped in order to close the SYSDB databank file for a host system backup. This unlocks SYSDB and ensures that no operations are performed between taking copies of the databanks and dropping the log.
However, if using shadowing, databank shadows allow a copy of a databank to be temporarily set offline, e.g. to be backed up, without interrupting normal system use.

To backup databanks using the system backup method:
  1. Set the database offline using the following command:
    2. SQL> SET DATABASE OFFLINE;
  2. Stop the database server so that the system databanks are closed and can therefore be backed up
    3. mimcontrol -t database
  3. Run the DBC program on each databank to verify the physical integrity of the databank files
    4. dbc  backup-filename  report-filename  sysdb-filename
  4. Perform the backup, e.g. copy all databank files to tape (including the system databanks SYSDB, LOGDB, TRANSDB and SQLDB).
  5. Start the database server
    6. mimcontrol -s database
  6. Set the database online again using the following command to clear all log records:
    7. SQL> SET DATABASE ONLINE RESET LOG;

The RESET LOG option removes all records written to LOGDB since the last backup.
This is essential to maintain consistency between the log and the backup. If the backup fails, the PRESERVE LOG option should be used when setting the databank online to leave LOGDB unaltered.

It is essential that all databanks are backed up at the same time to ensure logical consistency between them.

It is also important that transactions are in a consistent state which is ensured by using the SET DATABASE OFFLINE statement. The statement will not return until the database has been brought into a consistent state prior to going offline. In particular, setting the database offline will ensure all background processing done by the database server has completed.


Restoring a Databank

Restoring a databank after it has been damaged or destroyed will typically involve both the host file system and SQL statements.

Note: Data need not be restored in the event of a power failure or system shut-down that does not damage the databank files, since any transactions that were committed but not completed at the time of the failure are automatically completed when the databank involved is next accessed.

Any databank restore operation must start with a backup copy of the databank file that is not damaged or corrupt. This is generally the copy taken during the last backup, either taken by the host operating system or by using the SQL system management statements for online backup.

Usually, the host file system is used to copy the backup file from the backup media to disk. The file is generally placed in the normal location for the databank file (as recorded in the data dictionary, SYSDB). However, in certain circumstances it may be necessary to place it in an alternative location, e.g. if the disk is unavailable.

The procedure for restoring a databank is as follows:

Note: Step 2 and 3 are only required during certain circumstances:

  1. Bring a valid backup copy of the databank from the backup media to disk.
  2. If the file has been placed in a location that is different to the location of the original databank file, alter the databank to reference the new file location using the following command:
    3. SQL> ALTER DATABANK databank-name INTO 'new-file-name'
  3. If restoring from an older backup, i.e. not the latest one, information should be restored from the LOGDB included in the following backup (that was taken after the time the backup restored in step 1 was taken).

    4. For each LOGDB backup file, the information recorded in it should be applied to the databank using the following command:

    SQL> ALTER DATABANK databank-name RESTORE USING 'logdb-backup-file-name'
  4. Finally, apply the updates made since the most recent backup(s) restored in the preceding steps were taken. These updates are currently recorded in LOGDB and they are restored using the following command:
    5. SQL> ALTER DATABANK databank-name RESTORE USING LOG

Restoring SYSDB

If SYSDB is lost, a backup copy of SYSDB must be restored to allow Mimer SQL to start again. No Mimer SQL-based application can be used before this is done.

If SYSDB is lost or corrupted, a backup copy should be copied to the same file location as the original SYSDB. The contents of SYSDB may then be brought completely up to date by restoring LOGDB information. This is done using the backup and restore functionality in the BSQL program.

Start BSQL and login as SYSADM, or another user with BACKUP privilege. A message is displayed saying that you have an old version of SYSDB that must be restored. Answer Y to the question Restore SYSDB to restore the copy of SYSDB.

Since SYSDB always has the LOG option, this will restore SYSDB to the state it had before it was lost.

Example, assuming a backup of SYSDB has been copied to the original location:

MIMER/DB fatal error -16159 in function CONNECT
         Old version of the databank SYSDB cannot be accessed without
         restoring the databank with the backup and restore utility

                     -- Restore databank --

Restore SYSDB?[Y]: Y

Databank SYSDB restored from log

Re-creating TRANSDB, LOGDB and SQLDB

No Mimer SQL applications can be run if LOGDB, TRANSDB or SQLDB is missing. In this event, starting the BSQL program and logging in as SYSADM will give you an opportunity to re-create the missing databanks with the same file names as the lost databanks, or to alter the recorded filenames in the case where the physical files were moved.

The following example shows how to re-create LOGDB for a database where this system databank is missing:

Mimer SQL command line utility, version 9.3.7
Username: SYSADM
Password:
2007-06-17 23:15:16.94   <Error>
MIMER/DB kernel error -16142 in function DKOPD1
Databank LOGDB, filename logdb.dbf
File not found, OS error message:
'%SYSTEM-W-NOSUCHFILE, no such file'

-- Redefinition of system databank --

-- Description of databank name and file --

DATABANK
FILENAME
========================================
LOGDB
logdb.dbf

Redefinition of LOGDB? [Y]: y
CREATE new file or ALTER filename for LOGDB? (C/A): c
Size [1000]                   : 5000
Databank LOGDB redefined

These databanks (at least TRANSDB and LOGDB) are vital to the system consistency, so we strongly recommend that these files are kept intact whenever possible. A complete backup of the entire database should be made before any system databanks are recreated.

If a database has been operational for some time, a situation may arise where one or more of the system databanks LOGDB, TRANSDB or SQLDB has grown very large. In those cases the ALTER DATABANK DROP FILESIZE statement can be used to shrink the file sizes.

The following sections describe how to re-create each of the respective system databanks.

Creating a New LOGDB
  1. Shut down the database server (if not already stopped).
  2. Run the DBC tool (Databank Check Utility) on SYSDB and all the user databank files to ensure that none are corrupted.
  3. Take a valid backup of the whole database.
  4. Archive a copy of the LOGDB databank file and delete the original file from disk.
  5. Start the database server.
  6. Start the BSQL program, logging in as SYSADM, and when prompted, select the CREATE option and specify a path name and a size for the new LOGDB databank file.
Creating a New TRANSDB

Note: Do only perform this operation in case of emergency. Important information may be lost and database consistency can not be guaranteed.

  1. Shut down the database server (if not already stopped).
  2. Ensure that all pending transactions have been flushed to the user-databank files on disk by successful execution of DBOPEN in single-user mode.
  3. Archive a copy of the TRANSDB databank file and delete the original file from disk.
  4. Start the database server.
  5. Start the BSQL program, logging in as SYSADM, and when prompted, select the CREATE option and specify a path name and size for the new TRANSDB databank file.
Creating a New SQLDB
  1. Shut down the database server (if not already stopped).
  2. Delete SQLDB from disk.
  3. Start the database server.
  4. Start the BSQL program, logging in as SYSADM, and when prompted, select the CREATE option and specify a size for the new SQLDB databank file.


Audit trail with READLOG

READLOG is a BSQL function which enables you to read the contents of LOGDB so that you can check logged operations performed on the database since the last backup copy backup was taken.

You can use READLOG as an audit trail or, in the event of a system failure, to determine which databanks need to be restored (i.e. which databanks have been altered since the last backup).

See Mimer SQL User's Manual, READLOG.


Databank Check Functionality

The DBC program allows investigation of databank files to ensure that the physical structure is not damaged. The functionality may also be used to examine the internal organization of a databank file and display statistical information on the databank.

Note: The functionality checks only the physical condition of the databank. Informational errors such as data inconsistency, invalid data format, and data outside the validation limits of domains are not detected by DBC.


DBC - Databank Check

DBC investigates databank files to ensure that the physical structure is not damaged.

Syntax

The overall syntax for the DBC program is:

dbc [databank_filename [result_filename [sysdb_filename]]]

Command-line Arguments

Arguments
Function
databank_filename

File name for the databank to check.

result_filename

Sequential file created by DBC that contains the result of the verification.

sysdb_filename

Filename for SYSDB used by the databank to check. This filename is required if any tables are using collations. If not specified the correct sort order for such tables is unknown.

If the filenames are not specified on the command-line, the program prompts for the name of the databank file, a name for the result file and the name of the system databank (SYSDB) file.

If an error occurs when opening the databank file (e.g. file not found or file locked by another user), or while creating the result file, an appropriate error message is displayed.

If the SYSDB filename is not specified and it is found that tables in the databank use collations, these tables are not verified and a warning message is displayed.

If no errors are detected in the databank file, the following message is shown:

No errors found

The result file then contains statistics describing the physical databank organization. Otherwise, error descriptions (see below) are written to the result file, and the following message is displayed:

* Errors logged in result file

The result file should be examined to investigate the nature of the errors, see Database Consistency.

It should be noted that the DBC program returns an error status (or warning) to the operating system when an error (or warning) is encountered. This may be useful when running it from scripts or in batch mode.

Exit Codes

DBC returns a status code to the environment executing the command. The status code can be examined by scripts.

VMS: On OpenVMS, the status codes correspond to the OpenVMS condition code severity levels.

Use the $SEVERITY symbol in DCL command procedures.

The following return codes are used:

UNIX/Windows
VMS
Usage

0 (success)

1 (success)

This code is used when the DBC command has executed all options with no problems.

1 (warning)

0 (warning)

Other Error.

2

2 (error)

Databank error.

Authorization

The DBC program operates directly against the databank file, with no reference to the Mimer SQL database server. The program may not be run on a file which is currently held open (an error message is displayed in such a case). The system administrator should arrange for exclusive access to the databank file during DBC operations.


Result File Contents

Any errors detected in the databank file are written to the result file directly after the identification record for the table affected.

The result file begins with the following information:

  • Databank file name.
  • Time when the DBC operation was performed.
  • Version of Mimer SQL under which the databank was created (if this is not the same as the current version there will also be a `converted to' message).
  • A backup timestamp.
  • Structure level.
  • System identifier for the databank.
  • Databank sequence number (0 = master databank, >0 = a shadow).
  • Check flag.
  • Number of bitmap pages (starting at 0).
  • Root pages (starting at 1).
  • Number of pages allocated.
  • Number of pages used.

If the check flag indicates that the databank was not properly closed when Mimer SQL is stopped, there may be an additional message saying:

    * No bitmap checking against databank!

DBC B*-tree Table Information

An identification record is given for each table in the databank. The following information is shown:

Information
Description
Tabid

The system identification number of the table. This is the number used to identify the table in the data dictionary. The table name is not stored directly in the databank file.

Startp

The page number of the start page for the highest index level.

If the number of levels is 1, this is the only data page. If the start page is 0, the table is empty.

Levels

The number of levels in the table storage structure.

Keylen

The length of the primary key in bytes.

Reclen

The record length (row length) of the table.

Type of table

Any of the following values:

Base table
Secondary index table
Collation description table
LOB directory table.

Type of
compression

Any of the following values:

Mimer LZM compression
Mimer LZU compression
Mimer RLE compression
none.

Collation version

Displayed if any key part of the table is using collation.

Status of table

Resident or Marked for delete.

Index page size

Size of the index page in bytes.

Data page size

Size of the data page in bytes.

Number of index
pages

Indicates how many index pages were checked.

Number of data
pages

Indicates how many data pages were checked.

Required/Allocated
datapages

Gives an approximate measure of the space used in the data pages.

This is expressed as a percentage, which may be >100% for data pages having variable-length (i.e. compressed) records because the required number of pages is calculated based on uncompressed record sizes.

Reached no of
records

If no errors are reported, the number of records reached is equal to the total number of records (rows) stored for the table.

DBC Sequential Table Information

The following information is shown:

Information
Description
Tabid

Ordered identification.

Startpage

First page in the sequential file.

Endpage

Last page in the sequential file.

Type of table

Any of the following values:

Sequential table
Collation description table
LOB directory table.

Type of compression

Any of the following values:

Mimer LZM compression
Mimer LZU compression
Mimer RLE compression
none.

Status of table

Resident or Marked for delete.

Index Page size

Size of the index page in bytes.

Data Page size

Size of the data page in bytes.

No. of index pages read

Number of index pages checked.

No. of data pages read

Number of data pages checked.

No. of records read

Number of records checked.

DBC LOGDB Backup Information

The following information is shown:

Information
Description
Timestamp page

Describes actual timestamps for databanks included in the log.

Error Messages

The following error situations are described by explicit messages in the result file:

Bitmap Errors

Error message
Explanation
* Illegal number of free bits in
bitmap

The number of free bits in the bitmap is marked as a negative number or as a number greater than the permissible value.

* Illegal pointer to first word
with free bit in bitmap

The pointer to the first word is either negative or greater than the number of words per page, or points outside the number of allocated pages.

Root Page Errors

Error message
Explanation
* Illegal record length in root
page

The record length is not valid for the current version or for the site.

* Pageno. outside databank: x

* Pageno. not marked as used: x

The reference to the page number (x) is invalid. (Applies only where there is more than one root page.)

Sequential Structure Errors

Error message
Explanation
* Pointer to previous page invalid

Error in page linkage.

* Invalid record length

The record length is either not the same as in the previous page or outside the page limits.

* Record crosses page boundary

A record stretches over the page limits.

Table Structure Errors

Error message
Explanation
* Error in root record

The value for start page, levels, key length or record length is outside the legal values for the site.

* Page has illegal record length: x

The record length (x) given in the page is not the same as that given for the table.

* Page has illegal last-record
pointer: x

The pointer (x) to data within a page is outside the page limits. The limits are the values of bytes/header and bytes/page.

* Page has records in wrong order.
Pos: x

The records in the page are not correctly sorted. The value of x is the byte position within the page for the start of the wrong record.

* Page has last-record key > index
key. Pos: x

The records within a page include key values greater than those in the index level above. The value of x is the byte position within the page for the start of the last record.

* Page no. outside databank: x

Reference is made to a page number (x) which is higher than the highest allocated page.

* Page no. referenced twice

There are at least two references to the same page number (x). One of these references should also give another error, or an error should have been notified earlier in the file.

* Page no. not marked as used: x
   Bitmap pageno:      Word:     Bit:

A page that is used is illegally marked as free.

Continued insertion of data in the databank will result in a double referenced page.

All table structure errors are followed by a listing of the page numbers passed in the B*-tree structure on the way to the error. In the example below a y-value indicates the byte position in the page (corresponding x-value) where the record holding the reference to the next level starts:

B*-tree
Page no:    x1   x2   x3  ....  xn
Byte pos:   y1   y2   y3  ....  yn

If the error occurs at an index level, the following additional message is given, and no checks are made at lower levels:

Branch is interrupted


Databank Open Function

The DBOPEN program is used to make sure that the users can open all databanks quickly.

It can take relatively longer to open a databank if it is a large databank that was closed abnormally (for example if the machine crashed) because a databank check is automatically initiated following such a situation.


DBOPEN - Databank Open functionality

Multi-user Mode

When DBOPEN is run in multi-user mode, the index pages of the databank are checked before databank access can proceed, but the bulk of the databank contents (contained in the data pages) is checked in the background, thus minimizing the delay.

Background Threads

The checking performed by DBOPEN is done by the background threads, see Number of Background Threads. Therefore, increasing the number of background threads will increase the efficiency of the checking.

The DBOPEN functionality should normally be run as soon as the database server has been started.

Syntax

The overall syntax for DBOPEN is:

dbopen [-s | -m] [-u[username]] [-p[password]] [database]

Command-line Arguments

UNIX-style
VMS-style
Function
-m
--multi

/MULTI

Connects to the database in multi-user mode.

-s
--single

/SINGLE

Connects to the database in single-user mode.

-u username
--username=username

/USERNAME=username

Specifies the username used when connecting to Mimer.

To connect as OS_USER, give -u "", --username="", or
/USERNAME="".

-p password
--password=password

/PASSWORD=password

Specifies the password used in connecting to Mimer.

If the switch is omitted the user is prompted for a password, unless OS_USER is specified with the username switch, as described above.

VMS: Note that in a Open VMS environment it might be necessary to enclose the password in quotation marks as the value otherwise is translated to upper case.

database

database

Specifies the name of the database to access.

-?
--help

/HELP

Show help text.

If the optional database name is not specified, the default database will be accessed, see The Default Database.

If the username or password arguments are omitted, the program will prompt for these values.

If neither -s nor -m is specified for the optional mode flag, the way the database is accessed will be determined by the setting of the MIMER_MODE variable, see Specifying Single-user Mode Access, or, if this is not set, it will be accessed in multi-user mode.

.

UNIX: The UNIX-style command-line flags must be used on a UNIX machine.

VMS: Either the UNIX-style or the VMS-style command-line flags may be used on an OpenVMS machine - see the Mimer SQL VMS Guide for more details.

Win: The UNIX-style command-line flags can be used from a Command Prompt window.

Exit Codes

The DBOPEN program returns an error status to the operating system when an error is encountered. This may be useful when running it from scripts or in batch mode.


Functions

The DBOPEN functionality opens all available databanks in a system.

As each databank is opened, the integrity is checked and any transactions that were interrupted by the abnormal close are completed. (The integrity check is always performed when opening a databank that has not been closed normally.)

The checks performed when an abnormally closed databank is opened may take some time, particularly if the databank is large. Running DBOPEN means that the checks are performed at a time determined by the system administrator, rather than a time determined by application programs. As a result, users will always have fast access to all databanks.

The databanks are opened in a randomly determined order. Running several DBOPEN sessions in parallel may speed up the checking process for the database as a whole.

Authorization

Any user who has SELECT access to the data dictionary table SYSTEM.DATABANKS can run DBOPEN.

Output Example

The following DBOPEN example shows the output from running dbopen after the system was shut down improperly:

dbopen -s -usysadm -p******

MIMER Databank Open Utility
Version 9.3.7

2006-09-15 14:19:12.22   <Information>
MIMER/DB kernel error -16211 in function DKVED0
Databank SYSDB not properly closed, dbcheck initiated

2006-09-15 14:19:22.67   <Information>
MIMER/DB kernel error -16211 in function DKVED0
Databank TRANSDB not properly closed, dbcheck initiated

2006-09-15 14:19:22.71   <Information>
MIMER/DB kernel error -16211 in function DKVED0
Databank LOGDB not properly closed, dbcheck initiated

Opening databank APPDB1

All user databanks opened without errors


Loading and Unloading Data and Definitions

Mimer SQL provides you with flexible methods for loading information to and from databases using the LOAD and UNLOAD commands and the MIMLOAD program.

LOAD and UNLOAD can be used with Mimer SQL from any ODBC based SQL command interpreter.

Using the LOAD command, you can:

  • load information from one or more files into a Mimer SQL database
  • optimize loading for best performance
  • select exactly the information you want and place it precisely where you want it using an SQL statement
  • log the load operation.

Using the UNLOAD command, you can:

  • customize the information - unload whole databanks or select specific information using an SQL statement
  • log the unloading operation.

Using the MIMLOAD program, you can use LOAD and UNLOAD directly from your operating system command prompt. Using the STDIN, STDOUT and STDERR options in the LOAD/UNLOAD syntax, you can enable command line file redirection for input, output and logging.


MIMLOAD - Data Load and Unload

MIMLOAD enable you to use the LOAD and UNLOAD commands at your operating system's command prompt.

Syntax

You control MIMLOAD using flagged information specified on the command-line.

The overall syntax for MIMLOAD (expressed in short form UNIX-style) is:

mimload [-u user] [-p password] [-e program -i password] "statement" [database]

Note:	If you are using double quotes in the LOAD/UNLOAD statement, they must be escaped using the back-slash character \ when UNIX-style switches are used. For VMS-style switches, a double quote " is used as escape character.

If you start MIMLOAD command without specifying options, it displays help on the command-line arguments.

Command-line Arguments

You can use the following arguments with MIMLOAD.

UNIX/Windows-style
VMS-style
Function
-u user
--username=username

/USERNAME=username

Specifies the username used when connecting to Mimer.

If not specified, OS_USER is assumed.

-p password
--password=password

/PASSWORD=password

Specifies the password for the user.

When connecting an OS_USER, no password is needed.

VMS: Note that in a Open VMS environment it might be necessary to enclose the password in quotation marks as the value otherwise is translated to upper case.

-e program
--program=program

/PROGRAM=program

Specifies an optional program ident, to be entered in the connection phase.

This can be specified more than once and thus allows entering deeper entry levels.

-i password
--using=password

/USING=password

Password for the program ident specified by the preceding --program switch.

"statement"

"statement"

Specifies the LOAD/UNLOAD statement. Enclose in double quotes.

database

database

Specifies the name of the database to access. If specified, it must be the last argument.

If you do not specify a database name, the default database will be used.

-?
--help

/HELP

Show help text.

Exit Codes

The following error codes are used:

UNIX/Windows
OpenVMS
Usage
0 (success)

1 (success)

This code is used when MIMLOAD has executed successfully.

> 1 (error)

2 (error)

This error code is used when MIMLOAD failed to execute successfully.

Examples

Loading Data

The following example loads the file store.sql into the default database.

UNIX/Windows-style
mimload --username=user1 --password=UsrPwd "load from 'store.sql' log stderr"
VMS-style
MIMLOAD /USERNAME=USER1 /PASSWORD="UsrPwd" "LOAD FROM 'STORE.SQL' LOG STDERR"
Unloading Data

'The following example unloads all the definitions and data owned by the user store_adm from the database store to the file store.sql.

UNIX/Windows-style
mimload -u store_adm -p StrPwd "unload to 'store.sql' from current user" store
VMS-style
MIMLOAD /USERNAME=STORE_ADM /PASSWORD="StrPwd" "UNLOAD TO 'STORE.SQL' FROM
CURRENT USER" STORE

Using STDIN/STDOUT/STDERR

STDIN, STDOUT and STDERR are short names for the standard input, standard output and standard error streams, respectively.

Windows/UNIX-style Examples

In the commands below, STDIN is denoted by '<' (this is short for '0<', equal to reading from file descriptor number 0 which is the standard input). STOUT is denoted by '>' (this is short for '1>', equal to writing to file descriptor number 1 which is the standard output) and STDERR is denoted by '2>' (equal to writing to file descriptor number 2 which is the standard error).

In the following example, the generated output from UNLOAD is written to standard output, which in this case is redirected to the file store_db.unl. The log information is written to standard error, which is redirected to the file store_db.log.

mimload -u store_adm -p StrPwd "unload to stdout log stderr from databank
store_db" store > store_db.unl 2> store_db.log

In the following example, input to LOAD is taken from standard input, i.e. from the file store_db.unl, and log information is written to standard error, in this case to the file store_db_load.log.

mimload -u store_adm -p StrPwd "load from stdin log stderr" store <
store_db.unl 2> store_db_load.log
VMS-style Examples

In the OpenVMS environment, you can use STDIN, STDOUT and STDERR in two ways, either by defining SYS$INPUT, SYS$OUTPUT and SYS$ERROR or by using pipe mode which gives the UNIX behavior for use of file redirection. Both methods are shown below.

In the following example, the generated output from UNLOAD is written to standard output, which in this case is redirected to the file D1:<DATA>STORE_DB.UNL. The log information is written to standard error, which is redirected to the file D1:<LOG>STORE_DB.LOG.

Example using SYS$OUTPUT and SYS$ERROR:

DEFINE/USER SYS$ERROR D1:<LOG>STORE_DB.LOG
DEFINE/USER SYS$OUTPUT D1:<DATA>STORE_DB.UNL
MIMLOAD /USERNAME=STORE_ADM /PASSWORD="StrPwd" "UNLOAD TO STDOUT LOG STDERR
FROM DATABANK STORE_DB" STORE

Example using pipe mode:

PIPE MIMLOAD /USERNAME=STORE_ADM /PASSWORD="StrPwd" "UNLOAD TO STDOUT LOG
STDERR FROM DATABANK STORE_DB" STORE > D1:<DATA>STORE_DB.UNL 2>
D1:<LOG>STORE_DB.LOG

In the following example, input to LOAD is taken from standard input, i.e. from the file D1:<DATA>STORE_DB.UNL, and log information is written to standard error, in this case to the file D1:<LOG>STORE_DB_LOAD.LOG.

Example using SYS$OUTPUT and SYS$ERROR:

DEFINE/USER SYS$ERROR D1:<LOG>STORE_DB_LOAD.LOG
DEFINE/USER SYS$INPUT D1:<DATA>STORE_DB.UNL
MIMLOAD /USERNAME=STORE_ADM /PASSWORD="StrPwd" "LOAD FROM STDIN LOG STDERR"
STORE

Example using pipe mode:

PIPE MIMLOAD /USERNAME=STORE_ADM /PASSWORD="StrPwd" "LOAD FROM STDIN LOG
STDERR" STORE < D1:<DATA>STORE_DB.UNL 2> D1:<LOG>STORE_DB_LOAD.LOG


LOAD - Loading Data

You load definitions and/or data into a Mimer SQL database using the LOAD statement.

Syntax

The LOAD command has the following syntax:

where file-format-spec is:

Usage

MIMLOAD, or with any ODBC-based SQL interpreter.

For information on MIMLOAD, see EXPTOLOAD - Convert Old Format Export Files.

Description

The LOAD command copies definitions and/or data from one or more files. When loading information from more than one file, the files are read in the order defined. The input file(s) are expected to form a valid sequence of definitions, data descriptions and data.

Triggers defined against the affected tables are applied when the data is loaded.

About Files

When a file contains data for more than one table, the data for each table must be contained in a section that is introduced by a data description header. For more information, see Data Description Headers and Files.

If the data in the file does not have a data description header, there must be a data description file that contains the header information.

This means that the file can only contain data from one table. Data description files and data files can of course be concatenated into a single file containing data for several tables.

A definition file contains definition statements to create objects. A definition file can, for example, be divided into two files where one file is place first in the file list; i.e. executed before any data is loaded, and the other file is placed at the end of the file list, i.e. executed after the data is loaded.

An example file sequence can be as follows: first in the file list, a file that contains object definitions; second, a file that describes the data to be loaded (the information in this file is equal to the corresponding information that can be given as a header in the data file); third, the data file; and fourth, a second definition file including referential constraints and triggers.

You can specify the name of the table into which the information shall be loaded in the data file header(s) or the data description file. The default is the table name from which the data was unloaded.

When LOAD scans a file, it detects if a field uses a text qualifier by checking if the first character in the field is a text qualifier. If a text qualifier character is found in the field data, the character is doubled, i.e if the text qualifier is a double quote, the data ab'c is equal to the data 'ab''c'.

The STDIN Option

When you use the STDIN option, input is read from the standard input stream. See Using STDIN/STDOUT/STDERR.

The AS Option

By using the AS option together with a file name specification, you can specify the character set used by the file to be loaded.

The character sets available are: LATIN1, UTF8, UTF16, UTF16BE, UTF16LE, UTF32, UTF32BE and UTF32LE.

UTFxxBE and UTFxxLE means UTFxx format with big or little endian byte order. UTFxx without endian notion means that the common endian for the current platform is assumed.

The default character set used, if you do not use the AS option, is the default used in your host operating system.

For more information, see File Format Specifications.

LOG

You specify the log file using LOG. This log file will include warnings and progress information about the load operation.

The STDERR Option

When you specify LOG STDERR, informational messages are written to the standard error stream. See Using STDIN/STDOUT/STDERR.

The WITH SHARED ACCESS Option

LOAD's default behavior implies that you have exclusive access to the databank being loaded with data. If you need shared access, you can use the WITH SHARED ACCESS option. In most cases, this will lead to a slower data load using row-wise insert.

By default, LOAD uses a fast data load facility to increase performance. The alternative is to insert data row-wise as if using an SQL INSERT statement.

LOAD uses the fast data load facility in most cases, but there are some situations that need row-wise insertion due to certain referential constraints. In such cases, a warning message will tell you that fast data load cannot be used and the operation will continue using row-wise insertions.

When row-wise insertions are performed, loads are recorded in LOGDB (assuming the databank is defined with the LOG option).

The START AT Option

You can use the START AT option to restart a failed load operation.

The START AT value can be set to a line where a data definition statement is located or a data descriptor header starts (#data).

The USING Option

The USING option enables you to use an SQL statement to specify the information to be loaded and the target for the information.

The SQL statements you can use are: INSERT, UPDATE, DELETE or CALL to a procedure with input parameter markers only.

Examples

The following example is a straightforward import of the input file, using default options:

LOAD FROM 'table_t.data' LOG 'table_t.log';

The following example imports a data file, preceded by a data description file, using the default options:

LOAD FROM 'table_t.desc', 'table_t.data' LOG 'table_t.log';

The following example imports the first four columns of data in the file to the table named details from a file in UTF16 format:

LOAD FROM 'table_t.data' AS UTF16
   LOG 'table_t_dataload.log'
   USING INSERT INTO details VALUES (?,?,?,?);

The following example uses an UPDATE statement where the first column C1 and the second column C2 of the data input file are used:

LOAD FROM 'table_t.data' AS UTF16
   LOG 'table_t_dataload.log'
   USING UPDATE details SET c1=? WHERE c2=?;

The following example uses a DELETE statement where the input data is used to qualify records to delete:

LOAD FROM 'table_t.data' AS UTF16
   LOG 'table_t_dataload.log'
   USING DELETE FROM details WHERE c2=? AND c3=?;


UNLOAD - Unloading Data

You use the UNLOAD command to unload data and/or definitions from a Mimer SQL database to a file.

Syntax

where file-format-spec is:

Usage

Any ODBC-based SQL interpreter or with the MIMLOAD program.

For information on MIMLOAD, see EXPTOLOAD - Convert Old Format Export Files.

Description

The UNLOAD command generates data and/or definitions and places the result in a single file. You can use the options ALL (default), DEFINITIONS or DATA to specify the information you want.

When generating the data and definitions, a data description header is created before information is written to the file. If information from several tables is generated, a data description header for each data section is created.

A data description header contains escaping information and column separator information. For more information, see Data Description Headers and Files.

Data Escape Mode

UNLOAD generates data in escaped mode. This means that the data description header includes the data escape mode option.

When using data escape mode, the following characteristics are enabled, from the UNLOAD perspective:

  • Data from a specific table is ended by the escape sequence '\_' to mark end-of-table.
  • NULL values are indicated by the escape sequence '\-'.
  • BLOB and BINARY columns are unloaded in HEX code with a leading '\x' escape sequence for each byte.
  • BLOB, CLOB and NCLOB columns are unloaded so that the value length is given in front of the value as in the following CLOB example: '11:Abracadabra'
  • For CHAR, NCHAR, CLOB and NCLOB columns, the escape sequence '\x' is used only when there is binary data, such as ISO control codes, new-line characters, etc. in the data.
  • The '\u' escape sequence is used only when Unicode data is to be written to Latin1 files.

For information on escape sequences, see Escape Character Sequences.

The STDOUT Option

When you use the STDOUT option, generated output is written to the standard output stream. See Using STDIN/STDOUT/STDERR.

The AS Option

By using the AS option together with a file name specification, you can select the character set of the generated file. You can choose: LATIN1, UTF8, UTF16, UTF16BE, UTF16LE, UTF32, UTF32BE or UTF32LE.

UTFxxBE and UTFxxLE means UTFxx format with big or little endian byte order. UTFxx without endian notion means that the common endian for the current platform is assumed.

The character set used, if you do not use the AS option, is the default used in your host operating system.

For more information, see File Format Specifications.

The LOG Option

You can generate a log of the operation using the LOG option. The log file will include warnings and progress information about the operation. If you do not use the LOG option, warnings and progress information are suppressed.

The STDERR Option

When you use the LOG STDERR option, informational messages are written to the standard error stream. See Using STDIN/STDOUT/STDERR.

The USING and FROM Options

To specify the information to be unloaded, you use the USING or FROM options.

With the USING option, an SQL statement, such as SELECT * FROM T1; or a CALL to a procedure with parameter markers (?) for output parameters only, can be used to specify the source.

By using an SQL statement to form the source for the export operation, there are many possibilities available to format and customize the output.

With the FROM option, one or several databanks, tables or schemas can be used to form the source for the export operation. If using the FROM CURRENT USER option, the current ident is exported.

If tables are joined in the SQL statement used, and definitions are generated, a new table that is a reflection of the result of the join is defined. The default name of the new table is table1.

Error Management

The UNLOAD command runs until a major error is encountered. Minor problems are reported as warnings if LOG is enabled. If a fatal error occurs, an error message is displayed and the operation is aborted.

Examples

The following example will export the table details, with all related definitions, to a file:

UNLOAD DEFINITIONS TO 'table_t.def' FROM TABLE details;

The following example will export the CREATE statement for table details together with all data in the table to a file in UTF16 format. A log file is used:

UNLOAD TO 'table_t.all' AS UTF16
   LOG 'table_t.log'
   USING SELECT * FROM details;

Data Description Headers and Files

Data description headers and files are used to describe the data that follows.

The following table describes data description header elements:

Element
Usage
Description

#data

Required

Data description header start identifier.

escape mode

Optional

Indicates that the data is escaped, i.e. that some elements of the data are tagged for secure recognition at LOAD.

See the table below. When using UNLOAD, data escape mode is always used.

column separator 'x'

Optional

Indicates which character is the column separator when reading the data.

The default is the comma character (,).

If this option is not used, LOAD assumes that the comma character is the column separator.

text qualifier 'x'

Optional

Indicates which character is the qualifier for text strings in the data.

The default is the double quote character (").

If this option is not used, LOAD assumes the double quote character as the text qualifier or unqualified data.

null indicator 'x'

Optional

Indicates which character is the NULL value if found in a data field.

If this option is not stated, LOAD assumes the empty string, i.e. two consecutive column separators, as a NULL value.

In data escape mode, '\-' is treated as the NULL value.

using insert-statement

Optional

The SQL INSERT statement that indicates where, and in what way, data should be loaded.

This statement is used in the situation where the LOAD statement itself does not include a USING clause.

;

Required

Data description header end identifier.

As shown in the table above, the characters used to specify column separators, text qualifiers and NULL indicators must be enclosed in single quotes. If you use a single quote to specify a column separator, text qualifier or NULL indicator, you must enter it twice, for example, you would specify a single quote as a column separator as ''''.

Data Description Header Examples

For data unloaded from a Mimer SQL database using UNLOAD, the data description header generated could look as follows:

#data escape mode using insert into t (c) values (?);

The example above implies the following for LOAD:

  • The column separator is the comma character (default).
  • Text strings are presumed to be unqualified or qualified with the double quote character.
  • Data escaping is assumed (see the table below).
  • The USING statement in the header will be used if no USING clause is given in the LOAD statement.

The following is another example of a data description header where all optional elements mentioned above, except data escape mode, are used:

#data
column separator ':'
text qualifier '!'
null indicator '§'
using insert into t1 (c1,c2,c3) values (?,?,?);

In the example above, the table t1 and the columns c1, c2 and c3 are supposed to exist when starting the data load. Specific characters for column separator, text qualifier and null indicator are defined.

Escape Character Sequences

If data escape mode is specified, the back-slash character (\) is used as the escape character. The character following the escape character can be one of 'x', 'u', '-' or '_'. See the following table for a description of valid escape character sequences:

Escape character sequence
Usage Description
Example

\x (lower case letter 'x')

Preceding a hexadecimal byte value. A HEX value is assumed to be two HEX value digits, i.e. 0-F.

\x1A

\u (lower case letter 'u')

Preceding a unicode value. A Unicode value is assumed to be eight HEX value digits, i.e. 0-F.

\u12345678

\- (dash)

Null value

\-

\_ (underscore)

End of table, including end of stream or file

\_

Note: If you do not use data escape mode, end of file is treated as end of table. This means that such a data file only can contain data for one table.

File Format Specifications

The various file formats that can be used are described briefly in the following table:

File Format
Description

latin1

ISO 8859-1, i.e. ISO's 8-bit single-byte coded graphic character set for Western languages.

utf8
utf16
utf32

Unicode Transformation Formats, standard character encoding schemes in accordance with ISO 10646.

For more information, see http://www.unicode.org

utf16be

UTF16 format with big endian byte order.

utf16le

UTF16 format with little endian byte order.

utf32be

UTF32 format with big endian byte order.

utf32le

UTF32 format with little endian byte order.


EXPTOLOAD - Convert Old Format Export Files

EXPTOLOAD is a utility that convert files exported with the deprecated UTIL Export/Import functionality into files that can be loaded with the Load/Unload functionality. See Loading and Unloading Data and Definitions (this chapter).

UTIL export files may contain the following data definition statements, which are all handled by the EXPTOLOAD program:

CREATE SCHEMA
CREATE DOMAIN
CREATE TABLE
CREATE INDEX
ALTER TABLE

Note: No CREATE DATABANK statements are included in the UTIL export files, and no CREATE DATABANK statements are written to the load files produced by EXPTOLOAD. Databanks must be created manually before the load file is loaded with the LOAD/UNLOAD functionality.

Data for the tables begin after all data definition statements.

Syntax

The syntax for using the EXPTOLOAD program is:

exptoload  export-file  [load-file]

Command-line Arguments

Arguments
Function
export-file

File name for the UTIL Export/Import file to convert

load-file

File name for the LOAD/UNLOAD file to create. If load-file is omitted the output is written to standard out.

The following example translates an export file to a load file (logging information is written to the screen):

exptoload mimer_store.exp mimer_store.load

If an error occurs when opening the export file (e.g. `File not found'), or while creating the load file, an appropriate error message is displayed.

Stdout/stderr

Stdout and stderr can be used with the EXPTOLOAD command. Stdout is used if the load-file command line argument is omitted. See the following examples, where `>' is a redirection of stdout and `2>' is a redirection of stderr:

exptoload mimer_store.exp mimer_store.load 2> mimer_store.err

exptoload mimer_store.exp > mimer_store.load 2> mimer_store.err

exptoload mimer_store.exp 2> mimer_store.err |
mimload -d store -u s_adm -p s_pwd "load from stdin log stderr" 2> store.log

Exit Codes

The EXPTOLOAD program returns an error status to the operating system when an error is encountered. This may be useful when running EXPTOLOAD from scripts or in batch mode.

The following error codes are used:

UNIX/Windows
OpenVMS
Usage
0 (success)

1 (success)

This code is used when EXPTOLOAD has executed successfully.

> 1 (error)

2 (error)

This error code is used when EXPTOLOAD failed to execute successfully.


Replication

Mimer SQL supports continuous replication of data from a source database to a target database. The replication is based on triggers that store changes for replicated tables in log tables. The replication system reads these log tables and carries out the operations on the target system. The replication system consists of three separate programs, MIMREPADM, REPSERVER, and MIMSYNC.

MIMREPADM is a command line based tool for defining which tables that should be replicated. It is also used for install and uninstall of the replication dictionary, which is needed for maintaining information about replicated tables.

MIMREPADM handles the actual replication.

The synchronization program, MIMSYNC, performs data manipulation operations to ensure that source and target tables contain the same rows.

Note: The replication system cannot be used to create tables on the target database. It is the responsibility of the user to create these tables.

Requirements

These programs use ODBC for database access. The Mimer SQL version of the source database must be 9.3.5 or later. The Mimer SQL version of the target database must be 9.2.4 or later.

There must be a Mimer License Key that allows replication on the source database. No license key is required on the target database.

Restrictions

For a replicated table, the value of a primary key column must not be updated.

Note: If a primary key value is updated, that row will not be replicated. In that case mimsync can be used to correct the replicated data. See MIMSYNC - Synchronizing Tables.


MIMREPADM - Replication Administration

This section describes how to set up a replication environment.

Syntax

The following options can be specified as command line arguments for the MIMREPADM program:

usage: mimrepadm [common-options] [install-options | options]
[source_database]

  common-options:

    -?, --help            Display this help and exit
        --version         Output version information and exit

  install-options:

    --install=source      Creates the source replication dictionary
    --install=target      Creates the target replication dictionary
    --uninstall=source    Drops the source replication dictionary
    --uninstall=target    Drops the target replication dictionary

  options:

    -r password, --rpassword=password    REPADM password
                 --susername=user        Source user name
                 --spassword=password    Source password
                 --tdatabase=database    Target database
                 --tusername=user        Target user name
                 --tpassword=password    Target password

Replication Setup

The first step in setting up a replication environment is to use the install option of the MIMREPADM program. This needs to be done on both the source database and the target database. This is done by running the MIMREPADM program with the following arguments

mimrepadm --install=source [source-database-name]

and

mimrepadm --install=target [target-database-name]

If the database name is omitted, the program will prompt for a database name. The installations must be run as a user with DATABANK and IDENT privilege. The program will prompt for name and password for such a user.

The installation on the source database consists in creating two users, REPADM and REP_SOURCE_USER. REPADM is the user that will own the replication dictionary. These dictionary tables are created in a databank named REPADM that is created by the install program. REP_SOURCE_USER is the user, which will be used when performing the actual replication on the source database.

On the target database the installation consists in creating the user REP_TARGET_USER. This user is used for performing operations on replicated tables on the target database.

The replication environment can be removed by using the uninstall option for the MIMREPADM program, i.e.

mimrepadm --uninstall=source [source-database-name]

and

mimrepadm --uninstall=target [target-database-name]

Uninstall is not allowed if there are any subscriptions (see next chapter) defined for the database.

Note: Do not try to remove the replication environment by dropping any users explicitly. Always use the uninstall option for this.

Replication Administration

A subscription defines which tables that should be replicated for a specific source database and target database. It is possible to have multiple subscriptions between the same source and target database, e.g. if tables owned by different users should be replicated. Subscriptions are defined by using the MIMREPADM program. In this case, the MIMREPADM program is started with the following options specified

mimrepadm -r password  database

where password is the password for the REPADM user and database is the name of the source database. Before creating a subscription it is required to specify the target database and the users, which own the tables to be replicated. These values can be specified by giving arguments to the MIMREPADM program or by using the CONNECT SOURCE USER and CONNECT TARGET USER statements.

When the MIMREPADM program is started, it will prompt for commands. Since the program reads from standard input and writes to standard output it is possible to use OS primitives for piping and redirection. The following sections describe the available commands.

As a step in the setup process, the target database environment (users, databanks and tables) must be created. This must be done manually, since the replication system cannot be used to any target database objects. (Perhaps MIMLOAD can be useful.)

CREATE SUBSCRIPTION

Creates a subscription.

Description

The name of a subscription follows the normal rules for identifiers in SQL (see Mimer SQL Reference Manual for more details.) When a subscription is created, a log table and triggers for logging all write operations are created for each table in the subscription. Since it is only the owner of a table that has the right to create triggers on a table, all tables in a subscription must be owned by the source user. Further requirements is that there is a primary key constraint defined for the replicated table and that the table is not located in a databank having work option. The table used for logging all changes done on the replicated table is created in the same databank in which the replicated table is located. The logging will include information about which transaction the operation belongs to.

When a subscription is created, a corresponding table for each replicated table must exist on the target database. They do not need to have the same name or be located in the same schema as on the source database but the definition must be the same. Within a subscription it is possible to define that a table is replicated to multiple tables on the target database.

Note: There is currently no option for automatically creating the tables in the target database. It is the responsibility of the user to create these tables.

Since the MIMREPADM program grants delete, insert and update privilege on the specified tables to the REP_TARGET_USER user, the target user must have these privileges with grant option for all tables in a subscription.

Example
REPLICATION>create subscription SUB_MIMER_STORE to DUSTPUPPY
REPLICATION& for schema MIMER_STORE as ROC
REPLICATION& interval '10' minute
REPLICATION& commit after 10;

This means that all tables in the schema MIMER_STORE will be replicated to similarly named tables in the schema ROC on the database DUSTPUPPY. The interval value specifies at which interval the REPSERVER program will look for data to replicate. The interval literal must be a short interval, i.e. date fields from day to second can be used. (The interval literal format is described in Mimer SQL Reference Manual). The value cannot be negative. The default value is 15 minutes. If a zero interval is given, the replication will be continuous. The commit after clause tells how many source transactions should be bundled in a target transaction. The default value is 1.

REPLICATION>create subscription SUB_MIMER_STORE_MB to CANARDO
REPLICATION& for table MIMER_STORE.MUSIC, MIMER_STORE.BOOKS;

Create a subscription for replication of the table MIMER_STORE.MUSIC and MIMER_STORE.BOOKS to the database CANARDO using default value for timing and commit rate. There must exist a schema named MIMER_STORE containing the tables BOOKS and MUSIC on the database CANARDO.

ALTER SUBSCRIPTION

Alters a subscription.

Description

The alter subscription statement is used to add or drop tables to an existing subscription. It can also be used to change the default replication interval or the commit rate.

It is not possible to alter a subscription if the REPSERVER program is running for that subscription.

Examples
REPLICATION>alter subscription SUB_MIMER_STORE
REPLICATION& drop table MIMER_STORE.MUSIC;

The table MIMER_STORE.MUSIC will not be replicated any more. The triggers defined on this table and the log table will be dropped.

REPLICATION>alter subscription SUB_MIMER_STORE commit after 1;

Change the transaction rate so that each transaction on the source database will be treated as one transaction on the target database.

DROP SUBSCRIPTION

Drops a subscription.

Description

Drop all information about a subscription from the replication dictionary. It will also drop triggers and the log table, which were created when the subscription was created.

It is not possible to drop a subscription if the REPSERVER program is running for that subscription.

Example

REPLICATION>DROP SUBSCRIPTION SUB_MIMER_STORE;

DESCRIBE SUBSCRIPTION

Describes a subscription.

Description

Display information about the specified subscription.

Example
REPLICATION>describe subscription SUB_MIMER_STORE_MB;

Subscription SUB_MIMER_STORE_MB:

Target database: CANARDO
Interval:        600 second(s)
Commit after:    1
SYSTEM_STARTUP:  2006-08-27 18:08:52
TRANSNO:         4711
SEQNO:           12
STOPPING_FLAG:   NO

Tables:

Source: MIMER_STORE.MUSIC
Target: MIMER_STORE.MUSIC

Source: MIMER_STORE.BOOKS
Target: MIMER_STORE.BOOKS

LIST SUBSCRIPTIONS

Lists subscriptions.

Description

Lists all defined subscriptions

Example
REPLICATION>LIST subscriptions;

Subscriptions
=============
MIMER_STORE
MIMER_STORE_MB

CONNECT SOURCE USER

Connects user to source database.

Description
Connect the table owner to the source database.
Example
REPLICATION>CONNECT SOURCE USER MIMER_STORE
REPLICATION& using 'GoodiesRUs';

CONNECT TARGET USER

Connects user to target database.

Description

Specify user on target database.

Example
REPLICATION>CONNECT TARGET to CANARDO
REPLICATION& user MIMER_STORE using 'niTeoW1';

DISCONNECT SOURCE

Disconnects user from source database.

Description
Disconnect the user connected to the source database.
Example
REPLICATION>DISCONNECT SOURCE;

DISCONNECT TARGET

Disconnects user from target database.

Description
Disconnect the user connected to the target database.
Example
REPLICATION>DISCONNECT TARGET;

ENTER SOURCE

Connects a PROGRAM ident to source database.

Description
Connects a PROGRAM ident to the source database.
Example
REPLICATION>ENTER SOURCE 'PgmIdnt' USING 'SecrtPlees';

ENTER TARGET

Connects a PROGRAM ident to target database.

Description
Connects a PROGRAM ident to the target database.
Example
REPLICATION>ENTER TARGET 'PgmIdnt' USING 'SecrtPlees';

LEAVE SOURCE

Leaves a PROGRAM ident from the source database.

Description
The current source PROGRAM ident is left and the saved environment of the previous ident is restored.
Example
REPLICATION>LEAVE SOURCE;

LEAVE TARGET

Leaves a PROGRAM ident from the target database.

Description
The current target PROGRAM ident is left and the saved environment of the previous ident is restored.
Example
REPLICATION>LEAVE TARGET;

SHOW SETTINGS

Shows source and target info.

Description

Display information about source and target user.

Example
REPLICATION>SHOW SETTINGS;

Settings
========
Source database: MOONBASE_ALPHA
Source user:     MIMER_STORE
Target database: CANARDO
Target user:     MIMER_STORE

Source program:  PgmIdnt
Target program:  Not connected

EXIT

Exits MIMREPADM.

Description

Exit from the MIMREPADM program.

Example
REPLICATION>EXIT;


REPSERVER - Replicating the Data

The actual replication is performed by running the REPSERVER program. This program will handle the replication for one subscription. This program connects to source database as REP_SOURCE_USER and to the target database as REP_TARGET_USER.

MIMSYNC is typically used before replication is first set-up, or has been halted for some reason, to make sure that source and target tables have the same contents. After the synchronization, the replication functionality ensures that the tables remain identical.

Syntax

usage: repserver [options (except -t)] [source_database] [subscription]
       repserver -t [--spassword=password] [source_database] [subscription]

  options:

    -l logfile, --logfile=logfile Logfile, if omitted standard output
    -e,         --exit                  Exit option
    -t,         --stop                  Terminate replication server
    -?          --help                  Display this help and exit
                --verbose               Turn on verbose output
                --version               Output version information and exit
                --spassword=password    REP_SOURCE_USER source password
                --tpassword=password    REP_TARGET_USER target password

If any required parameter is omitted, the program will prompt for these values.

The REPSERVER program will read the log tables for all tables in the subscription and perform the same operations on the target database. After each commit on the target database the data in the log tables will be deleted. Once all operations have been done the program will sleep for the rest of the interval specified for the subscription. If the interval for the subscription is set to 0 the program will poll the log tables for any data continuously.

Start the Replication

To start the replication for a subscription the REPSERVER program can be started with following command line arguments:

repserver [--spassword=password] [--tpassword=password] [--logfile=logfile]
    [--exit] database [subscription-name]

Note:	The REPSERVER program should normally be run as a detached process on VMS, or as a background process on Unix.

Stop the Replication

To stop the replication the REPSERVER program should be run with the following options

REPSERVER -t [--spassword=password] database subscription-name

This will set the stopping flag in the replication dictionary to 'YES' for the specified subscription. The REPSERVER program will periodically look at this flag, when not active. This means that it can take some time before the replication is stopped.

Error handling

Most Mimer SQL errors are considered fatal for REPSERVER, with the exception of the following three:

  • Error -10101, INSERT operation invalid because the resulting table will contain a primary key duplicate
  • Error -10110, unique constraint violation
  • Error 100, record for update or delete not found

These errors will only result in a warning. This is to make replication possible even if the target table is not identical to the source table.

If REPSERVER gets a transaction conflict, it will try to execute the transaction once more. If the second attempt fails REPSERVER considers this a fatal error.


MIMSYNC - Synchronizing Tables

A third component in the replication service is the MIMSYNC program. It is typically run in batch and operates on pairs of tables, where one table resides in the source database and the other resides in the target database. Data manipulation operations are performed to ensure that the two tables contain the same rows. The table in the source database is considered to be the master, which means that MIMSYNC will only modify (delete/insert/update) the table in the target database.

Synchronization can in some cases be used instead of replication. If the replication updates only need to be performed, say, every 24 hours, this could be done by a batch job running MIMSYNC each night.

The MIMSYNC program supports synchronization between tables in the source and target database. The program operates on pairs of tables, and compares the contents of the two tables in a pair and makes both contain the same records. The table in the source database is considered to be the master, which means that it is the table in the target database that will be updated. The SQL statements needed to modify the target table are constructed and grouped into reasonably large transactions (1 000 rows).

Syntax

usage: mimsync -s [options] [subs-options]  [source_database] [subscription]
       mimsync -t [options] [table-options] [source_database]

   options:
    -s    Synchronizes all tables in a subscription
    -t    Synchronizes a pair of tables
    -l logfile, --logfile=logfile    Logfile name, if omitted standard output
    -?          --help               Display this help and exit
                --noexecute          Do not update target, verify only
                --version            Output version information and exit

  subs-options:
    --spassword=password    REP_SOURCE_USER source password
    --tpassword=password    REP_TARGET_USER target password

  table-options:
    --stable=table          Source table
    --ttable=table          Target table
    --susername=user        Source user
    --spassword=password    Source password
    --sprogram=program      Source program
    --susing=password       Source program password
    --tdatabase=database    Target database
    --tusername=user        Target user
    --tpassword=password    Target password
    --tprogram=program      Target program
    --tusing=password       Target program password

If the database, user and/or password switches are not given the program will prompt for database, user and/or password. The other switches are optional.

Examples
$ mimsync -s --logfile=synclog --spassword=secret --tpassword=secret
    sourcedb subs1

$ mimsync -t --suuser=SrcUsr --spassword=scrt
    --tdatabase=TrgDb --tuser=TrgUsr --tpassword=scrt
    table1 table2 SrcDb
Authorization

When synchronizing a subscription MIMSYNC is run as REP_SOURCE_USER and REP_TARGET_USER.

When synchronizing a pair of tables MIMSYNC can be run as any user (having SELECT and INSERT rights).

Restrictions
  • The tables must have a primary key
  • The tables must have the same definition

    • Note: MIMSYNC may fail to synchronize a table that has a foreign key reference to itself. Also circular foreign keys may cause problems.

Output

When executing MIMSYNC, it will write execution information to its log file. (If no log file is specified, standard output will be used.)

Example log file:
2006-11-29 09:33:41.62   <Information>
------------------------------------------------------------------------
Starting Mimer SQL Synchronization

2006-11-29 09:33:42.74   <Information>
========================================================================
Mimer SQL 9.3.3C Beta Test Nov 12 2006
Mimer SQL Synchronization
for subscription S on database SOURCEDB STARTED

Synchronizing table SYSADM.T1...
Synchronization of table SYSADM.T1 complete

Synchronizing table SYSADM.T3...
Synchronization of table SYSADM.T3 complete

2006-11-29 09:33:42.98   <Information>
------------------------------------------------------------------------
Mimer SQL Synchronization for subscription S STOPPED


Mimer SQL Shadowing

Mimer SQL Shadowing makes it possible to create and maintain one or more simultaneously updated copies of a databank. This allows for a higher degree of data availability by giving extra protection from disk crashes, etc.

This chapter describes the functions and benefits of databank shadowing, how to use Mimer SQL Shadowing, and how to handle problems.

Note: Mimer SQL Shadowing is not included in the standard Mimer SQL distribution. In order to implement shadowing, you must have the correct type of license. Contact Mimer Information Technology AB for more information.


About Databank Shadowing

Databank shadowing means updating one or more copies of a databank simultaneously.

The master is the `normal' databank file which is accessed for data storage and retrieval. The copies are called shadows. A databank can have more than one shadow.

Any changes to the master databank are automatically made to the shadow, thus protecting data from a disk crash or other event that might cause a databank to be lost.

If a master databank is lost, a shadow will automatically take over from the master and operations can be resumed immediately, assuming the shadow is not also damaged.

A shadow can be transformed to a master databank to permanently replace it and this process is much faster than restoring a databank from a backup copy.

Using offline shadows provides a straightforward way of backing-up your databanks. Once you have completed the backup and set the shadow online, all operations performed on the master databank are applied to the shadow automatically.

Databank shadowing is entirely invisible to an application. This means that shadows can be added to existing applications. No special handling is needed to access tables in a shadowed databank.

Shadowing Requirements

A databank must support transaction control, see Transaction Control, to be shadowed.

Databanks with the WORK option cannot be shadowed, because shadowing requires transaction handling.

The databank to be shadowed cannot be used by any other users while a shadow is being created.

The shadow name cannot be the same as the name of the master databank, of any other shadow, or of any shadow that has been transformed to a master.

SYSDB and Shadowing

Because the SYSDB databank holds all the data dictionary information about your database, protecting it with shadowing and/or backups is essential.

Otherwise, if SYSDB is lost, the whole database will be unreachable.

SQLDB and Shadowing

Shadowing SQLDB is not allowed, as it is a TEMPORARY databank, and not needed because SQLDB only contains temporary data.

Creating Shadows

When you create a shadow for a databank, all tables and indexes in the databank are copied to the shadow.

Creating a shadow for a large databank may take some time, and thus should be carefully planned.

Altering Shadows

When you alter a shadow to a master, it only affects the Mimer SQL data dictionary. The databank file names are not changed. The databank cannot be used by any other user when a shadow is being transformed into the master (this is not very likely to happen since this function is normally used when the master has been lost or damaged).

Backups

We recommend that you take conventional backups as a supplement to databank shadows to protect data in the event of a crash that destroys both the master and the shadow.

Because operations are not interrupted when shadow-backups are taken, and because Mimer SQL databanks are automatically reorganized, you get true 24 hour-a-day operation.

Dropping Shadows

When a shadow is dropped, the file where the shadow is stored is usually deleted from the file system. If it is not deleted, you can use operating system facilities to delete it.

The databank cannot be used by any other users while a shadow is being dropped.


Levels of Data Protection

Backing-up and Restoring Data, describes the role of the system databanks LOGDB and TRANSDB when used in conjunction with backup and restore, in protecting data against loss.

Databank shadowing provides an even higher level of protection. Listed below are the different ways in which data can be protected from loss (from the least amount of protection to the highest).

All Databanks on One Disk and No Logging

If a databank is lost with this level of protection, it is only possible to continue operations from the last backup copy (all changes since the last backup was taken are lost).

Databanks can be lost due to accidental deletion, disk crashes (which can destroy all files on a disk), etc. This level of protection is not recommended except for trash databanks with unimportant contents.

Logging, with LOGDB and TRANSDB on a Separate Disk from the Data

LOGDB and TRANSDB are vital databanks if the system stops or if any databanks are lost. Because of this, LOGDB and TRANSDB should be stored on separate disks, as shown in the following figure:

Application data should be stored on the TRANSDB disk if it cannot be stored separately.

If a databank is lost, it can be restored to its original state by applying the transactions in LOGDB and TRANSDB to a restored backup of the databank.

This may take some time, especially if the databank is large and if there is a lot of transaction information stored in LOGDB.

Caution: If the databank disk and the TRANSDB or LOGDB disk are handled by the same disk controller, a disk controller failure may cause both disks to crash.

If this happens, the databanks can only be restarted from the state of the last backup copy. Therefore, we advise you to use separate disks with separate disk controllers.

This security level gives a high degree of security and is recommended for databanks containing important data used in a system where the delay before the system is restored after a crash is not critical.

To assure this high degree of security, backup files should always be stored on separate removable media (e.g. CD/RW).

Shadowing, with Shadows on a Separate Disk

Shadows should always be stored on a separate disk from the masters to protect them from a total disk crash that could destroy both the master and shadow databanks.

It is also advisable to use separate disk controllers to assure that a corrupt disk controller does not destroy the disks holding both the masters and the shadows.

If a databank is lost, its shadow can be transformed into a master and the shadow automatically takes over with no loss of data.

Since shadows are updated after the master, and operations are saved in TRANSDB until the shadow is updated, it is important that TRANSDB is consistent when a shadow is transformed.

To ensure this, you should shadow TRANSDB. We strongly recommend that TRANSDB and its shadow are stored on separate disks, as shown in the following diagram:

This arrangement gives a high degree of security and is recommended for databanks containing important data used in a system where it is vital to be able to get the system running again quickly after a disk crash.

Shadowing and Logging

Combining shadowing and logging, see Backing-up and Restoring Data, gives the highest level of data protection.

If logging is used, the data is protected if both the master and the shadow disks become corrupted.

And, when shadowing is combined with logging (with LOGDB on a third disk) and backups are regularly taken to separate media (CD/RW, etc.), then data is always protected if any two of the disks crash, for example:

Of course, additional disks can be used, just as long as the databanks that are separated above are not placed on the same disks. If you only have two disk drives available and all the databanks are shadowed, then logging is of little value. Shadowing LOGDB will not increase data protection significantly in this configuration.

Different degrees of data security can be used for different databanks, depending on the importance of the data. It is however important that all inter-dependent databanks (because of foreign key relationships, etc.) have the same level of protection. Otherwise logical inconsistencies may result if there is a disk crash.


Creating and Managing Shadows

Shadows are managed using the SQL shadowing commands:

CREATE SHADOW - creates a shadow

SET SHADOW - sets shadows on- or offline

ALTER SHADOW - swaps a shadow with its master

DROP SHADOW - drops a shadow.

Note: For shadow information, use the LIST SHADOWS command in BSQL.

Privileges

If you have SHADOW or BACKUP privilege, you can list shadowing information for all databanks.

Users can always backup and restore, set shadows offline and online, and list shadowing information for any databank that they have created.

SYSADM is initially granted SHADOW and BACKUP privilege with the WITH GRANT OPTION.

The following table shows the privileges you need to work with shadows:

Tasks
Privilege
Backup
Shadow

Create a shadow *)

X

Drop a shadow *)

X

Transform a shadow *)

X

List shadow info

X

X

Backup and restore

X

Set shadow offline

X

*) You must have exclusive use of the databank. This means that no other user can access the databank until the operation is finished.


SQL Shadowing Commands - an Example Session

In the following sections, we use an example session to show how to:

  • create shadows
  • set them offline and take backup
  • set them online again
  • restore both user databanks and shadows
  • drop shadows.
About the Examples

The examples in the following sections are based on:

Ident:

BACADM

Ident Privileges:

DATABANK, SHADOW and BACKUP privileges

User databanks:

ARTICLES, CUSTOMERS with the LOG option enabled

System databanks:

TRANSDB, SYSDB and LOGDB

We assume that you are the ident BACADM and have connected to the database using Mimer BSQL, as follows:

SQL> CONNECT;
User: BACADM
Password: Masterp1ece

Creating a Shadow

The following example creates shadows for the user and system databanks:

SQL> CREATE SHADOW   TRANSDB_SH   FOR TRANSDB   IN 'transdb_sh.dbf';
SQL> CREATE SHADOW   SYSDB_SH     FOR SYSDB     IN 'sysdb_sh.dbf';
SQL> CREATE SHADOW   LOGDB_SH     FOR LOGDB     IN 'logdb_sh.dbf';
SQL> CREATE SHADOW   ARTICLES_SH  FOR ARTICLES  IN 'articles_sh.dbf';
SQL> CREATE SHADOW   CUSTOMERS_SH FOR CUSTOMERS IN 'customers_sh.dbf';

For information on the CREATE SHADOW command, see the Mimer SQL Reference Manual, CREATE SHADOW.

Setting a Shadow Offline

You must set a shadow offline, for example, when backing-up a databank, to ensure that the databank shadow file is in a consistent state when the backup copy is taken.

The following example sets all the shadows created in the previous example offline:

SQL> SET SHADOW TRANSDB_SH,LOGDB_SH,SYSDB_SH,ARTICLES_SH,CUSTOMERS_SH
OFFLINE;

For information on the SET SHADOW command, see the Mimer SQL Reference Manual, SET SHADOW.

Caution: TRANSDB stores all operations carried out while shadows are offline. We recommend that you always set shadows online as soon as possible. If you do not, you risk TRANSDB filling disk capacity.

Backing-up from Shadows

You can back-up a databank using its shadow instead of the master databank.

This allows the backup process to proceed without affecting the users working with data contained in the databank.

When a shadow is set offline, the relevant transactions will be written to the online databank and remain in TRANSDB until the shadow is set online again and the transactions can be written to it.

To back-up your database from shadows:
  1. Set the shadows offline, as shown in the previous example.
  2. Use your operating system's functionality to copy the shadow files. For example, on UNIX:
    3. $ cp sysdb_sh.dbf      sysdb_sh.bac
$ cp transdb_sh.dbf    transdb_sh.bac
$ cp logdb_sh.dbf      logdb_sh.bac
$ cp articles_sh.dbf   articles_sh.bac
$ cp customers_sh.dbf  customers_sh.bac
  3. Set the shadows online and reset the log, as shown in the next example.
  4. Move the backup files to a safe medium, such as CD/RW. Your backup is complete.

Setting a Shadow Online

When you set databank shadows online, they are automatically updated in the background to the current state of the master database.

Continuing with the previous example, you can set the shadows online, as follows:

SQL> SET SHADOW SYSDB_SH,LOGDB_SH,TRANSDB_SH,ARTICLES_SH,CUSTOMERS_SH ONLINE
RESET LOG;

Note:	To get a backup timestamp, to be able to use the log when restoring from this backup, the RESET LOG option is used.

For information on the SET SHADOW command, see the Mimer SQL Reference Manual, SET SHADOW.

Restoring a User Databank

If an error is encountered on a user databank, the system will continue to operate using the databank shadow. You can restore the damaged user databank by swapping it with its shadow using the ALTER SHADOW command.

For example, if the ARTICLES databank has been damaged, you can restore it by swapping it with its shadow ARTICLES_SH using the ALTER SHADOW command:

SQL> ALTER SHADOW ARTICLES_SH TO MASTER;

Now, the faulty ARTICLES databank is the shadow. However, it has the same name as the master databank.

To return to our original situation, we must delete (drop) the faulty shadow, create a new shadow and swap the shadow with the master so that the databanks are correctly named.

SQL> DROP SHADOW ARTICLES;
SQL> CREATE SHADOW ARTICLES FOR ARTICLES_SH IN 'articles.dbf';
SQL> ALTER SHADOW ARTICLES TO MASTER;

The first command deletes the shadow.

The second command creates a shadow for the master with the original name and location.

The third command swaps the shadow with the master.

Note: If the original situation is not restored as shown above, the shadow name, in this case ARTICLES_SH, will remain allocated internally which could be confusing.

For information on the ALTER SHADOW command, see the Mimer SQL Reference Manual, ALTER SHADOW.

Restoring Both a User Databank and Its Shadow

If both a user databank and its shadow are lost or damaged, you can restore the data using the shadow's backup files and LOGDB.

  1. We recommend that you stop the Mimer SQL database server when replacing databanks.
  2. Copy the shadow's backup file to the position of the damaged databank file, for example, on a UNIX system:
    3. $ cp articles_sh.bac articles.dbf
  3. Restart the Mimer SQL database server and use the ALTER DATABANK RESTORE command to restore the databank:
    4. SQL> ALTER DATABANK ARTICLES RESTORE USING LOG;

Note:	The restore command above will automatically recreate the corresponding shadow databank.

For more information on ALTER DATABANK RESTORE, see the Mimer SQL Reference Manual, ALTER DATABANK RESTORE

Restoring System Databanks

You cannot use the ALTER SHADOW command to swap the system databanks SYSDB, TRANSDB, and LOGDB with their shadows in order to restore them. You must alter them using the BSQL program.

For more information, see:

  • Transforming a SYSDB Shadow to a Master
  • Transforming a TRANSDB Shadow to a Master
  • Transforming a LOGDB Shadow to a Master.

Dropping a Shadow

As seen in a previous example, you can delete a shadow by dropping it, for example:

SQL> DROP SHADOW ARTICLES_SH;

For information on the DROP SHADOW command, see the Mimer SQL Reference Manual, DROP.


Shadowing System Databanks

The system databanks (SYSDB, TRANSDB, LOGDB, and SQLDB) require special handling in some situations.

If a problem occurs with these databanks or their shadows, the only permitted login is SYSADM logging into the BSQL program. The BSQL program will then recognize the problem and help you correct it.

System databanks are handled in the same way as other databanks, with the following exceptions:

  • If an error is encountered on a user databank, automatic shadowing fail-over takes place. However, if there is a problem with SYSDB, TRANSDB, SQLDB or LOGDB, new users cannot login. Users already active will receive an error message when attempting operations that depend on the affected system databank, while other operations continue to work. The error state is held until Mimer SQL is stopped and the error is corrected.
  • If there is a problem with SYSDB, TRANSDB or LOGDB shadows, new users cannot login until the faulty shadow is dropped or suspended, see If a Shadow for SYSDB, TRANSDB or LOGDB Is Not Accessible.
  • No users can be connected while a shadow for SYSDB, TRANSDB, or LOGDB is being created, altered or dropped.
  • You cannot create a shadow for SQLDB as it is a TEMPORARY databank.

Transforming a SYSDB Shadow to a Master

If SYSDB is lost or corrupt, any existing SYSDB shadow can be altered to become the master in order to allow Mimer SQL to start again.

The SYSDB shadow file should be renamed and/or moved to the location where the master SYSDB was. Then the BSQL is started and login is performed as SYSADM. Enter the name of the shadow to be transformed into the master, and exit.

Example
Mimer SQL command line utility, version 9.3.7
Username: SYSADM
Password:
MIMER/DB warning -18013 in function CONNECT
         MIMER/DB started from SYSDB shadow. Transform SYSDB shadow to master
         with BSQL, or restart system from master SYSDB

 -- Transform shadow --

DATABANK
SHADOW
OFFLINE
FILE
========================================
SYSDB
SYSSH
N
SYSDB_S
---
One shadow found
Name of shadow to transform (<CR> = skip): sysdb_s

Shadow SYSSH transformed to master

If the disk where SYSDB is located becomes inaccessible, it may be more suitable to redefine the database home directory (to point out the SYSDB shadow) instead of restoring the original directory structure.

Note: In this case the ALTER DATABANK statement must be used for all databanks explicitly defined to be located on the halted disk, i.e. with an absolute file specification in the data dictionary.

Restoring SYSDB

If SYSDB is lost and no shadows exist, a backup copy of SYSDB can be restored to allow Mimer SQL to start again, an example of how to do this is given in Backing-up and Restoring Data.

TRANSDB and Shadowing

Shadowing TRANSDB assures that you can bring your database up-to-date if the TRANSDB master is lost or damaged.

Transforming a TRANSDB Shadow to a Master

Start the BSQL program and login as SYSADM. A message is displayed saying that TRANSDB cannot be opened, and a shadow must be transformed to the master, this is similar to the example in Transforming a SYSDB Shadow to a Master.

If there are uncompleted transactions they will be completed, as if the original TRANSDB was still functioning.

LOGDB and Shadowing

If some databanks are not shadowed but backup copies of the databanks exist, then a shadow of LOGDB is useful since LOGDB, in this case, is even more important from a restore perspective.

Transforming a LOGDB Shadow to a Master

Start the BSQL program and login as SYSADM. A message is displayed saying that LOGDB cannot be opened, and a shadow must be transformed to the master, this is similar to the example in Transforming a SYSDB Shadow to a Master.

If there are transactions not yet written to the log, they will be written automatically.

SQLDB and Shadowing

Shadowing SQLDB is not necessary because SQLDB only contains temporary data.

However, SQLDB is required when a user logs on to Mimer SQL. If SQLDB is corrupt or lost, you must recreate it by logging on to the BSQL program as SYSADM. This automatically recreates SQLDB if the databank is not found.

If a Shadow for SYSDB, TRANSDB or LOGDB Is Not Accessible

If a shadow for SYSDB, TRANSDB or LOGDB is not accessible, SYSADM should login to the BSQL program.

An error message is given followed by the option to drop the shadow or set it offline. If the shadow is corrupt or missing, you should drop it. For example:

Mimer SQL command line utility, version 9.3.7
Username: SYSADM
Password:
MIMER/DB fatal error -16142 in function CONNECT
         Cannot open databank LOGDB_S,
         file logdb_s not found

Inaccessible shadow encountered. DROP or SET OFFLINE? (D/S): D

                                -- Drop shadow --

Shadow LOGDB_S dropped

If the shadow is only temporarily unavailable, it may be enough to set it offline for a short period of time.


Data Protection Strategy

So far, this chapter has described the facilities that are available for a system manager to maintain the system.

But how do you know that you are using the functionality in the right way, and that you will be able to get the system running again if something happens?

The answer is planning and practice. When you have planned a data protection strategy, you should put it into practice by simulating a disk crash and restoring the databanks according to your strategy.

Check that the contents of all tables are the same as before the simulated crash. Do not forget to simulate a crash of the system databanks (SYSDB, TRANSDB, LOGDB and SQLDB) and then restore or recreate them.

Once you have a successful strategy, build command files (scripts) that perform the correct operations regularly.


Configuring Your System

In Levels of Data Protection, we discussed various levels of data security.

When you configure your system, there are some additional questions to be answered:

· How do I divide the workload over several disks to get the best performance?

TRANSDB and its shadows should preferably be on fast disks, see Performance Aspects of Shadowing.

· How much disk space do I need for shadowing?

A shadow file occupies the same amount of disk space as the master databank file.

In general, you will have problems if TRANSDB or LOGDB run out of disk space. When this happens the system cannot continue. Therefore, it is important to make sure that there is enough space for these databanks.

How large TRANSDB grows depends on the number of transactions and how fast the background threads are able to perform the transactions on the shadows. When the transactions are performed on the shadows, the space in TRANSDB where they were stored is released.

How large LOGDB grows depends on whether the LOG option is used and how often backups are taken and DROP LOG is performed, since DROP LOG clears LOGDB and releases space (but the file size remain unchanged).

Note: Try and keep the length of time the shadows are offline to a minimum. If you do not, you risk TRANSDB growing and filling the disk to capacity.


Performance Aspects of Shadowing

In multi-user systems, performance is not noticeably affected by shadowing, even though the shadowing system needs more machine resources because more files need to be updated.

Applications do not have to wait for the shadows to be updated as this is done in the background. Actually, all updates to the disk, even to master databanks, are performed by background processes (except for updates to TRANSDB and its shadows).

In single-user systems, no background process is used. This means that an application has to wait for the shadows to be updated.


Troubleshooting

If shadow updating is delayed TRANSDB grows. This can happen for several reasons:

  • A shadow has been set offline and forgotten. If this happens, transactions will be buffered until the shadow is set online again.

    • To check if a shadow is offline, in BSQL use the LIST SHADOWS command or the The Performance Report.

  • A shadow is corrupt. Updates to the shadow result in an I/O error, and are buffered in TRANSDB.

    • When this happens, the operator is notified by the system. To check, for notification, use the The Performance Report and check your Mimer SQL database server log, see Database Server Log.

  • There are too few background threads to update the shadows, or they get too little machine resources.


Database Statistics

The SQL statistics statements collect statistical information about table and index data in the database and store this information in the data dictionary.

The information is used by the SQL compiler in optimizing access paths for SQL queries.

The statistical information includes:

  • the total number of rows in each base table
  • the number of distinct values in each column of a table
  • the number of non-NULL values in each column of a table
  • the lowest and highest values in each column of a table


Authorization

The user executing the SQL statistics statements must either have STATISTICS privilege or be the owner of the table(s) or ident(s) for which statistics are being collected.

The database administration ident SYSADM holds STATISTICS privilege with the WITH GRANT OPTION, and may thus take responsibility for maintaining statistics for the whole system or delegate the responsibility to selected idents.

Note: A user with STATISTICS privilege is not necessarily permitted to read the contents of the databank using data manipulation statements, this privilege only permits access for the collection of statistics.


The SQL Statistics Statements

The SQL statistics statements UPDATE STATISTICS may be used to collect statistical information in the areas described below. Also refer to the Mimer SQL Reference Manual, UPDATE STATISTICS for details.

Statistics may be collected for the entire database, i.e. all tables in all databanks recorded in the same SYSDB, for tables owned by specified idents, or for specific tables.

The statement DELETE STATISTICS is used to remove the statistics collected. See Mimer SQL Reference Manual, DELETE STATISTICS for details.

Note: The database remains fully accessible while statistics are being collected (or deleted).

Statistics for the Entire Database

To collect statistical data for all tables in the database, use the following function:

SQL> UPDATE STATISTICS;

The user must have STATISTICS privilege.

Note: Even in a database of only moderate size, collecting statistical data for all tables is time-consuming. We recommend that you run this option in particular at off-peak times.

Statistics for Specified Idents

To collect statistics for all base tables belonging to schemas owned by a list of specified idents, use the following function:

SQL> UPDATE STATISTICS FOR IDENT list-of-idents;

A user requesting statistics for tables belonging to a schema owned by an ident other than himself must have STATISTICS privilege.

To collect statistics for SYSDB, the pseudo-ident SYSTEM may be specified.

Statistics for Specified Tables

To collect statistics for a list of specified tables, use the following function:

SQL> UPDATE STATISTICS FOR TABLE list-of-tables;

The user requesting statistics for the tables specified in the list must either be the owner of them or have STATISTICS privilege.

Secondary Index Consistency

The update statistics facility includes an automatic function which ensures that all secondary indexes on tables contained in databanks with the TRANSACTION or LOG option are in a consistent state.

This function is performed in a way that makes it transparent to other users of the database and it is only performed on secondary indexes created on tables actually selected by the UPDATE STATISTICS statement.

It will take some time to verify the consistency of a secondary index. The data dictionary table TABLE_CONSTRAINTS can be used to determine which secondary indexes are flagged as not consistent (shown in the column named IS_CONSISTENT).

An index which is in a consistent state will offer optimal performance when used in a query.

All secondary indexes contained in a databank with the WORK option and those contained in a databank that has been upgraded from Mimer SQL version 7 or 8.1 will be flagged as not consistent.


When to Use the SQL Statistics Statements

Mimer SQL collects basic statistics for each table whenever the table is opened. These statistics may suffice for maintaining high performance in many situations.

If optimal performance is required for an application, the SQL statistics statements should be used to collect detailed information (this includes information on value distribution and table size).

When this is the case, statistics should be typically updated in the following situations:

  • when the size of a table has changed significantly
  • when the maximum/minimum limits on values in a table have altered significantly
  • when a databank has been altered from having the WORK option to having the TRANSACTION or LOG option and contains secondary indexes
  • when a databank with the TRANSACTION or LOG option contains secondary indexes and has just been upgraded from Mimer SQL version 7 or 8.1.

The statistics information in the data dictionary is used only by the Mimer SQL compiler.

Note: Only the performance, not the result, of an SQL statement is affected by gathering and using the statistical information and by ensuring the consistency of secondary indexes.


Executing in Single-user Mode

Usually, users access a Mimer SQL database via a database server (multi-user mode), but in some situations it may be necessary to restrict use of a database to a single user.

Any local database can be opened in single-user mode, provided there is no database server currently running against the database.

Note: An application started in single-user mode will access the databank files directly from within its own process.
This means that the operating system user who is running the application must have access, at the operating system level, to all the existing databank files.
All new databank files created in single-user mode will typically be owned in the operating system by that user.


File Protection in Single- and Multi-user Mode

If a database which has been created in single-user mode is to be used by a database server, or vice versa, certain precautions must be observed with regard to the databank files:

  • Files created in single-user mode must be accessible for read and write by a database server if the database is to be subsequently used in multi-user mode, since databank file access is performed by the database server process. The creator of the files should change the protection if necessary. Suitably the database server should have exclusive access to the databank files.
  • Conversely, files created in multi-user mode are created by the database server process and will not be accessible by a specific user who needs to access the database in single-user mode. The protection on these files can only be changed by an operating system user who has privileges equivalent to those of the database server process.

    • Note: Individual users should not generally have direct access to databank files.


Specifying Single-user Mode Access

If the database is to be accessed in single-user mode by default, the environmental variable or logical name called MIMER_MODE should be defined as SINGLE, as shown in the examples that follow.

If MIMER_MODE is not defined or is set to MULTI, or the database is a remote one, it will be accessed in multi-user mode by default.

If MIMER_MODE is set to SINGLE and the default database, see The Default Database, is set to point to a local database, the database will be opened in single-user mode. (Remote databases will be accessible through the client/server interfaces in multi-user mode).

Note: Many of the programs which are part of the Mimer SQL distribution support the command-line flags -s and -m (or /SINGLE, /MULTI) which control whether they access a database in single-user or multi-user mode.


Accessing in Single-user Mode

Mimer SQL applications that connect to a local database server when single-user access is indicated, will dynamically include a shared library when activated. This library holds all the functionality that normally is provided by the database server program.

Example

The following example session first connects to the INVENTORY database in single-user mode and then connects to the STAFF database, administered by a running database server process.

On UNIX:
$ MIMER_DATABASE=INVENTORY
$ MIMER_MODE=SINGLE
$ export MIMER_DATABASE MIMER_MODE
$ bsql
SQL> .
SQL> .
SQL> exit;
$ unset MIMER_MODE
$ MIMER_DATABASE=STAFF
$ export MIMER_DATABASE
$ bsql
SQL> .
SQL> .
SQL> exit;
On OpenVMS:
$ DEFINE MIMER_DATABASE INVENTORY
$ DEFINE MIMER_MODE SINGLE
$ RUN MIMEXE9:BSQL
SQL> .
SQL> .
SQL> exit;
$ DEASSIGN MIMER_MODE
$ DEFINE MIMER_DATABASE STAFF
$ RUN MIMEXE9:BSQL
SQL> .
SQL> .
SQL> exit;
On Windows:
C:\> SET MIMER_DATABASE=INVENTORY
C:\> SET MIMER_MODE=SINGLE
C:\> BSQL
SQL> .
SQL> .
SQL> exit;
C:\> SET MIMER_MODE=
C:\> SET MIMER_DATABASE=STAFF
C:\> BSQL
SQL> .
SQL> .
SQL> exit;


The SINGLEDEFS Parameter File

The use of a SINGLEDEFS parameter file is optional.

When a single-user mode connection is established, the SQLPOOL and bufferpool data areas are dynamically created.

The SQLPOOL area will grow dynamically if more space is needed, see SQLPOOL.

UNIX + VMS: To change the size of the bufferpool in single-user mode a SINGLEDEFS file, similar to the MULTIDEFS file, should be created in the database home directory.

A template of this file, showing the default values for the relevant parameters, can be found in the examples directory which is located in the Mimer SQL installation directory.

--
--  Parameters for single-user system
--
Databanks  100    -- (40-1000)     Max number of databanks
Tables     4000   -- (500-1000000) Max number of tables
Pages2K    128    -- (22-1000000)  Size of 2k bufferpool region (pages)
Pages16K    32     -- (22-1000000)  Size of 16k bufferpool region (pages)
Pages64K    32     -- (22-1000000)  Size of 64k bufferpool region (pages)

Note:	When changing parameters in the SINGLEDEFS file, always change the copy in the database home directory. Never change the template file in the examples directory.

Win:	The use of a SINGLEDEFS is not supported on the Windows platform.


The SQLHOSTS File on VMS and UNIX

This appendix applies to the OpenVMS and UNIX platforms only.

It describes the SQLHOSTS file which is used to list all the databases that are accessible to a Mimer SQL application from the node on which it resides.

For general information on how to make databases accessible, refer to Registering the Database.

UNIX: On a UNIX node, the path name of SQLHOSTS file is:

/etc/sqlhosts

The program called mimsqlhosts can be used to manage the contents of the SQLHOSTS file instead of editing it manually.

When the dbinstall program is used to install a local database on a UNIX node, an entry for it is automatically added to the LOCAL section, see LOCAL Section, of the SQLHOSTS file on that node.

If the file is not found, a default SQLHOSTS file is automatically generated. (See the mimsqlhosts and sqlhosts man-pages).

VMS: On an OpenVMS node, the SQLHOSTS file can have any name and is located by translating the logical name MIMER_SQLHOSTS. The MIMSETUP9 command will define it to be:

SYS$SPECIFIC:[SYSMGR]SQLHOSTS.DAT

A default SQLHOSTS file is generated by the installation of the Mimer SQL software.


The SQLHOSTS File

A line of text beginning with the character sequence -- is interpreted as a comment in the SQLHOSTS file.

The SQLHOSTS file contains three sections, called: DEFAULT, LOCAL and REMOTE.

The names of the local databases on the current node are listed in the LOCAL section, see LOCAL Section and the names of the remote databases accessible from the node are listed in the REMOTE section, see REMOTE Section.

One of the local or remote databases can be set to be the default database for the node by specifying its name in the DEFAULT section, see Default Section.

Database names may, in general, be up to 128 characters long and are case-insensitive.

VMS: The maximum length for the name of a database on an OpenVMS node is 30 characters.

The Default SQLHOSTS File

When the Mimer SQL system is installed on a node, the following default SQLHOSTS file is automatically generated:

-- -----------------------------------------------------------------------------
--
--  S Q L H O S T S
--  ===============
--
--  This file contains a list of all databases, local and remote, accessible
--  from the node where the file resides.
--
--  The DEFAULT label
--  -----------------
--  Name of default database. Can be either a REMOTE or LOCAL database name.
--  Can be overridden by setting MIMER_DATABASE to the name of a database.
--
--  The LOCAL label
--  ---------------
--  A list of all local databases on the current node, containing the
--  database name and a directory specification (Path).
--  UNIX Path -     database home, and directory path for databank lookup.
--  VMS Path -     database home.
--
--  The REMOTE label
--  ----------------
--  A list of all remote databases containing the database name, the database
--  node, the protocol to be used, the protocol interface and the protocol
--  service to be used.
--
--  Protocol, Interface and Service may be defaulted by entering ''.
--
--  Node -      network node name for computer on which the database resides.
--  Protocol -  currently tcp is supported. (tcp or '' should be specified)
--  Interface - currently not used ('' should be specified).
--  Service -   corresponds to the port number used in TCP/IP. The port number
--              Default is 1360, i.e. the port number reserved for MIMER.
--              On UNIX: The port number may either be a number or a name of a
--              service stored in the /etc/services file.
--
-- =============================================================================
DEFAULT:
--
-- Database
-- -----------------------------------------------------------------------------
   example_localdb
-- =============================================================================
LOCAL:
--
-- Database           Path
-- ------------------ ----------------------------------------------------------
(UNIX)      SINGLE             .
(VMS)       SINGLE             SYS$DISK:[]
(UNIX)      example_localdb    /directory
(VMS)       example_localdb    DISK:[DIRECTORY]
-- =============================================================================
REMOTE:
--
-- Database           Node               Protocol Interface Service
-- ------------------ ------------------ -------- --------- --------------------
   example_remotedb   server_nodename    ''       ''        1360

Default Section

The DEFAULT section contains a single line that specifies the default database which will be used by an application that does not explicitly specify a database to connect to, see The Default Database.

The default database should be one of those listed in the LOCAL or REMOTE sections.

LOCAL Section

The LOCAL section contains a list of all the local databases residing on the current machine, see The Local Database.

Each line under the LOCAL keyword should contain two fields, separated by one or more blanks or tab characters. The first field specifies the database name.

UNIX: On a UNIX node, the second field may be a colon (:) separated search path specification.

The first directory in the search path is taken as the database home directory and the other directories in the search path will be used to locate databank files which have a file specification stored in the data dictionary without an explicit directory.

VMS: On an OpenVMS node, the second field specifies a directory which will be the home directory for the database.

The Mimer SQL system databank SYSDB will be located in the database home directory and other databanks will typically be located relative to it, see Locating Databank Files.

REMOTE Section

The REMOTE section contains a list of all accessible databases that reside on other nodes in the network environment, see Accessing a Database Remotely.

Access to these databases is provided by using either DECNET or TCP/IP to establish a client/server connection to the remote machine.

Each entry in the REMOTE section contains up to five fields, separated by spaces and/or tab characters.

The DATABASE field specifies the name of the remote database.

The NODE field should specify the network node name of the remote machine. If the
TCP/IP interface is used, the IP address may be specified here.

UNIX: The PROTOCOL field should specify tcp or two single quotation marks ''.

VMS: The PROTOCOL field may specify DECNET or TCP depending on the type of network protocol that should be used to create the client/server connection. The default, specified by two single quotation marks '', is TCP.

The INTERFACE field is currently not used. Specify '' (two single quotation marks) here.

If using TCP/IP, the SERVICE field specifies the TCP/IP port number the database server uses. The default is 1360, which has been reserved by Mimer Information Technology AB for Mimer SQL client/server communication.

UNIX: When TCP/IP is used under UNIX, the value in the SERVICE field may be the actual port number, the name of a service stored in the /etc/services file or two single quotation marks '' for the default value 1360.

VMS: For a Mimer SQL version 9 (or 8) database server using DECNET, the SERVICE field should contain the database name, which is also the default.

The server listens to the network object using the same name as the database.

A Mimer SQL version 7 database server using DECNET listens to the network object named MIMER.

Remote Section Parameters

The remote section parameters are summarized below, depending on the protocol selected. The character sequence '' is two single quotation marks and specifies the default value for a parameter:

TCP

Parameter
Explanation
DATABASE

remote database name

NODE

TCP/IP node name or IP number

PROTOCOL

''

INTERFACE

''

SERVICE

CP/IP_port_number or TCP/IP service name or ''

Note: When '' is used to specify the default SERVICE, the TCP/IP port number 1360 will be used.

DECNET - OpenVMS Only

Parameter
Explanation
DATABASE

remote database name

NODE

decnet node name

PROTOCOL

DECNET

INTERFACE

''

SERVICE

decnet network object or ''

Note: When '' is used to specify the default SERVICE, the value of remote database name will be used for Mimer SQL version 8 and 9 and the value MIMER will be used for Mimer SQL version 7.

Local Client/Server Access

It is possible to use the client/server interface locally, i.e. having a remote database definition pointing to a local database server. This is the recommended access method when using Mimer SQL version 7 tools, such as QL and PG, towards a Mimer SQL version 9 database un Unix (on OpenVMS, use the MIMBUILD9 procedure).

To achieve this, the SQLHOST file must be updated in a specific way using a 6th parameter in the remote definition, as shown in the following example (current node is startrek, i.e. where the Mimer SQL version 9 database m9server runs):

--
-- ===========================================================================
DEFAULT:
--
-- Database
-- ---------------------------------------------------------------------------
   m9access
-- ===========================================================================
LOCAL:
--
-- Database           Path
-- ------------------ --------------------------------------------------------
   m9server           /database/m9server:/mnt1/m9server:/mnt2/m9server
-- ===========================================================================
REMOTE:
--
-- Database           Node               Protocol Interface Service
-- ------------------ ------------------ -------- --------- ------------------
   example_database   server_nodename    ''       ''        1360
   m9access           startrek           ''       ''        1360      m9server

To access the m9server database, the Mimer SQL version 7 application must connect to the database m9access.

In this case the startrek node must have both a Mimer SQL version 7 license (for the application) and a Mimer SQL version 9 license (for the database server). The Mimer SQL version 9 license must include client/server connections.


The MULTIDEFS File on VMS and UNIX

This appendix applies to the OpenVMS and UNIX platforms only.

It describes the MULTIDEFS file which forms part of a local database definition, see The Local Database, for a database residing on an OpenVMS or UNIX node.

This file contains operational parameters for the database server for such a database and these are read when the database server is started. It is not possible to change the parameters for a running database server.


The MULTIDEFS Parameter File

Comments in MULTIDEFS are introduced by the character sequence --, or by the character ! or #.

A new MULTIDEFS file can be generated by using the command:

mimcontrol -g

If the MULTIDEFS file is not found when starting a database server, the MIMCONTROL command will create a new file and fill it with the default values for all parameters.

UNIX: On a UNIX node, the MULTIDEFS file is located in the database home directory and is called multidefs.

VMS: On an OpenVMS node, the MULTIDEFS file is located in the database home directory and is called MULTIDEFS.DAT.

The actual default values used may vary and may depend on factors like machine type and the amount of physical memory available on the machine.

The following is an example of the MULTIDEFS parameter file which may be generated by MIMCONTROL:

Example of MULTIDEFS File on UNIX

-- MIMER version 9.3.3 parameters generated 2006-06-06 15:08
Databanks          100                 # Max # of databanks (20-1000)
Tables             4000                # Max # of tables (500-1000000)
ActTrans           30000               # Max # of active trans (500-1000000)
SQLPool            1000                # Initial SQLPool (400-2000000 kb)
RequestThreads     8                   # # of request threads (1-100)
BackgroundThreads  3                   # # of background threads (1-100)
IOThreads          2                   # # of I/O threads (1-64)
Users              100                 # Max # of logged in users (1-5000)
DBCheck            1                   # DB check, 0=index, 1=full (0-1)
Pages2K            8406                # # of 2K bufferpool pages (11-1000000)
Pages16K           815                 # # of 16K bufferpool pages (11-1000000)
Pages64K           145                 # # of 64K bufferpool pages (0-1000000)
Oper                                   # Receivers for messages
DumpPath           .                   # Path for dump directory
TCPPort            inetd               # TCP/IP port
MaxSQLPool         216000              # SQLPool max size (2400-2000000 kb)
MemLock            0                   # Lock bpool in memory, 0=No 1=Yes (0-1)
BackgroundPriority 0                   # Thread priority, 0=Off, 1=On (0-1)
AutoStart          1                   # Autostart, 0=No, 1=Yes (0-1)
DumpScript         ./.dumper.sh %p     # Dump Script
Example of MULTIDEFS File on OpenVMS

-- MIMER version 9.3.3 parameters generated 2006-06-26 13:18
Databanks          100                 # Max # of databanks (20-1000)
Tables             4000                # Max # of tables (500-1000000)
ActTrans           4000                # Max # of active trans (500-1000000)
SQLPool            1000                # Initial SQLPool (400-2000000 kb)
RequestThreads     8                   # # of request threads (1-100)
BackgroundThreads  3                   # # of background threads (1-100)
Users              100                 # Max # of logged in users (1-5000)
DBCheck            1                   # DB check, 0=index, 1=full (0-1)
Pages2K            22443               # # of 2K bufferpool pages (11-1000000)
Pages16K           2086                # # of 16K bufferpool pages (11-1000000)
Pages64K           289                 # # of 64K bufferpool pages (0-1000000)
Oper               OPER                # Receivers for messages
DumpPath           <>                  # Path for dump directory
TCPPort            1360                # TCP/IP port
MaxSQLPool         216000              # SQLPool max size (2400-2000000 kb)
MemLock            0                   # Lock bpool in memory, 0=No 1=Yes (0-1)
DECPort            MYDB                # Decnet network object
ProcName           MYDB                # Process name prefix
NetUsers           5000                # Max # of network users (1-5000)
ServPrio           5                   # VMS prio for server process (0-16)
Cleanup            60                  # Cleanup interval (10-10000 seconds)


MULTIDEFS Parameters

Parameter
Definition
Databanks

Specifies the maximum number of databank files that the database server can have open at any one time.

Tables

Specifies the maximum number of tables that can be accessed simultaneously by the database server.

ActTrans

Specifies the maximum number of transactions that can be active in the database server.

SQLPool

Initial size of the SQLPool area in K bytes. This area contains information about each session, i.e. opened tables and databanks, compiled SQL programs, etc. The SQLPool area will expand automatically if it is too small, but it will not be larger than MaxSQLPool.

RequestThreads

The number of threads in the database server that can serve client requests.

BackgroundThreads

The number of background threads in the database server.

Users

The maximum number of users that are allowed to connect to the database server. This parameter should not exceed the number of users specified in the Mimer SQL license key.

This number is also used to calculate the size of the shared memory region used for local database server communication. About 70 Kbytes of shared memory will be allocated for each user.

DBCheck

A number which specifies whether a full check (1) or only an index page check (0) should be performed when a databank is opened which previously was not closed properly.

A full databank check (involving index and data pages) provides for more secure operations, but may take much longer to execute than an index page check.

When a full check is done, however, the data pages are checked in the background so there is a minimal negative effect on performance.

Databank checks can be avoided by always shutting down the database server properly with the MIMCONTROL command, especially prior to shutting down the machine.

Pages2K

The number of 2 Kbytes pages in the bufferpool area containing pages from the databank files. The default value of this parameter is 12.5% of the total RAM memory in the machine.

VMS: There may be an OpenVMS limit set for the amount of memory a process may allocate, this limit will not be exceeded. Among the various OpenVMS parameters, WSMAX is likely to be of primary interest in connection with this limit.

Pages16K

The number of 16 Kbytes pages in the bufferpool area containing pages from the databank files. The default value of this parameter is 8.33% of the total RAM memory in the machine.

Pages64K

The number of 64 Kbytes pages in the bufferpool area containing pages from the databank files. The default value of this parameter is 5% of the total RAM memory in the machine.

Oper

This parameter gives a list of host system users, i.e. operators, or e-mail addresses that should receive e-mail notification of serious problems with the database server.

VMS: On OpenVMS, you can also specify OPER.

This will enable that notification messages are sent to the central operator, i.e. processes that have set:

$ REPLY/ENABLE=CENTRAL.

DumpPath

This parameter may specify an alternate path for the dump directories. The default is to create dump directories under the database home directory.

TCPPort

Specifies how the database server should handle incoming TCP/IP connection requests. If this parameter is set to - (a single dash), the TCP/IP capability will be disabled for the database server.

UNIX: On UNIX, the TCPPort parameter is, by default, set to inetd which means that the TCP/IP port server program, mimtcp, will be used for establishing a connection to any Mimer SQL version 8 database server. In this case clients may connect to the port to which mimtcp listens, usually 1360, and the handshake will be passed over to the requested Mimer SQL database server.

If a TCP/IP port number is specified, the database server will listen directly to that port.

VMS: On OpenVMS, the TCPPort parameter is, by default, set to the TCP/IP port number 1360. The TCP/IP port server program, MIMTCP, will automatically be started to listen to the given port, serving all Mimer SQL version 8 database servers set up to use that port.

MaxSQLPool

The maximum size (in kilobytes) of the SQLPool. The SQLPool memory area grows dynamically, but the size will never exceed this parameter. Use this parameter to control the maximum virtual size (maximum page file usage) for the database server process.

MemLock

A number which specifies whether the bufferpool and communication buffers should be locked in memory (1) or not locked in memory (0).

AutoStart

UNIX: By default, this parameter is set to 1 which indicates that the database should be started automatically when the operating system goes into multi-user mode. If the parameter is set to 0 the database will not be started automatically.

DumpScript

UNIX: If the database server goes into an erroneous and unrecoverable state, it will produce dumps of the current internal database structures before it goes down. If this situation occurs, it is of great importance for the error detection process to get a UNIX kernel stack trace from the location where the error was located.

By defining this parameter to a command that can produce a kernel trace, such as pstack, stack information will be automatically generated to mimer.log. The %p option, used in the example above, is used to get the current process ID as a parameter to the command given.

DECPort

VMS: Specifies the DECNET network object that the database server listens to. Each database server must use a unique network object. The default value is the database name.

If you set this parameter to - (a single dash), the DECNET capability will be disabled for the database server.

ProcName

VMS: This parameter specifies the process name prefix for the database server. (The last part of the process name is always Srv).

Specify a maximum of 11 characters. The default value is to use the first 11 characters of the database name.

NetUsers

VMS: This parameter specifies the number of users who can access the database through a network connection.

The value used by the system will be the minimum of this parameter and the Users parameter. The default value is 5000.

Since the Users parameter can not be larger than 5000, this means that all users may be network users.

ServPrio

VMS: This parameter specifies the OpenVMS priority for the database server process.

BackgroundPriority

UNIX: Specifies if the background threads should run at a higher priority than other server threads. During certain circumstances like in situations where the background threads cannot manage to shorten a transaction queue this can be an alternative.

Cleanup

VMS: Specifies the time (in seconds) between the cleanup sweeps that check for terminated database clients.

IOThreads

UNIX: The number of threads in the database server that can serve I/O requests. This parameter is only present and used for some Unix implementations (if it is not present in a default generated multidefs file, the most common reason is that the native Asynchronous I/O is used.)


UNIX Specifics


Man Pages

Man pages are included in your Mimer SQL distribution. There are man pages for all commands available and for various configuration files, such as SQLHOSTS and MULTIDEFS. For general information about Mimer SQL, read the mimersql man page.

Man pages are usually installed automatically at /usr/man when installing Mimer SQL.

Details on Man Pages

Refer to the information provided by your operating system manufacturer concerning the UNIX man and catman commands and the use of the MANPATH environment variable.


Automatic Database Start and Stop

When Mimer SQL is installed, autostart is automatically enabled.

The mimservers program will start or stop all local Mimer SQL v9.3 servers defined in the SQLHOSTS file.

Automatic start and stop for local servers is achieved by adding routines to the init.d environment of the operating system that performs calls to the mimservers program.

The init.d setup is done by using the mimautoset command, invoked during installation. For details, see the man-page for init.d.

To exclude a server from the automatic start/stop procedure, set the AutoStart parameter in the multidefs file for that server to: 0.

Note: For systems for which the method using an init.d environment for automatic start/stop does not apply, no setup will be made. In this case explicit calls to the mimservers program must be added to rc-files, or corresponding.


MIMTCP -Database TCP/IP connect dispatcher

When a Mimer SQL database is created using the dbinstall command the definitions needed for remote access to the database is installed automatically. This setup is done in one of the files /etc/inetd.conf or /etc/xinetd.conf or in the directory
/etc/xinetd.d.

In all these cases the mimtcp command is used as described under Networking Setup. The mimtcp command listens to a specified TCP/IP port for an incoming Mimer SQL database connection request. It finds out the database name in the handshake message and redirects the connection to the target database using the registered information in /etc/sqlhosts.

Note: It is possible to let a Mimer SQL database server listen directly to a TCP/IP port, i.e. not using the mimtcp redirecting function. This is achieved by changing the TCPPort parameter in the multidefs file from the default inetd value to the actual port number used. See Appendix C The MULTIDEFS File on VMS and UNIX.

Syntax

The mimtcp syntax is as follows:

mimtcp [ -l [logfile]]

Command-line arguments

Argument
Function
-l [filename]

If mimtcp is used without any option, no logging is performed.

If a string value is given in addition to the -l option, that value will be used as the log file.

If the -l option is used without a value, the filename mimtcp.log will be used that will end up in the root home folder under a sub directory called .mimer_log.

Example

The following example will start mimtcp with logging using the default log file, located in ~/.mimer_log/mimtcp.log:

mimtcp -l

Exit Codes

The following error codes are used:

Code
Usage
0 (success)

This code is used when MIMTCP has executed successfully.

1 (error)

This error code is used when MIMTCP failed to execute successfully.


Services setup

The /etc/services file holds the Internet network services list. The list is a mapping between names for internet services, and their underlying assigned port numbers and protocol types. The following excerpts from the file shows the header for the list and the mimer entries:

# Port Assignments:
#
# Keyword         Decimal    Description                     References
# -------         -------    -----------                     ----------

mimer           1360/tcp   # MIMER
mimer           1360/udp   # MIMER

Having this definition done, the name mimer can be used instead of 1360 when dealing with services.


Networking Setup

inetd setup

The Unix command inetd is the Internet services daemon, the server process for the Internet standard services. It is usually started up at system boot time. The configuration file /etc/inetd.conf lists the services that inetd should handle. An excerpt from the file shows the syntax used in the file:

#
# Syntax for socket-based Internet services:
#  <service_name> <socket_type> <proto> <flags> <user> <server_pathname>
<args>
#

When dbinstall is executed, and the inetd.conf file is found, the following line is added to the configuration file:

mimer stream tcp nowait root /usr/bin/mimtcp mimtcp -l

This indicates that mimtcp should be started for the mimer service. The -l option is used standalone which implies that the default log file should be used.

When the inetd configuration is changed, for example if mimer is added like described above, the inetd daemon must reread it. This is triggered by sending the HUP signal to the inetd process (located using the ps -ef command):

# ps -ef | grep inetd
root      8796     1  0  2006 ?        00:00:12 inetd
# kill -HUP 8796
#

xinetd setup

The Unix command xinetd stands for "the extended Internet services daemon". It is the successor to inetd and works in a slightly different way. Instead of having tasks started at system initialization time, and be dormant until a connection request arrives, xinetd is the only daemon process started and it listens on all service ports for the services listed in its configuration file. When a request comes in, xinetd starts the appropriate server.

The default xinetd definitions for Mimer SQL can be found in the file mimersql.xinetd in the installation directory called misc:

$ cat /opt/MimerSQL-9.3.7A/misc/mimersql.xinetd
# default: on
# description: The MIMER service allows remote users to access the
#              Mimer SQL database servers on this node.
service mimer
{
        port            = 1360
        socket_type     = stream
        wait            = no
        user            = root
        server          = /usr/bin/mimtcp
        server_args     = -l
        log_on_failure  += USERID
        disable         = no
        protocol        = tcp
}
$

If the /etc/xinetd.d directory is found when dbinstall is executed, the mimersql.xinetd file is copied there and is given the name mimer.

If the /etc/xinetd.d is not found, but /etc/xinetd.conf is found, the mimersql.xinetd contents is added at the end of the /etc/xinetd.conf file.

When the xinetd configuration is changed, for example if mimer is added like described above, the xinetd daemon must reread it. This is triggered by sending the HUP signal to the xinetd process (located using the ps -ef command):

# ps -ef | grep xinetd
root      8796     1  0  2006 ?        00:00:12 xinetd
# kill -HUP 8796


Using Raw Device Partitions

The term raw devices applies to the character oriented disk device files (as opposed to the block oriented ones) normally found in /dev. These device files are a part of the interface between the hardware disks and the UNIX system software.

In most UNIX systems it is a performance advantage to use raw device files for data storage. By using raw devices, the UNIX file system (which uses index pages to locate the file blocks) is bypassed and the operating system is able to perform more effective I/O.

Of course, the effect also depends on several other factors such as file size, transaction rate, I/O-implementation, etc. Generally, large and/or frequently used databanks should be stored in raw device files.

Note: Familiarity with raw device files is recommended. It is essential that a raw device file be correctly defined, since the normal UNIX file handling safeguards do not apply. Some important points to be considered:

  • Usually the first cylinder on the disk (cylinder 0), or corresponding, should be avoided when partitioning, since operating system information for the disk is stored there.
  • Overlapping partitions must be manually avoided.
  • It should be verified that backup procedures include raw devices.
  • Always take a complete backup before enabling use of a raw device.

The major disadvantage of using a raw device file is that the maximum file size is fixed by the size of the partition. If the partition becomes full, the raw device file must be moved to a larger partition, if one is available.

In the worst case, the disk must be reformatted in order to create a larger partition.

Caution: Always take a complete backup before reformatting a disk and before setting up any raw device files for use!

Use of the raw device interface is completely transparent from the point of view of a Mimer SQL user. A user has no way of distinguishing a system using raw device files from a system where ordinary UNIX files are used to store databanks.

Creating a Raw Disk Partition

If it is decided to use raw I/O towards a disk and a character special device for the disk partition in question is missing, such a device must be created. This can be done from a Logical Volume Manager (LVM) or on some systems by using the mknod command.

LVM

When a logical volume is created a corresponding character oriented raw device is created along with the block oriented one. When using raw I/O towards the character oriented device, make sure the block oriented device is not used (and not mounted at boot).

Mknod

The major and minor numbers for the disk partition must first be identified and then the character special, non-buffering, device can be created:

# ls -l sdb2
brw-rw----   1 root     disk       8,  18 May  5  2005 /dev/sdb2
# mknod rawname c 8 18
# ls -l *sdb2
crw-rw----   1 root     root       8,  18 Oct 18 08:51 /dev/rsdb2
brw-rw----   1 root     disk       8,  18 May  5  2005 /dev/sdb2

Arranging to Put a Databank on a Raw Device

The database server must be stopped:

# mimcontrol -t m93server

The databank file should be renamed:

# mv transdb.dbf transdb.dbf.ORDINARY

Create a soft link to the raw device file, using the original databank file name. Make sure that the owner of the databank, usually root, is the only one with access to the partition:

Note: It must be verified that the partition that corresponds to the raw device is not part of a file system that can be mounted.

# ln -s /dev/rdsk/rawname transdb.dbf
# chmod 600 /dev/rdsk/rawname
# chmod 600 /dev/dsk/rawname

Copy the original databank file onto the soft link and restart the database server:

# cp transdb.dbf.ORDINARY transdb.dbf
# mimcontrol -s m93server

Arranging to Take a Databank off a Raw Device

The database server must be stopped:

# mimcontrol -t m93server

The raw device databank file must be copied to an ordinary block oriented file, located on a partition large enough to hold the complete raw device partition. Then the soft link can be removed:

# cp transdb.dbf /extra/transdb.dbf
# unlink transdb.dbf

Now the raw device partition can be used for other purposes. However, to be able to start the database system again, the databank file (in this case the TRANSDB file
/extra/transdb.dbf) must be locatable by the database server. This can be achieved by arranging for the databank file to be moved back to its original location.

Another solution is to update the /etc/sqlhosts file by adding an optional databank location.


Environment Variables

The following table lists and explains the environment variables Mimer SQL uses in UNIX.

Variable
Explanation
HOME

Used to locate the home directory from within various Mimer SQL programs.

LD_LIBRARY_PATH

Used on most platforms to locate shared libraries in runtime.

LIBPATH

Used on AIX to locate shared libraries.

MIMER_DATABASE

Used to point out which database to access. If not set, the default database, set in /etc/sqlhosts, is used.

MIMER_EXTEND

Used to change the number of pages to allocate when dynamically extending a databank file.

If not set, the default is 128 pages (each of 2048 bytes). The variable must be set for the process starting the dbserver program.

MIMER_HISTLINES

Used to change the number of command lines to be stored in the recorded history for a Mimer BSQL session.

If not set, the default is 23.

MIMER_HOME

Used on HP-UX when dynamically loading the Mimer single-user shared library.

MIMER_KEYFILE

If set, the given string is treated as the name of the license key file (overriding the /etc/mimerkey file).

MIMER_MAXASYNC

Used to change the number of asynchronous I/Os allowed to be operating at the same time.

If not set, the default is 40. The maximum limit is 128.

This variable must be set for the process starting the dbserver program.

Note: Does not apply to Linux.

MIMER_MODE

Used to indicate the mode for which the database should be accessed, that is, SINGLE or MULTI.

Use single mode if accessing a database for which the dbserver program is not started. If not set, MULTI is assumed.

MIMER_NOEDIT

If set, the command line editing package for a Mimer BSQL session is disabled.

MIMER_ODBCINI

If set, the given string is treated as the name of the file for ODBC Data Source lookup.

If not set, and if ODBCINI is not set, the home directory is searched for the .odbc.ini file (using the HOME environment variable).

MIMER_PATH

Used when searching for sequential files, that is, ordinary text files (not databank files).

ODBCINI

Same as MIMER_ODBCINI.

Overrides MIMER_ODBCINI if set.

PATH

Used to locate Mimer executables.

SHELL

Used shell when temporarily entering the operating system shell prompt from within Mimer SQL.

If not set, /bin/sh is used.

SHLIB_PATH

Used on HP-UX to locate shared libraries.

TMPDIR

If set, it is used as the placeholder for temporary files created by Mimer SQL. If it is not set, the directory $HOME/.mimer_tmp is used.


UNIX Commands

Command
Function
Used by

bsql

SQL command interpreter. See Mimer SQL User's Manual, Mimer BSQL for more information.

mimdevenv, mimexampledb

dbc

Databank check utility. See Databank Check Functionality for more information.

dbfiles

Lists the databank files for a database server, as stored in the data dictionary.

mimdbfiles

dbinstall

Command used to install a new database.

dbopen

Opens all user defined databanks at once. See Databank Open Function for more information.

dbserver

The Mimer SQL database server program. You start dbserver using the mimcontrol or mimdbserver commands.

mimcontrol

dbuninstall

Command used to remove a database, including its data files, registrations and related resources.

dumper

Command used to create a report of the environment during database server dump situations.

esql

Embedded SQL pre-processor. See Mimer SQL Programmer's Manual, Embedded SQL for more information.

exload

Program used to create or delete the example environment (i.e. MUSIC_STORE, see
http://developer.mimer.com/features/feature_19.htm).

dbinstall, mimexampledb

exptoload

Program used to convert old UTIL export files to the new format used by the Mimer SQL load/unload functionality, see EXPTOLOAD - Convert Old Format Export Files.

mimaddpath

Used to add a value to an environment variable (with duplicate check). The new definition is displayed - not installed. Especially used to update the shared library search path.

(internal use)

mimadmin

Menu-based database server administration utility.

mimautoset

Switches on/off the automatic server start and stop functionality or gives the current state of this interaction with the operating system.

dbinstall

mimcontrol

Manages database servers. See Managing a Database Server for more information.

dbinstall, mimadmin, mimlistdb

mimdbfiles

Lists the databank file names for a database server, as stored in the UNIX file system. Can also be used to change the ownership of the databank files (i.e. the new owner will be the one that is dedicated to manage the database server).

In addition, the command can be used for displaying the database server log and configuration files.

dbinstall, dbuninstall, mimadmin

mimdbserver

A front-end to the mimcontrol program, used to control the database server.

dbinstall, mimadmin

mimdbvis

Installs the DbVisualizer tool bundled with Mimer SQL. For details on DbVisualizer, see
http://developer.mimer.com/tools/tool_3.htm.

miminstall, mimuninstall

mimdesktop

Installs Mimer SQL items into the desktop menu system.

miminstall, mimuninstall

mimdevenv

Command used to create a beginner's development environment.

dbinstall

mimexampledb

Installs the example database environment. (Invokes the exload program.)

mimexec

mimexec command is used to execute a given program and stay attached. This command is used internally, especially when invoking terminal based programs using icons on the desktop

(internal use)

mimhome

Displays the home directory for the effective user. Especially used to find location for log and tmp files.

(internal use)

mimhosts

Program to manage and to do lookup in the /etc/sqlhosts file. See Appendix B The SQLHOSTS File on VMS and UNIX for more information.

dbinstall, dbuninstall, mimadmin, mimowner, mimdbfile, mimdbserver, mimdevenv, mimexampledb, mimexec

miminetd

Command used to administer Mimer SQL in the operating system Internet services daemons, i.e. inetd and/or xinetd

dninstall

miminfo

Program to monitor database servers. See Managing a Database Server for more information.

mimadmin

miminstall

Command delivered with the distribution TAR file used to unpack and install Mimer SQL.

mimjdbcver

Displays the version of the JDBC drivers delivered with Mimer SQL.

mimlicense

Used to manage the license keys in /etc/mimerkey. See Mimer SQL License Key for more information.

mimadmin, miminstall

mimlink

Used to link Mimer SQL libraries, man pages and executables to
/usr/lib, /usr/man and
/usr/bin, respectively.

miminstall

mimlistdb

Lists started database servers.

mimadmin, mimuninstall

mimload

A command line front end to the Mimer SQL Load/Unload functionality, see EXPTOLOAD - Convert Old Format Export Files.

mimlocation

Displays the location of the Mimer SQL installation currently accessed.

mimmem

Lists current limits on memory usage.

mimodbc

Program used to administer ODBC data sources and ODBC drivers (especially aimed at the managing iODBC data sources, see www.iodbc.org)

mimodbcadmin, mimodbcdm

mimodbcadmin

Menu based ODBC data source and ODBC driver administration

dbinstall, dbuninstall

mimodbcdm

A front-end to the mimodbc program, used to administer ODBC data sources and drivers.

mimodbcadmin

mimowner

Displays the name of the operating system user that is dedicated to manage a specific database server.

mimadmin, mimdbfiles, mimdbserver

mimpath

Gets the path to databank locations.

dbinstall, dbuninstall, mimadmin, mimdbfiles, mimexampledb, mimowner

mimrepadm

Program used to administrate the Mimer SQL replication dictionary. See MIMREPADM - Replication Administration.

mimservers

Starts/stops all database servers (of current version) defined in
/etc/sqlhosts.

mimautoset

mimsqlhosts

A front-end to the mimhosts program, used to control the database registration file /etc/sqlhosts.

dbinstall, mimadmin

mimstatln

Used to follow and display the source for a symbolic link.

(internal use)

mimsync

Program used to synchronize replicated Mimer SQL tables. See MIMSYNC - Synchronizing Tables.

mimsysconf

Displays the values of various host system configuration parameters, all related to the Mimer SQL system performance.

mimtcp

Manages TCP port dispatching, i.e. distributing incoming connect-attempts to the requested database server. See Creating a Mimer SQL Database for more information.

mimuninstall

Command to uninstall Mimer SQL.

mimunlink

Command used to remove symbolic links from /usr/bin, /usr/man and /usr/lib, previously created by the mimlink command.

mimuninstall

mimversion

Command used to get the installed Mimer SQL version.

mimadmin, mimodbcadmin

repserver

The Mimer SQL replication server program. See REPSERVER - Replicating the Data.

mimrepadm

sdbgen

Command used to create the system databanks for Mimer SQL. See SDBGEN - Generating the System Databanks for more information.

dbinstall


Unix Link Libraries

Library
Description

compat.a

This static library can be necessary to add to the library list if linking really old and deprecated modules.

libmimcomm.so

This is the shared library used when using Mimer JDBC with local communication, i.e. not via TCP/IP.

libmimdbi.so

This shared library contains the Mimer DBI database interface used for Embedded SQL client applications.

libmimdbs.so

This is the shared library used when accessing the database server in single user mode. It is automatically invoked when a single user access is identified.

libmimer.so

This shared library contains several of the client interfaces supported by Mimer SQL, i.e. DBI, ODBC4 and the Level2.

libmimerS.so

This is the Mimer setup library used by the unixODBC Driver Manager GUI interface.

libmimodbc2.so

This is the shared library for the Mimer ODBC database interface when the ODBC client presuming the SQLWCHAR datatype being 2 bytes.

libmimodbc4.so

This is the shared library for the Mimer ODBC database interface when the ODBC client is presuming the SQLWCHAR datatype being 4 bytes.

mimjdbc1.jar

This is the jar file to be used when accessing the Mimer JDBC database interface from a JAVA client using JRE 1.1.8 or later.

mimjdbc2.jar

This is the jar file to be used when accessing the Mimer JDBC database interface from a JAVA client using JRE 1.2 or later.

mimjdbc3.jar

This is the jar file to be used when accessing the Mimer JDBC database interface from a JAVA client using JRE 1.4 or later.

mimsqlxa.o

This object file should be linked in when using the XA functionality.

Note: Level2 is deprecated and is not supported on all platforms.


Data Dictionary Tables

This appendix documents the organization of the data dictionary tables, which are stored in the databank SYSDB.

The tables are created by the SDBGEN program when the system is created. The tables are created in a schema called SYSTEM and are thus effectively owned by a pseudo ident called SYSTEM.

The database administration ident SYSADM is granted SELECT access on the dictionary tables with the WITH GRANT OPTION. No other user may read the data dictionary base tables unless authorized to do so by SYSADM.

A set of system views is defined on the data dictionary tables when the system is installed, see the Mimer SQL Reference Manual, Data Dictionary Views for more information).

The logical group PUBLIC is granted SELECT access on these views, so that any user may read the dictionary information presented in the views. Many of the view definitions restrict the information presented to descriptions of objects and privileges accessible to the current user.

Note: If SYSADM reads the contents of such a view, the result shows only the objects and privileges to which SYSADM has access.
In order to gain information on inaccessible objects and privileges, SYSADM must read the contents of the dictionary base tables directly.

The SYSADM ident may define installation-specific views on the data dictionary tables to supplement the system-defined views. Such views may be tailor-made for the installation or system in use, and SELECT access on the views may be granted to limited user groups if desired.

All maintenance of the data dictionary is performed by internal routines and is invisible to the user. No user, including SYSADM, may alter the contents of the data dictionary directly.

Note: Mimer reserves the right to change the internal organization of the data dictionary, without maintaining backward compatibility with user-written application programs which read the data dictionary tables directly.


Summary of Data Dictionary Tables

Table name
Description

API_FUNCTION

translation of id to function or module name. See SYSTEM.API_FUNCTION.

AST_CODES

binary representation of the search condition of views in the database (for internal use). See SYSTEM.AST_CODES.

AST_SOURCES

textual representation of view definitions and tables and domains with check constraints. See SYSTEM.AST_SOURCES.

CHAR_SETS

character sets in the database. See SYSTEM.CHAR_SETS.

CHECK_CONSTRAINTS

check constraints defined for tables and domains. See SYSTEM.CHECK_CONSTRAINTS.

COLLATE_DEFS

collation definitions. See SYSTEM.COLLATE_DEFS

COLLATIONS

character collations in the database. See SYSTEM.COLLATIONS.

COLUMN_OBJECT_USE

columns that depend on other database objects. See SYSTEM.COLUMN_OBJECT_USE.

COLUMN_PRIVILEGES

instances of privileges granted on a column. See SYSTEM.COLUMN_PRIVILEGES.

COLUMNS

columns in tables or views. See SYSTEM.COLUMNS.

DATABANKS

databanks in the database. See SYSTEM.DATABANKS.

DOMAIN_CONSTRAINTS

constraints defined for domains. See SYSTEM.DOMAIN_CONSTRAINTS.

DOMAINS

domains in the database. See SYSTEM.DOMAINS.

EXEC_STATEMENTS

precompiled statement. See SYSTEM.FIPS_FEATURES.

FIPS_FEATURES

details about all FIPS features. See SYSTEM.FIPS_FEATURES.

FIPS_SIZING

details about FIPS limits. See SYSTEM.FIPS_SIZING.

KEY_COLUMN_USAGE

columns in an index or in a primary, unique or foreign key. SYSTEM.KEY_COLUMN_USAGE.

LEVEL2_RESTRICT

restrictions for domains legal for use by DB level 2. See SYSTEM.LEVEL2_RESTRICT.

LEVEL2_VIEWCOL

columns of views acceptable by DB level 2. See SYSTEM.LEVEL2_VIEWCOL.

LEVEL2_VIEWRES

restrictions for DB level 2. See SYSTEM.LEVEL2_VIEWRES.

MANYROWS

dummy table with more than one row. See SYSTEM.MANYROWS.

MESSAGE

translation of message code to message text. See SYSTEM.MESSAGE.

MODULES

SQL-server modules in the database. See SYSTEM.MODULES.

OBJECT_COLUMN_USE

columns referenced by other database objects. See SYSTEM.OBJECT_COLUMN_USE.

OBJECT_OBJECT_USE

objects that have a dependency on another object. See SYSTEM.OBJECT_OBJECT_USE.

OBJECTS

objects in the database. See SYSTEM.OBJECTS.

ONEROW

dummy table containing one row. See SYSTEM.ONEROW.

PARAMETERS

parameters of routines in the database. See SYSTEM.PARAMETERS.

REFER_CONSTRAINTS

referential constraints in the database. See SYSTEM.REFER_CONSTRAINTS.

ROUTINES

procedures and user-defined functions in the database. See SYSTEM.ROUTINES.

SCHEMATA

schemas in the database. See SYSTEM.SCHEMATA.

SEQUENCES

sequences in the database. See SYSTEM.SEQUENCES.

SERVER_INFO

attributes of the current database system or server. See SYSTEM.SERVER_INFO.

SEVERITY

severity levels and optional module for error codes. See SYSTEM.SEVERITY.

SOURCE_DEFINITION

source definitions for defaults, check constraints, views, modules, procedures, functions and triggers. See SYSTEM.SOURCE_DEFINITION.

SPECIFIC_NAMES

specific names of the routines in the database. See SYSTEM.SPECIFIC_NAMES.

SQL_CONFORMANCE

SQL functionality. See SYSTEM.SQL_CONFORMANCE.

SQL_LANGUAGES

SQL standards and SQL dialects supported. See SYSTEM.SQL_LANGUAGES.

STATEMENT_DESCRIPTORS

compiled code for statements. See SYSTEM.STATEMENT_DESCRIPTORS.

STATEMENT_ROUTINE_USE

compiled routine used by precompiled statements. See SYSTEM.STATEMENT_ROUTINE_USE.

SYNONYMS

synonyms and shadows in the database. See SYSTEM.SYNONYMS.

TABLE_CONSTRAINTS

table constraints in the database. See SYSTEM.TABLE_CONSTRAINTS.

TABLE_PRIVILEGES

instances of privileges granted on a table. See SYSTEM.TABLE_PRIVILEGES.

TABLE_TYPES

the types of table supported. See SYSTEM.TABLE_TYPES.

TABLES

tables and views in the database. See SYSTEM.TABLES.

TRANSLATIONS

character translations in the database. See SYSTEM.TRANSLATIONS.

TRIGGERED_COLUMNS

table columns explicitly specified in an UPDATE trigger event. See SYSTEM.TRIGGERED_COLUMNS.

TRIGGERS

triggers in the database. See SYSTEM.TRIGGERS.

TYPE_INFO

information about data types supported. See SYSTEM.TYPE_INFO.

USAGE_PRIVILEGES

instances of privileges which grant the right to use a database object. See SYSTEM.USAGE_PRIVILEGES.

USER_DEF_TYPES

user defined types in the database. See SYSTEM.USER_DEF_TYPES.

USERS

idents (GROUP, OS_USER, PROGRAM or USER) in the database. See SYSTEM.USERS.

VIEWS

views in the database. See SYSTEM.VIEWS.

SYSTEM.API_FUNCTION

Records translations of id to function or module name.

Column name
Data type
Description

MODULEID

INTEGER

System identifier for module.

API_FUNCTION

INTEGER

System identifier for function.

TEXT

CHAR(40)

Name of the function or module.

Primary key: MODULEID, API_FUNCTION

SYSTEM.AST_CODES

Records the binary representation of the search condition of views in the database (for internal use).

Column name
Data type
Description

AST_SYSID

INTEGER

System identifier for the view.

AST_VERSION

INTEGER

Current AST-revision version number.

SEQUENCE_NO

INTEGER

Sequence number within representation.

AST_LENGTH

INTEGER

Length of binary data in AST_CODE.

AST_CODE

VARCHAR(1200)

Binary representation of the view search condition.

Primary key: AST_SYSID, AST_VERSION, SEQUENCE_NO

SYSTEM.AST_SOURCES

Records the textual representation of view definitions and domains or tables with check constraints in the database (for internal use).

Column name
Data type
Description

ASTS_SYSID

INTEGER

System identifier for the object which the definition represents.

SEQUENCE_NO

INTEGER

Sequence number within representation.

ASTS_LENGTH

INTEGER

Length of representation.

ASTS_SOURCE

NCHAR VARYING(400)

Textual representation of view, domain or table.

Primary key: ASTS_SYSID, SEQUENCE_NO

SYSTEM.CHAR_SETS

Records character sets in the database.

Column name
Data type
Description

CHARSET_SYSID

INTEGER

The system identifier for the character set.

FORM_OF_USE

VARCHAR(128)

A user-defined name that indicates the form-of-use of the character set.

NUMBER_OF_CHARS

INTEGER

Number of characters in the character set.

DEF_COLLATE_SYSID

INTEGER

System identifier for the character collation.

Primary key: CHARSET_SYSID

SYSTEM.CHECK_CONSTRAINTS

Records check constraints defined for tables and domains in the database.

Column name
Data type
Description

CONSTRAINT_SYSID

INTEGER

System identifier for the check constraint.

CHECK_CLAUSE

NCHAR VARYING(200)

The text of the search condition of the check constraint.

If the text is too long or NULL value, it will be stored in the SOURCE_DEFINITION table.

Primary key: CONSTRAINT_SYSID

SYSTEM.COLLATE_DEFS

Records collation definitions in the database.

Column name
Data type
Description

COLLATION_SYSID

INTEGER

System identifier for the collation.

COLLATION_SEQNO

INTEGER

Sequence number for each row of a definition (starts by 1).

COLLATION_TYPE

INTEGER

Internal identification for the base of the collation.

FROM_COLLATION_SYSID

INTEGER

System identifier for the collation on which this collation is based.

COLLATION_DEF_CHAR_LENGTH

INTEGER

Total length of the collation definition in number of characters.

COLLATION_DEF_CHAR_OFFSET

INTEGER

Offset to where the delta string for this collation starts in the definition.

COLLATION_DEF

NCHAR VARYING(800)

Complete collation definition for this collation (i.e. including the base collation).

Primary key: COLLATION_SYSID, COLLATION_SEQNO

SYSTEM.COLLATIONS

Records character collations in the database.

Column name
Data type
Description

COLLATION_SYSID

INTEGER

System identifier for the collation.

CHARSET_SYSID

INTEGER

System identifier for the character set.

PAD_ATTRIBUTE

CHAR(20)

One of the following values:
NO PAD = the collation has the no pad attribute PAD SPACE = the collation has the pad space attribute.

Primary key: COLLATION_SYSID

SYSTEM.COLUMNS

Records table and view columns in the database.

Column name
Data type
Description

TABLE_SYSID

INTEGER

System identifier for the table or view.

COLUMN_NAME

VARCHAR(128)

The name of the table or view column.

ORDINAL_POSITION

INTEGER

The ordinal position of the column in the table. The first column in the table is number 1.

DOMAIN_SYSID

INTEGER

System identifier for the domain used by the column.

COLLATION_SYSID

INTEGER

System identifier for the collation used by the column.

CHARSET_SYSID

INTEGER

System identifier for the character set used by the column.

UDT_SYSID

INTEGER

System identifier for a user-defined type.

DATA_TYPE

CHAR(30)

Identifies the data type of the column.
Can be one of the following:
BIGINT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
NATIONAL CHAR LARGE OBJECT
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
Float(p)
INTEGER
Integer(p)
INTERVAL
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP.

INTERVAL_TYPE

CHAR(30)

For INTERVAL data types, this is a character string specifying the interval qualifier for the named interval data type, see the Mimer SQL Reference Manual.

For other data types it is the NULL value.

CHAR_MAX_LENGTH

BIGINT

For CHARACTER, BINARY, CHARACTER LARGE OBJECT and BINARY LARGE OBJECT data types, this shows the maximum length in characters or bytes as appropriate.

For all other data types it is the NULL value.

CHAR_OCTET_LENGTH

BIGINT

For a CHARACTER, BINARY, CHARACTER LARGE OBJECT and BINARY LARGE OBJECT data types, this shows the maximum length in octets.

For all other data types it is the NULL value. (For single octet character sets, this is the same as CHAR_MAX_LENGTH.)

NUMERIC_PRECISION

INTEGER

For NUMERIC data types, this shows the total number of decimal digits allowed in the column.

For all other data types it is the NULL value. NUMERIC_PREC_RADIX indicates the units of measurement.

NUMERIC_SCALE

INTEGER

For NUMERIC data types, this shows the total number of significant digits to the right of the decimal point. For INTEGER values this is 0. For all other types, it is the NULL value.

NUMERIC_PREC_RADIX

INTEGER

For NUMERIC data types, the value 10 is shown because NUMERIC_PRECISION specifies a number of decimal digits. For all other data types it is the NULL value. NUMERIC_PRECISION and NUMERIC_PREC_RADIX can be combined to calculate the maximum number that the column can hold.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and interval data types, this column contains the number of digits of precision for the fractional seconds component.

For other data types it is the NULL value.

INTERVAL_PRECISION

INTEGER

For INTERVAL data types, this is the number of significant digits for the interval leading precision, see the Mimer SQL Reference Manual.

For other data types it is the NULL value.

IS_NULLABLE

CHAR(3)

One of:
NO = the column is not nullable, according to the rules in the international standard
YES = the NULL value is allowed in the column.

IS_UPDATABLE

CHAR(3)

One of:
NO = the column is not updatable
YES = the column is updatable.

IS_INSTEAD_OF

CHAR(3)

One of:
NO
YES.

INTERNAL_LENGTH

INTEGER

Internal length in bytes of value.

INTERNAL_OFFSET

INTEGER

Internal offset for value in record.

INTERNAL_VC_OFFSET

INTEGER

Internal offset for field containing actual length for a varying length item.

COLUMN_CARD

BIGINT

Number of unique values in column.

COLUMN_CARD_NONULL

BIGINT

Number of values in column that are not null.

COLUMN_LOW_VALUE

CHAR(16)

Lowest value in column.

COLUMN_HIGH_VALUE

CHAR(16)

Highest value in column.

COLUMN_DEFAULT

NCHAR VARYING(200)

This shows the default value for the column.

If the default value is a CHARACTER string, the value shown is the string enclosed in single quotes.

If the default value is a NUMERIC LITERAL, the value is shown in its original character representation without enclosing quotes.

If the default value is a DATE, TIME or TIMESTAMP, the value shown is the appropriate keyword (e.g. DATE) followed by the literal representation of the value enclosed in single quotes, see the Mimer SQL Reference Manual for a description of DATE, TIME and TIMESTAMP literals).

If the default value is a pseudo-literal, the value shown is the appropriate keyword (e.g. CURRENT_DATE) without enclosing quotes.

If the default value is the NULL value, the value shown is the keyword NULL without enclosing quotes.

If the default value cannot be represented without truncation, then a zero-length string is stored.

If no default value was specified then its value is the NULL value.

The value of COLUMN_DEF is syntactically suitable for use in specifying default-value in a CREATE TABLE or ALTER TABLE statement.

REMARKS

NCHAR VARYING(254)

A comment on the column. If no comment has been defined a zero length string is stored.

Primary key: TABLE_SYSID, COLUMN_NAME

Unique constraint: TABLE_SYSID, ORDINAL_POSITION

Secondary index: TABLE_SYSID, INTERNAL_OFFSET

SYSTEM.COLUMN_OBJECT_USE

Records table or view columns that depend on other objects in the database.

.

Column name
Data type
Description

OBJECT_TYPE

CHAR(20)

One of:
DOMAIN
SEQUENCE
COLLATION.

OBJECT_SYSID

INTEGER

System identifier for the object on which the column depends.

TABLE_SYSID

INTEGER

System identifier for the table or view.

COLUMN_NAME

VARCHAR(128)

The name of the table or view column.

Primary key: OBJECT_TYPE, OBJECT_SYSID, TABLE_SYSID, COLUMN_NAME

Secondary index: TABLE_SYSID, COLUMN_NAME

SYSTEM.COLUMN_PRIVILEGES

Records instances of privileges granted on a column.

.

Column name
Data type
Description

TABLE_SYSID

INTEGER

System identifier for the table or view.

COLUMN_NAME

VARCHAR(128)

The name of the table or view column.

PRIVILEGE_TYPE

CHAR(20)

One of:
INSERT
REFERENCES
SELECT
UPDATE.

GRANTEE

VARCHAR(128)

The name of the ident to whom the privilege was granted.

GRANTOR

VARCHAR(128)

The name of the ident granting the privilege.

IS_GRANTABLE

CHAR(3)

One of:
NO = WITH GRANT OPTION is not held for the privilege
YES = WITH GRANT OPTION is held for the privilege.

IS_INSTEAD_OF

CHAR(3)

One of:
NO = The privilege has been granted explicitly
YES = The privilege is granted because an instead of trigger has been defined on the view to which the column belongs.

Primary key: TABLE_SYSID, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE, GRANTOR

Secondary index: GRANTEE

Secondary index: GRANTOR

SYSTEM.DATABANKS

Records the databanks in the database.

Column name
Data type
Description

DATABANK_SYSID

INTEGER

System identifier for the databank.

DATABANK_SEQNO

INTEGER

The shadowing sequence number for the databank.

DATABANK_FILENO

INTEGER

The file number of the databank file.

DATABANK_NAME

VARCHAR(128)

The name of the databank.

SHADOW_NAME

VARCHAR(128)

The shadow name if the databank is a shadow.

DATABANK_FILENAME

NCHAR VARYING(256)

The path name for the databank file.

DATABANK_TYPE

CHAR(20)

Indicates type of transaction handling:
LOG = transaction handling with logging
READ ONLY = allows read operations only
TEMPORARY = work databank (SQLDB)
TRANSACTION = transaction handling without logging
WORK = transaction handling not used

IS_MASTER

CHAR(3)

One of:
NO = the databank is a shadow
YES = the databank is a master.

IS_ONLINE

CHAR(3)

One of:
NO = the databank is OFFLINE
YES = the databank of ONLINE.

LOBDIR_SYSID

INTEGER

System identifier for the large object directory in the databank.

LOBDATA_SYSID

INTEGER

System identifier for the large object table in the databank

MAXSIZE

BIGINT

Maximum size for databank file, kilo bytes

GOALSIZE

BIGINT

Ideal size for databank file, kilo bytes

MINSIZE

BIGINT

Minimum size for databank file, kilo bytes

IS_REMOVABLE

CHAR(3)

One of:
NO = databank is not removable
YES = databank is removable

BACKUPED

TIMESTAMP(2)

Last date for backup, currently always set to NULL

Primary key: DATABANK_SYSID, DATABANK_SEQNO, DATABANK_FILENO

Unique constraint: SHADOW_NAME, DATABANK_FILENO

Secondary index: DATABANK_NAME, DATABANK_SEQNO

SYSTEM.DOMAINS

Records the domains in the database.

Column name
Data type
Description

DOMAIN_SYSID

INTEGER

System identifier for the domain.

CHARSET_SYSID

INTEGER

System identifier for the character set.

COLLATION_SYSID

INTEGER

System identifier for the collation for a domain associated with a character set.

DATA_TYPE

CHAR(30)

Identifies the data type of the domain.
Can be one of the following:
BIGINT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
NATIONAL CHAR LARGE OBJECT
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
Float(p)
INTEGER
Integer(p)
INTERVAL
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP.

INTERVAL_TYPE

CHAR(30)

For INTERVAL data types, this is a character string specifying the interval qualifier for the named interval data type, see the Mimer SQL Reference Manua l.

For other data types it is the NULL value.

CHAR_MAX_LENGTH

BIGINT

For CHARACTER, BINARY, CHARACTER LARGE OBJECT and BINARY LARGE OBJECT data types, this shows the maximum length in characters or bytes as appropriate.

For all other data types it is the NULL value.

CHAR_OCTET_LENGTH

BIGINT

For a CHARACTER, BINARY, CHARACTER LARGE OBJECT and BINARY LARGE OBJECT data types, this shows the maximum length in octets.

For all other data types it is the NULL value. (For single octet character sets, this is the same as CHAR_MAX_LENGTH.)

NUMERIC_PRECISION

INTEGER

For NUMERIC data types, this shows the total number of decimal digits allowed in the column.

For all other data types it is the NULL value. NUMERIC_PREC_RADIX indicates the units of measurement.

NUMERIC_SCALE

INTEGER

This defines the total number of significant digits to the right of the decimal point.

For INTEGER and SMALLINT, this is 0.

For CHARACTER, VARCHAR, DATETIME, FLOAT, INTERVAL, REAL and DOUBLE PRECISION data types, it is the NULL value.

NUMERIC_PREC_RADIX

INTEGER

For NUMERIC data types, the value 10 is shown because NUMERIC_PRECISION specifies a number of decimal digits.

For all other data types it is the NULL value.

NUMERIC_PRECISION and NUMERIC_PREC_RADIX can be combined to calculate the maximum number that the column can hold.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and interval data types, this column contains the number of digits of precision for the fractional seconds component.

For other data types it is the NULL value.

INTERVAL_PRECISION

INTEGER

For INTERVAL data types, this is the number of significant digits for the interval leading precision, see the Mimer SQL Reference Manual.

For other data types it is the NULL value.

INTERNAL_LENGTH

INTEGER

Internal length in bytes of value.

DOMAIN_DEFAULT

NCHAR VARYING(200)

This shows the default value for the domain.

If the default value is a character string, the value shown is the string enclosed in single quotes.

If the default value is a numeric literal, the value is shown in its original character representation without enclosing quotes.

If the default value is a DATE, TIME or TIMESTAMP, the value shown is the appropriate keyword (e.g. DATE) followed by the literal representation of the value enclosed in single quotes, see Mimer SQL Reference Manual for a description of DATE, TIME and TIMESTAMP literals.

If the default value is a pseudo-literal, the value shown is the appropriate keyword (e.g. CURRENT_DATE) without enclosing quotes.

If the default value is the NULL value, the value shown is the keyword NULL without enclosing quotes.

If the default value cannot be represented without truncation, then a zero-length string is stored.

If no default value was specified then its value is the NULL value.

The value of DOMAIN_DEFAULT is syntactically suitable for use in specifying default-value in a CREATE TABLE or ALTER TABLE statement.

ANY_CONSTRAINT

CHAR(3)

One of:
NO = There are no constraints for the domain
YES = There are constraints for the domain.

IS_LEVEL2_APPROVED

CHAR(3)

One of:
NO = Tables using this domain can not be used with the level 2 interface
YES = The use of this domain does not prohibit the table being used with the level 2 interface.

Primary key: DOMAIN_SYSID

SYSTEM.DOMAIN_CONSTRAINTS

Records the constraints defined for domains in the database.

.

Column name
Data type
Description

DOMAIN_SYSID

INTEGER

System identifier for the domain.

CONSTRAINT_SYSID

INTEGER

System identifier for the constraint.

IS_DEFERRABLE

CHAR(3)

One of:
NO = the constraint is not deferrable.
YES = the constraint is deferrable.

INITIALLY_DEFERRED

CHAR(3)

One of:
NO = the constraint is not initially deferred
YES = the constraint is initially deferred.

Primary key: DOMAIN_SYSID, CONSTRAINT_SYSID

SYSTEM.EXEC_STATEMENTS

Records precompiled statements in the database.

Column name
Data type
Description

STATEMENT_SYSID

INTEGER

System identifier for the precompiled statement.

STATEMENT_TYPE

INTEGER

Type of statement (internal value).

STATEMENT_SOURCE

NCHAR VARYING(200)

Source text for the precompiled statement. If the length exceeds 400 characters, this value is NULL and the source text is stored in SOURCE_DEFINITION.

STATEMENT_SOU_LENGTH

CHAR(3)

Length of source text in characters.

SYSTEM.FIPS_FEATURES

Lists details of all FIPS features.

Column name
Data type
Description

FEATURE_ID

SMALLINT

Identification number of the FIPS feature.

FEATURE_NAME

CHAR(50)

The name of the FIPS feature.

CLASSIFICATION

CHAR(12)

The conformance level of the feature.
One of:
TRANSITIONAL
INTERMEDIATE
FULL.

IS_SUPPORTED

CHAR(3)

One of:
YES = Mimer SQL supports the feature
NO = Mimer SQL does not support the feature.

IS_VERIFIED

CHAR(3)

One of:
YES = Mimer SQL support for the feature has been tested and verified
NO = Mimer SQL support for the feature has not been verified.

FEATURE_COMMENTS

VARCHAR(100)

Comments about the feature.

Primary key: FEATURE_ID

SYSTEM.FIPS_SIZING

Lists details about FIPS limits.

Column name
Data type
Description

SIZING_ID

SMALLINT

Identification number for limit.

DESCRIPTION

CHAR(50)

Description of limit.

ENTRY_VALUE

INTEGER

Limit for entry SQL.

INTERMEDIATE_VALUE

INTEGER

Limit for intermediate SQL.

VALUE_SUPPORTED

INTEGER

Limit for Mimer SQL.

SIZING_COMMENTS

VARCHAR(100)

Comments for limit.

Primary key: SIZING_ID

SYSTEM.KEY_COLUMN_USAGE

Records columns in an index or in a primary, unique or foreign key.

Column name
Data type
Description

TABLE_SYSID

INTEGER

System identifier for the table.

CONSTRAINT_SYSID

INTEGER

System identifier for the table constraint.

ORDINAL_POSITION

INTEGER

The ordinal position of the column in the table.

The first column in the table is number 1.

COLUMN_NAME

VARCHAR(128)

The name of the table column.

KEY_TYPE

CHAR(20)

One of:
FOREIGN KEY
INDEX
INTERNAL KEY
PRIMARY KEY
UNIQUE.

IS_UNIQUE

CHAR(3)

One of:
NO = the column may contain non-unique values
YES = the column is constrained to contain only unique values.

IS_ASCENDING

CHAR(3)

One of:

NO = the column is indexed in descending value order
YES = the column is indexed in ascending value order.

COLLATION_SYSID

INTEGER

System identifier for the collation used by the index.

Primary key: TABLE_SYSID, CONSTRAINT_SYSID, ORDINAL_POSITION

Unique constraint: CONSTRAINT_SYSID, COLUMN_NAME

SYSTEM.LEVEL2_RESTRICT

Records restrictions for domains legal for use by DB level 2.

Column name
Data type
Description

DOMAINID

INT(10)

System identifier for the domain with restrictions.

SEQNO

INT(3)

Restriction order number.

LOGOP

CHAR(3)

Logical operator (AND, OR).

RELOP

CHAR(2)

Relational operator (EQ, ...).

LENGTH

INT(5)

Length of the value.

RESVALUE

CHAR(64)

Restriction value.

Primary key: DOMAINID, SEQNO

SYSTEM.LEVEL2_VIEWCOL

Records columns of views acceptable by DB level 2.

Column name
Data type
Description

VTABID

INT(10)

View table identifier.

VCOLNO

INT(3)

View table column number.

BTABID

INT(10)

Base table identifier.

BCOLNO

INT(3)

Base table column number.

Primary key: VTABID, VCOLNO

SYSTEM.LEVEL2_VIEWRES

Records restrictions for DB level 2.

Column name
Data type
Description

VTABID

INT(10)

View table identifier.

SEQNO

INT(3)

Restriction order number.

LOGOP

CHAR(3)

Logical operator (AND, OR).

BTABID

INT(10)

Base table identifier.

BCOLNO

INT(3)

Base table column number.

RELOP

CHAR(2)

Relational operator (EQ, ...).

LENGTH

INT(5)

Length of the value.

RESVALUE

CHAR(64)

Restriction value.

Primary key: VTABID, SEQNO

SYSTEM.MANYROWS

Dummy table with more than row.

Column name
Data type
Description

C

INT(3)

The values column.

Primary key: C

SYSTEM.MESSAGE

Records translations of message codes to message text.

Column name
Data type
Description

MODULEID

INTEGER

Identification number for Mimer SQL module to which error belongs.

MESSAGEID

INTEGER

Identification number for message

LANGUAGE

INTEGER

Language number for message
1 = English.

LINENO

INTEGER

Line number for message.

MESSAGE

CHAR(80)

Message text.

Primary key: MODULEID, MESSAGEID, LANGUAGE, LINENO

SYSTEM.MODULES

Records the SQL-server modules in the database.

Column name
Data type
Description

MODULE_SYSID

INTEGER

System identifier for the module.

DEF_CHARSET_SYSID

INTEGER

System identifier for the default character set for the module.

DEF_SCHEMA_SYSID

INTEGER

System identifier for the default schema for the module.

MODULE_LENGTH

INTEGER

Length of the module definition.

MODULE_DEFINITION

NCHAR VARYING(200)

Source text for module definition, if the length of the module definition exceeds 200 characters, this value is NULL and the source text is stored in SOURCE_DEFINITION.

SQL_PATH

VARCHAR(200)

The path for the module.

Primary key: MODULE_SYSID

SYSTEM.OBJECTS

Records objects in the database.

Column name
Data type
Description

OBJECT_SYSID

INTEGER

System identifier for the database object.

OBJECT_TYPE

VARCHAR(20)

One of:
BASE TABLE
CHARACTER SET
COLLATION
CONSTRAINT
DATABANK
DOMAIN
FUNCTION
INDEX
IDENT
MODULE
PROCEDURE
SCHEMA
SEQUENCE
SHADOW
STATEMENT
SYNONYM
TRANSLATION
TRIGGER
VIEW.

OBJECT_SCHEMA

VARCHAR(128)

The name of the schema containing the object.

OBJECT_NAME

VARCHAR(128)

The name of the object.

OBJECT_CREATED

TIMESTAMP(2)

The date and time the object was created.

OBJECT_ALTERED

TIMESTAMP(2)

The date and time the object was last altered.

REMARKS

NCHAR VARYING(254)

Comments defined on the object. If there is no comment defined the empty string ("") will appear. Remarks relating to columns will be stored in the COLUMNS table.

Primary key: OBJECT_SYSID

Unique constraint: OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME

SYSTEM.OBJECT_COLUMN_USE

Records columns referenced by other database objects.

Column name
Data type
Description

TABLE_SYSID

INTEGER

System identifier for the table.

COLUMN_NAME

VARCHAR(128)

The name of the table column.

USED_BY_SYSID

INTEGER

The system identifier of the referencing object.

USED_BY_TYPE

CHAR(20)

One of:
VIEW
PROCEDURE
FUNCTION
CHECK
TRIGGER
STATEMENT.

PRIVILEGE_TYPE

CHAR(20)

One of:
INSERT
DELETE
SELECT
UPDATE
REFERENCES.

Primary key: TABLE_SYSID, COLUMN_NAME, USED_BY_SYSID, USED_BY_TYPE, PRIVILEGE_TYPE

Secondary index: USED_BY_SYSID, USED_BY_TYPE

SYSTEM.OBJECT_OBJECT_USE

Records objects referenced by other objects.

Column name
Data type
Description

OBJECT_SYSID

INTEGER

System identifier for the object used.

OBJECT_TYPE

CHAR(20)

One of:
BASE TABLE
VIEW
PROCEDURE
FUNCTION
DOMAIN
SEQUENCE
CHARACTER SET
COLLATION
TRANSLATION.

USED_BY_SYSID

INTEGER

The system identifier of the referencing object.

USED_BY_TYPE

CHAR(20)

One of:
BASE_TABLE
VIEW
PROCEDURE
FUNCTION
DOMAIN
TRIGGER
CHARACTER SET
COLLATION
TRANSLATION
CHECK
INDEX
STATEMENT.

IS_STRONG

CHAR(3)

One of:
NO
YES.

Primary key: OBJECT_SYSID, OBJECT_TYPE, USED_BY_SYSID, USED_BY_TYPE

Secondary index: USED_BY_SYSID, USED_BY_TYPE

SYSTEM.ONEROW

Dummy table containing one row.

Column name
Data type
Description

M

CHAR(1)

Contains the value M.

Primary key: M

SYSTEM.PARAMETERS

Records parameters of routines in the database.

Column name
Data type
Description

ROUTINE_SYSID

INTEGER

System identifier for the function or procedure.

ORDINAL_POSITION

INTEGER

The ordinal position of the parameter in the routine. The first parameter in the routine is number 1.

IS_RESULT

CHAR(3)

One of:
NO = This parameter is not part of the result clause for a result set procedure
YES = This parameter is part of the result set.

PARAMETER_MODE

CHAR(5)

One of:
IN
OUT
INOUT.

PARAMETER_NAME

VARCHAR(128)

The name of the parameter.

DOMAIN_SYSID

INTEGER

System identifier of the domain that defines the data type of the parameter.

COLLATION_SYSID

INTEGER

System identifier of the collation associated with the parameter.

CHARSET_SYSID

INTEGER

The system identifier for the character set.

UDT_SYSID

INTEGER

System identifier for a user-defined type.

DATA_TYPE

CHAR(30)

The data type of the parameter.

INTERVAL_TYPE

CHAR(30)

For INTERVAL data types, this is a character string specifying the interval qualifier for the named interval data type, see the Mimer SQL Reference Manual.

For other data types it is the NULL value.

CHAR_MAX_LENGTH

BIGINT

For CHARACTER, BINARY, CHARACTER LARGE OBJECT and BINARY LARGE OBJECT data types, this shows the maximum length in characters or bytes as appropriate.

For all other data types it is the NULL value.

CHAR_OCTET_LENGTH

BIGINT

For a CHARACTER, BINARY, CHARACTER LARGE OBJECT and BINARY LARGE OBJECT data types, this shows the maximum length in octets.

For all other data types it is the NULL value. (For single octet character sets, this is the same as CHAR_MAX_LENGTH.)

NUMERIC_PRECISION

INTEGER

For NUMERIC data types, this shows the total number of decimal digits allowed in the column.

For all other data types it is the NULL value. NUMERIC_PREC_RADIX indicates the units of measurement.

NUMERIC_SCALE

INTEGER

This defines the total number of significant digits to the right of the decimal point. For INTEGER and SMALLINT, this is 0. For CHARACTER, VARCHAR, DATETIME, FLOAT, INTERVAL, REAL and DOUBLE PRECISION data types, it is the NULL value.

NUMERIC_PREC_RADIX

INTEGER

For numeric data types, the value 10 is shown because NUMERIC_PRECISION specifies a number of decimal digits.

For all other data types it is the NULL value.

NUMERIC_PRECISION and NUMERIC_PREC_RADIX can be combined to calculate the maximum number that the column can hold.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and INTERVAL data types, this column contains the number of digits of precision for the fractional seconds component.

For other data types it is the NULL value.

INTERVAL_PRECISION

INTEGER

For INTERVAL data types, this is the number of significant digits for the interval leading precision, see the Mimer SQL Reference Manual.

For other data types it is the NULL value.

Primary key: ROUTINE_SYSID, ORDINAL_POSITION, IS_RESULT

SYSTEM.REFER_CONSTRAINTS

Records referential constraints in the database.

Column name
Data type
Description

TABLE_SYSID

INTEGER

System identifier for the table.

CONSTRAINT_SYSID

INTEGER

System identifier for the referential constraint.

UNIQUE_TABLE_SYSID

INTEGER

System identifier for the table that is referenced in the foreign key.

UNIQUE_CONST_SYSID

INTEGER

System identifier for the constraint that is (implicitly) referenced in the foreign key.

MATCH_OPTION

CHAR(20)

One of:
NONE
PARTIAL
FULL.

UPDATE_RULE

CHAR(20)

One of:
CASCADE
SET NULL
SET DEFAULT
NO ACTION
RESTRICT.

DELETE_RULE

CHAR(20)

One of:
CASCADE
SET NULL
SET DEFAULT
NO ACTION
RESTRICT.

Primary key: TABLE_SYSID, CONSTRAINT_SYSID

Secondary index: UNIQUE_TABLE_SYSID, UNIQUE_CONST_SYSID

SYSTEM.ROUTINES

Records procedures and user-defined functions in the database.

Column name
Data type
Description

ROUTINE_SYSID

INTEGER

System identifier of the function or procedure.

MODULE_SYSID

INTEGER

System identifier of the module to which the routine belongs.

ROUTINE_TYPE

CHAR(20)

One of:
PROCEDURE
FUNCTION.

SQL_DATA_ACCESS

CHAR(20)

One of:
NO SQL
CONTAINS SQL
READS SQL DATA
MODIFIES SQL DATA.

ROUTINE_OFFSET

INTEGER

Offset for routine within module definition.

This value is zero if the routine ins not defined in a module.

ROUTINE_LENGTH

INTEGER

Length of routine definition

TOTAL_PARAMS

INTEGER

The total number of routine parameters.

RESULT_PARAMS

INTEGER

The number of result parameters.

INPUT_PARAMS

INTEGER

The number of input parameters.

OUTPUT_PARAMS

INTEGER

The number of output parameters.

ROUTINE_DEFINITION

NCHAR VARYING(200)

Routine definition.

If the length of the routine definition exceeds 400 characters, this value is NULL and the source text is stored in SOURCE_DEFINITION.

SQL_PATH

VARCHAR(200)

The search path for the routine.

IS_DETERMINISTIC

CHAR(3)

One of:
NO = The routine is not deterministic. i.e. invoking the routine with the same input values is not guaranteed to return the same result
YES = The routine is deterministic, i.e. when invoked with same input values it will always return the same result.

ROUTINE_BODY

CHAR(20)

One of:
SQL = The routine is an SQL routine
EXTERNAL = The routine is external

EXTERNAL_NAME

VARCHAR(128)

The name of the program that implements the routine if it is an external routine otherwise null.

EXTERNAL_LANGUAGE

CHAR(20)

The language for the routine if it is external otherwise null.

PARAMETER_STYLE

CHAR(20)

The parameter passing style for the routine if it is an external routine, otherwise null.

SCHEMA_LEVEL_ROU

CHAR(3)

One of:
NO = The routine is defined within a module
YES = The routine is not defined in a module.

MX_DYN_RESULT_SETS

INTEGER

The maximal number of result sets that the routine may return.

UDT_SYSID

INTEGER

(currently not used)

IS_USER_DEFINED_CAST

CHAR(3)

(currently not used)

IS_IMPLICITLY_INVOCABLE

CHAR(3)

(currently not used)

MS_SYSID

INT

(currently not used)

Primary key: ROUTINE_SYSID

Secondary index: MODULE_SYSID

SYSTEM.SCHEMATA

Records schemas in the database.

Column name
Data type
Description

SCHEMA_SYSID

INTEGER

System identifier of the schema.

DEF_CHARSET_SYSID

INTEGER

System identifier of the default character set.

SQL_PATH

VARCHAR(200)

The default search path for objects belonging to the schema.

Primary key: SCHEMA_SYSID

SYSTEM.SEQUENCES

Records sequences in the database.

Column name
Data type
Description

SEQUENCE_SYSID

INTEGER

System identifier of the sequence.

DATA_TYPE

CHAR(30)

One of:
SMALLINT
INTEGER
BIGINT.

NUMERIC_PRECISION

INTEGER

Precision for data type.

MAXIMUM_VALUE

BIGINT

The maximum value of the sequence.

MINIMUM_VALUE

BIGINT

The minimum value of the sequence.

INCREMENT

BIGINT

The increment value for sequence.

CYCLE_OPTION

CHAR(3)

One of:
'YES ' = the sequence will restart when all values are exhausted
'NO ' = the sequence will not restart when all values are exhausted.

START_VALUE

BIGINT

Start value for sequence when first used.

CURRENT_VALUE

BIGINT

Start value at system start up.

IS_EXHAUSTED

CHAR(3)

One of:
YES = the series of values defined by the sequence is exhausted
NO = the series of values defined by the sequence is not exhausted.

CYCLES

BIGINT

Number of times the maximum value of the sequence is exceeded.

DATABANK_SYSID

INTEGER

Databank location.

Primary key: SEQUENCE_SYSID

SYSTEM.SERVER_INFO

Records attributes of the current database system or server.

Column name
Data type
Description

SERVER_ATTRIBUTE

VARCHAR(254)

One of:
AUTOUPGRADE_ENABLED
CATALOG_NAME
COLLATION_SEQ
CURRENT_COLLATION_ID
IDENTIFIER_LENGTH
INTERVAL_FRAC_PREC
INTERVAL_LEAD_PREC
ROW_LENGTH
TIME_PREC
TIMESTAMP_PREC
TXN_ISOLATION
USERID_LENGTH
CATALOG_VERSION_CREATED
CATALOG_VERSION_CURRENT.

ATTRIBUTE_VALUE

VARCHAR(254)

The value for the attribute.

Primary key: SERVER_ATTRIBUTE

SYSTEM.SEVERITY

Records severity levels and optional module for error codes.

Column name
Data type
Description

MODULEID

INTEGER

Identification number for the Mimer SQL module to which the message belongs.

MESSAGEID

INTEGER

Identification number for message.

SEVERITY

INTEGER

Severity for message:
1 = Message
2 = Warning
3 = Error
4 = Fatal error
5 = Internal error
6 = Code.

MODULE

CHAR(20)

Name of Mimer SQL module to which the message belongs.

SQLSTATE_VALUE

CHAR(5)

Corresponding SQLSTATE value for message.

CLASS_ORIGIN

CHAR(20)

Class origin for the SQLSTATE value.

SUBCLASS_ORIGIN

CHAR(20)

Sub class origin for the SQLSTATE value.

Primary key: MODULEID, MESSAGEID

SYSTEM.SOURCE_DEFINITION

Records source definitions for defaults, check constraints, views, modules, procedures, functions, triggers and statements.

Column name
Data type
Description

SOURCE_SYSID

INTEGER

System identifier of the source definition.

COLUMN_NAME

VARCHAR(128)

Column name if the source types is a default value for a column.

SOURCE_TYPE

CHAR(20)

One of:
DEFAULT
CHECK
VIEW
ROUTINE
STATEMENT.

SEQUENCE_NO

INTEGER

Sequence number in source definition.

SOURCE_LENGTH

INTEGER

The length of the stored source text.

SOURCE_DEFINITION

NCHAR VARYING(400)

The stored source text.

Primary key: SOURCE_SYSID, COLUMN_NAME, SOURCE_TYPE, SEQUENCE_NO

SYSTEM.SPECIFIC_NAMES

Records specific names of the routines in the database.

Column name
Data type
Description

ROUTINE_SYSID

INTEGER

System identifier of the routine.

SPECIFIC_SCHEMA

VARCHAR(128)

The name of the schema containing the routine.

SPECIFIC_NAME

VARCHAR(128)

The specific name for the routine.

UDT_SYSID

INTEGER

(currently not used)

Primary key: ROUTINE_SYSID

Unique constraint: SPECIFIC_SCHEMA, SPECIFIC_NAME

SYSTEM.SQL_CONFORMANCE

The SQL_CONFORMANCE base table has one row for each conformance element identified by ISO/IEC 9075.

Column name
Data type
Description

CONFORMANCE_ID

CHAR(20)

Identification of the conformance element described.

CONFORMANCE_SUB_ID

CHAR(20)

If the conformance element is a subfeature then it's identification, otherwise a single space.

CONFORMANCE_TYPE

CHAR(20)

Type of conformance element. FEATURE, SUBFEATURE, PACKAGE, or PART.

CONFORMANCE_IS_CORE_SQL

CHAR(3)

YES if the conformance element belongs to Core SQL, otherwise NO.

CONFORMANCE_NAME

VARCHAR(100)

Short description of the conformance element.

CONFORMANCE_SUB_NAME

VARCHAR(100)

Short description of a subfeature, otherwise a single space.

CONFORMANCE_IS_SUPPORTED

CHAR(3)

YES if fully supported by Mimer SQL, otherwise NO.

CONFORMANCE_IS_VERIFIED_BY

CHAR(3)

Should identify conformance test used to verify the conformance (always NULL).

CONFORMANCE_REMARKS

VARCHAR(100)

Comments pertinent to the conformance element.

Primary key: CONFORMANCE_ID, CONFORMANCE_SUB_ID

SYSTEM.SQL_LANGUAGES

Records the SQL standards and SQL dialects supported.

Column name
Data type
Description

SOURCE

VARCHAR(100)

Body that has defined standard.

SOURCE_YEAR

VARCHAR(100)

Which year the standard was approved.

CONFORMANCE

VARCHAR(100)

Level of conformance within standard.

INTEGRITY

VARCHAR(100)

If the supported standard is ISO 9075 the value YES in this column means that the integrity enhancement feature of this standard is supported, otherwise the value of this column is null.

IMPLEMENTATION

VARCHAR(100)

Implementation style, always null in Mimer SQL.

BINDING_STYLE

VARCHAR(100)

One of:
DIRECT = Support for interactive (ad hoc) access to the database
EMBEDDED = Support for access through an application programming interface.

PROGRAMMING_LANG

VARCHAR(100)

Name of programming languages that are supported if the binding style is EMBEDDED.

ORDINAL_NO

INTEGER

Ordinal number.

Primary key: SOURCE, SOURCE_YEAR, ORDINAL_NO

SYSTEM.STATEMENT_DESCRIPTORS

Records compiled code for precompiled statements.

Column name
Data type
Description

STATEMENT_SYSID

INTEGER

System identifier for the precompiled statement.

DESC_MODE

INTEGER

Mode, one of:
1 = no scroll
2 = scroll

DESC_TYPE

INTEGER

Internal. Naming or section descriptor type.

DESC_SEQNO

INTEGER

Sequence number for compiled code.

DESC_CODE_LENGTH

INTEGER

Length of compiled code.

DESC_CODE

BINARY(1000)

Compiled code.

DESC_VERSION

INTEGER

Compiler version used.

SYSTEM.STATEMENT_ROUTINE_USE

Records compiled routines used by precompiled statements.

Column name
Data type
Description

STATEMENT_SYSID

INTEGER

System identifier for the precompiled statement.

USE_SEQNO

INTEGER

Sequence number.

USE_LEVEL

INTEGER

Call level for routine.

USE_ROUTINE_SYSID

INTEGER

System identifier for called routines.

SYSTEM.SYNONYMS

Records synonyms and shadows in the database.

Column name
Data type
Description

SYNONYM_SYSID

INTEGER

System identifier of the synonym.

ORDINARY_SYSID

INTEGER

System identifier of the original object.

Primary key: SYNONYM_SYSID

SYSTEM.TABLES

Records tables and views in the database.

Column name
Data type
Description

TABLE_SYSID

INTEGER

System identifier of the table or view.

TABLE_TYPE

CHAR(20)

One of:
BASE TABLE
VIEW.

TABLE_NOCOLS

INTEGER

Number of columns in table.

TABLE_RECLEN

INTEGER

Record length for table.

TABLE_CARD

BIGINT

Number of records in table.

STATISTIC_GATHERED

TIMESTAMP(2)

Date and time when statistics for the table or view were lasted updated.

IS_LEVEL2_APPROVED

CHAR(3)

One of:
YES = The table can be used with level 2 DB interface.
NO = The table cannot be used with level 2 DB interface.

IS_PERSISTENT

CHAR(3)

One of:
NO = The table is a temporary table
YES = The table is not temporary.

DATABANK_SYSID

INTEGER

System identifier of the databank in which the table is stored.

LATEST_ROOTID

INTEGER

Identifier for the table used in the rootpage of the databank file.

Primary key: TABLE_SYSID

Secondary index: DATABANK_SYSID

SYSTEM.TABLE_CONSTRAINTS

Records table constraints in the database.

Column name
Data type
Description

TABLE_SYSID

INTEGER

System identifier of the table.

CONSTRAINT_TYPE

CHAR(20)

One of:
PRIMARY KEY
UNIQUE
FOREIGN KEY
INDEX
INDEX UNIQUE
INTERNAL KEY
CHECK.

CONSTRAINT_SYSID

INTEGER

System identifier of the constraint.

CONSTRAINT_KEYCOLS

INTEGER

Number of key columns in the constraint.

CONSTRAINT_KEYLEN

INTEGER

Record length for key columns in the constraint.

CONSTRAINT_RECCOLS

INTEGER

Number of columns in constraint.

CONSTRAINT_RECLEN

INTEGER

Record length for constraint.

CONSTRAINT_CARD

BIGINT

Number of records in constraint.

DATABANK_SYSID

INTEGER

System identifier of the databank in which the table is stored.

IS_CONSISTENT

CHAR(3)

One of:
NO = The index may be inconsistent and should not be used for index lookup only
YES = The index may be used for index lookup only.

IS_DEFERRABLE

CHAR(3)

One of:
NO = The constraint is not deferrable
YES = The constraint is deferrable.

INITIALLY_DEFERRED

CHAR(3)

One of:
NO = The constraint is immediate
YES = The constraint is not immediate.

Primary key: TABLE_SYSID, CONSTRAINT_TYPE, CONSTRAINT_SYSID

Unique constraint: CONSTRAINT_SYSID

SYSTEM.TABLE_PRIVILEGES

Records instances of privileges granted on a table.

Column name
Data type
Description

TABLE_SYSID

INTEGER

System identifier of the table.

PRIVILEGE_TYPE

CHAR(20)

One of:
DELETE
INSERT
LOAD
REFERENCES
SELECT
UPDATE
TRIGGER.

GRANTEE

VARCHAR(128)

The name of the ident to whom the table privilege was granted.

GRANTOR

VARCHAR(128)

The name of the ident granting the table privilege.

IS_GRANTABLE

CHAR(3)

One of:
YES = WITH GRANT OPTION is held with the privilege
NO = WITH GRANT OPTION is not held with the privilege.

IS_INSTEAD_OF

CHAR(3)

One of:
NO = The privilege was granted explicitly
YES = The privilege was granted implicitly when an instead of trigger was created.

IS_ON_TABLE

CHAR(3)

One of:
NO = The privilege was granted on individual columns
YES = The privilege was granted on the complete table.

ALL_COLUMNS

CHAR(3)

One of:
YES = the privilege was granted on the table and therefore applies to all table columns, including new ones added
NO = the privilege only applies to the table columns explicitly specified when the privilege was granted.

Primary key: TABLE_SYSID, PRIVILEGE_TYPE, GRANTEE, GRANTOR

Secondary index: GRANTEE

Secondary index: GRANTOR

SYSTEM.TABLE_TYPES

Records the types of table supported.

Column name
Data type
Description

TABLE_TYPE

CHAR(20)

One of:
SYNONYM
SYSTEM TABLE
TABLE
VIEW.

Primary key: TABLE_TYPE

SYSTEM.TRANSLATIONS

Records character translations in the database.

Column name
Data type
Description

TRANSLATION_SYSID

INTEGER

System identifier of the character set translation.

SRC_CHARSET_SYSID

INTEGER

System identifier of the source character set for the translation.

TGT_CHARSET_SYSID

INTEGER

System identifier of the target character set for the translation.

Primary key: TRANSLATION_SYSID

SYSTEM.TRIGGERED_COLUMNS

Records table columns explicitly specified in an UPDATE TRIGGER event.

Column name
Data type
Description

TRIGGER_SYSID

INTEGER

System identifier of the trigger.

EVENT_TABLE_SYSID

INTEGER

System identifier of the table on which the trigger is defined.

EVENT_COLUMN_NAME

VARCHAR(128)

The name of the column in which updates will cause the trigger to execute.

Primary key: TRIGGER_SYSID, EVENT_TABLE_SYSID, EVENT_COLUMN_NAME

SYSTEM.TRIGGERS

Records triggers in the database.

Column name
Data type
Description

TRIGGER_SYSID

INTEGER

System identifier of the trigger.

EVENT_TABLE_SYSID

INTEGER

System identifier of the table on which the trigger is defined.

EVENT_MANIPULATION

CHAR(20)

One of:
DELETE
INSERT
UPDATE.

ACTION_ORDER

INTEGER

Ordinal number for trigger execution.

This number will define the execution order of triggers on the same table and with the same value for EVENT_MANIPULATION, ACTION_CONDITION, CONDITION_TIMING and ACTION_ORIENTATION.

The trigger with 1 in this column will be executed first, followed by the trigger with 2 etc.

ACTION_CONDITION

NCHAR VARYING(200)

The text of the search condition of the trigger action WHEN clause.

ACTION_STATEMENT

NCHAR VARYING(200)

The character representation of the body of the trigger.

If the length of the text exceeds 200 characters, this value is NULL and the source text is stored in SOURCE_DEFINITION.

ACTION_ORIENTATION

CHAR(20)

One of:
ROW
STATEMENT.

CONDITION_TIMING

CHAR(20)

One of:
BEFORE
AFTER
INSTEAD OF.

COND_REF_NEW_TABLE

VARCHAR(128)

Name of new table/row alias.

COND_REF_OLD_TABLE

VARCHAR(128)

Name of old table/row alias.

REFERENCE_NEW

CHAR(3)

One of:
NO = The trigger has no new table/row alias
YES = The trigger has new table/row alias.

REFERENCE_OLD

CHAR(3)

One of:
NO = The trigger has no new table/row alias
YES = The trigger has new table/row alias.

COLS_IS_IMPLICIT

CHAR(3)

One of:
NO = The trigger is invoked on update on any column (if the event is update)
YES = The trigger will only be invoked if a column in the for update of list is updated.

REF_SYSID

INTEGER

System identifier for foreign key constraint, if the trigger is defined for checking of a delete rule.

Primary key: TRIGGER_SYSID

Secondary index: EVENT_TABLE_SYSID

SYSTEM.TYPE_INFO

Records information about data types supported.

Column name
Data type
Description

DATA_TYPE

SMALLINT

Identification number for data type.

TYPE_NAME

CHAR(30)

Name of data type.

COLUMN_SIZE

INTEGER

Length of data type name.

LITERAL_PREFIX

CHAR(30)

Optional prefix for a literal of this type.

LITERAL_SUFFIX

CHAR(30)

Optional suffix for a literal of this type.

CREATE_PARAMS

CHAR(30)

A description of how to specify length attributes for the data type.

NULLABLE

SMALLINT

One of:
1 = A not null constraint can be used with this type in a domain or column definition
0 = A not null constraint can not be used with this type in a domain or column definition.

CASE_SENSITIVE

SMALLINT

One of:
1 = The data type name is case sensitive
0 = The data type name is not case sensitive.

SEARCHABLE

SMALLINT

One of:
0 = The data type can not be used with any comparison operator
1 = The data type can only be used with the LIKE operator
2 = The data type can be used with any comparison operator except like
3 = The data type can be used with any comparison operator.

UNSIGNED_ATTRIBUTE

SMALLINT

One of:
1 = The data type is unsigned
0 = The data type is signed
The value is NULL if the data type is non-numeric.

FIXED_PREC_SCALE

SMALLINT

One of:
1 = The data type has a determined precision and scale
0 = The data type does not have a determined precision and scale.

AUTO_UNIQUE_VALUE

SMALLINT

One of:
1 = The data type will generate unique values
0 = The data type will not generate unique values.

LOCAL_TYPE_NAME

CHAR(30)

Local name for data type.

MINIMUM_SCALE

SMALLINT

Minimum value for scale for a numeric data type.

MAXIMUM_SCALE

SMALLINT

Maximum value for scale for a numeric data type.

SQL_DATA_TYPE

SMALLINT

A numeric value representing the data type.

SQL_DATETIME_SUB

SMALLINT

Sub type for an interval data type.

NUM_PREC_RADIX

SMALLINT

Radix for numeric data type.

INTERVAL_PRECISION

SMALLINT

Precision for an interval data type.

PRECISION

INTEGER

Precision for data type.

INTERNAL_LEN_DIFF

SMALLINT

Internal length difference.

INTERNAL_TYPENAME

CHAR(30)

Internal type name.

INTERNAL_VERSION

SMALLINT

Internal version number.

INTERNAL_TYPEORDER

SMALLINT

Internal type ordering number.

Primary key: DATA_TYPE, TYPE_NAME

SYSTEM.USAGE_PRIVILEGES

Records instances of privileges which grant the right to use a database object.

Column name
Data type
Description

OBJECT_SYSID

INTEGER

System identifier of the database object.

OBJECT_PRIVILEGE

CHAR(20)

One of:
STRUCTURED
DISTINCT.

GRANTEE

VARCHAR(128)

The name of the ident to whom the usage privilege was granted.

GRANTOR

VARCHAR(128)

The name of the ident granting the usage privilege.

IS_GRANTABLE

CHAR(3)

One of:
YES = WITH GRANT OPTION is held with the privilege
NO = WITH GRANT OPTION is not held with the privilege.

Primary key: OBJECT_SYSID, OBJECT_PRIVILEGE, GRANTEE, GRANTOR

Secondary index: GRANTEE

Secondary index: GRANTOR

SYSTEM.USER_DEF_TYPES

Records user-defined types in the database.

Column name
Data type
Description

UDT_SYSID

INTEGER

System identifier of the user-defined type.

CATEGORY_TYPE

CHAR(20)

One of:
STRUCTURED
DISTINCT.

COLLATION_SYSID

INTEGER

System identifier for the collation used by the column.

CHARSET_SYSID

INTEGER

System identifier for the character set used by the column.

DATA_TYPE

CHAR(30)

Identifies the data type of the column.
Can be one of the following:
BIGINT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
BOOLEAN
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
NATIONAL CHARACTER
NATIONAL CHARACTER
VARYING
NATIONAL CHAR LARGE
OBJECT
DATE
DECIMAL
DOUBLE PRECISION
FLOAT
Float(p)
INTEGER
Integer(p)
INTERVAL
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP.

INTERVAL_TYPE

CHAR(30)

Identifies the interval type of the column

CHAR_MAX_LENGTH

BIGINT

For CHARACTER, BINARY, CHARACTER LARGE OBJECT and BINARY LARGE OBJECT data types, this shows the maximum length in characters or bytes as appropriate.

For all other data types it is the NULL value.

CHAR_OCTET_LENGTH

BIGINT

For a CHARACTER, BINARY, CHARACTER LARGE OBJECT and BINARY LARGE OBJECT data types, this shows the maximum length in octets.

For all other data types it is the NULL value. (For single octet character sets, this is the same as CHAR_MAX_LENGTH.)

NUMERIC_PRECISION

INTEGER

For NUMERIC data types, this shows the total number of decimal digits allowed in the column.

For all other data types it is the NULL value. NUMERIC_PREC_RADIX indicates the units of measurement.

NUMERIC_PREC_RADIX

INTEGER

For NUMERIC data types, the value 10 is shown because NUMERIC_PRECISION specifies a number of decimal digits. For all other data types it is the NULL value. NUMERIC_PRECISION and NUMERIC_PREC_RADIX can be combined to calculate the maximum number that the column can hold.

NUMERIC_SCALE

INTEGER

For NUMERIC data types, this shows the total number of significant digits to the right of the decimal point. For INTEGER values this is 0. For all other types, it is the NULL value.

DATETIME_PRECISION

INTEGER

For DATE, TIME, TIMESTAMP and interval data types, this column contains the number of digits of precision for the fractional seconds component.

For other data types it is the NULL value.

INTERVAL_PRECISION

INTEGER

For INTERVAL data types, this is the number of significant digits for the interval leading precision, see the Mimer SQL Reference Manual.

For other data types it is the NULL value.

INTERNAL_LENGTH

INTEGER

Internal length in bytes of value.

IS_INSTANTIABLE

CHAR(3)

(currently not used)

IS_FINAL

CHAR(3)

(currently not used)

ORDERING_FORM

CHAR(20)

(currently not used)

ORDERING_CATEGORY

CHAR(20)

(currently not used)

ORDERING_ROUTINE_SYSID

INTEGER

(currently not used)

NO_OF_ATTRIBUTES

INTEGER

(currently not used)

TOTAL_VLF_LENGTH

INTEGER

(currently not used)

CONSTRUCTOR_FUNCTION

INTEGER

(currently not used)

OBSERVER_METHOD

INTEGER

(currently not used)

TOTAL_DEFAULT_LENGTH

INTEGER

(currently not used)

TOTAL_ATTRIBUTE_NAME_LENGTH

INTEGER

(currently not used)

Primary key: UDT_SYSID

SYSTEM.USERS

Records idents (GROUP, OS_USER, PROGRAM or USER) in the database.

Column name
Data type
Description

USER_NAME

VARCHAR(128)

The ident name.

USER_SYSID

INTEGER

System identifier for the ident.

USER_TYPE

CHAR(20)

One of:
USER
OS_USER
PROGRAM
GROUP.

USER_PASSWORD

BINARY(18)

The encoded password for the ident.

USER_PASSWORD
_MINIMUM_LENGTH

INTEGER

The minimum length for a password.

Primary key: USER_NAME

Unique constraint: USER_SYSID

SYSTEM.VIEWS

Records views in the database.

Column name
Data type
Description

VIEW_SYSID

INTEGER

System identifier of the view.

CHECK_OPTION

CHAR(20)

One of:
CASCADED
LOCAL
NONE.

IS_UPDATABLE

CHAR(3)

One of:
NO = The view can not be updated
YES = The view can be updated.

IS_INSTEAD_OF

CHAR(3)

One of:
NO = The view can be updated per se
YES = The view can be updated due to the existence of an instead of trigger.

VIEW_DEFINITION

NCHAR VARYING(200)

The text of the view definition.

Primary key: VIEW_SYSID


Deprecated Features

This chapter discusses features and functionality that have been deprecated.


Export/Import

The Export/Import functionality in UTIL has been deprecated. It has been replaced with Mimer SQL LOAD/UNLOAD, see Loading and Unloading Data and Definitions.


Load/Unload

The Load/Unload functionality in BSQL has been deprecated. It has been replaced with Mimer SQL LOAD/UNLOAD, see Loading and Unloading Data and Definitions.

Load/Unload is still available from the BSQL menu for backward compatibility.


Readlog from UTIL

The UTIL Readlog functionality has been deprecated. It is now available from BSQL, see Mimer SQL User's Manual, READLOG.


Backup/Restore from UTIL

The Backup/Restore functionality in UTIL has been deprecated. It is now available as SQL statements, see Mimer SQL Reference Manual, System Administration Statements.


Statistics from UTIL

The statistics functionality in UTIL has been deprecated. It is now available as the SQL statement UPDATE STATISTICS, see Mimer SQL Reference Manual, UPDATE STATISTICS.


Shadowing Management from UTIL

The UTIL shadowing management has been deprecated. Use the BSQL functionality instead, see Creating and Managing Shadows


Mimer SQL Engine
System Management Handbook
Version 9.3

Mimer SQL Engine System Management Handbook, Version 9.3, March 2008
© Copyright Mimer Information Technology AB.

The contents of this manual may be printed in limited quantities for use at a Mimer SQL installation site. No parts of the manual may be reproduced for sale to a third party.
Information in this document is subject to change without notice. All registered names, product names and trademarks of other companies mentioned in this documentation are used for identification purposes only and are acknowledged as the property of the respective company. Companies, names and data used in examples herein are fictitious unless otherwise noted.

Produced and published by Mimer Information Technology AB, Uppsala, Sweden.

P.O. Box 1713,
SE-751 47 Uppsala, Sweden.
Tel +46(0)18-780 92 00.
Fax +46(0)18-780 92 40.

Mimer Web Sites:
http://www.mimer.com
 http://developer.mimer.com