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:

1. Create a home directory for your Mimer SQL database

   See Creating a Home Directory.

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

   See Editing the SQLHOSTS File.

3. Run 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 MIMSETUP command will define it to be:

 SYS$SPECIFIC:[SYSMGR]SQLHOSTS.DAT
 

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

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:

1. Open the SQLHOSTS file in a text editor and locate the LOCAL section.
2. Under Database, enter the name of the database.
3. Under 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 10.0. Upgrade can be done for databank files created by Mimer SQL version 7.1 and later.

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

SDBGEN Syntax

Assuming that SET COMMAND MIMLIB:MIMER is done, 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 2 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

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.

The following table shows how to use quotes to set the password case.

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	The INTERFACE field is currently not used. Specify '' (two single quote characters) here.
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. (A Mimer SQL 7 database server using DECNET listens to the network object named "MIMER").

Adding a Remote Database

To add a remote database:

1. Open the SQLHOSTS file in a text editor and locate the REMOTE section.
2. Fill 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 set-up logical names and install images, command style and automatic database server startup and shutdown.

Setting-up Logical Names and Install Images

In order to set-up Mimer SQL logical names and install images each time your OpenVMS system starts up, you must edit your startup file.

Edit SYS$MANAGER:SYSSTARTUP_VMS.COM to include the following line:

 $ @disk:[MIMER1001B]MIMSETUP SYSTEM

Setting the Command Style

You can use either OpenVMS- or UNIX-style commands.

To set-up your system to automatically accept the style you prefer, you can edit the LOGIN.COM file.

OpenVMS-style Commands

In LOGIN.COM, add the following line:

 $ SET COMMAND MIMLIB:MIMER

UNIX-style Commands

In LOGIN.COM, add the following line:

 $ DEFINE DCL$PATH MIMEXE

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:

1. Check that no one is using the database.
2. Check that no database server is started against the database you are going to remove.
3. Create 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;
 

4. Using 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.
5. Delete any directories that have been specifically created to hold databank files for the database.
6. Delete the database entry in:

 SYS$SPECIFIC:[SYSMGR]SQLHOSTS.DAT

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se