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.
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:
- Create a home directory for your Mimer SQL database
See Creating a Home Directory.
- Prepare access to the database by editing the SQLHOSTS file
See Editing the SQLHOSTS File.
- 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:
The LOCAL section contains the names of the local databases on the current node, see Adding a Local Database.
The REMOTE section contains the names of remote databases accessible from the node, see Accessing a Remote Database.
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:
- Open the SQLHOSTS file in a text editor and locate the LOCAL section.
- Under Database, enter the name of the database.
- Under Path, enter the name of the directory which is to be the database's home directory.
Example of a LOCAL EntryLOCAL: -- -- 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 EntryDEFAULT: -- -- 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.
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.
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
Password for SYSADM Database name Size of SYSDB Filename for TRANSDB Size of TRANSDB Filename for LOGDB Size of LOGDB Filename for SQLDB 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.
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.
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.
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:
The DATABASE field specifies the name of the remote database. 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. 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. The INTERFACE field is currently not used. Specify '' (two single quote characters) here. TCP/IPIf using the TCP/IP protocol, enter the TCP/IP port number the database server uses. The default is 1360.DECNETIf 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:
- Open the SQLHOSTS file in a text editor and locate the REMOTE section.
- Fill in the fields, as specified above, according to your network configuration.
Example of a REMOTE EntryREMOTE: -- -- 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.
In LOGIN.COM, add the following line:$ SET COMMAND MIMLIB:MIMER
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:
- Check that no one is using the database.
- Check that no database server is started against the database you are going to remove.
- 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;
- 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.
- Delete any directories that have been specifically created to hold databank files for the database.
- 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