Managing a Database Server

This chapter contains a short guide to administrating database servers under OpenVMS. It also describes how to use the MIMCONTROL command under OpenVMS.

For general information on managing database servers, refer to the Mimer SQL System Management Handbook.

The MIMCONTROL Command

Before you can access a database, the Mimer SQL database server must be started on the node it resides on.

You start, stop and control database servers on OpenVMS using the MIMCONTROL command.

Note:You cannot start the MIMCONTROL program by using the DCL command RUN.

Required Privileges

To use MIMCONTROL you must have either:

SETPRV privilege

or

CMKRNL, CMEXEC, SHMEM, SYSPRV, WORLD, TMPMBX, OPER, NETMBX, PSWAPM, DETACH, ALTPRI, PRMGBL, SYSGBL, SYSLCK and SYSNAM privileges.

If the resident memory feature is used (the BPResident parameter in MULTIDEFS.DAT is set), you must have the VMS$MEM_RESIDENT_USER process right.

Database Server Parameters – the MULTIDEFS File

When you start a database server on an OpenVMS machine for the first time, MIMCONTROL will create a MULTIDEFS file containing parameter default values for the database server. These parameter values are based on the amount of memory installed on the machine.

You may also generate the MULTIDEFS file manually by using the MIMCONTROL/GENERATE command. For example:

$ MIMCONTROL/GENERATE TESTDB

 

It is not possible to change the parameters for a running database server.

You can fine-tune database server performance by adjusting the parameters as required.

Refer to the Mimer SQL System Management Handbook for details.

MIMCONTROL Syntax

You run the MIMCONTROL program is using arguments specified on the command-line.

For example:

$ MIMCONTROL/START TESTDB

 

Starts the database server and provides access to TESTDB.

If you run the MIMCONTROL command without any options it displays help on command-line arguments.

MIMCONTROL Command-line Arguments

For more information on MIMCONTROL arguments and their combinations, see the Mimer SQL System Management Handbook.

Argument

Function

/STATUS/DCL

Output status information about the specified database server into the symbol MIMER_STATUS for use in a command procedure.

For details about the output string resulting from this option, see MIMCONTROL (/STATUS/DCL).

/STATUS

Output status information about the specified database server.

/DISABLE

Disable new user connections to the database server. Users already connected are not affected.

/ENABLE

Enable new user connections to the database server.

/KILL

Kill the database server immediately. This should only be used in emergency situations when a normal stop does not work.

The next time the database is started, all databanks that were open at the time the server was killed will be automatically checked. Connected users will receive an error the next time they attempt to access the database.

/LOGOUT=chan

Force logout of the specified channel number.

Use channel numbers displayed by the USERS option of the MIMINFO command, see the Mimer SQL System Management Guide.

/START [=timeout]

Start the database server.

If the server does not become operational within the specified number of seconds, the server will be killed. (The default timeout is 600 seconds.)

/HOLD

This switch can only be used together with the /START switch.

After the server has been started, the MIMCONTROL command will not return to the DCL prompt, but will wait for the server to stop. This can simplify writing scripts that automatically restart database servers.

The termination status from the MIMCONTROL command will be the final status code from the database server process.

/STOP [=timeout]

Stop a database server. Any remaining users will be logged out.

If the database server does not stop within the specified number of seconds, the server will be killed. (The default timeout is 120 seconds.)

/WAIT [=timeout]

Wait for all connected users to log out.

If there are still users connected after the timeout period expires, the command fails.

The timeout period should be given in seconds. If no timeout period is specified wait will be performed without any timeout.

/DUMP

Create a dump directory and produce dumps of all internal database server areas to files in that directory.

The files produced can be examined by using MIMINFO, see the Mimer SQL System Management Guide.

/GENERATE

Create a new MULTIDEFS.DAT file with default for parameters, if the file is missing.

database

Specifies the name of the database to access.

If a database name is not specified, the default database will be controlled.

The default database is determined by setting the MIMER_DATABASE logical name.

The DEFAULT setting in SQLHOSTS is not used for MIMCONTROL.

MIMCONTROL (/STATUS/DCL)

The MIMCONTROL/STATUS/DCL command is a special form of the MIMCONTROL/STATUS command which returns the database server status information in the form of a single string containing a comma-separated list which is useful when writing command procedures.

On OpenVMS, the MIMCONTROL command is silent and sets the DCL symbol MIMER_STATUS to the value of the status string.

You can use the lexical function F$ELEMENT() to extract the list elements. For example:

$ MIMCONTROL/STATUS/DCL

$ SHOW SYMBOL MIMER_STATUS

MIMER_STATUS == "Running,Enabled,LOKE_0:[PER.LOKE],0,A2,2023-12-06 16:10,121323127"

$ DIR=F$ELEMENT(2,",",MIMER_STATUS)

$ USERS=F$ELEMENT(3,",",MIMER_STATUS)

