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 in different groups and volumes
Moving DBEFiles to different groups
Detaching and attaching database files
CHECKPOINT statement host variable
Console message file
User-initiated change to a new log file
System catalog information

DBEFiles in Different Groups and Volumes

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.

Moving DBEFiles to Different Groups

You can move a DBEFile to a different group with the SQLUtil command MOVEFILE. If you had created the SWING DBEFILE in the EMPLOYEES group and wanted to later move it to the PARTTIME group, the MOVEFILE command would be used as follows:

   isql=>sqlutil

   >>movefile

   DBEnvironmentName: PARTSDBE

   Current FileName: SWING

   New FileName: SWING.PARTTIME

   Current Device: DISK

   New Device (opt): 2

   Access Mode (mapped/pseudo) (opt):  RETURN

The MOVEFILE command must be executed from the group containing the file to be moved. Thus, the above command must be executed from the EMPLOYEES group. The DBECon file can be moved to a different device, but not to a different group.

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 setvar command is used to create a console file as follows:

   setvar HPSQLconsole console_file_name 

   setvar HPSQLconsole cnslfile.group.account

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

The console file is created in the group where the DBECon file resides.

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.




	NOTE: Messages are color coded for use with OpenView on Emerald systems.

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.

The DBEFile view specifies the group name as well as the file name for the DBEFile in the FileID field. The group name of a DBEFile can optionally be specified as different than that of the DBECon file when a DBEFile is created.