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.

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.

 

 

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.

 

 

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.

 

 

 

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.

 

 

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

 

 

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

 

 

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

 

 

 

    

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.

 

 

 

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.

 

3.6.3Syntax

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:

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.

 

 

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.

 

 

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.

 

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

 

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

•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

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.

.