$ PID=F$ELEMENT(4,",",MIMER_STATUS)

$ SHOW SYMBOL DIR

DIR = "LOKE_0:[PER.LOKE]"

$ SHOW SYMBOL USERS

USERS = "0"

$ SHOW SYMBOL PID

PID = "A2"

The MIMPERF Command

The database server maintains a number of performance related counters counting events such as transactions, disk I/O, buffer pool accesses, client/server accesses and much more. The MIMPERF command can be used to monitor these counters.

MIMPERF can access remote databases running under any type of host. It can display statistics from Mimer database servers from version 9 and up.

VSI has a tool named T4 that continually collects performance counters from the VMS host system. The collected data is stored in simple CSV (Comma Separated Values) files. These files can be analyzed by tools such as TLViz or Excel. The MIMPERF tool has an option to generate CSV files so that the Mimer server data can be used together with other data collected by T4.

For more information about T4, please see the VSI web site:

https://vmssoftware.com/products/t4/

For more information on how to use MIMPERF with T4, please read MIMER$DOC:MIMPERF_T4.TXT.

MIMPERF Syntax

$ MIMPERF [options] database

 

The options can be given either is VMS style (preceded by a /) or in UNIX style, depending on how the Mimer SETUP command was executed. Please note that UNIX style options in uppercase should be enclosed in double quotes on VMS.

Most counters are displayed as the number of events that happened per second. If this number is greater than zero but less than one, it may be displayed as "0." (a zero followed by a decimal point) to distinguish it from zero (meaning that no such events happened).

MIMPERF Command-line Arguments

VMS style

Unix style

Description

/SYSTEM

--system, -S

Display general database server counters interactively

/TRANSACTION

--transaction, -T

Display counters detailing transaction rates

/COMMUNICATION

--communication, -C

Display counters from the communication between the database server and connected clients

/POOL

--pool, -P

Show the buffer pool usage and hit rates

/COMPILER

--compiler, -Q

Show how the compilers for SQL statements and PSM procedures are used

/SPACE

--space, -A

Show the space usage in the SQL pool

/EXPLAIN

--explain, -x

Use this switch together with any of the switches above to display a short explanation of the counters

/T4=file

--t4=file, -4 file

Collect all Mimer counters to a T4 compatible CSV file

/INTERVAL=n

--interval=n, -i n

Set the sample interval to n seconds. The default is 10 seconds for the interactive displays and 60 seconds for T4.

/ENDING="[YYYY-MM-DD ]HH:MM[:SS]"

--ending=t, -e t

Stop collection at the specified time. Note that there should be a space between the (optional) date and the time. If a space is included, the value must be enclosed in double quotes.

The MIMTCP Server

If you are using the TCP/IP protocol, a MIMTCP server listening to a specific port (usually port 1360) will be started the first time the database server is started.

TCP/IP Port Number

The TCP/IP port number that the MIMTCP server will listen to is specified in the TCPPort parameter in the MULTIDEFS.DAT file. If several database servers specify the same port number, they will share the same MIMTCP server.

When a client connects to the TCP/IP port, the MIMTCP server will accept the connection. The client specifies the database to which a connection is to be established and the MIMTCP server will hand over the connection to the appropriate database server. All further communication between the client and the database server is then done directly without involving the MIMTCP server.

Note that if you specify a TCPPort parameter with a preceding + sign (like +1377), the database server will listen directly on the specified port. A MIMTCP server will not be started for that port. Clients connecting to the port will reach the database server directly, avoiding the overhead of going through the MIMTCP server. However, such port number can not be shared between database servers.

A server that listens on its own port will still accept connections from MIMTCP servers listening on other ports.

System Logical Names

Whenever a MIMTCP server starts, it will define the system logical name “MIMTCP_xxxx” (where xxxx is the port number) to be the PID of the MIMTCP server process. This makes it easy to find the MIMTCP server process that is listening to a particular TCP/IP port.

MIMTCP log file

The MIMTCP server will log startup and error messages in the log file SYS$MANAGER:MIMTCP_xxxx.LOG, where xxxx is the port number that the MIMTCP server listens to.

Controlling MIMTCP servers

The command procedure MIMER$EXE:TCPCONTROL.COM can be used to manage MIMTCP processes. The first parameter given to the procedure controls what the procedure should do. If no parameter is given, a short help message is displayed. The following example shows how the MIMTCP server for port 1360 is stopped and a new server for port 1337 is started:

$ @MIMER$EXE:tcpcontrol

Usage: TCPCONTROL STATUS        ! Display status for all MIMTCP processes

       TCPCONTROL START  [port] ! Start MIMTCP process for a port

       TCPCONTROL STOP   [port] ! Stop the MIMTCP process for a port

       TCPCONTROL STOP   ALL    ! Stop all running MIMTCP processes

$ @MIMER$EXE:tcpcontrol status

     Pid    Port Version     Username   Started

