IMAGE/SQL is an HP3000 MPE/iX product which contains TurboIMAGE as a component along with other software components which enable you to provide SQL read/write access to your TurboIMAGE databases.

Simply installing IMAGE/SQL on your HP3000 system does not automatically provide SQL access to any of your databases. To prepare for SQL access to a database you must provide IMAGE/SQL with descriptive information about the database and its authorized SQL users. This so-called “attachment” process is accomplished by running the IMAGESQL.PUB.SYS program and entering the appropriate commands.

Attaching to a DBE & detaching from a DBE

The information provided is stored in a set of privileged files called a Database Environment (DBE).

If, subsequent to attachment, perhaps weeks or months later, you need to make “structural changes” (add fields, delete sets, rename items, etc.) to your attached database, IMAGE/SQL requires that you first “detach” the database from the DBE and, after completing the changes, redo the attachment process in order that the access information needed by SQL corresponds to the revised state of the database.

History

The first release of IMAGE/SQL was made available to HP3000 MPE/iX users in 1993.

Unfortunately, the design of the data structures of the DBE files combined with a poorly designed method of maintaining those structures resulted in the attachment process being tedious, error-prone and annoying, especially if you have many databases and users to deal with.

In 1993, as a member of the Adager development and support team, I shared in the effort of enhancing Adager to automatically perform the required detaching and re-attaching for you thus relieving you of this burdensome re-attachment process.

During this effort, I became aware of other design flaws and errors of omission within IMAGE/SQL which, while having no perceived impact on SQL access, needlessly threaten the integrity of your DBEs and require more disk space than necessary without providing commensurate administrative or functional benefits.

I have chosen a “question and answer” format to clarify my concerns and to identify the issues that HP should address to improve the usability and flexibility of future releases of IMAGE/SQL.

Is the database attachment process easy?

No. If you have only one or two databases and a handful of users, using the program IMAGESQL.PUB.SYS to establish and maintain the DBE metadata about users and databases should not be too painful.

For medium-to-large installations, the process is tedious and error-prone, the major reasons being:

Do the IMAGESQL commands employ syntax familiar to MPE users and not pose any privacy or security threats?

No. The “ADD USER” command requires (who knows why?) an account name separated from the user name by a commercial at sign (@) instead of the familiar period (.).

The same command also requires an IMAGE password which

Is it easy to make structural changes to “attached” TurboIMAGE databases?

No. My “no” response here is prompted by the detachment/re-attachment complexities.

Making the structural changes is still as easy as ever. The problem is that, as mentioned earlier, the database must be detached and, after the changes, re-attached.

These re-attachment problems would be profoundly simpler and less error-prone, if the metadata about the database (and its OWNER and SQL users) were retained (instead of deleted) at detach time.

Also, it would make it much simpler for Adager to programmatically detach and re-attach if IMAGE/SQL included a set of procedures for accessing and updating the DBE metadata.

Are the safeguards against the DBE files getting out-of-synch adequate?

No. Since keeping database files undamaged and “in-synch” are two of the most difficult challenges facing database systems, I was shocked to find little or no discussion of a) transaction logging and recovery or b) the storing and/or restoring of DBEs or c) DBE diagnostic and repair facilities.

The SQLUTIL program does permit the “stopping” (see below) of DBEs and the “storing” of stopped DBEs.

If you perceive this as a solution to the problem of backing up your DBE files and databases to keep them “in-synch”, you have been deceived. In reality it is a non-solution since

a) stopping a DBE terminates and prevents further SQL access but doesn't terminate or prevent non-SQL access and

b) other available MPE/iX store and restore facilities are not DBE-aware (i.e., any non-DBE related STORE or RESTORE may unknowingly include files of a DBE).

Consequently, the use of either backup/restore facility can unknowingly destroy the integrity of your DBEs.

Last, but by no means least, can anyone explain to me how stopping and storing a DBE (and later restoring a DBE) can maintain file synchronization when the current version of IMAGE/SQL permits databases to be “attached” to more than one DBE?

Are the DBE metadata files and their structures designed so that the system is flexible and powerful while still conserving disk space?

No.

Proposals

These defects could be removed by changes to the attachment process such as:

Is there a good transaction logging facility covering DBE files supported with a fast, reliable recovery facility which is intelligent and has minimal input requirements?

No. A facility is desperately needed to control transaction logging and recovery at either the DBE level or, perhaps, the domain level, if that makes sense.

All file utilities, such as STORE and DBUTIL must be made DBE-aware so that they don't violate the integrity of DBEs.

(Hopefully, in the course of making utilities DBE-aware, the lab could also make them TurboIMAGE database aware even in non-SQL environments. This has never been attempted and its absence has led to out-of-synch databases for the last 19 years.)

Are there other issues which need addressing?

Yes. Miscellaneous issues have arisen and will continue to arise. For instance:

Good news

Jim Sartain, HP's SQL Database Program Manager, invited me to Cupertino where we held an all-day discussion of my concerns and suggestions.

I am pleased to report that Jim's team was already addressing my concerns along with enhancement requests submitted by others.

Some enhancements have already been implemented and others are in progress.

Some issues are extensive in scope and will require the cooperative efforts of the MPE/iX Lab and the people working on HP's utilities. These, of course, will take more time.

Practical suggestions

If you are planning on using IMAGE/SQL, I have a few simple suggestions which will simplify your life and make it easier for you to maintain the synchrony of your database and DBE files: