Establishing a Database

This chapter describes how to establish a local database and how to access a remote database already established in the network. It also describes how to upgrade and remove a database.

Refer to the Mimer SQL System Management Handbook for background information which is useful for understanding the issues and the different components involved in establishing a Mimer SQL database.

Overview

Having installed Mimer SQL, you can now establish a local database on the node on which you unpacked the Mimer SQL distribution.

To establish a database on an OpenVMS node, you must carry out the following steps:

Step 1Create a home directory for your Mimer SQL database

See Creating a Home Directory.

Step 2Prepare access to the database by editing the SQLHOSTS file

See Editing the SQLHOSTS File.

Step 3Run SDBGEN to generate the system databanks and the SYSADM ident

See Generating System Databanks and SYSADM.

Creating a Home Directory

First of all you must create a home directory for your database, for example:

$ CREATE/DIR somedisk:[TESTDB]

Editing the SQLHOSTS File

The SQLHOSTS file is used to list all the databases that are accessible to a Mimer SQL application from the node on which it is installed.

On a VMS node, the SQLHOSTS file is located by translating the logical name MIMER_SQLHOSTS.

The SETUP command will define it to be:

SYS$SPECIFIC:[SYSMGR]SQLHOSTS.DAT

 

A default SQLHOSTS file is installed the first time you run SETUP.

The SQLHOSTS file contains three sections:

LOCAL

The LOCAL section contains the names of the local databases on the current node, see Adding a Local Database.

REMOTE

The REMOTE section contains the names of remote databases accessible from the node, see Accessing a Remote Database.

DEFAULT

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 Specifying the Default Database.

Note:In the SQLHOSTS file, a line of text beginning with the character sequence -- is interpreted as a comment.
The maximum length for the name of a database on an OpenVMS node is 30 characters.

Adding a Local Database

To add a local database:

1Open the SQLHOSTS file in a text editor and locate the LOCAL section.

2Under Database, enter the name of the database.

3Under Path, enter the name of the directory which is to be the database’s home directory.

Example of a LOCAL Entry

LOCAL:

--

-- Database           Path

-- -------------------------------------------------------------------

   TESTDB             DISK:[TESTDB]

--====================================================================

Specifying the Default Database

The DEFAULT section in the SQLHOSTS file contains a single line that specifies the default database. This is the database which will be used if a database is not explicitly specified when logging on.

The default database must be listed in either the LOCAL or the REMOTE section.

Example of a Default Entry

DEFAULT:

--

-- Database

--====================================================================

   TESTDB

--====================================================================

 

Generating System Databanks and SYSADM

You generate the Mimer SQL system databanks SYSDB, TRANSDB, LOGDB and SQLDB by running the SDBGEN program.

When you run SDBGEN, it also generates the system administration ident SYSADM.

SDBGEN loads the system 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.

SDBGEN

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 11.0. Upgrade can be done for databank files created by Mimer SQL version 10.1 only.

For more information on upgrading, see Mimer SQL Release Notes.

SDBGEN Syntax

 

Assuming a SETUP is done with VMS-style commands, you run SDBGEN from the command line, using arguments.

The syntax for creating databank files is as follows:

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

sdbgen Command-line Arguments

Argument

Function

/PASSWORD=password

Password for SYSADM

dbase

Database name

syssz

Size of SYSDB

tfn

Filename for TRANSDB

tsz

Size of TRANSDB

lfn

Filename for LOGDB

lsz

Size of LOGDB

sfn

Filename for SQLDB

ssz

Size of SQLDB

Generating the System Databanks

For example, the following SDBGEN call:

$ SDBGEN /PASSWORD=ooops TESTDB

 

will generate a database named my_database and the database administration ident SYSADM will be assigned the password ooops.

If you do not enter the password parameter, SDBGEN will prompt you for all parameters that are missing, including the password for SYSADM.

If you enter the password parameter, SDBGEN will not prompt for any missing parameters, it will use default values.

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

Setting the Initial Size

You can specify the initial size for each of the Mimer SQL system databanks.

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

SYSADM Password

When you run SDBGEN, the database administration ident SYSADM is created and you must specify a password (passwords are case-sensitive) for this ident.

