Stan Seiler ([log in to unmask]) suggested that I upload the file in txt
format. So, I did. If you are interested in IMAGE/SQL and Client/Server
access, you will want to peruse it. It is Appendix B of the TCU 20 January
1994 Workbook.
If you have questions or comments, do not hesitate to send them..
If you have criticism and it is constructive, great, send it on!
If you have criticism and it is not constructive, great, send it to yourself!
Kind regards,
Denys. . .
Client/Server with IMAGE/SQL
Denys P. Beauchemin (713) 626-3842
Introduction
In 1992 Hewlett-Packard introduced an SQL interface for IMAGE, under the name
SQL for IMAGE. A
few months later, HP renamed it to IMAGE/SQL and today, it is probably the
hottest topic of the
HP3000. This paper will adress all aspects of client/server with IMAGE/SQL.
We will cover the latest
developments in IMAGE, attach a TurboIMAGE database to an ALLBASE/SQL
database environment
(DBE) and use SQL to retrieve data in the IMAGE database. Next, we will
configure a PC on a network
to access the database with some of the new client/server PC tools, using
both the GUPTA API and
ODBC. Finally, we will look at some code examples of programs written on the
HP3000 to access
IMAGE through the SQL interface.
ALLBASE and TurboIMAGE
One can look at IMAGE/SQL as the end product of marrying two existing
technologies, to wit
TurboIMAGE and ALLBASE. We are all quite familiar with the IMAGE side, so
let's spend a very short
amount of time on the ALLBASE aspect.
ALLBASE TurboIMAGE
Concept equivalent
DBE Collection of databases
Objects of one OWNER DATABASE
TABLE DATASET
ROW or TUPLE IMAGE RECORD
COLUMN FIELD or ITEM
INDEX CHAIN or KEY
GROUP/USERS PASSWORDS
Figure 1. Database Objects.
A DBE (Data Base Environment) is made up of various MPE files on the system
and is created with an
SQL command issued programmatically or through ISQL.
The command, START DBE allows the creator (also known as the Data Base
Administrator DBA by
default, though more users can be granted DBA capabilities later) to specify
the name of the DBECON,
DBEFILEs and DBELOG files. The DBECON file's name is also the name of the
DBE and this file
contains the configuration of the entire DBE. One should view the DBEFILEs
simply as repositories of
data. The DBA can add more space to the repository by simply adding more
DBEFILEs to the existing
DBE. This is done via other SQL commands. Finally, the DBELOG files are
used for, logging... Of
course, all these files are privileged files on MPE/iX.
The DBE in effect is a collection of databases which share the same logging
and recovery mechanism.
DATABASE is a collection of tables which belong to the same OWNER. More on th
is a little later.
A TABLE contains the data, like a stand alone detail dataset.
A ROW or TUPLE is a collection of columns within a table.
A COLUMN is a single storage entity within a ROW.
An INDEX is the method used by SQL to streamline access to the data contained
in various tables. Where
IMAGE makes use of KEYs for masters and CHAINs for details, ALLBASE/SQL
supports b-tree access
to all tables and a form of HASHED INDEX to specifically designated tables.
The GROUP/USER concept is used by SQL to control which user can perform which
action to what data.
A GROUP is made up of USERs and other GROUPs. USERs and GROUPs can be
GRANTed various
capabilities by the DBA. These same capabilities can be REVOKEd. A
The mission is therefore to map the IMAGE database into the ALLBASE DBE and
away we go.
INTERLUDE
Before going any further, it is important to realize a few things about
ALLBASE/SQL and to keep in
mind a few rules for this exercise.
ALLBASE uses itself to store the directory of tables, indexes, columns,
groups, and other objects. Entries
regarding these objects are stored in a database which is made up of tables
belonging to a user called
SYSTEM. Thus if one wants to see which tables are in a DBE, one could
retrieve this information from a
table called SYSTEM.TABLE.
Only one DBE can be opened by a session at any one time. Therefore we
should plan on ATTACHing
all related IMAGE databases to the same DBE. In our example, we will create
the SALESDBE and we
will ATTACH two IMAGE databases: ORDERS and INVTRY. With release F.0 of
ALLBASE, a process
can connect to up to 32 DBEs, but a transaction must still be limited to a
single DBE. The command SET
CONNECTION TO 'dbe'; is used to switch the connection to the various DBEs
during the process.
The concept of passwords does not exist in ALLBASE/SQL. The user name is
used at CONNECT time to
acquire the capabilities in the DBE.
IMAGE/SQL follows all MPE restrictions so let us be aware of the various datab
ase, DBEs, groups and
accounts. In our example, the DBE and the IMAGE databases are in the same
group and account and we
are the creator for all of them.
IMAGESQL will prompt for a maintenance word for both the DBE and the IMAGE
database if the user is
not the creator. So make sure that the maintenance words are set properly for
both.
CREATING A DBE
The first thing to do is to create a DBE, in this case we will call it
SALESDBE. We use the program
ISQL (Interactive SQL) to issue the following SQL command:
:ISQL
isql=>START DBE
'SALESDBE' MULTI NEW (1)
DBEFILE0 DBEFILE DBEFILE0 (2)
WITH PAGES = 1000, NAME = 'SALESD0',
LOG DBEFILE DBELOG0 (3)
WITH PAGES = 1000, NAME = 'SALESLG0';
isql=>EXIT;
Figure 2. Creating a DBE.
(1) Here we specify the name of the DBE, the fact that it is new and the fact
that is can be accessed by
multiple users.
(2) Here we specify the size and the names (internal and external) of the
original DBEFILE DBEFILE0.
The size is in pages which represent 4k bytes each. Here we request that the
DBEFILE0 be called
DBEFILE0 internally, that it be allotted 1000 pages of 4k and that it be
created as MPE file SALESD0.
(3) Here we specify the type, name (internal and external) and size of the
log file. In our example with are
not using DUAL logging and the logging will be circular and to the MPE file
SALESLG0. Note the ; to
end the command.
We use SQLUTIL to switch settings and assign a maintenance word.
:SQLUTIL
>>SETDBEMAINT
DBEnvironment name: SALESDBE
Current maintenance word: <cr>
New maintenance word : mword
Retype New maintenance word: mword
>>EXIT
Figure 3. Using SQLUTIL.
ATTACHING AN IMAGE DATABASE
Once we have created the DBE, we want to attach the two IMAGE databases to
it. For this we will use
the program called ATCUTIL or IMAGESQL. By the way, ATCUTIL will create a
DBE for you, if you
wish.
:IMAGESQL
>> SET SQLDBE SALESDBE
>> SET TURBODB ORDERS
>> ATTACH
various warnings and voila!
>> EXIT
Figure 4. Attaching a database.
What just happened? Well, many things, let us examine closer.
Each dataset in the IMAGE database has been added to SYSTEM.TABLE with the
name of the IMAGE
database as the owner. The default owner can be altered at ATTACH time by
specifying OWNER=.
However, remembering that in ALLBASE parlance a database is a collection of
tables belonging to one
owner, IMAGE/SQL fits very well within the concept.
Each field in each dataset has been added to SYSTEM.COLUMN with the NOT NULL
attribute as
IMAGE currently does not allow valid null items.
A file called ATCINFO is created which contains mapping information for each
dataset in the IMAGE
base. This ATCINFO file is created as a privileged file which accompanies
the DBE, in essence, it is now
part of the DBE and is declared in the DBECON file. Mapping involves
transforming data items or
dataset names, data types and security features. Specifically if item or set
names contain special
characters, IMAGESQL will transform these characters to an underscore. For
example, IMAGE's
CUSTOMER-MASTER will become CUSTOMER_MASTER, and MARKUP% will become MARKUP_.
There are more things which will be added to this file in a few minutes.
A DBE can be attached to multiple IMAGE databases, so the ATCINFO file will
contain information
about each and every attached IMAGE base.
A file called dbnameTC is created which is a privileged file that accompanies
the TurboIMAGE database.
Since an IMAGE database can be attached to multiple DBEs, the accompanying
DBTC file will contain
entries for each DBE to which this IMAGE base is attached.
MAPPING DATA TYPES
If a data field in any of the datasets of the IMAGE base is a compound item,
a message about compound
items being SPLIT will be displayed at ATTACH time. This is because
ALLBASE/SQL does not support
compound items. What actually happened at ATTACH time, is that IMAGE/SQL
generated (read
mapped) a series of columns which correspond to the subitem count of the
IMAGE item. For example, if
the IMAGE item ADDRESS is defined as 4X30, IMAGE/SQL will map this item into
4 columns like so:
IMAGE item IMAGE/SQL MAPPED column
ADDRESS 4X30 ADDRESS_1 char(30)
ADDRESS_2 char(30)
ADDRESS_3 char(30)
ADDRESS_4 char(30)
Figure 5. Splitting compound items.
But data type mapping goes further than splitting compound items into
multiple columns. IMAGE/SQL
allows the DBA to map a a non-compound item into multiple columns. IMAGE/SQL
allows data type
mapping so that the data contained in the item can be handled as a type
differing from the IMAGE type.
Let us first look at user mapping.
There are many databases which have been designed with a single item, usually
rather large, that actually
(logically) encompasses many fields. This is prevalent in databases which
came into being before
TurboIMAGE. In pre-Turbo days, an IMAGE base could only have 255 distinct
data items declared for
use throughout the database. This presented problems for larger, more
complex databases. The common
solution was to declare a large item, like x200 or j50 and have the program
actually interpret the data
correctly. For users of 4GLs, this was a neat trick, as the various
dictionaries handled these items with no
problems. For example, an item defined as x200 could actually contain
various integers and strings which
add up to 200 bytes. In this case, the item is just used as a storage
space and IMAGE as a storage
manager with non-representative data items.
However, this will not work for IMAGE/SQL in the SQL aspect. SQL has
something known as data
integrity constraint. This means that you cannot have the same X200 as
above, to contain a mix of
integers and characters and other types. SQL, when manipulating this column,
will be very unhappy
finding data which is not a valid character string. So IMAGESQL enables the
DBA to create new
columns which map onto this X200 field and actually represent the data
properly. This mapping is done
via the SPLIT command in IMAGESQL. It is important to know precisely what is
contained in the sub-
fields and the length of each sub-field.
Let's use an example: In our x200 field above, which we will call CUST-INFO,
contains the customer
name, 4 address lines, country, telephone number and related customer number.
The split command is
>>SPLIT CUST_MASTER.CUST_INFO INTO
CUST_NAME: X30: CHAR(30),&
C_ADDR1: X30: CHAR(30),&
C_ADDR2: X30: CHAR(30),&
C_ADDR3: X30: CHAR(30),&
C_ADDR4: X30: CHAR(30),&
C_COUNTRY: X30: CHAR(30),&
C_TELEPHONE:X16: CHAR(16),&
C_RELATED: I2: INTEGER
Figure 6. Using the SPLIT command
The ATCINFO file would then be updated to contain this mapping and the newly
specified columns
would also be inserted in SYSTEM.COLUMNS. These new columns would now be
available for SQL
access.
The last important concept of data type mapping has to do with the data type
itself. IMAGE, while
having several data types, does not have data constraint. Which means that
the data is not checked for
proper fit going in or out of IMAGE. In fact, the only common tool from HP
which makes use of a data
integrity constraint concept is QUERY. For example, QUERY will not allow the
user to enter a non-
numeric character when it is asking for customer-no. QUERY verifies that the
input is valid for the
corresponding IMAGE data type.
SQL on the other hand, while having fewer and sometimes different data types,
has strong data integrity
constraints. This means the data must fit the data type or something
untoward is going to happen at some
point. During the original ATTACH, IMAGE/SQL will map the IMAGE data types
to equivalent SQL
types. But as is usual in cases of conversion, it is not 100%.
SQL has SMALLINT, INT, DECIMAL and FLOAT data types to which IMAGESQL can map
the
following types: LOGICAL, REAL, ZONED and PACKED data. It is outside of the
scope of this
presentation to go into data mapping at this time, suffice it to say that
IMAGESQL allows for alternate
types in cases of problems. By using the UPDATE command, the various
columns can have their data
types changed to alternates which can better contain the data.
The mapping is elementary for X and U fields into CHAR of corresponding
length and I1 and I2 types
map into SMALLINT and INT respectively.
Again, all this mapping information is carried in the ATCINFO file and is of
course, contained in
SYSTEM.COLUMN for each column.
It is recommended to perform the data mapping task before adding users (next
step), since all views
relating to the altered data are dropped if the data type is modified or the
data is split.
ADDING USERS
After the ATTACH command, only one user is defined in IMAGE/SQL, the database
creator or DBC. In
order to allow other users, the DBC, having DBA capability in the DBE, has to
declare additional users.
As we said earlier, the concept of passwords is not implemented in SQL.
Instead, it has USERs and
GROUPs with capabilities. For our purposes, the USER is the MPE USER and
ACCOUNT of the user
CONNECTing to the database. For example if I had logged on as MGR.DENYS my
CONNECT ID to
IMAGE/SQL would be [log in to unmask]
In IMAGE, the access to the data is decided at DBOPEN time, using a
combination of the password and
the DBOPEN mode. The password used, if valid, corresponds to a user class
number between 1 and 63.
Class 64 is reserved for the creator and class 0 is used for any invalid or
nonexistent password. In the
schema, the creator will have specified the READ and WRITE accesses to data
items and datasets, by
specifying which classes can do what action to what data.
For IMAGE/SQL, we need to map USERs to MODE/PASSWORD combinations. This is
done as we
declare new users to IMAGE/SQL using the ADD USER command:
>> ADD USER MGR@DENYS WITH PASS=PW, MODE=5
Figure 7. Adding a user.
Now we get various messages.
IMAGE/SQL will equivalence the password SUPERPW to a user class, for example
63 and will create a
GROUP to represent this class. The GROUP name is made up of the IMAGE
database name (the
OWNER, remember?) and the user class. In our example this would be
ORDERS_V63. The USER is
added to the GROUP and then IMAGE/SQL creates a series of VIEWs which
reflects the access that the
MODE/PASSWORD combination allows. There is one VIEW created per each dataset
which is
accessible. The VIEW is named with the owner, the dataset name and the class
number. Within the
view, the accessible columns are represented.
In our example, if class 63 permitted access to the dataset CUSTOMER-MASTER,
the VIEW
ORDERS.CUSTOMER_MASTER_V63 would be created and it would contain the names of
the columns
to which class 63 had access.
A VIEW is nothing more than a stored SELECT command.
ACCESSING IMAGE/SQL
Now that the preparations are done, let us access the IMAGE data via the SQL
environment.
There are three ways to do this. One, write a program, two, using any of the
neat 3rd party products, and
finally, via ISQL. For the purposes of our discussion, we will limit
ourselves to ISQL.
Actually we have already used ISQL when we created the DBE originally. This
shows us that ISQL is
more than just a QUERY equivalent, in fact, ISQL, which stands for
Interactive SQL, allows one to issue
ALL SQL commands.
For IMAGE/SQL tables, however, these commands are limited to the commands of
the Data Manipulation
Language (DML) portion of SQL. IMAGE/SQL does not support the Data
Definition Language (DDL)
commands. The core of the DML is the SELECT command:
select-statement ::=
SELECT [ ALL | DISTINCT] select-list
INTO host-variable-reference, [,host-variable reference]...
FROM table-reference [,table-reference]...
[WHERE search-condition]
[GROUP BY column-name[,column-name]...]
[HAVING selection-condition]
[ORDER BY column-name | column-number [ASC|DESC]]
and don't forget the ';' at the end of the command.
SELECT Figure 8. The command.
SELECT-LIST: By using DISTINCT, the user prevents the retrieval of rows with
duplicate column
values. If the user wishes to retrieve all the columns, the asterisk can be
used. Also, aggregate functions
can be specified here, as well as expressions and constants. Aggregate functi
ons are: MIN, MAX, AVG,
COUNT and SUM. An expression is simply a mathematical expression enclosed in
parentheses, eg: (1.10
* SALE_PRICE) would return all sales_price marked up by 10%. This is done
by SQL not ISQL.
ISQL permits the user to directly issue a SELECT command. Since ISQL is a
program itself, the user
cannot specify the INTO clause. Programmatically, the host-variables are the
storage areas in the user
program to which SQL will return the retrieved data.
FROM: This instructs SQL as to which tables are to be retrieved from. Multipl
e joins are easily executed,
simply by specifying multiple tables. Note that if in the select list, there
are duplicate column names due
to multiple tables, these columns must be fully qualified by adding the table
name in front or the column
name.
WHERE: This is how one controls the selection of rows during a retrieval.
One or more search condition
may be specified thus giving greater control over the retrieval. Of course,
using KEY items and SEARCH
items in the where clause accelerates the retrieval of the rows. If the
where clause is not specified, or the
selection criteria are not KEY or SEARCH items, the datasets will be scanned
serially.
GROUP BY: Using this clause, one identifies columns which are used for
grouping for aggregate
functions applied to a group of rows.
HAVING: This is how one further controls the retrieval of groups defined by
the GROUP BY clause. The
GROUP BY has to be specified in order to use this feature.
ORDER BY: This clause is used to sort the retrieved rows by the specified
column or columns in either
ascending or descending order. The columns can be specified by column name
or column number in the
select list.
If the WHERE is used, it takes precedence over the GROUP BY, HAVING and ORDER
BY. The rows
are selected before the groups are formed. If the GROUP BY clause is used,
SQL creates groups out of the
retrieved rows. If the HAVING clause is used, the groups which do not qualify
will be eliminated.
The selection criteria are many and quite powerful, but one must pay
attention lest a large select be sent
improperly specified, against a large dataset. SQL supports partial keys,
betweens, comparisons, exits,
ins, nulls, quantifieds.
SOME ISQL EXAMPLES
1- Retrieve all customer numbers and name of customers in Texas, sort them by
customer name in
ascending order:
SELECT customer_no, customer_name
FROM dbcust.cust_master
WHERE state = 'TX'
ORDER BY customer_name;
Figure 9. Example 1 - A simple select.
2- Retrieve all customer numbers, customer names and year-to-date orders, and
group them by
state:
SELECT customer_no, customer_name, ytd_sales
FROM dbcust.cust_master
GROUP BY state
ORDER BY customer_name;
Figure 10. Example 2 - Using GROUP BY in a select.
3- Now we are on a roll, let's retrieve the outstanding orders from the
orders file and select only
the customers which have orders outstanding for over $10,000. Further let's
group the orders by
customer and get the customer name as well.
SELECT c.cust_master.customer_no, customer_name, SUM(total_value)
FROM dbcust.cust_master c, dbcust.order_header o
WHERE c.customer_no=o.customer_no, order_status ='OP'
GROUP BY c.customer_no, customer_name
HAVING SUM(total_value) > 10000
ORDER BY c.customer_name;
Figure 11. Example 3 - A complex SELECT.
We have seen that it is quite straightforward to make a TurboIMAGE database
into an IMAGE/SQL
database. Only in trying it out, and experiencing for one's self will one be
able to get a glimpse at what is
now possible with this new environment.
CLIENT/SERVER Access to IMAGE/SQL
IMAGE/SQL ushers in OPEN SYSTEMS for IMAGE users. Whilst not a Relational
Data Base
Management System (RDBMS) in the true sense, IMAGE/SQL enables
relational-type access to IMAGE
data, and for virtually all users this will be more than sufficient. For
now...
The 3rd parties, now able to access IMAGE through its OPEN SYSTEMS interface,
are going to supply,
in fact are supplying now, client server-based tools which make
decision-support tasks a joy. There are
going to be many more tools, and opportunities. Well, let s see how this
works.
ON THE PC
We are now going to install a network for a PC to an HP3000, load some
communication software on the
PC, along with tools from the IMAGE/SQL Upgrade kit. We will download the
APIs and Information
access. At the end of this section, we will set up the database for Gupta
and ODBC API access.
Contructing the network
We are now going to add our PC to the HP3000 over a network. Chances are
very high that your HP3000
has a LANIC (LAN Interface Card) already on it. NS is most probably already
installed on it. So what
we want to do is prepare the PC and connect. We are going to map our steps
on the OSI model.
1. Physical Layer: I am using Ethernet 802.3 protocol running on an thin co-ax
. This layer is
concerned only with ensuring that the raw data get from one node to the
other, in whatever shape it s
in. It addresses the the actual physical cable and the electrical
characteristics of said cable.
2. Data-Link Layer: This layer is basically encompassed by the Ethernet
protocol. This is the layer
which packages the data into packets and puts them on the physical link. It
is here that the network
is managed in terms of packet creation and transfer, error detection and
control and also flow control.
It uses the CSMA/CD (Carrier Sense Multiple Access/Collision Detection)
protocol.
3. Network Layer: I use IP, or Internet Protocol. This layer and the next
one are usually talked
about, and addressed together: TCP/IP. This layer is the one concerned with
establising, maintaining
and terminating network connections. It is at this layer that the work of
translating logical addresses
and /or node names into physical addresses.
4. Transport Layer: I use TCP, or Transmission Control Protocol. The other
part of TCP/IP. This
layer is the one which makes sure that the data gets to where it is supposed
to go. If the data does not
get to the target syste, this layer will initiate the retransmission.
5. Session Layer: This layer is basically encompassed in the TCP/IP standard.
It is here that
communication between two nodes are coordinated. This is accomplished by
establishing a
conversation between the programs on the two nodes.
6. Presentation Layer: I use NS/VT, or NS Virtual Terminal. This is
proprietary to HP and this
protocol also encompasses the next layer. This is where the data is
reformated and any conversions
which are necessary are also handled here.
7. Application Layer: Again, I use NS/VT. This layer is acts as the
interface between the programs
on the HP3000 and on the PC. It also has functions such as FTP, a File
Transport Protocol; Telnet, a
terminal access to a server; NFS, Network File System; SNMP, Small Network
Management
Protocol; and SMTP, Simple Mail Transfer Protocol.
What I used:
1 co-ax cable with BNC connectors.
2 BNC T-Connectors
2 BNC Terminators.
For the PC:
386 or better MS/DOS PC
MS/DOS 3.3 or newer
Miscrosoft Windows 3.1
About 10 megabytes of disc space.
1 LAN card for the PC with the driver. Remember the driver name, you will
need it later.
TCP/IP Software for the PC. I used WRQ Reflection Network Series, 3000
connection version 2.11
The IP address.
The node, domain and organization names
For the Server:
The IMAGE/SQL Upgrade Kit
MPE/iX 4.0 or newer
ThinLAN 3000/XL and a LANIC card and ThinMAU.
The IP address.
The node, domain and organization names
Setting up the hardware.
Remember that a thin LAN connects to a card through a T-connector, not
directly into the card. The
thinlan must have a terminator at both end. So, at a minimum, you need two
T-connectors, 2
terminators, and one co-ax cable with 2 connectors.
On the HP3000, there is a ThinMAU where I plugged the T-connector and then
one end of the co-ax. On
the other side of the T, I placed a terminator. On the PC, I installed a
LAN card. I used a KATRON ET-
16TB costing $89.00. I loaded the card driver that came with the card, onto
my PC and installed the card
but not before I placed the jumper JP1 to SOFT ;. I then placed a
T-Connector at the appropriate spot
and connected the co-ax coming from the HP3000. On the other side, I
connected another co-ax which
leads to an HP9000/300 and the rest of the network. If I did not have any
other nodes, I would have
placed the other terminator instead.
The LAN card driver.
The KATRON came with the driver I needed, A:\NDIS\E200PLUS.DOS, and a setup
program TBSETUP.
I used TBSETUP to configure my card for the I/O base address, 300; the interru
pt or IRQ, 10; and the
connector type, BNC. I then cycled the power on my PC so that the
configuration took on the LAN card.
TCP/IP software on the PC.
WRQ has a comprehensive communications package that enables communications
between the PC and
the HP3000. This software is part of the Reflection Network Series, RNS. It
can also be used to support
Reflection 1 for Windows.
RNS 3000 connection provides access to the following protocols:
1. Telnet-MGR: This provides access to 2 TCP/IP standard, Telnet and FTP.
Since I have a
9000 on the network, and both the 3000 and the 9000 support FTP, I use this
connection.
2. LAT: (Local Area Transport) This is a DECnet protocol governing the
communications between terminals and hosts. It is required to emulate a termi
nal on a VAX/VMS. I
don t have a VAX, so I don t use this one.
3. VT-MGR (NS/VT): This is what we will use for our exercise. It handles the
communications
between the HP3000 and the PC.
Step 1, create a directory on your PC to hold the files.
C:\>MD HPPCAPI
Step 2, download the file HPPCAPI.PUB.SYS and unpack it.
Run Reflection and download the file HPPCAPI.PUB.SYS to HPPCAPI.EXE in the
HPPCAPI directory.
The
|
