IMAGE/SQL: Part 2 of 5

IMAGE/SQL: Part 2 of 5

Fred White

Adager Corporation

Sun Valley, Idaho 83353-2358 USA
adager.com
In Part 1, I recommended enhancements to DBSCHEMA which would assist development of SQL-supportive databases. Please include support for new data types corresponding to HP SQL types DATE, TIME, DATETIME and INTERVAL as enhancement #4.

IMAGESQL

In this part, I discuss global defects in the program called IMAGESQL.PUB.SYS (IMAGESQL) which make it difficult for Database Administrators (DBAs) to maintain their SQL DBEnvironments (DBEs) and their IMAGE/SQL databases (DBs). I could advocate a total redesign of the DBE data structures and the IMAGESQL command set but will not do so since I doubt that HP can/will invest in such a project at this time. Instead, I limit my recommendations to modifications which minimize the impact of these defects and make the DBA's job easier.

The primary metadata contained in a DBE fileset to support SQL access to a DB includes the dataset names, dataitem names and dataitem specs as they existed in the DB's root file at attach time. Secondary metadata includes an SQL-required OWNER name and manually entered mapping information which may be needed to override IMAGESQL's default mappings. Tertiary metadata includes tables of SQL-authorized users grouped by DBOPEN access class (password) and DBOPEN mode.

To create the primary metadata, the DB creator (DBC) performs three commands: SET SQLDBE, SET TURBODB and ATTACH. Secondary metadata, if needed (see table 2-6 of the HP IMAGE/SQL Administration Guide), is provided via the UPDATE TYPE and/or SPLIT commands. Tertiary metadata is provided by the ADD USER command.

To subsequently add/delete/change any of the primary elements (or access class PASSWORDs) the DBC must detach the DB from the DBE, make the changes and then perform the entire attachment process all over again.

To meet this need, during the initial attachment process IMAGESQL logs all of your commands to an EDITOR format file which, assuming you have saved it, can be used as an XEQ file in a subsequent re-attachment.

Due to flawed syntax, flawed functionality and flawed error handling, XEQ file re-attachments can completely fail or, perhaps worse yet, succeed (i.e., the ATTACH worked) but with erroneous or incomplete metadata left in the DBE.

Defects

Such “failures” arise from the following defects:

The SET SQLDBE command fails if the DBC does not have DBA authority for the DBE or the DBE_MaintWord in the command is incorrect.
The ATTACH command fails because the OWNER name is in use by some other attached DB.
Any changes to a dataitem name (or type) or dataset name which participated in a mapping command will result in the command being rejected during the re-attachment process.
Furthermore, if the DBC adds a new dataset which contains a field whose dataitem name was referenced by a mapping command (or adds such a field to an existing dataset), the necessary mapping for the field will not occur since UPDATE TYPE and SPLIT commands are performed at the dataset level.

In both cases the DBE is left with erroneous metadata.

Tertiary metadata will be missing if the DBC changed any PASSWORDs which were referenced in any ADD USER commands. (IMAGESQL rejects the ADD USER commands referencing the old PASSWORDs.)
In Job mode, IMAGESQL terminates as soon as it encounters an input line which it rejects.
This form of failure is aggravated by the fact that IMAGESQL logs all responses, including MPE commands and responses which are rejected as invalid or unacceptable IMAGESQL commands.

The presence of such unnecessary “information” in the XEQ file is a major cause of re-attachment problems.

Eliminating MAINT words and PASSWORDs from IMAGESQL commands (as suggested in the modifications described below) in addition to eliminating some re-attachment problems also eliminates (1) the security risks associated with their being logged to an unprotected file and (2) the warning message which accompanies each such command (very annoying when entering hundreds of user names).

Suggested modifications

The following modifications to IMAGESQL will eliminate (or minimize the effects of) the correspondingly numbered defects described earlier:

Ignore the MAINT part in the SET SQLDBE command and, if present, exclude it from the log record.
Having DBA authority should be the only requirement for a DBC to attach a DB to a DBE and to maintain secondary (mapping) and tertiary (user) metadata.

The metadata consisting of the DB name, DBC name and OWNER name of each attached DB should not be “dropped” by a DETACH and the DBEC should not be permitted to revoke the DBA authority from any DBC identified by such metadata.
A PURGE command which drops all metadata can replace the current DETACH command.

Add a new mapping command (named “MAP”) whose syntax doesn't include the terms “UPDATE TYPE” or “SPLIT” but provides both functions. It requires only a dataitem name and the mapping specs (NO dataset name).
IMAGESQL applies the specified mapping to all datasets having fields defined by the named dataitem. Secondary metadata won't be lost due to changed dataset names and, if new datasets are added (or new fields added to existing datasets), all pertinent “MAP” commands will be applied automatically.

Allow the ADD USER command to have an “ACCESS=” part instead of the “PASS=” part. The “ACCESS=” phrase is followed by the access class number corresponding to the PASSWORD of the equivalent “PASS=” part (“ACCESS=17”, for example).
Compatibility with existing XEQ files (and DBAs) can be retained by permitting the “PASS=” part but replacing it with the equivalent “ACCESS=” part in the log record.

This allows the DBC to change access class PASSWORDs in the DB without needing to detach/re-attach and, even if other changes are made that do require the detach/re-attach steps, no ADD USER command will fail simply because the DBC modified an access class PASSWORD.

a) Log only those responses that represent successful IMAGESQL commands.
b) Do not require a MAINT part in the SET TURBODB command. If present, ignore it and exclude it from the log record. If you are the DBC, you don't need it.

If you aren't the DBC, the apparent advantages are more than offset by the disadvantages described earlier. Also, without DBA authority you can't SET SQLDBE and even with DBA authority, you can't ADD/DELETE/DISPLAY USERs.

c) In Job mode, terminate only if (1) a SET SQLDBE, SET TURBODB or ATTACH command fails or (2) a mapping or ADD USER command fails because there is no currently attached DB or the Job is being run by someone other than the DBC.

To be continued ...

In Part 3, I will begin a review of all of the IMAGESQL commands. I will point out their defects and suggest improvements and/or workarounds.

What do your worldwide HP e3000 colleagues think of Adager? See a sample of comments from real people who use Adager in the real world, where performance and reliability really count.
Back to Adager