Creating a Mimer SQL Database

Once the Mimer SQL 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.

Linux:On Linux and macOS the provided dbinstall command performs all necessary installation steps to create an initial database and getting it up and running. The options available in dbinstall give opportunities to control and carry out the following:

Deciding a database home directory

Registering the database

Deciding the SYSADM password

Creating the system databanks, including the data dictionary

Deciding owner of the database

Setting up the networking environment

Setting up autostart procedure

Setting up a data source definition for ODBC use

Creating an example database

Creating a basic development setup with an OS_USER

Creating the default database configuration file

Starting the database created

Many of these tasks are described in a more general and detailed manner further on in this chapter.

Note:On macOS the dbinstall command is complemented with a Graphical User Interface.

3.1Registering 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.

Linux & VMS:On Linux, macOS 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 Linux 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 Windows help provided with the Mimer Administrator for details on how to use it.

 

3.1.1Database name recommendations

When selecting the database name there are some guidelines to be considered. The most recommended names are those built on ordinary letters, possibly combined with digits and dash/underscore. The case of letters used is not significant.

Please regard the following:

Avoid having a database name that contains digits only. A database named as a number can get syntactical problems when used in commands, for example where optional numerical parameters can be given.

Avoid using question marks and asterisks, since they can be treated as wildcard characters in some situations.

Avoid using white space in the name, since this will make the use of the name more complex, requiring use of quotes or escape characters.

Avoid using quotes in the database name, since this will be confusing and hard to use.

3.2The 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.

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

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 Linux 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.

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.

3.3Accessing 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.

Linux:The definition of a remote database under Linux involves creating an entry in the /etc/sqlhosts file, see The SQLHOSTS File on VMS and Linux 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 the Linux Getting Started G.

 

VMS:The definition of a remote database under OpenVMS involves creating an entry in the SQLHOSTS file, see The SQLHOSTS File on VMS and Linux 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. Windows help is provided with the Mimer Administrator to guide you through the creation of a remote database.

 

3.3.1Client/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.

3.4Mimer 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 Linux machine).

Version number.

Expiration date for the key.

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

Linux & 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 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, or serial number (depending on platform,) of the computer on which the database server will run. On non-Windows platform, use mimlicense to retrieve this information:

mimlicense --cpuid

 

Or, VMS-style:

MIMLICENSE/CPUID

 

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 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.

3.5MIMLICENSE - Managing the license key

Linux & VMS:The mimlicense application is currently available on Linux, macOS and OpenVMS.

 

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.

Note that the database server must be restarted for the key changes to apply.

3.5.1Syntax

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 [-a hexcode | -c | -d keyid | -f file | -l | | -r | -i |-c | -n]

 

mimlicense [--add=hexcode | --combined | --delete=keyid | --filename=file |

    --cpuid | --list | | --nologo | --remove]

mimlicense [-v|--version] | [-?|--help]

3.5.2Command-line Arguments

Unix-style

VMS-style

Function

-a hexcode

--add=hexcode

/ADD=hexcode

Add a license key.

-c

--combined

/COMBINED

Describe what the combined keys permits.

-d keyid

--delete=keyid

/DELETE=keyid

Delete the specified key.

-f filename

--file=filename

/FILE=filename

Add a license key from a .mcfg file.

-i

--cpuid

/CPUID

Show the computer's CPU id.

-l

--list

/LIST

List the contents of the key file.

-n

--nologo

/NOLOGO

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

-r

--remove

/REMOVE

Remove keys.

mimlicense will prompt for each key if it is supposed to be removed or not.

-v

--version

/VERSION

Show mimlicense version information.

-?

--help

/HELP

Show help text.

 

 

Linux:The Unix-style command-line flags must be used on a Linux or macOS 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

    

3.6SDBGEN - 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.

Linux:A local database is set up on a Linux or macOS 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. Windows help is provided with both the Mimer Administrator and SDBGEN to guide you through setting up a local database.

 

3.6.1Setting 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 4 kilobytes.

3.6.2Setting 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.

 

3.6.3Syntax

Linux:On Linux or macOS, 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 the current version.

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

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

sdbgen [-p pass] [database [syssz [trafn [trasz [logfn [logsz [sqlfn

    [sqlsz]]]]]]]]

 

sdbgen [--password=pass] [database [syssz [trafn [trasz [logfn [logsz

    [sqlfn [sqlsz]]]]]]]]

 

sdbgen -u|--upgrade [database]

 

sdbgen [-v|--version] | [-?|--help]

3.6.4Command-line Arguments

When creating databanks files:

Unix-style

VMS-style

Function

-p password

--password=password

/PASSWORD=password

Password for SYSADM

-u

--upgrade

/UPGRADE

Initiate a database upgrade.

-v

--version

/VERSION

Display version information.

-?

--help

/HELP

Display help text.

database

database

Database name

syssz

syssz

Size of SYSDB, 4K pages (prompted for if omitted.)

tfn

tfn

Filename for TRANSDB

tsz

tsz

Size of TRANSDB, 4K pages (prompted for if omitted.)

lfn

lfn

Filename for LOGDB

lsz

lsz

Size of LOGDB, 4K pages (prompted for if omitted.)

sfn

sfn

Filename for SQLDB

ssz

ssz

Size of SQLDB, 4K pages (prompted for if omitted.)

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 database parameter is missing, the environment variable MIMER_DATABASE is used to determine which database the databank files should be created for.

3.7Establishing 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.

An example database is delivered with Mimer SQL. See Mimer SQL User's Manual, The Example Environment.

3.8Managing 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 Statements for details.

3.8.1Selecting 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.

3.8.1.1The 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.

3.8.1.2Defining a Node-specific Default Database

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

Linux & VMS:The default database for Linux, macOS 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 Linux.

 

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 Windows help provided with the Mimer Administrator for details on how to create System Wide Mimer ODBC Data Sources.

 

3.8.1.3Defining 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.

Linux & VMS:A user-specific default database is defined under Linux, macOS 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 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.

 

3.8.2Troubleshooting 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.

Linux: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.

Linux & 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

 

3.8.2.1NAMED 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 8 or later, 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 newer 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 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 8 or later 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.

3.9Executing 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 System Management Manual, 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.

.

Linux:The Unix-style command-line flags must be used on a Linux or macOS 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.