Tasks for the DBA [ ALLBASE/SQL Database Administration Guide ] MPE/iX 5.5 Documentation
ALLBASE/SQL Database Administration Guide
Tasks for the DBA
The database administrator is responsible for the overall operation of
DBEnvironments. This includes:
* 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:
* DBEFiles for storage of data and index pages
* a DBEFile for system information
* a DBECon file containing startup parameters for the DBE
* log files.
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 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 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 , 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:
* A DBECon file. This file contains information about the
DBEnvironment's configuration, such as the size of various buffers
and other startup parameters. The name of the DBECon file is the
same as the name of the DBEnvironment.
* Log files. A log file contains a record of DBEnvironment changes.
ALLBASE/SQL uses log files to undo (roll back) or redo (roll
forward) changes made in the DBEnvironment. You can add
additional log files as needed. In the case of dual logging, two
sets of log files are maintained.
* A system catalog. The system catalog is a collection of tables
and views that contain data describing DBEnvironment structure and
activity. The parts of the system catalog necessary for
DBEnvironment startup reside in a DBEFile known as DBEFile0. All
system catalog DBEFiles are associated with a DBEFileSet called
SYSTEM.
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:
* In single-user mode, only one user or program can access a
DBEnvironment at a time. Single-user mode is employed by the DBA
to perform maintenance and restructuring tasks.
* In multiuser mode, more than one user and/or program can access a
DBEnvironment at a time. Multiuser mode is for production, in
which the DBA's responsibility is to maintain the DBEnvironment
for the multiple users that access it.
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).
Defining Logs.
You choose single or dual logging initially in the START DBE NEW
statement. You also determine the size and location of initial logs
using the LOG clause of this statement:
LOG DBEFILE DBELog1ID [AND DBELog2ID] WITH PAGES = DBELogSize, NAME =
'SystemFileName1' [AND 'SystemFileName2']
You can define a new log with the START DBE NEWLOG statement. This lets
you change the log file name and size, turn archive mode on or off, and
change from single to dual logging and back.
Detailed information about START DBE NEW and START DBE NEWLOG appears in
the "DBEnvironment Configuration and Security" chapter. The syntax of
both statements appears in the "SQL Statements" chapter of the
ALLBASE/SQL Reference Manual.
The DBA must also manage the size, number, and location of all
ALLBASE/SQL logs. This is done through using the SQLUtil log commands:
ADDLOG MOVELOG
PURGELOG RESCUELOG
RESTORELOG SHOWLOG
STORELOG
SQLUtil commands are fully explained in the "SQLUtil" appendix. Detailed
information about managing logs appears in the "Backup and Recovery"
chapter.
Logs and Recovery.
After a system failure (other than a media failure), all the data within
a DBEnvironment is automatically recovered to a consistent state the next
time the DBEnvironment is started. Changes performed by any transactions
that were incomplete at failure time are rolled back. Changes performed
by transactions that were complete at failure time are written to the
data files on disk from the log. In the case of a media failure, you
must initiate a manual recovery of the DBEnvironment from backups. This
process is described fully in the "Backup and Recovery" chapter.
Rollback recovery is an automatic feature of both nonarchive and archive
log modes. Rollback recovery has two purposes:
* to let users roll back the effects of a transaction with the
ROLLBACK WORK statement
* to let ALLBASE/SQL automatically roll back the DBEnvironment to a
consistent state after a soft crash, and whenever the START DBE
statement is executed.
A soft crash is a program abort or a system failure that does not damage
the storage media. ALLBASE/SQL always does rollback recovery when a
DBEnvironment starts up, and this ensures that whether or not there was a
crash, all complete transactions are made permanent to disk, and all
incomplete transactions are undone (rolled back).
Rollforward recovery is possible only with archive log mode. It allows
you to reconstruct a DBEnvironment from a backup copy and one or more
stored archive log files in the event of a hard crash. A hard crash is a
failure, such as a disk head crash, that damages files on disk. Complete
details about rollforward recovery from archive log files is presented in
the "Backup and Recovery" chapter.
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 log buffer, which holds log records that reflect changes made
to data pages by active transactions.
* the data buffer, which holds DBEFile pages from tables and indexes
currently being accessed.
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:
* a COMMIT WORK ends a transaction that modified the DBEnvironment.
* the data buffer is full, so changes to the DBEnvironment must be
written to disk to free data buffer space. Log buffers must also
be flushed because of writeahead logging.
* the log buffer is full, so changes to the DBEnvironment must be
written to disk to free log buffer space.
* when a checkpoint is taken by means of a user's CHECKPOINT
statement or by ALLBASE/SQL internally. The checkpoint writes a
system checkpoint record to the log, and it flushes the log buffer
as well as the data buffer.
ALLBASE/SQL flushes pages from the data buffer to DBEFiles when one of
the following occurs:
* the data buffer is full, so individual changed pages are written
to disk to free data buffer space.
* when a checkpoint is taken by means of a user's CHECKPOINT
statement or by ALLBASE/SQL internally. The checkpoint writes a
system checkpoint record to the log, and it flushes the log buffer
as well as the data buffer.
Figure 1-4 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:
* Run time control blocks
* Maximum transactions
* Number of log and data buffer pages
* Timeout values
Others are ON/OFF:
* DDL Enabled
* Autostart mode
* Single or multi startup
* Archive mode
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:
* What tables, views, and indexes exist.
* How much DBEFile space is available.
* The size of rows and columns in tables.
* The cluster count of indexes.
* Which transactions are waiting for locks to be released.
* The names of users on the system and their session ids.
* View and table definitions.
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:
STORE or STOREONLINE PARTIAL Stores parts of a DBEnvironment
(DBEFiles or DBEFileSets).
STOREINFO Displays DBEFile information stored on
a backup device.
RESTORE PARTIAL Restores a set of DBEFiles.
SETUPRECOVERY PARTIAL Rolls forward a set of DBEFiles.
ATTACHFILE or DETACHFILE Attaches or detaches a DBEFile or
DBEFileSet.
CHANGELOG Causes a DBEnvironment to change to a
new log file.
SHOWDBE Displays database attribute
information including audit
DBEnvironment parameters and whether
it is a wrapper database.
MOVEFILE Moves a DBEFile.
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:
CHECKPOINT statement Retrieves the number of free blocks available
in a log file.
CREATE DBEFILE statement Creates a DBEFile in a particular group or on a
particular volume.
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:
* Choosing either archive or nonarchive logging.
* Implementing a backup strategy.
* Backing up DBEnvironments and log files regularly.
* Adding and dropping log files as needed.
* Restoring the system from backups as needed.
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."
MPE/iX 5.5 Documentation