SYSADM Password Case

If your process uses TRADITIONAL parsing, DCL converts all VMS-style commands to uppercase and all Unix-style commands to lowercase. To control the case used in your password, you may have use quotes.

If your process uses EXTENDED parsing, DCL does not convert command arguments to uppercase.

For more information on PARSING_STYLE, use HELP:

$ HELP SET PROCESS/PARSE_STYLE

 

The following table shows how to use quotes to set the password case if TRADITIONAL parsing is used.

Entering:

Sets the password to:

SDBGEN/PASS=oops

OOPS

SDBGEN/PASS="oops"

oops

SDBGEN -p OOPS

oops

SDBGEN -p "OOPS"

OOPS

SYSADM Password Security

For security reasons, the password specified for SYSADM is not echoed on the screen when you enter it.

You should change the password at appropriate intervals using Mimer SQL with the ALTER IDENT statement.

Caution:Take care 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.

Accessing a Remote Database

You can access databases that reside on other nodes on the network by editing the REMOTE section in the SQLHOSTS file and adding information about the remote database.

For more information on the SQLHOSTS file, see Editing the SQLHOSTS File.

Access to remote 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 can contain up to five fields, separated by spaces and/or tab characters.

The fields in the REMOTE section specify the following:

Field

Explanation

DATABASE

The DATABASE field specifies the name of the remote database.

NODE

The NODE field specifies the network node name of the remote machine.

If you are using the TCP/IP interface, you can specify the IP address here.

PROTOCOL

You can specify DECNET or TCP depending on the type of network protocol to be used to create the client/server connection.

The default, specified by '' (two single quote characters), is TCP.

INTERFACE

For TCP/IP, this field specifies whether IPv4 or IPv6 should be used.

Specify 4 for IPv4 only

Specify 6 for IPv6 only

Enter an empty string with '' (two quotes) to use either IPv4 or IPv6 as indicated by the Node name lookup.

SERVICE

TCP/IP

If using the TCP/IP protocol, enter the TCP/IP port number the database server uses. The default is 1360.

DECNET

If using DECNET, enter the database name.

The server listens to the network object using the same name as the database. (An old Mimer SQL version 7 database server using DECNET listens to the network object named “MIMER”).

Adding a Remote Database

To add a remote database:

1Open the SQLHOSTS file in a text editor and locate the REMOTE section.

2Fill in the fields, as specified above, according to your network configuration.

Example of a REMOTE Entry

REMOTE:

--

-- Database           Node               Protocol Interface Service

-- ------------------ ------------------ -------- --------- -------

   REMTEST            STARTREK           TCP      '' 1360

Mimer SQL System Settings

You can edit your startup and shutdown files to automatically start and stop database servers at system startup and shutdown.

Automatic Database Server Start

If you want the Mimer SQL database server to start automatically whenever the system is booted, you must edit the SYS$MANAGER:SYSTARTUP_VMS.COM file.

The following example starts two Mimer SQL database servers:

$ MIMCONTROL/START TESTDB

$ MIMCONTROL/START INVENTORY

Automatic Database Server Shutdown

If you want to perform a controlled shutdown of the database server whenever the OpenVMS system is shut down, you must edit the SYS$MANAGER:SYSHUTDWN.COM file and add the relevant commands at the end.

The following example stops two database servers:

$ MIMCONTROL/STOP TESTDB

$ MIMCONTROL/STOP INVENTORY

Removing a Mimer SQL Database

To remove a database, perform the following steps:

1Check that no one is using the database.

2Check that no database server is started against the database you are going to remove.

3Create a list of all databank files by doing the following:

$ BSQL/SINGLE database

Username: SYSADM

Password: xxxxxx

SQL> SELECT DATABANK_FILENAME FROM SYSTEM.DATABANKS;

SQL> EXIT;

 

4Using the list generated in the previous step, locate and delete all the physical databank files. If the file name does not contain a directory specification, the directory will be the home directory of the database.

5Delete any directories that have been specifically created to hold databank files for the database.

6Delete the database entry in:

SYS$SPECIFIC:[SYSMGR]SQLHOSTS.DAT