IMAGE/SQL: Part 5 of 5
Fred White
Adager Corporation
Sun Valley, Idaho 83353-2358 USA
adager.com
Some Final Thoughts on IMAGESQL.PUB.SYS Commands
IMAGE/SQL is an HP3000 product which provides SQL read/write access to TurboIMAGE (IMAGE) databases (DBs) under MPE/iX 4.0 or later.
SQL access is via industry standard data manipulation language (DML) constructs whose table and column naming conventions differ from IMAGE's dataset and field naming conventions. IMAGE/SQL also limits the data sizes it supports for DECIMAL, INTEGER and REAL data types.
In conformance with SQL standards, IMAGE/SQL also does not support compound columns (i.e., IMAGE fields defined by an item whose specs are, for example, “4X8”).
The mapping information needed to support SQL access to a DB is created when the DB is attached to an SQL DBEnvironment (DBE) and may subsequently be modified using the “UPDATE TYPE” and “SPLIT” commands of the utility program IMAGESQL.PUB.SYS (IMAGESQL).
If all of your IMAGE item types truly reflect the data types of the fields they define, the attachment process will be seamless in that there will be no need to employ the “UPDATE TYPE” command to correct any falsely typed fields.
A seamless data type interface simplifies the initial attachment process as well as all subsequent attachments necessitated by database changes as they occur over time. It also minimizes your efforts and the possibility of errors.
For these reasons, it is recommended that you correct the item definitions in your database prior to attaching the database to any DBE.
When you do attach your DB to a DBE:
- IMAGE field names are mapped into column names (with special characters not supported by SQL replaced with underscores).
- Compound IMAGE fields are mapped into a set of uniquely named columns. Each column corresponds to a TurboIMAGE subfield and the names of these columns are created by suffixing the IMAGE item name with, successively, underscore 1, underscore 2,..., underscore N, where N is the number of subfields of the IMAGE field.
Thus, if the item name is “BILL” and its data type is “4X8”, the mapping creates four mapped columns all of whose data types are “CHAR(8)” and whose names, respectively are “BILL_1”, “BILL_2”, “BILL_3” and “BILL_4”.
- If a field's item type is Z, P, I, J, K. R or E and its data length is not supported by IMAGE/SQL, it is assigned an SQL type default as shown in Table 2-6 of the “HP IMAGE/SQL Administration Guide”.
If this should happen for N>0 fields, IMAGESQL will emit the warning:
Mapped N incompatible/imprecise source type(s) (ATCWARN 32061)
If the IMAGE item type of an incompatible field truly represents the data format of the field, valid SQL access is impossible.
If the field is in a dataset for which SQL access is not granted, this is no problem. Otherwise, you will need to modify (using Adager, for example) the field and data to a size and/or format acceptable to IMAGE/SQL prior to attaching your DB to any DBE.
After attachment, you should use IMAGESQL's “DISPLAY MAP” command to review the data types of all mapped columns of all SQL accessible tables to verify that the mapped data type of each column truly portrays the data format of the column. If not, you are provided with the mapping commands “UPDATE TYPE” and “SPLIT” which you can use to change the data type for a column and/or split the column into multiple columns in order for SQL to access the data correctly.
“UPDATE TYPE” is the mapping command which enables you to override default mappings. This command, unfortunately, contains flaws both in form and in function:
- Type overriding is generally applied at the dataset (table) level rather than at the database level. Consequently, if the same IMAGE item identically defines fields in several datasets, you must perform identical UPDATE TYPE commands for each of those datasets. Failure to do so will result in SQL access problems and/or incorrectly formatted data being entered into the database.
- The one form of the command which provides database level mapping is:
UPDATE TYPE SourceType IN *
Unfortunately this form is SourceType-relative rather than Item-relative. Consequently, the mapping is applied to all IMAGE items sharing the SourceType specified in the command. In the vast majority of databases, this capability will, at worst, be dangerous and, at best, be useless.
Exercise extreme care in using this form of the UPDATE TYPE command.
“SPLIT” is a mapping command which enables you to divide a mapped column of a table (dataset) into smaller columns with the names and data types of these smaller columns individually specifiable.
The problem with the SPLIT command is that it is applicable only at the dataset level. If an IMAGE item is used to define fields in several datasets and those fields need to be SPLIT the same way, you must perform identical SPLIT commands to that field in all of those datasets. Failure to do so will result in SQL access problems and/or incorrectly formatted data being entered into your DB.
Not to be continued.
This is the 5th and last Adager Column addressing the command flaws of the IMAGE/SQL utility IMAGESQL.PUB.SYS. There are other flaws, but it's simply too depressing and unrewarding for me to continue addressing them.
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