Tasks for the DBA

• Creating logical and physical objects.

• Starting and stopping DBE sessions.

• Establishing multiple DBEnvironment connections.

• Managing ALLBASE/SQL logs.

• Maintaining buffers for data and log pages.

• Managing transactions and locks.

• Setting parameters in the DBECon file.

• Monitoring the system catalog.

• Managing nonstop production systems.

• Backing up and restoring DBEnvironments.

• Migrating DBEnvironments between releases of ALLBASE/SQL.

Creating Logical and Physical Objects

The largest physical unit in ALLBASE/SQL is the DBEnvironment, which is a collection of files for one or more logical databases. A DBEnvironment is the maximum unit of transaction scope and recovery. The DBEnvironment contains:

A DBEFile is an MPE/iX file that can be associated with a DBEFileSet. ALLBASE/SQL database tables are stored in one or more DBEFiles. Indexes for a table are also stored in DBEFiles. Figure 1-1 “Tables, DBEFiles, and DBEFileSets” illustrates the relationship among tables, indexes, DBEFileSets, and DBEFiles.

Figure 1-1 Tables, DBEFiles, and DBEFileSets

A DBEFileSet is a collection of DBEFiles containing data for one or more tables. The tables and indexes associated with a DBEFileSet do not have to be for the same database.

Figure 1-2 “Databases and DBEFileSets” illustrates that while a DBEFileSet can contain data for all the tables in a database, a DBEFileSet can also contain data for some of the tables in a database, or for tables in more than one database.

Figure 1-2 Databases and DBEFileSets

Thus, DBEFileSets offer a way to allocate data storage independently of how users think about the data. During physical design and database creation, the DBA determines space requirements for the tables and indexes and creates DBEFiles and DBEFileSets to accommodate them.

A DBEnvironment, illustrated in Figure 1-3 “Elements of an ALLBASE/SQL DBEnvironment”, houses the DBEFiles for one or more ALLBASE/SQL databases.

Figure 1-3 Elements of an ALLBASE/SQL DBEnvironment

The DBEnvironment also contains the following entities, which contain information for all databases in the DBEnvironment:

The DBA determines the configuration, the startup parameters, the name and size of DBEFile0, the name and size of data and index DBEFiles, and the name and size of initial log files before configuring the DBEnvironment. ALLBASE/SQL uses defaults for any of these values if a choice is not made.

Starting and Stopping DBE Sessions

The DBA controls access to each DBEnvironment by turning the AUTOSTART flag ON or OFF and by issuing START DBE and STOP DBE commands. Use the SQLUtil ALTDBE command (described later in this chapter) to turn AUTOSTART mode ON or OFF.

When users have the proper authorization, they access a database by first connecting to the DBEnvironment in which the database resides. To connect, you use a CONNECT statement, as in the following example:

 

   isql=> CONNECT TO 'PartsDBE';

Following a successful CONNECT, ALLBASE/SQL establishes a DBE session for the user, which allows SQLCore to process commands.

If AUTOSTART is OFF, the DBA must start the DBEnvironment using the START DBE statement:

 

   isql=> START DBE 'PartsDBE';

Following this statement, the DBEnvironment remains available to users until the DBA issues the STOP DBE statement.

A DBEnvironment can be started in one of two user modes:

You can access a DBEnvironment interactively or through an application program.

Establishing Multiple DBEnvironment Connections

Users can access multiple DBEnvironments at the same time. Each connection is assigned a different connection name, as in the following:

 

   CONNECT TO 'PartsDBE' AS 'DBE1'

   CONNECT TO 'MusicDBE' AS 'DBE2'

The SET CONNECTION statement establishes the current DBEnvironment connection:

 

   SET CONNECTION 'DBE1'

To support the use of multiple connections, the DBA should set default and maximum user timeout values in each DBEnvironment. For additional information, see the section "Using Multiple Connections and Transactions with Timeouts" in the ALLBASE/SQL Reference Manual.

Managing ALLBASE/SQL Logs

For each DBEnvironment you create, ALLBASE/SQL automatically starts a log containing log records which reflect the DBEnvironment's activities. ALLBASE/SQL uses writeahead logging. This means that actual changes are not made to the DBEnvironment until the changes are first written to the log files as log records. Log records enable an ALLBASE/SQL DBEnvironment to roll back transactions and to recover in the event of a soft crash or a media failure.

For additional security, you can specify dual logging, which means that ALLBASE/SQL maintains two identical logs. If there is a write or read failure in one log, the other will then be used. ALLBASE/SQL has two log modes: nonarchive and archive. Nonarchive mode, the default, permits only rollback recovery. Archive mode, which you enable with the SQLUtil STOREONLINE command, permits both rollback and rollforward recovery (that is, recovery from an earlier stored version of the DBEnvironment).

 

   ADDLOG       MOVELOG

   PURGELOG     RESCUELOG

   RESTORELOG   SHOWLOG      

   STORELOG

LOG FULL Condition

Under some circumstances, the log can become full, which means that no additional transactions can be logged until log space is provided. When a LOG FULL condition arises, ALLBASE/SQL performs a special rollback operation which rolls back all transactions and issues the following error message:

Log Full. (DBERR 14046)