00000227    1360 1108B       SYSTEM     25-AUG-2023 22:14:29.22

$ @MIMER$EXE:tcpcontrol stop 1360

MIMTCP process for port 1360 STOPPED

$ @MIMER$EXE:tcpcontrol start 1337

Starting MIMTCP process version 1108B

%RUN-S-PROC_ID, identification of created process is 00004D04

$ @MIMER$EXE:tcpcontrol status

     Pid    Port Version     Username   Started

00004D04    1337 1108B       SYSTEM     25-AUG-2023 23:09:11.96

 

Starting and stopping MIMTCP servers explicitly using the TCPCONTROL procedure is rarely needed. When the MIMCONTROL/START command is used, a MIMTCP process will be started automatically. This process will normally be active until the machine is shut down. Since the MIMTCP process does not hold any resources, it is not necessary to shut it down explicitly in the machine shutdown procedure.

Using a Memory Resident Buffer Pool

The buffer pool can be created in two different ways. If the MULTIDEFS.DAT parameter BPResident is left blank (the default) the buffer pool is allocated in normal process memory and is backed by the paging file. This means that the buffer pool is subject to normal virtual memory paging; if the system memory requirements increases the operating system may page out parts of the buffer pool.

Since the paging file is used as backing store the paging file quota of the database server process is increased to a suitable value. The working set quotas are also increased. These quotas are ultimately limited by the system parameter WSMAX, so when a large buffer pool is created with this method the WSMAX parameter must be increased accordingly.

At database server startup, the WSMAX parameter is checked. If it is insufficient an error message is displayed with the required value.

If the BPResident parameter specifies a name, the buffer pool is allocated as a memory resident area, i.e. physical memory in the machine is reserved. This has several advantages:

Since physical memory is used, the buffer pool contents are always available and is never swapped out.

The paging files are not used for the buffer pool; they do not need to be extended.

The buffer pool does not use working set quota for the server process.

The buffer pool uses a larger virtual page size which makes memory accesses more efficient.

It is possible to reserve a large memory area for the buffer pool at VMS boot time.

The name that BPResident specifies will be use to name the memory resident global section. Please note that it is case sensitive. The name must be unique (the same section can not be used by two different database servers).

The user that starts a server using a resident memory area must have the process right VMS$MEM_RESIDENT_USER. This can be granted to a user by using AUTHORIZE:

$ SET DEF SYS$SYSTEM

$ MCR AUTHORIZE

UAF> GRANT/IDENTIFIER VMS$MEM_RESIDENT_USER SMITH

%UAF-I-GRANTMSG, identifier VMS$MEM_RESIDENT_USER granted to SMITH

UAF> EXIT

 

It is also possible to create a resident memory reservation so that the VMS system puts aside resident memory for the buffer pool at system boot time. By doing this and then running AUTOGEN, the VMS system can be appropriately tuned. This is described in the VMS document System Manager Manual Vol 2, section 3.11 Reserved Memory Registry. The name used in the registration must match the name used in the BPResident parameter.

Determining Buffer Pool Size

The buffer pool size is calculated by the MIMCONTROL/STATUS command. This command reads the parameter configuration in the MULTIDEFS.DAT file and calculates the required buffer pool size. The size is displayed in KBytes or MBytes and is rounded upwards so the value can be used for a resident memory reservation.

To see the exact buffer pool size, use the MIMCONTROL/STATUS/DCL command. The last value returned in the MIMER_STATUS symbol is the buffer pool size in bytes.

Process Quotas for the Database Server

When MIMCONTROL/START is used to start a database server process quotas are calculated according to the MULTIDEFS.DAT parameter file.

The size of the database server is calculated. The size includes communication areas (70K per User as specified in MULTIDEFS.DAT), thread stacks, local data, initial SQL Pool (SQLPool parameter) and code. If BPResident is not specified, the buffer pool size is also included.

The WSDEFAULT and WSQUOTA (working set quotas) of the server process is set to the calculated server size.

The PGFLQUOTA (page file quota) is set to the calculated server size plus the size the SQL pool can grow to according to the MULTIDEFS.DAT parameter MaxSQLPool. This means that the MaxSQLPool parameter can be used to control the paging file quota for the process.

The WSEXTENT quota is set to the WSMAX system parameter.

Troubleshooting Tips

In order to successfully start a database server, the following conditions must be fulfilled:

The system databank file, SYSDB110.dbf, must have been created. See Generating System Databanks and SYSADM

There must be an entry for the database in the local section of the SQLHOSTS file. See Editing the SQLHOSTS File

The ProcName of the MULTIDEFS file must not specify a process name prefix that is identical to that of another running multi-user system.

There must not be any other node in a cluster which has started the same database server.

The database must not be in use in single-user access mode at the time the database server is started.

The file SYS$MANAGER:MIMERKEY.DAT must contain a valid Mimer SQL license key.