Maintaining a Nonstop Production System

A collection of ALLBASE/SQL features can be used to satisfy needs for nonstop, continuously available operations. These features consist of ALLBASE/SQL statements and SQLUtil commands that perform the tasks of database creation, maintenance, and recovery. The statements and commands involved are described in this section under the following topics:

DBEFiles on Different Devices
Detaching and attaching database files
CHECKPOINT statement host variable
Console message file
User-initiated change to a new log file
System catalog information

Storing DBEFiles on Different Devices

A DBEFile can be defined on a particular device by using the DEVICE option to specify either the volume name or the volume identifier. For example, to create a DBEFile on device 3, the following command can be used:



   CREATE DBEFILE PARTSDBE,

     WITH PAGES = 4,

     NAME = 'PARTSDBE',

     TYPE = TABLE,

     DEVICE = 'number3';

A DBEFile can also be defined in a particular group of an account by qualifying the volume name or the volume identifier. All DBEFiles for a DBEnvironment must be created in groups within the same account. If the DBEFile name is not qualified by a group name, the file is created in the same group as the DBECon file.

For example, suppose that a DBEnvironment was created in an EMPLOYEES group. (The DBECon file would also be created in the EMPLOYEES group.) To create a DBEFile, SWING, in the PARTTIME group, the following command might be used:



   CREATE DBEFILE SWING, 

     WITH PAGES = 4,



     NAME = 'SWING.PARTTIME',

     TYPE = TABLE;

     ON DEVICE = 'VOL1';

This command would create the DBEFile SWING.PARTTIME in the PARTTIME group on VOL1.

Detaching and Attaching Database Files

You can detach a DBEFile or DBEFileSet from a DBEnvironment. Detaching a DBEFileSet is equivalent to detaching all of the DBEFiles in the DBEFileSet. Once a DBEFile is detached, data in the DBEFile is inaccessible until it is attached again. The SQLUtil commands, DETACHFILE and ATTACHFILE are used to detach and attach files.

Detaching DBEFiles from a database is useful for restricting access to parts of a database while the remaining files are online and operational. Detaching files is also useful during file level recovery processing when DBEFiles must be detached before such processing.

To detach a DBEFile or DBEFileSet, the SQLUtil command DETACHFILE is used as shown in the following example of detaching the WareFS DBEFileSet:



   isql=>sqlutil

   >> detachfile

   DBEnvironment Name: PartsDBE

   Maintenance Word: MaintenanceWord



   Enter DBEFileset Name (return to finish): WareFS

   Enter DBEFileset Name (return to finish): 

   Enter DBEFile name (return to finish):

   Do you wish to proceed (y/n)? y

To attach a file, the SQLUtil command ATTACHFILE is used as shown in the following example of attaching the WareFS DBEFileSet:



   isql=>sqlutil

   >> attachfile

   DBEnvironment Name: PartsDBE

   Maintenance Word: MaintenanceWord



   Enter DBEFileset Name (return to finish): WareFS

   Enter DBEFileset Name (return to finish): 

   Enter DBEFile name (return to finish):

   Do you wish to proceed (y/n)? y

Using a Host Variable with the CHECKPOINT Statement

The CHECKPOINT statement returns a host variable that contains the approximate number of free blocks (NFB) available in the log file.

The syntax of the CHECKPOINT statement is as follows:



   CHECKPOINT [:HostVariable

              :LocalVariable

              :ProcedureParameter]

Using Console Message Files

Occurrences of certain events causes messages to be written to a console. In an operatorless environment, these messages can be redirected to a file that can later be queried.

All messages are written to the console if you enter nothing or CONSOLE. If you want messages to also be written to a file, you must enter a file name other than CONSOLE.

The setenv (C shell) command is used to create a console file as follows:



   setenv HPSQLconsole cnslfile

   setenv HPSQLconsole /dir1dir2/console_file_name

   setenv HPSQLconsole /tmp/cnslfile

The % (Bourne shell) command is used as follows:



   % HPSQLconsole cnslfile

   % export HPSQLconsole

If no console file name is given, no error is issued and no file is created. The messages are written to the console instead.

If a relative pathname is specified, the file is created in a file relative to where the first START DBE or CONNECT statement is issued. For example, if a START DBE statement is issued from /dir1/dir2/dir3 after executing the command setenv HPSQLconsole ../../cnslfile, messages are written to the file, /dir1/dir2/dir3/../../cnslfile. The absolute pathname cannot be more than 44 characters.

The messages written to the console file are 80 character ASCII records. They can be read with any editor. The text of the message is the same whether it is written to the console or the console file.

Messages are written to the console file when the following events occur:

A log file is full and the system initiates a switch to a new log file.
A log file is ready for backup.
A read or write error occurs on a log file.

Making Changes to a New Log File

The CHANGELOG command forces a change to a new log file instead of having to wait for the system to switch logs when the present log file becomes full. ALLBASE/SQL changes to a new log file only when the current log is full. However, if you want to force a change to a new log even if the log file is only 90% full, for example, you can perform a backup on the log file at a certain time of day.

The CHANGELOG command returns the sequence number of the current and new log file, as follows:



   isql=>sqlutil

   >>changelog



   DBEnvironmentName: PartsDBE

   Maintenance Word: MaintenanceWord

   Change to a new log (y/n) y



   Changed log from Sequence Number 2 to Sequence Number 3.

Now, the log file with the sequence number 2 can be backed up.

Checking the System Catalog

The system catalog views, SYSTEM.DBEFile and SYSTEM.DBEFILESET, have an additional column called ATTACHED which indicates whether the DBEFile is attached.