To avoid a LOG FULL condition, make sure there are enough log files available for all the concurrent transactions running on your system. Refer to the "Backup and Recovery" chapter for additional information about managing log files.

	NOTE: When LOG FULL arises, in most cases all transactions are rolled back. This includes transactions that have performed no updates.

Maintaining Buffers for Data and Log Pages

ALLBASE/SQL uses two kinds of buffers to hold data as it is passed between your applications and the operating system:

The DBA must decide on the appropriate number of log and data buffer pages for the system. Buffers are flushed (written to disk) only at specific times. Once they are flushed, the buffers can be used by other transactions.

ALLBASE/SQL flushes log buffers to the log file when one of the following occurs:

ALLBASE/SQL flushes pages from the data buffer to DBEFiles when one of the following occurs:

Figure 1-4 “The Relationship between Files and Buffers” shows the relationship between files and buffers.

Figure 1-4 The Relationship between Files and Buffers

As you see from the figure, data is transferred from DBEFiles on disk and loaded into the data buffer when an SQL statement requiring data is executed. When user or system data must be changed, log records are first written to the log buffer, and then DBEFile pages in the data buffer are modified. If the data buffer is full or if a checkpoint is taken, some data and log buffer pages will be flushed to disk.

Managing Transactions and Locks

Within a DBEnvironment, ALLBASE/SQL manipulates data in units of recoverable work known as transactions. A transaction is one or more SQL statements that together perform a unit of work on one or more databases in a DBEnvironment. A transaction begins with an SQL statement and ends with either a COMMIT WORK statement or a ROLLBACK WORK statement. All work done within a transaction can be made permanent (committed) or undone (rolled back).

Transactions acquire locks, which regulate concurrent access to the DBEnvironment. The DBA keeps track of the locking behavior of the DBEnvironment, monitoring the number of lock waits and deadlocks, and choosing approaches to locking and isolation levels that can minimize deadlock while obtaining the greatest system throughput. Refer to the chapter "Concurrency Control through Locks and Isolation Levels" in the ALLBASE/SQL Reference Manual for basic information about transactions and locking. Additional information is found in the ALLBASE/SQL Performance and Monitoring Guidelines.

Logging and recovery are also performed in terms of transactions. For more information, refer to the "Backup and Recovery" chapter.

Setting Parameters in the DBECon File

The DBE configuration file (DBECon file) contains startup parameters for each DBEnvironment. The DBA adjusts these parameters as needed as the DBEnvironment is developed, put into production, and modified. Some DBECon parameters are quantitative:

Others are ON/OFF:

All these are useful in tuning the performance of the DBE for your specific installation's needs.

The "DBEnvironment Configuration and Security" chapter describes the initial state of each DBECon parameter at START DBE NEW time, and the "Maintenance" chapter shows how to alter DBECon parameters using SQLUtil.

Monitoring the System Catalog

The system catalog contains information about all the objects stored in the DBEnvironment and about ongoing processes while the DBEnvironment is active. The DBA can monitor this information to determine when it is necessary to add objects, remove them, add file space, reallocate buffer space, or adjust other parameters.

The system catalog contains information about:

The "Maintenance" chapter shows how to perform many useful maintenance tasks using system catalog information. The "System Catalog" chapter describes each view and pseudotable in the system catalog with examples of its contents. Much of the information contained in the system catalog is displayed by SQLMON, the online monitoring tool. See the ALLBASE/SQL Performance and Monitoring Guidelines for more information.

Managing Nonstop Production Systems

A collection of ALLBASE/SQL features can be used to help keep systems available with as few stops as possible. These features also help users who have large databases servicing a large number of concurrent sessions and requiring lengthy backup and recovery times. Users who experience a large amount of Online Transaction Processing (OLTP) and have their systems run for significant periods without an operator present will also find these features useful for performing tasks when operator time permits.

These features are implemented through SQLUtil commands and ALLBASE/SQL statements. The commands and statements comprise the tasks of database creation, maintenance, and recovery.

The SQLUtil features are implemented in the following commands:

The syntax for the SQLUtil commands is found in Appendix F of this manual.

The following ALLBASE/SQL statements can be used interactively or programmatically:

The full syntax for the SQL statements is found in the "SQL Statements" chapter of the ALLBASE/SQL Reference Manual.

Backing Up and Restoring DBEnvironments

The DBA is also responsible for routine backup and, when necessary, the restoring of DBEnvironments following a system failure. This means:

The subject is discussed fully in the chapter "Backup and Recovery."

Migrating DBEnvironments Between Releases of ALLBASE/SQL

The internal structure of a DBEnvironment must be compatible with the particular release of ALLBASE/SQL software being used. After installing a new version of ALLBASE/SQL, use SQLMigrate to migrate a DBEnvironment forward to the current release or backward to an earlier release. Under normal conditions, you would not need to perform a backward migration. This functionality is provided so that if you ever choose to restore older software, you will be able to migrate your DBEnvironment backward quickly and easily.

Prior to release A.20.00 of ALLBASE/SQL, it was sometimes necessary to use the ISQL UNLOAD command, recreate the DBEnvironment, and use the LOAD command to migrate your DBEnvironment to one that was compatible with a new release. This approach may still be useful. The process of unloading and reloading is described in the "Maintenance" chapter. Additional information is provided about SQLMigrate later in this chapter under "Using SQLMigrate."