Tools for the DBA [ ALLBASE/SQL Database Administration Guide ] MPE/iX 5.5 Documentation
ALLBASE/SQL Database Administration Guide
Tools for the DBA
In addition to the general set of SQL statements, the DBA uses several
utility programs in creating and maintaining DBEnvironments. These are:
* ISQL
* SQLUtil
* SQLGEN
* SQLMigrate
* SQLMON
* SQLVer
* SQLAudit
The use of each toolset is described in the following paragraphs.
Complete command syntax for ISQL, SQLUtil, SQLGEN, SQLMigrate, and
SQLAudit is in the appendices.[REV BEG] In addition, there is a
discussion of SQLINSTL. SQLINSTL is a script used when moving to a new
version of ALLBASE/SQL.[REV END]
Using ISQL
ISQL lets you issue most SQL commands interactively. In addition, it
lets you load and unload tables using the LOAD and UNLOAD statements.
ISQL also includes a help facility which explains the syntax of SQL and
ISQL commands.
ISQL command files offer a shortcut to creating databases. Command files
allow you to store a series of ISQL and SQL commands and then, with a
single START command, execute all the commands in that file. This is
very useful for groups of commands you execute together frequently. In
addition, if an entire DBEnvironment is created by using command files,
it is easy to recreate the DBEnvironment, as well as examine or modify
any part of its definition. The sample DBEnvironment used in all the
examples in this manual can actually be created with the series of
command files found in the "Sample DBEnvironment" appendix in the
ALLBASE/SQL Reference Manual.
SQL data definition statements such as CREATE and maintenance commands
such as UPDATE STATISTICS obtain locks on the system catalog. To avoid
contention, you can use command files to execute these statements on a
DBEnvironment during off hours. This will reduce the amount of waiting
on locks during peak working hours.
You can use SQLGEN to create command files for use through ISQL. See the
section "Using SQLGEN" later in this chapter. For complete information
on ISQL command files, refer to the ISQL Reference Manual for ALLBASE/SQL
and IMAGE/SQL.
Using SQLUtil
SQLUtil is an ALLBASE/SQL utility program that lets you manage the
startup parameters for each DBEnvironment and perform several other
maintenance tasks. With SQLUtil, you can:
* Display and change startup parameters in the DBECon file.
* Move DBEFiles or log files from one location to another.
* Purge DBEFiles.
* Purge the DBEnvironment.
* Back up and restore a DBEnvironment.
* Monitor the log.
* Add, purge, store, and restore log files.
* Perform rollforward recovery.
Starting SQLUtil.
There are two ways to run the SQLUtil program: directly from MPE/iX, or
within ISQL. For the first method, type the following command:
: RUN SQLUTIL.PUB.SYS
To execute SQLUtil within ISQL, type:
isql=> sqlutil;
No matter which method you use, you will see the SQLUtil banner and
prompt (>>).
The SQLUtil commands are described in the appendix, "SQLUtil." Now you
can execute SQLUtil commands until you enter an EXIT or QUIT command.
SQLUtil requires the use of the message catalog, SQLCT000.PUB.SYS. If
you are using native language data, the name of the catalog is
SQLCTxxx.PUB.SYS, where xxx is the number of your current language. See
"Native Language Support" for information about specifying a native
language as the current language. SQLUtil accepts responses to prompts
for a DBEnvironment name or for a filename in either the current language
or NATIVE-3000.
Instead of entering commands at the keyboard, you may redirect input from
a command file by specifying the STDIN option in the run string, as in
the following example:
: RUN SQLUTIL.PUB.SYS;STDIN=Commands.SomeGrp.SomeAcct
As with ISQL, you can use a semicolon to terminate an SQLUtil command.
Semicolons are not needed, however. All responses to commands must be
contained on one line; continuation of responses is not supported.
Some SQLUtil commands display a subsidiary prompt ("->"). From this
prompt, you can enter // to return to the SQLUtil prompt (">>"). Also,
if input from within the ISQL session was coming from a command file or
the command buffer when SQLUtil was invoked, that input is suspended
during the SQLUtil session until the user enters either EXIT or QUIT.
Finally, prompting-mode is the only mode of operation in SQLUtil. You
cannot specify a complete command line as you would in ISQL.
JCWs Set by SQLUtil.
When running SQLUtil in batch mode, your job can check the following JCWs
to ensure that SQLUtil completed successfully.
Table 1-1. JCWs Set by SQLUtil
--------------------------------------------------------------
| | | |
| JCW Name | Contents | Range |
| | | |
--------------------------------------------------------------
| | | |
| UTILERR | number of SQL errors | 0 - 32767 |
| | encountered | |
| | | |
--------------------------------------------------------------
| | | |
| UTILWARN | number of SQL warnings | 0 - 32767 |
| | encountered | |
| | | |
--------------------------------------------------------------
| | | |
| JCW | FATAL if SQL error | FATAL or 0 |
| | encountered, | |
| | 0 if SQL error not | |
| | encountered. The system JCW | |
| | is set only if the SET | |
| | EXIT_ON_DBERR command is ON. | |
| | | |
--------------------------------------------------------------
Using SQLGEN
SQLGEN is a utility that generates the commands used to recreate all or
part of an existing ALLBASE/SQL DBEnvironment. It also generates
LOAD/UNLOAD and UPDATE STATISTICS commands. These commands are placed in
one or more data files called schema files which can then be used as ISQL
command files to recreate the DBEnvironment.
SQLGEN has several uses. It aids in migrating to new releases of
ALLBASE/SQL when unloading or reloading are necessary. In addition,
SQLGEN helps with other database tasks. DBEnvironments can be designed
and tuned on a development system and then the entire schema can be
transferred to a production system. SQLGEN can also help move part of a
DBEnvironment. For example, if a department transfers from one site to
another, that department's portion of a DBEnvironment can be easily moved
to a new system.
You can also use SQLGEN to create a replicate DBEnvironment schema from
the master DBEnvironment. When you do so, you must use the START DBE
NEWLOG statement to specify the AUDIT LOG, AUDIT NAME, DEFAULT PARTITION,
MAXPARTITIONS, and AUDIT ELEMENTS parameters. You must also use CREATE
PARTITIONS to create partitions and ALTER TABLE to assign tables to the
partitions.
Starting SQLGEN.
You must have DBA authority to use SQLGEN. Enter commands at the keyboard
or redirect the input by specifying a command file name in the run
string. To run SQLGEN directly from the operating system, type:
: RUN SQLGEN.PUB.SYS
To run SQLGEN from within ISQL, type:
isql => sqlgen;
No matter which method you use, the SQLGEN prompt (>>) appears, ready for
you to enter commands.
SQLGEN requires the use of the message catalog, SQLCT000.PUB.SYS. If
you are using native language data, the name of the catalog is
SQLCTxxx.PUB.SYS, where xxx is the number of your current language. See
"Native Language Support" for information about specifying a native
language as the current language.
Usually, STARTDBE will be the first command you enter. This command
connects to the DBEnvironment and checks to make sure you have DBA
authority. It is recommended that the DBEnvironment be shut down prior
to issuing the STARTDBE command to ensure that system catalogs are not
being altered during SQLGEN execution. When you finish entering
commands, use the EXIT command to return either to MPE/iX or to ISQL.
When you run SQLGEN directly from MPE/iX, you can specify the editor name
as an INFO string. When you run SQLGEN from ISQL, the current editor
name is passed to and displayed by SQLGEN. Refer to the SET EDITOR
command for more information.
SQLGEN Commands.
SQLGEN commands can be divided into two categories, generate commands and
auxiliary commands. Generate commands such as GENERATE ALL build the SQL
statements necessary to recreate all or part of a DBEnvironment.
Auxiliary commands provide services needed to use SQLGEN. For example,
the STARTDBE command connects to the DBEnvironment. The EDITOR command
allows you to access an editor from within SQLGEN. With SET commands you
redefine the editor or designate a schema file name. HELP, RELEASE, and
EXIT are also auxiliary commands. SQLGEN also allows you to enter MPE/iX
commands when they are preceded by the character ':'.
The SQLGEN commands are described in the appendix, "SQLGEN."
SQLGEN Schema Files.
SQLGEN places the statements it generates in schema files which are then
used as ISQL command files for recreating the DBEnvironment. All
GENERATE commands prompt for a schema file name unless you have already
designated the name with the SET SCHEMA command. If you enter a carriage
return instead of a name at the schema file name prompt, the output is
displayed on your terminal screen. In most cases, GENERATE commands
automatically include a COMMIT WORK at the end of the schema file.
However, if the generate command was not able to build any commands (for
example, GENERATE GROUP cannot build commands if no groups exist in the
DBEnvironment), then no COMMIT WORK is generated.
When a GENERATE command encounters an unexpected ALLBASE/SQL error,
SQLGEN automatically returns the schema file in use to the the state it
was in before the command began. For example, if an error occurs during
a GENERATE ALL command, any commands that have already been placed in the
schema file are automatically rolled back. To execute the command,
correct the problem and re-enter GENERATE ALL.
SQLGEN Conventions.
To use SQLGEN effectively you must be familiar with the conventions it
uses. The SQLGEN prompt is >>. Unlike ISQL, SQLGEN does not require a
command-terminating semicolon. Once you enter a command, SQLGEN prompts
for your responses. At an object name prompt, enter the name of the
object for which you wish to generate commands. When you enter an object
name, SQLGEN prompts you for another. When you finish entering object
names, a carriage return returns you to the SQLGEN prompt. If you wish
to select all qualifying names, enter an '@' at the object name prompt.
To see a list of all qualifying names, type a '?'. If no objects
qualify, SQLGEN displays a message and automatically returns you to the
previous prompt.
When choosing from two options, (y/n) for example, the first choice
listed is always the default (carriage return). To return to the SQLGEN
prompt from any command, type '//' or '/'. To access the operating
system, enter a colon (:) and a carriage return. Type RESUME to return
to SQLGEN. To enter system commands from within SQLGEN, type a colon (:)
as the first character of the command. Only commands allowed in break
can be executed. Table 1-2 summarizes SQLGEN's general conventions.
Table 1-2. SQLGEN General Conventions
-----------------------------------------------------------------------------------------------
| | | |
| Convention | Explanation | Example |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| No Semicolons | Command terminating semicolons are | EXIT or EXIT; |
| | not needed, but allowed. | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| :<Command> to | Use any command allowed in break. | >> :LISTF Return |
| enter system | | |
| commands | | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| : to escape to | Use any command allowed in break | >> : Return |
| MPE | Type resume to return to SQLGEN. | : resume Return |
| | | >> |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| '//' OR '/' to | Single or double slashes end a | Table Name >> // Return |
| return to the | command. | >> |
| SQLGEN prompt | | |
| | | |
-----------------------------------------------------------------------------------------------
Entering Object Names.
SQLGEN automatically upshifts all object names not entered in quotes. To
enter an object name, in most cases all you need to do is type the name.
SQL pattern matching symbols (% and _) can be used. For example, at a
table name prompt, P% selects all tables starting with P.
Single and double quotation marks are used for special cases.
* Use single quotes around lower case names. Pattern matching
symbols are allowed within the single quotes. For example, to
select all objects starting with Na, enter 'Na%'.
* Use double quotes around object names that contain one of the
pattern matching symbols (% or _). In this case, pattern matching
cannot be used. For example, the table name NJS% must be entered
"NJS%".
Table 1-3 summarizes SQLGEN's naming conventions.
Table 1-3. SQLGEN Name Entry Conventions
-----------------------------------------------------------------------------------------------
| | | |
| Convention | Explanation | Example |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| Pattern Matching | % replaces 0 or more | P% all objects starting with the |
| Using % and _ | characters. | character P |
| | | |
| | _ replaces one character. | P_ all two-letter names starting with |
| | | P |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| No Quotes | Use in most cases. Name is | PurchDB PURCHDB |
| | upshifted. Pattern matching | |
| | allowed. | P% all names starting with P |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| Single Quotes | Use around lower case names. | 'tab' tab |
| | Names are not upshifted. | |
| | Pattern matching allowed. | 'T%s' all names starting with T and |
| | | ending with s |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| Double Quotes | Use when the name itself | "t%s" t%s |
| | contains a % or_. Names are | |
| | taken exactly as entered. No | |
| | pattern matching allowed. | |
| | | |
-----------------------------------------------------------------------------------------------
JCWs Set by SQLGEN.
When running SQLGEN in batch mode, your job can check the following JCWs
to ensure that SQLGEN completed successfully.
Table 1-4. JCWs Set by SQLGEN
--------------------------------------------------------------
| | | |
| JCW Name | Contents | Range |
| | | |
--------------------------------------------------------------
| | | |
| GENERR | number of SQL errors | 0 - 32767 |
| | encountered | |
| | | |
--------------------------------------------------------------
| | | |
| GENWARN | number of SQL warnings | 0 - 32767 |
| | encountered | |
| | | |
--------------------------------------------------------------
| | | |
| JCW | FATAL if SQL error | FATAL or 0 |
| | encountered, | |
| | 0 if SQL error not | |
| | encountered. The system JCW | |
| | is set only if the SET | |
| | EXIT_ON_DBERR command is ON. | |
| | | |
--------------------------------------------------------------
Using SQLMigrate
[REV BEG]
If you are updating from an earlier release of ALLBASE/SQL, you must
perform the ALLBASE/SQL migration to migrate your DBEnvironment. The
method used depends upon the version of ALLBASE/SQL that you are
currently using. Use SQLMigrate to convert the DBEnvironment between
major releases such as from F.0 to G.0. Use the SQLINSTL script to
migrate between versions of a release (such as from G1.14 to G1.15) or
minor releases (such as from G.0 to G.1).
The SQLINSTL script is provided in the G.1 and later versions of
ALLBASE/SQL to make it easy for the DBA to move to a delta release.
Using SQLINSTL ensures that you will have access to the most recent
version of the System and Catalog views, and it also uses VALIDATE FORCE
statements to revalidate all stored sections to be compatible with the
new release.
If SQLINSTL is not executed on a DBEnvironment after installing a new
version, stored sections may not be properly revalidated causing run-time
errors. Revalidating stored sections at run-time during production hours
can also cause concurrency problems due to write locks placed on the
system catalog. You must use SQLINSTL whenever a new version of
ALLBASE/SQL is installed; however, SQLINSTL does not need to be executed
separately if SQLMigrate is being executed to migrate between major
releases. Refer to the section, "Using SQLINSTL," later in this chapter
for more information.[REV END]
[REV BEG]
NOTE ALLBASE/SQL no longer supports pseudo-mapped files. You must
convert all pseudo-mapped DBEFiles back to mapped DBEFiles before
you upgrade.
Use the SQLUtil SHOWACCESS command to list DBEFiles. If any are listed
as pseudo-mapped, use the SQLUtil MOVEFILE command to convert the
pseudo-mapped files to mapped before you upgrade ALLBASE/SQL. Refer to
the commands in the appendix, "SQLUtil."[REV END]
WARNING SQLINSTL and SQLMigrate drop all system catalog views. If any
view has been created upon a system catalog view, that view will
also be dropped. To avoid dropping views, before executing
SQLINSTL or SQLMigrate, use the GENERATE VIEWS command in SQLGEN
to create a script to recreate the user view.
Running SQLMigrate.
Use SQLMigrate to migrate between major revisions of ALLBASE/SQL. Backup
the DBEnvironment prior to running SQLMigrate.
You must be the DBECreator or have SM capability to migrate a
DBEnvironment.
To run SQLMigrate, use the following command:
:RUN SQLMIG.PUB.SYS
You will see the SQLMigrate banner and prompt (SQLMIGRATE=>). Command
entry follows ISQL rules; that is, commands are terminated with a
semicolon. The SQLMigrate commands are described in the appendix
"SQLMigrate."
Forward and backward migration steps are discussed below. Only one
DBEnvironment can be migrated at a time.
Types of Forward Migration.
There are two flavors of forward migration to consider:
1. Migrating a DBEnvironment without audit logging into audit logging
releases.
2. Migrating a DBEnvironment with audit logging into audit logging
releases.
For both cases, all tables are placed in the default partition. In the
first case, no audit logging is enabled for the DBEnvironment. In the
second case, the only audit logging element allowed is DATA because
SQLMigrate does not support any other audit elements.
If you migrate an audit logging DBEnvironment, it should not be migrated
to a non-audit logging DBEnvironment. Therefore, you must specify AUDIT
LOG in any START DBE NEWLOG statement that SQLMigrate performs.
For backward migration, since previous releases do not support
partitions, the only supported option for a DBEnvironment with audit
logging in use is to place all the tables in the default partition with
the only audit logging element specified being DATA. Then the
DBEnvironment can be migrated backward unchanged.
If the audit elements include something other than DATA, one of the
following may happen:
* The migration is not allowed to proceed.
* The elements are reset to be only DATA.
Steps for Forward Migration.
1. Prior to updating the operating system and ALLBASE/SQL software,
do the following for each DBEnvironment that will be migrated:
a. Run ISQL and issue a START DBE statement. This ensures
that the DBEnvironment is logically consistent.
b. Run SQLUtil and issue the STORE command to backup each
DBEnvironment. Note: Log files are not stored using this
command. Application programs associated with the
DBEnvironment must be backed up separately.
2. Backup the ALLBASE/SQL software.
3. Update the operating system and the ALLBASE/SQL software. If you
are updating the operating system, make sure you have a backup of
the operating system (including the old release of ALLBASE/SQL
software).
4. Type the following command at the operating system prompt:
:RUN SQLMIG.PUB.SYS
5. Issue the SHOW VERSIONS command to determine the possible version
values that can be entered as the version parameter in both the
PREVIEW and the MIGRATE commands. The version parameter indicates
to SQLMigrate the release of ALLBASE/SQL software with which you
wish the DBEnvironment to be compatible.
For example, you can enter the following command:
SQLMIGRATE=> show versions;
VERSION RELEASE
E HP36216-02A.E
F HP36216-02A.F
G HP36216-02A.G
SQLMIGRATE=>
This shows the MPE/iX ALLBASE/SQL versions and releases for which
you can use SQLMigrate.
6. For each DBEnvironment that will be migrated, issue the PREVIEW
command to check for errors that might occur during migration.
The syntax for PREVIEW of a forward migration is:
SQLMIGRATE=> PREVIEW 'DBEnvironmentName' FORWARD [TO 'Version'];
Note that the version parameter is optional. If this parameter is
omitted, the most recent version supported by SQLMigrate will be
used as the default.
If you receive the message that there is not enough space in the
SYSTEM DBEFileSet to complete the migration successfully, the
number of DBEFile pages needed will be returned. Use the
following commands to create a new DBEFile and add it to the
SYSTEM DBEFileSet:
SQLMIGRATE=> CREATE DBEFILE DBEFileName
WITH PAGES = DBEFileSize, NAME ='SystemFileName';
SQLMIGRATE=> ADD DBEFILE DBEFileName TO DBEFILESET SYSTEM;
Note that the syntax of these commands is the same as in ISQL.
Repeat this step until no errors are encountered and SQLMigrate
returns the message:
The proposed migration should be successful.
If you encounter errors during the PREVIEW step that you do not
understand, contact your HP Service Representative or Response
Center.
7. Once you have completed the PREVIEW step, issue the MIGRATE
command to modify the DBEnvironment so that it is compatible with
the release of ALLBASE/SQL that you wish to use. The MIGRATE
FORWARD syntax is:
SQLMIGRATE=> MIGRATE 'DBEnvironmentName' FORWARD [TO 'Version'];
Note that the version parameter is optional. If it is omitted,
the most recent version supported by SQLMigrate will be used as
the default.
When the MIGRATE command is finished, SQLMigrate automatically
purges the old user log files and performs a START DBE NEWLOG
statement to create new user log files. The options of the START
DBE NEWLOG statement match the startup parameters contained in the
DBECon file. SQLMigrate does not issue a START DBE NEWLOG
statement if the NEWLOG option has been set to OFF.
If the START DBE NEWLOG statement fails you must exit from
SQLMigrate, run ISQL, and issue a START DBE NEWLOG statement.
This creates a new log file that will be compatible with the
target release of ALLBASE/SQL. Note that you cannot use your
DBEnvironment after it has been migrated until the START DBE
NEWLOG statement executes successfully.
If you encounter errors during the MIGRATE step that you do not
understand, contact your HP Service Representative or Response
Center.
8. Make a backup of the migrated DBEnvironment immediately after the
START DBE NEWLOG statement completes. If you wish to use
nonarchive logging, run SQLUtil and use the STORE command.[REV
BEG] For archive logging, you should choose one of the following:
a. If you have TurboSTORE software, do a concurrent backup
using the SQLUtil STOREONLINE command.
b. If you do not have TurboSTORE, issue the following SQL
statements in single user mode in ISQL:
isql=> BEGIN ARCHIVE;
isql=> COMMIT ARCHIVE;
Exit from ISQL and run SQLUtil, then issue the STORE
command.
[REV END]
9. Run SQLUtil (if you are not already in SQLUtil from the previous
step) and issue the SHOWDBE command to check the parameters of the
new version of the DBEnvironment. Use the ALTDBE command if
changes are necessary. Use the SHOWLOG command to display current
log information.
10. You may have received a message stating that stored sections were
invalidated. This is to be expected. Stored sections will be
revalidated automatically when they are executed. For some users,
production may be faster if revalidation is done before that time.
Revalidation can be accomplished by preprocessing the application
programs that contain the stored sections. If you are migrating
from release F (or later), you can revalidate stored sections with
the VALIDATE statement in ISQL.
Steps for Backward Migration.
1. Run SQLMigrate and issue the SHOW VERSIONS command to determine
the possible values that can be entered as the version parameter
in both the PREVIEW and the MIGRATE commands. The version
parameter indicates to SQLMigrate the release of ALLBASE/SQL
software with which you wish the DBEnvironment to be compatible.
2. Prior to restoring the backup version of the operating system and
ALLBASE/SQL, do the following for each DBEnvironment that will be
migrated:
a. Run ISQL and issue a START DBE statement. This ensures
that the DBEnvironment is logically consistent.
b. Run SQLUtil and issue the STORE command to make a backup of
the DBEnvironment. Note: Log files are not stored using
this command. Application programs associated with the
DBEnvironment must be backed up separately.
c. Run SQLMigrate and issue the PREVIEW command to check for
errors that might occur during migration. The
DBEnvironment is not modified during this command. The
syntax for the PREVIEW of a backward migration is:
SQLMIGRATE=> PREVIEW 'DBEnvironmentName' BACKWARD TO 'Version';
If you receive the message that there is not enough space
in the SYSTEM DBEFileSet to complete the migration
successfully, the number of DBEFile pages needed will be
returned. To increase the number of pages, you need to add
another DBEFile to the SYSTEM DBEFileSet. To do this, use
the following syntax:
SQLMIGRATE=> CREATE DBEFILE DBEFileName
WITH PAGES = DBEFileSize, NAME ='SystemFileName';
SQLMIGRATE=> ADD DBEFILE DBEFileName TO DBEFILESET SYSTEM;
Repeat this step until no errors are encountered and
SQLMigrate returns the message:
The proposed migration should be successful.
If you encounter errors during the PREVIEW step that you do
not understand, contact your HP Service Representative or
Response Center.
d. Run SQLMigrate and issue the MIGRATE command to modify your
DBEnvironment to make it compatible with the old release of
ALLBASE/SQL. The MIGRATE BACKWARD syntax is:
SQLMIGRATE=> MIGRATE 'DBEnvironmentName' BACKWARD TO 'Version';
If you encounter errors during the MIGRATE step that you do
not understand, contact your HP Service Representative or
Response Center.
3. Restore the backup versions of the operating system and of
ALLBASE/SQL that were made during the forward migration steps.
4. For each DBEnvironment that was backward migrated, do the
following:
a. Run ISQL and issue the START DBE NEWLOG statement. This
creates a new log file that will be compatible with the old
release of ALLBASE/SQL. Note that you will not be able to
use your DBEnvironment after it has been migrated until
this step has been completed. SQLMigrate does not
automatically perform a START DBE NEWLOG statement during
backward migration.
b. Run SQLUtil and issue the SHOWDBE command to check the
parameters of the new version of the DBEnvironment. Use
the ALTDBE command if changes are necessary.[REV BEG]
To enable archive mode logging, use the following steps:
i. If you have TurboSTORE software, run SQLUtil and use
the STOREONLINE command.
ii. If you do not have TurboSTORE, issue the following
SQL statements from ISQL:
isql=> BEGIN ARCHIVE;
isql=> COMMIT ARCHIVE;
Exit from ISQL and run SQLUtil, then issue the STORE
command.
[REV END]
c. Drop views and any stored sections created under the later
release, because these views and sections are no longer
usable. Refer to the DROP VIEW and DROP MODULE commands in
the ALLBASE/SQL Reference Manual. Do not try to install
modules that were created under the release you are
migrating back from as they will not be compatible with the
release of ALLBASE/SQL you are migrating to.
d. You may have received a message stating that stored
sections were invalidated. This is to be expected. Stored
sections will be revalidated automatically when they are
executed. For some customers, production may be faster if
revalidation is done before that time. Revalidation can be
accomplished by preprocessing the application programs that
contain the stored sections. If you are migrating from
release F (or later), you can revalidate stored sections
with the VALIDATE statement in ISQL.
JCWs Set by SQLMigrate.
When running SQLMigrate in batch mode, your job can check the following
JCWs to ensure that SQLMigrate completed successfully.
Table 1-5. JCWs Set by SQLMigrate
--------------------------------------------------------------
| | | |
| JCW Name | Contents | Range |
| | | |
--------------------------------------------------------------
| | | |
| MIGERR | number of SQL errors | 0 - 32767 |
| | encountered | |
| | | |
--------------------------------------------------------------
| | | |
| MIGWARN | number of SQL warnings | 0 - 32767 |
| | encountered | |
| | | |
--------------------------------------------------------------
| | | |
| JCW | FATAL if SQL error | FATAL or 0 |
| | encountered, | |
| | 0 if SQL error not | |
| | encountered. The system JCW | |
| | is set only if the SET | |
| | EXIT_ON_DBERR command is ON. | |
| | | |
--------------------------------------------------------------
[REV BEG]
Using SQLINSTL
SQLINSTL is a script that you use instead of SQLMigrate when migrating
between releases of versions of ALLBASE/SQL (such as G1.14 to G1.15) or
minor releases (such as G.0 to G.1). When migrating between major
releases (such as F.0 to G.0 or F.0 to G.1), you must instead use
SQLMigrate to migrate your DBEnvironment. Refer to the previous section,
"Using SQLMigrate," for more information.
When using SQLINSTL, execute SQLINSTL on each DBEnvironment on the system
to ensure that you have access to the most recent version of the system
catalog views. SQLINSTL also ensures that stored sections are properly
revalidated to be compatible with the new release. If SQLINSTL is not
executed, errors may result when stored sections are executed due to
compatibility problems.
WARNING SQLINSTL and SQLMigrate drop all system catalog views. If any
view has been created upon a system catalog view, that view will
also be dropped. To avoid dropping views, before executing
SQLINSTL, use the GENERATE VIEWS command in SQLGEN to create a
script to recreate the user view.
The following is an example of using SQLINSTL from ISQL:
:isql.pub.sys
isql=>start sqlinstl.pub.sys (mydbe);
isql=>exit;
Read the SQLINSTL file on your system for more information.
If you are using ARCHIVE MODE LOGGING, you must make a backup of the
DBEnvironment after using SQLINSTL. This backup must be used if
rollforward recovery is to be performed at some time in the future.[REV
END]
Using SQLMON
SQLMON is an online diagnostic tool that monitors the activity of an
ALLBASE/SQL DBEnvironment. SQLMON screens provide information on file
capacity, locking, I/O, logging, tables, and indexes. They summarize
activity for the entire DBEnvironment, or focus on individual sessions,
programs, or database components. SQLMON is a read only utility, and
cannot modify any aspect of the DBEnvironment. SQLMON is documented
fully in the ALLBASE/SQL Performance and Monitoring Guidelines.
[REV BEG]
To run SQLMON, log on as system manager, as the database administrator,
or a user that was granted MONITOR authority, and issue the following
command:
: SQLMON
[REV END]
Using SQLVer
SQLVer allows you to check the version strings of the ALLBASE/SQL files.
The syntax of SQLVer is as follows:
SQLVER [file]
The file parameter specifies the fully qualified name of the ALLBASE/SQL
file whose version you want to check. If the file parameter is omitted,
SQLVer verifies all of the ALLBASE/SQL files and summarizes the
following:
* current ALLBASE/SQL version
* number of missing files
* number of version incompatibilities
SQLVer sets the SQLVERERR JCW to the following values:
Table 1-6. SQLVERERR JCW
---------------------------------------------------------------------------------------------
| | |
| SQLVERERR Value | Explanation |
| | |
---------------------------------------------------------------------------------------------
| | |
| FATAL | SQLVer internal error |
| | |
---------------------------------------------------------------------------------------------
| | |
| 0 | no version incompatibilities or missing |
| | files detected |
| | |
---------------------------------------------------------------------------------------------
| | |
| 1 | missing files detected |
| | |
---------------------------------------------------------------------------------------------
| | |
| 2 | version incompatibilities detected |
| | |
---------------------------------------------------------------------------------------------
| | |
| 3 | version incompatibilities and missing files |
| | detected |
| | |
---------------------------------------------------------------------------------------------
In the example that follows, the version strings of all the ALLBASE/SQL
files are checked:
MPE:sqlver
ALLBASE/SQL G1 Version Checker
WED, FEB 5, 1997 2:42 PM
ALLBASE/SQL:
-----------
dbmon.pub.sys (created THU, OCT 17, 1996):
@(#)HPDBCORE A.G1.18 09/24/96 ALLBASE/SQL
hpsqludc.pub.sys (created THU, OCT 17, 1996):
isql.pub.sys (created THU, OCT 17, 1996):
HP36216-02A.G1.18 Interactive SQL/3000 ALLBASE/SQL
psqlc.pub.sys (created THU, OCT 17, 1996):
HP36216-02A.G1.18 C Preprocessor/3000 ALLBASE/SQL
psqlcob.pub.sys (created THU, OCT 17, 1996):
HP36216-02A.G1.18 COBOL Preprocessor/3000 ALLBASE/SQL
psqlfor.pub.sys (created THU, OCT 17, 1996):
HP36216-02A.G1.18 FORTRAN Preprocessor/3000 ALLBASE/SQL
psqlpas.pub.sys (created THU, OCT 17, 1996):
HP36216-02A.G1.18 PASCAL Preprocessor/3000 ALLBASE/SQL
sqlct000.pub.sys (created THU, OCT 17, 1996):
sqlgen.pub.sys (created THU, OCT 17, 1996):
HP36216-02A.G1.18 SQL Command Generator/3000 ALLBASE/SQL
sqlmonp.pub.sys (created THU, OCT 17, 1996):
HP36216-02A.G1.18 SQLMonitor/3000 ALLBASE/SQL
sqlmig.pub.sys (created THU, OCT 17, 1996):
HP36216-02A.G1.18 SQLMigrate/3000 ALLBASE/SQL
sqlutil.pub.sys (created THU, OCT 17, 1996):
HP36216-02A.G1.18 DBE Utility/3000 ALLBASE/SQL
sqlwl000.pub.sys (created THU, OCT 17, 1996):
ALLBASE/NET:
-----------
anutil.pub.sys (created THU, OCT 17, 1996):
@(#)HP36216-02A.G1.18 ALLBASE/NET 900 ALLBASE/SQL
hpdalstn.pub.sys (created THU, OCT 17, 1996):
@(#)HP36216-02A.G1.18 ALLBASE/NET 900 ALLBASE/SQL
hpdadvr.pub.sys (created THU, OCT 17, 1996):
HP36216-02A.G1.18 ALLBASE/NET 900 ALLBASE/SQL
hpipdvr.pub.sys (created THU, OCT 17, 1996):
@(#)HP36216-02A.G1.18 ALLBASE/PC API 900 ALLBASE/SQL
hpipns.pub.sys (created THU, OCT 17, 1996):
@(#)HP36216-03A.G1.12 ALLBASE/PC API 900 ALLBASE/SQL
netutil.pub.sys (created THU, OCT 17, 1996):
HP36216-02A.G1.18 ALLBASE/NET 900 ALLBASE/SQL
hpipfile.net.sys (created THU, OCT 17, 1996):
hpipmast.net.sys (created THU, OCT 17, 1996):
hpipserv.net.sys (created THU, OCT 17, 1996):
Common ALLBASE/SQL Libraries:
----------------------------
xl.pub.sys (created THU, OCT 17, 1996):
@(#)HPDBCORE A.G1.18 09/24/96 ALLBASE/SQL
HP36216-02A.G1.18 Replicate/3000 ALLBASE/SQL
HP36216-02A.G1.18 Query Processor/3000 ALLBASE/SQL
HP36216-02A.G1.18 SQL Parser/Linearizer/3000 ALLBASE/SQL
HP36216-02A.G1.18 ALLBASE/NET 900 ALLBASE/SQL
HP36216-02A.G1.18 SQLX/3000 ALLBASE/SQL
HP36216-02A.G1.18 SQLX/3000 ALLBASE/SQL
HP36216-02A.G1.18 SQLX/3000 ALLBASE/SQL
HP36216-02A.G1.18 PASCAL Preprocessor/3000 ALLBASE/SQL
HP36216-02A.G1.18 C Preprocessor/3000 ALLBASE/SQL
HP36216-02A.G1.18 FORTRAN Preprocessor/3000 ALLBASE/SQL
HP36216-02A.G1.18 COBOL Preprocessor/3000 ALLBASE/SQL
------------------------------------------------------------------------
SUMMARY:
-------
Current ALLBASE/SQL version installed: A.G1.18
ALLBASE/SQL has 0 file(s) missing.
ALLBASE/NET has 0 file(s) missing.
Number of version incompatibilities found: 0
Using SQLAudit
SQLAudit is an ALLBASE/SQL utility program that can be used in
conjunction with audit DBEnvironments to view the changes that have been
made to the DBEnvironment. They include such groups as DML commands
(INSERT, UPDATE, DELETE), and DDL commands (data definition, storage, and
authorization commands, for example).
The committed transactions are audited by extracting information from the
audit log records in the log files. These audit log records are
generated when audit logging is enabled on the DBEnvironment through the
START DBE NEW or START DBE NEWLOG commands. Besides enabling audit
logging, these commands include other audit logging parameters that
define the groups of SQL commands you want to audit. These groups are
discussed under "Creating Audit DBEnvironments" elsewhere in this manual.
They include such groups as DML commands (INSERT, UPDATE, DELETE), and
DDL commands (data definition, storage, and authorization commands, for
example).
SQLAudit is designed with a number of commands for viewing the changes
made to the DBEnvironment. The following list of SQLAudit commands
provides an overview of these commands:
AUDIT Audits changes made between two given audit
points.
EDITOR Invokes the currently set editor.
EXIT Terminates SQLAudit execution.
GET AUDITPOINT Determines the current audit point information
from the DBEnvironment.
HELP Displays and describes all SQLAudit commands.
LOCK AUDITPOINT Locks an audit point to prevent overwriting
transactions in the log files that have not
been audited
MODIFY AUDITPOINT Modifies the current audit point information
for the DBEnvironment.
QUIT Terminates SQLAudit execution.
SET Displays current SQLAudit settings.
SET DBENVIRONMENT Establishes or releases the connection to the
DBEnvironment.
SET ECHO_ALL Enables or disables echoing of user input to
the standard output.
SET EDITOR Defines the current editor.
SET EXIT_ON_DBERR Sets SQLAudit to exit or continue when a
database error is encountered.
SET RECOVERFILE Defines the recovery file used by the AUDIT
command.
SHOW AUDITPOINT Displays audit point information contained in a
file.
UNLOCK AUDITPOINT Removes a locked audit point from the log.
: Escapes temporarily to the operating system for
the execution of operating system commands.
Refer to the appendix "SQLAudit" for the complete SQLAudit command
reference.
SQLAudit Conventions.
The following conventions should be noted when using SQLAudit:
* Unlike ISQL, SQLAudit does not require a command terminating
semicolon. (If a semicolon is entered, SQLAudit ignores it.)
* When choosing from two options, (y/n), for example, the first
choice listed is always the default (carriage return).
* To return to the main prompt (SQLAudit >>) at any time, type a
double or single slash (// or /).
* If an SQLAudit command creates a file, the file is a Portable
Operating System Interface (POSIX) file. (POSIX is a command
shell that is part of MPE/iX and causes MPE/iX to be more similar
to HP-UX.) When you specify a file name in an SQLAudit command, it
is best to enter it in all uppercase letters. This way, you can
see the file in a file list from within SQLAudit. If the file
name contains lowercase letters, you must exit SQLAudit and enter
an ls command from the POSIX shell in order to see the file in a
list.
* To escape to the command interpreter (MPE/iX) enter a colon (:)
and a carriage return. To enter operating system commands from
within SQLAudit, type a colon (:) as the first character of the
command. (In either case, only commands allowed in MPE/iX can be
executed.) You then type RESUME RETURN to return to SQLAudit.
Table 1-7 summarizes these conventions.
Table 1-7. SQLAudit General Conventions
-------------------------------------------------------------------------------------
| | | |
| Convention | Explanation | Example |
| | | |
-------------------------------------------------------------------------------------
| | | |
| No Semicolons | Command terminating | EXIT or EXIT; |
| | semicolons are not | |
| | needed, but allowed. | |
| | | |
-------------------------------------------------------------------------------------
| | | |
| :<Command> to enter | Use any command allowed | SQLAudit >> :listf RETURN |
| system commands | in MPE/iX. | |
| | | |
-------------------------------------------------------------------------------------
| | | |
| : to escape to MPE/iX | Use any command allowed | SQLAudit >> : RETURN : |
| | in MPE/iX. | RESUME RETURN SQLAudit >> |
| | | |
-------------------------------------------------------------------------------------
| | | |
| // OR / to return to the | Single or double slashes | DBEnvironment Name >> // |
| SQLAudit prompt | end a command. | SQLAudit >> |
| | | |
-------------------------------------------------------------------------------------
| | | |
| ALL CAPS | When you use an SQLAudit | AUDIT BEGINFILE |
| | command to create a file, | |
| | it is recommended that | |
| | you enter the filename in | |
| | all uppercase letters, so | |
| | that you can see it from | |
| | the POSIX shell. | |
| | | |
-------------------------------------------------------------------------------------
Understanding Audit Points.
SQLAudit requires a beginning and ending point in the log files to
determine what portion of the log history to audit. These audit points
are defined by using the command GET AUDITPOINT. GET AUDITPOINT
determines all the current transaction information for the DBEnvironment
and stores it and identification information in a file for later use.
The audit point file can then be used by other commands, such as AUDIT or
SHOW AUDITPOINT.
The beginning audit point provides the AUDIT command with information
about the previously committed transactions. The AUDIT command uses this
information to audit all transactions that have committed after the
beginning audit point was defined by GET AUDITPOINT.
The ending audit point is used by the AUDIT command to provide a point
where the AUDIT command can stop processing audit log records. In
addition, the ending point also provides a list of partitions that have
committed transactions, and when compared with the beginning audit point,
a list of partitions to be audited.
If you do not specify a beginning audit point, the AUDIT command assumes
by default that you want to audit every transaction, starting from the
first committed transaction for each partition being audited. If you do
not specify an ending audit point file, the AUDIT command assumes by
default that you want to use the last committed transaction in the log
file as the stopping criteria.
Whether you specify a beginning audit point or assume the default,
SQLAudit must still be able to find the first committed transaction for
the partition in the log files. Therefore, if the first committed
transaction for an audited partition cannot be found, an error is
returned and the partition is dropped from the list of partitions being
audited.
Suppose, for example, that at the time the beginning audit point is
defined, the current list of partitions and their committed transactions
exists as follows:
Partition Transaction Identification Information
1 Transaction # 10
2 Transaction # 21
6 Transaction # 16
Transaction number 10 was the last transaction that changed data in
partition number 1. Similarly, transaction numbers 21 and 16 were the
last transactions to change data in partition numbers 2 and 6,
respectively.
Suppose that the following transaction information is reflected in the
ending audit point information:
Partition Transaction Identification Information
1 Transaction # 45
2 Transaction # 21
4 Transaction # 51
6 Transaction # 45
In comparing these two audit points, SQLAudit can determine several
facts:
* No new transactions have been committed on partition number two.
* Transactions have been committed (since the beginning audit point)
on partitions 1 and 6.
* Partition number 4 has had its first transaction committed since
the beginning audit point.
With this information, the AUDIT command can determine that the only
partitions that need to be audited are 1, 4, and 6. The AUDIT command
would still include partition 2 in the list of partitions being audited,
but no transactions should be found.
Note that in this example, two partitions have the same transaction
number (partitions 1 and 6). This means that transaction number 45 made
changes to data in both partitions 1 and 6.
Although this example is an oversimplification of what the transaction
information looks like, the concepts still hold true.
Example of Getting Audit Points.
The following example illustrates how GET AUDITPOINT defines and displays
an audit point:
SQLAudit >> GET AUDITPOINT
Audit Point File >> STARTPT
Lock Log for Current Audit Point (no/yes) >> NO
Display Current Audit Point Information (no/yes) >> YES
Creator Name: dbauser
Lockpoint: Log Sequence No (5) Page No (327)
No. of Partition Instances: 3
Audit Partition Global Newlog Local
Name Number CommitID Timestamp CommitID
-------- --------- ---------------- ----------------- ----------------
DBE1 DEF 0000000400000121 2C13D50F000896EB 0000000400000121
DBE1 1 0000000A00000105 2C13D50F000896EB 0000000A00000105
DBE1 2 00000004000000E0 2C13D50F000896EB 00000004000000E0
SQLAudit >>
Refer to the SHOW AUDITPOINT command for an explanation of the display of
current audit point information.
Establishing SQLAudit Log Locks.
Since audit log records for transactions are written to the same
DBEnvironment log files as nonaudit log records, it is possible that
transactions that have not been audited can be overwritten. To prevent
this, the LOCK AUDITPOINT command can be used to prevent users from
overwriting transactions that you have not audited.
While it is possible to have many different audit points defined for a
DBEnvironment, it is only possible to have one audit point locked for the
DBEnvironment. Therefore, only one database administrator should be
assigned to determine which audit point should remain locked. This log
lock defines the point in the log file which no user can write beyond.
If any user transaction attempts to write beyond this point, a LOG FULL
message is issued and the transaction is rolled back.
The audit point can be unlocked with the UNLOCK AUDITPOINT. Since only
one audit point can be locked for the DBEnvironment, locking a new audit
point automatically unlocks any previously locked audit point. Audit
points can also be locked when they are defined through the GET
AUDITPOINT command.
Performing an Audit.
Assume that a company (perhaps a bank) wants to determine what changes
have been made to their data during the course of a business day. They
have previously enabled audit logging for the DBEnvironment and are about
to start their business day.
The first thing that must be done is to determine the beginning audit
point in the DBEnvironment log files. This point will be used as the
starting point of the audit process used at the end of the business day.
Since the bank does not want to lose any information before they audit
it, they also lock the beginning audit point to make sure the
transactions are not overwritten. This is done with the SQLAudit command
GET AUDITPOINT as follows:
SQLAudit >> SET DBENVIRONMENT BankDBE
SQLAudit >> GET AUDITPOINT
Audit Point File >> STARTPT
Lock Log for Current Audit Point (no/yes) >> YES
Display Current Audit Point Information (no/yes) >> NO
This sequence of commands creates a file called StartPt containing the
beginning audit point information and locks the log files to prevent
unaudited transactions from being overwritten.
Since log files are being locked, the database administrator makes sure
that they have enough log space in the DBEnvironment to last the full
day. If they find that they are running out of log space during the day,
additional log files will be added.
At the end of the day the bank wishes to audit the changes that have been
made to the data during the course of the business day. Since the AUDIT
command needs an ending audit point, the following sequence of commands
can be used to define it:
SQLAudit >> SET DBENVIRONMENT BankDBE
SQLAudit >> GET AUDITPOINT
Audit Point File >> ENDPT
Lock Log for Current Audit Point (no/yes) >> NO
Display Current Audit Point Information (no/yes) >> NO
Notice that this audit point is not locked since that would drop the lock
on the previous audit point and allow transactions committed between the
two audit points to be overwritten.
Now the actual auditing of the database changes can be performed as
follows:
SQLAudit >> AUDIT
Beginning Audit Point File >> STARTPT
Ending Audit Point File >> ENDPT
Result File to be generated >> RESULTS
Do you wish to specify Partition Numbers (n/y) >> no
Generating Results ...
Records Audited: 10000 Records In Result File: 10000
Records Audited: 20000 Records In Result File: 20000
Records Audited: 21427 Records In Result File: 21427
Finished Generating Results.
SQLAudit >>
If the bank wants to do continuous auditing, the ending point of one day
can be used as the starting point for the next day. You should lock the
new starting audit point so that no transactions get overwritten, as
illustrated in the following example:
SQLAudit >> LOCK AUDITPOINT
Audit Point File >> ENDPT
Lock Log for Audit Point (n/y) >> yes
SQLAudit >> :Purge StartPt
SQLAudit >> :Rename EndPt,StartPt
By using this process every day, you should always be able to audit the
last 24 hours without the transactions ever being overwritten.
SQLAudit Result Files.
The results of an audit are put into a file in a user readable format.
The following example shows what the contents of this file could look
like for an audit of partition number 2:
***** SQLAUDIT: GENERATING RESULTS ****
Creator: dbauser Creation Time: 1993-05-11 14:22:16.531
BEGIN
INSERT (2) USER1.TABLE1 (123, 'test data', NULL, 1.23)
UPDATE (2) USER1.TABLE1 (123, 'test data', NULL, 1.23) ((3) 0x0000123C)
COMMIT User: USER1 Label: TRANS1 Time: 1993-05-11 10:15:00.123
BEGIN
DELETE (2) USER1.TABLE1 (123, 'test data', 0x0000123C, 1.23)
COMMIT User: USER1 Label: TRANS2 Time: 1993-05-11 10:15:01.455
End of File
In this example, the first transaction audited shows that a record was
inserted into table USER1.TABLE1 and that the third column in the record
was then updated. In the next transaction, the record was deleted. The
number in parenthesis following the operation type is the partition
number that the operation was performed against (partition number two in
this case).
For performance and log space reasons, some information is not contained
in the audit log record. For example, column names of the table being
updated are not contained in audit log records. Since log records are
being audited rather than an active DBEnvironment, it is not possible to
get the column names from the system catalogs. For example, a table may
have been dropped and recreated a number of times between the time the
audit log record was created and the time you run SQLAudit. Therefore
you should keep the following points in mind:
* DATE, DATETIME, TIME, INTERVAL, and DECIMAL data are printed in
the hexadecimal format.
* DDL commands (as they are categorized under the audit elements
DEFINITION, STORAGE, AUTHORIZATION, and SECTION) return only the
operation type such as whether the command was a CREATE TABLE or
GRANT, for example.
* Long fields in a record are implemented as a pointer to the data.
The pointer value is printed, not the data itself.
Checking the Exit Status
When running ISQL, SQLUtil, SQLGEN, or SQLMigrate in a script, you can
check the exit status to ensure that the utility completed successfully.
The exit status is set to the number of DBERRs encountered. If a DBERR
is not encountered, the exit status is set to 0. The exit status may
contain a value in the range 0 - 255.
MPE/iX 5.5 Documentation