IMAGE/SQL: Part 2 of 5
Sun Valley, Idaho 83353-3000 U.S.A.
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.
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.
Such failures arise from the following defects:
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