|
by Tom Cabanski Open Database Connectivity
(ODBC) is a standard call-level interface for connecting applications to databases. Over
the last several years it has become the most popular way for connecting custom and
off-the-shelf PC applications to relational databases and other data sources. As a result,
there are robust ODBC drivers available for every major database engine. ODBC is proven
technology that has been refined in the fire of literally millions of implementations.
ODBC is primarily concerned with passing SQL (Structured Query Language) commands to a
server and getting results back. SQL is a standard way for manipulating and getting data
stored in a relational database. The HP 3000 n-tier Web site, http://www.objective-advantage.com/hpntier,
includes links to resources that will help you learn more about SQL.
When we started to develop the
HP 3000 n-tier architecture, we suspected that our biggest problem would be the
performance of ODBC access to the HP 3000. We were very wrong. Instead, we experienced a
variety of problems with the stability and completeness of the available IMAGE ODBC
drivers. This article compares the drivers we looked at, discusses the problems we
experienced, and outlines the solutions we are currently implementing in our pilot
project.
The Competitors
We started with the M.B. Foster ODBC driver (Version 5.56.13) because of the vendor's
excellent reputation in the HP 3000 community. This driver works directly with IMAGE,
KSAM, flat files, and other files on the HP 3000. It does not require IMAGE/ SQL.
The second driver we looked at comes from Minisoft. Many HP 3000 folks may think of it
as the company that competes with WRQ in the terminal emulation market. However,
Minisoft has become more of a client-server middleware company with a very strong presence in the
HP 3000 world. Their driver (Version 2.01.4) works directly with IMAGE, KSAM, flat files,
and other files. It does not require IMAGE/SQL.
Installation
Each of these drivers has two major components to install: a host side and a client
side. The host side is a job that runs in the CS queue. It interprets requests from the
client, services those requests by reading or writing data, and returns results to the
client. The client side passes requests to the server side and returns data from the
server side to the requesting program.
M.B. Foster's server installation process will be very familiar to HP 3000 users. It
consists of restoring a tape and running some command files. Unfortunately, the tape we
received did not contain the proper licensing information for certain other M.B. Foster
applications we had previously installed on the machine. A quick call to M.B. Foster's
technical support solved this problem.
Minisoft's server installation process is quite unusual. It runs from the PC and uses a
mini version of Minisoft's terminal emulator to execute a command file that creates the
necessary accounts, uploads various files, and installs them on the HP 3000. The initial
install on our MPE/iX 5.0 machine failed because we did not have an up-to-date version of
the CSL utility for extracting LZW files on our machine. A call to Minisoft's technical
support solved this problem.
M.B. Foster's client installation process is a little rough around the edges. The basic
installation process is easy enough: download a self-extracting archive, run it to extract
the setup files, and run setup. However, the installer does not install CTL3D32.DLL, which
may or may not be present on your system. This problem is annoying and unnecessary. Other
vendor installation programs, including the one we'll build at the end of this series, can
install the correct version of this file when needed.
Minisoft's client installation program is as smooth as silk. You run the client
installation program, which extracts the necessary setup files, runs setup, and guides you
through the installation process. We installed the Minisoft client side on a variety of
PCs with no problems whatsoever.
Configuring the Listeners
Both of these products require TPI to take advantage of indices created by Superdex or
Omnidex. In our case, we used OMNIUTIL to turn TPI on for all our databases. The process
for Superdex should be similar.
The sample listener job that M.B. Foster ships with its driver is shown in Listing 1. M.B. Foster's technical support suggested that we
create and stream a separate listener job for each of our three accounts (development,
parallel test, and production) by making copies and changing the job card and the value
assigned to the odbc_socket_id variable. We decided to use odbc_socket_id 21245 for
development, 21246 for parallel test, and 21247 for production. The jobs were also changed
to log on as the database owner of their respective accounts. We streamed all three of
these jobs to start the server side of the driver.
Listing 2 is the default listener job that shipped with
the Minisoft driver. Minisoft technical support did not recommend any changes. We streamed
this job to start the server side of the Minisoft driver.
Configuring the Client
A Data Source Name (DSN) contains all the configuration data an ODBC driver needs to
connect with a server. Applications access ODBC through a DSN. Both drivers interface with
the ODBC control panel applet to allow the user to create DSNs. The ODBC control panel
applet main screen is shown in Figure 1. User DSNs are
available to the user who creates them, while system DSNs are available to anyone who logs
on to the machine. For simplicity we'll create system DSNs.
To create an M.B. Foster DSN we pressed the Add button and selected the M.B. Foster
driver to bring up the M.B. Foster DSN configuration dialog. Figure
2 shows the dialog filled in to allow a connection to our parallel test account.
We set the user name and password fields to "?" to force the driver to prompt
for a password when this DSN is accessed. The TCP/IP address of our HP 3000 machine is
223.223.223.1, and 21246 is the port number of the listener for the parallel test account.
Although we found the configuration to be simple, we found the configuration dialog to be
a bit annoying to use because the tab key does not work properly. The next release version
of the driver is supposed to fix this problem.
Next, we had to install M.B. Foster's HOSTCONF utility on a PC. The installation
process for this tool worked in the same way as the installation process for the client
side of their driver. Through HOSTCONF, we configured a list of users, a list of HP 3000
databases and files, and a list of files and IMAGE tables each user could access. We
tested two versions of this tool and found both to be functional but clumsy.
The Minisoft DSN configuration dialog contains six tabs reflecting the fact that
Minisoft offers a wide array of options for configuring security and data access. A basic
DSN requires filling in the Data Source, Connection, and Databases tabs. Our initial
settings are shown in Figures 3, 4,
and 5. We found the DSN configuration dialog elegant but a
little intimidating because of the number of options available.
Unlike the M.B. Foster driver, the Minisoft driver includes an option to configure
available databases in the DSN. Figure 6 shows the
Add/Configure database dialog that makes this possible. We used this option for our
initial testing.
Initial Testing
Initial testing was conducted by issuing queries from Access through ODBC to the HP
3000. We tested Version 5.56.13 of the M.B. Foster driver and Version 2.01.4 of the
Minisoft driver using Windows NT Workstation 4.0 (with service pack 4) as the client. The
server processes were running in the CS queue of an HP 967 running MPE/iX 5.0 under normal
production load.
The M.B. Foster driver supports all the expected elements of the SQL92 syntax,
including the JOIN operator. We were able to perform simple queries as well as queries
that joined two tables, as long as we joined on indexed fields (IMAGE or Omnidex).
Minisoft also supports all the expected elements of SQL92 including the JOIN operator.
Again, we were able to perform simple queries as well as queries that joined two tables as
long as we joined on indexed fields (IMAGE or Omnidex).
M.B. Foster requires the user to configure a file description (FD) on the server in
order to access non-IMAGE data sources. The process for creating an FD is about as
difficult as creating a database schema: You create a source file and compile it with an
included utility. Of course, this process requires a high degree of familiarity with the
HP 3000. We were able to create and use an FD to access a KSAM file with ease.
Minisoft, on the other hand, requires a schema definition for non-IMAGE sources. They
include a nifty PC-based configuration tool, shown in Figure 7,
that makes the process painless. We had no problem using the schema editor to set up
access to a KSAM file.
Omnidex Support and Query Optimization
Both drivers try to support TPI. Both drivers fall short in this area. I understand
that a large part of the problem has to do with the differences between the market leaders
in this area as well as the difficulty of deciding when it makes sense to use TPI indices
and when it makes sense to use native indices. Although I asked both vendors for a
detailed written explanation of how they used TPI, neither chose to provide the
information. Therefore, the rest of this discussion is based on the behavior we observed
during testing.
M.B. Foster generally takes good advantage of TPI indices. The driver gives precedence
to TPI indices except on key items of manual masters. In the case in which a partial key
search ("xxx@") is requested on a field that happens to be a native key item
that is indexed with TPI, the driver does a serial read through the table instead of
taking advantage of the TPI index. This has a significant impact on performance. This
problem will probably be fixed in a future release.
Minisoft also takes good advantage of Omnidex keys. In fact, in many ways they do a
better job than M.B. Foster in this area. However, they too have problems with key items
that are also TPI indices. They are aware of this problem and plan to fix it in the next
release.
M.B. Foster does a good job of optimizing the performance of simple queries. The
optimizer looks at the WHERE clause and determines which index of the first table in the
table list will give the best performance. This works fine as long as the query writer
remembers to put the table that will give the best performance first. For example, the
following two SELECT statements will bring back the same result set. Note that the
ITEM_CODE is an OMNIDEX key:
SELECT * FROM ORDER_MAST OM INNER JOIN ORDER_DTL OD ON
OM.ORDER_NO = OD.ORDER_NO WHERE OD.ITEM_CODE LIKE "9@"
SELECT * FROM ORDER_DTL OD INNER JOIN ORDER_MAST OM ON
OM.ORDER_NO = OD.ORDER_NO WHERE OD.ITEM_CODE LIKE "9@"
However, M.B. Foster's driver will serially read order detail in the first case instead
of using the Omnidex key. This results in very bad performance because the driver ends up
doing a serial read of a very large set. The second case does, in fact, use the Omnidex
key to execute quickly. A good optimizer should eliminate the need for this kind of hand
optimization. M.B. Foster recognizes this weakness and intends to redesign the optimizer
sometime in 1999.
Minisoft's driver has a much better optimizer. It knows how to look beyond the first
table in the table list to find the best access path. In addition, Minisoft's technical
support representative indicated that the optimizer looks to a configuration table to
determine how it goes about optimizing the query. This means that Minisoft can tweak the
behavior of the optimizer to fit the needs of a particular install.
Load Testing
Establishing a connection to the HP 3000 from either of these drivers takes a very long
time. In fact, connection time can account for more than 90 percent of the total time
required to execute a simple query. This time will vary depending on the number of tables
the driver is configured to access and the load on the HP 3000. We observed connection
overhead in excess of 10 seconds in some cases.
The HP 3000 n-tier architecture gets around this by using connection pooling. The
application server maintains a list of open connections that are shared among many users.
Therefore, the connection overhead is paid only the first time a connection is used.
Subsequent ODBC accesses avoid the connection overhead completely. Connection pooling is
common in applications that use MTS or serve Web users.
M.B. Foster's 5.56.13 driver does not support connection pooling. It crashed early and
often during our attempts to test connection pooling. Although this issue was not
documented, M.B. Foster technical support was quick to confirm that the 5.56.13 release
was never designed to support connection pooling. M.B. Foster hopes to have a new version
of their driver that supports connection pooling long before you read this. Check out our
Web site to see if they pulled it off.
M.B. Foster does, however, perform well if you don't use connection pooling. CPU usage
on the HP 3000 was below 1 percent even when executing complex queries. The 5.56 release
hogs the CPU on the PC side, but this problem should be resolved in the next version of
their driver.
Minisoft's driver passed the connection pooling tests with ease. CPU usage on the HP
3000 remained low even when supporting 80 clients simultaneously. Performance on the PC
side was also excellent. Minisoft uses more network resources than M.B. Foster, but
neither driver hogged the network.
Other Important Features
Both drivers have facilities for normalizing dates and dealing with things such as
implied decimal points in numeric fields. M.B. Foster requires the use of FDs for this,
while Minisoft supplies a Windows tool to create schemas. Both approaches work, but
Minisoft's is much easier to work with.
M.B. Foster allows the PC client to call routines stored in HP 3000 XL files using the
SQL92 standard CALL statement. There are a slew of bugs and limitations in this area, but
we were able to successfully call several COBOL routines.
Although Minisoft does not currently offer or plan to offer such a facility through
their ODBC driver, they do make a product called Middleman that can be used to access
IMAGE database and other PC files directly from any PC application. Middleman also
includes a way to write custom services that can be accessed from the PC. Overall,
Middleman is far more capable than the XL calling functionality found in the M.B. Foster
ODBC driver. Middleman can be bundled with the ODBC driver at a significant discount.
If you use M.B. Foster's report writer, you will like the way their ODBC driver shares
FDs and Data Catalogs with the report writer. However, there are some bugs in this area
that keep the ODBC driver from taking full advantage of this integration. Minisoft
does
not offer equivalent functionality. In addition, M.B. Foster data catalogs allow the
creation of database views that can be accessed from the ODBC driver. The data catalog is
an extra cost option.
ODBC Drivers and the HP 3000 N-Tier Architecture
Minisoft appears to be a nimble company with strong developers and a solid foundation.
Their ODBC driver is not feature rich, but what's there is highly polished. Middleman
should allow us to work around any shortcomings we find. Like many other PC software
companies, Minisoft is releasing new driver versions as quickly as they add features.
M.B. Foster, on the other hand, has a feature-rich product that seems to stand on a
shaky foundation. Although the driver is fine for most reporting applications, it lacks
the stability we need to build a transactional system. In addition, the PC-side installers
and configuration tools lack the professional polish we expect from a topnotch vendor.
For now, we are working with both vendors. The next article in this series will explain
which vendor won and why. It will also cover the details of making business objects work
with HP 3000 data.
Tom Cabanski is the president of Objective Advantage, Inc., which specializes in
bringing n-tier client-server solutions to businesses. He has worked on the HP 3000 for
the last ten years.
|
