NewsWire TestDrive: The ODBCLink to Data Mining and Extraction

Click here for WRQ sponsor message

DataExpress ODBCLink:
The Express Link
to Data Mining and Extraction

Review by John Burke

Second of two parts

DataExpress allows casual, unsophisticated users to rapidly select and extract data from HP 3000 data structures, so they can manipulate, calculate, summarize and reformat information and then download it to PCs or Macs. One tool DataExpress employs to do this magic is its optional ODBCLink interface.

Last month I covered the host-based, core portion of DataExpress. This month I will concentrate on the ODBCLink option and note some of its major differences from ODBCLink/SE.

A Level 2-compliant implementation of Microsoft’s Open Database Connectivity (ODBC) protocol, ODBCLink lets Windows-based applications and tools access TurboIMAGE natively, without relying on any Allbase/SQL overhead. It also connects clients to IMAGE/SQL, Oracle, and Allbase/SQL databases, as well as KSAM and MPE files on the HP 3000. It does all this in a client server environment secured and controlled at the server level by DataExpress.

ODBCLink is a more feature-rich version of stripped down middleware called ODBCLink/SE, which is distributed in the Express 3 version of MPE/iX 5.5 as part of an agreement with Hewlett-Packard. ODBCLink/SE ships without the ODBCLink control/security features and without the ability to access TurboIMAGE directly, or use KSAM or MPE files.

What does ODBCLink do
and how does it work?

ODBCLink is an implementation of Microsoft’s Open Database Connectivity (ODBC) protocol for communication with the HP 3000. ODBC was designed to enable Microsoft Windows-based applications and tools to access RDBMS databases over a LAN in a client-server environment. In M.B. Foster’s implementation of ODBCLink, application developers and end-users can take advantage of the PC’s strengths and the server’s strengths at the same time. The PC is great at presentation (the graphical user interface or GUI) and at dedicating processing power for applications such as spreadsheets. The server/host is great at raw storage capacity, security, integrity and database management.

ODBCLink on the server (the listener process) runs on all supported versions of MPE. On the client, the ODBCLink driver is compatible with Windows 3.1, 3.11, Windows 95 and Windows NT. Both a 16-bit and 32-bit driver come with the package. The 16-bit driver connects via Winsock, PPL, serial direct connect and serial modem. The 32-bit driver provides connection via Winsock over a LAN. (The latest version of the ODBCLink 32-bit client now also supports serial direct and serial modem connections.) A 32-bit client application such as Office95 or Office97 needs the 32-bit ODBCLink; a 16-bit client application needs the 16-bit ODBCLink. You cannot run 16-bit ODBCLink from a 32-bit client or 32-bit ODBCLink from a 16-bit client. The server side of ODBCLink is the same regardless of which client is used. M.B. Foster has been shipping 16-bit ODBCLink for over four years and 32-bit ODBCLink for over two years.

ODBCLink can directly support TurboIMAGE databases because it has its own SQL syntax parser that supports most SQL commands, including outer joins between tables in the same or different databases. DataExpress’ file definition generation utility (FDGEN) provides access to KSAM and MPE files and can also be used to “redefine” TurboIMAGE datasets. ODBCLink can also access DataExpress’ DataCatalog which, if you remember from last month, provides features such as Data Views (sort of pre-defined tables that may be made up of fields from multiple datasets and even databases) and field level security. For PowerHouse users, ODBCLink also supports the PowerHouse dictionary, including subfiles.

The server listener process can use IMAGE and TPI keys to optimize queries on a database. This optimization is transparent to the user. Of course, depending upon the query, the optimization could result in a serial read of a very large number of records. With ODBCLink you can limit the amount of time your users spend doing serial reads by entering a small number (for example 1 minute or 60000ms) in the Sort Timeout field in the data source setup screen (Figure 1). If this time is exceeded, the server will generate a “Host database timeout” error and the query will fail.

Supported client software includes:

• Cognos Axiant and Impromptu
• Crystal Reports
• Jetform
• Lotus 123
• MS-Access, Excel/Query
• Paradox
• PowerBuilder
• Visual Basic
• Visual C++
• Visual FoxPro

For this review, I tested ODBCLink with MS-Access and MS-Query. The Host Configuration Utility (Figures 2-4) is a client-server program that uses ODBCLink. It is written in Visual Basic and the source is included as an example of using ODBCLink programmatically. A special note for those who may want to write client-side applications using ODBCLink: with remote procedure call support, anything you can stuff into an XL can be called from your client application. This gives the developer tremendous flexibility and power.

Installation, Documentation and Support

Assuming you have already installed DataExpress (with the ODBCLink option), then to use ODBCLink you need only:

• Stream a job to create the configuration database on the server
• Start the listener job on the server if using Winsock (note that more than one listener job can be running)
• Install the client software on each PC that will be using ODBCLink and configure data sources (Figure 1)
• Install the Host Configuration software on one or more PCs that will be used for administration purposes
• Configure users and grant access to tables and datasets using the client-server Host Configuration program (Figures 2-4)

It is generally easier than it sounds. The only difficulty I had was with changes Microsoft has made in its products since the documentation was prepared.

The ODBCLink documentation is only adequate, but that is not M.B. Foster’s fault. Installing ODBCLink is not the problem - the documentation is fine for this. The problem comes in documenting how it works with various Microsoft and other manufacturer’s products. I had problems with different versions of Microsoft products. Even though ODBC is a Microsoft promoted “standard,” various versions of Microsoft software behaved differently.

For example, initially I could not get the version of MS-QUERY that comes with Office 97 Professional to work, though MS-Access from the same package worked just fine (Figures 7-8). An older version of MS-QUERY also worked just fine (Figures 5-6). Here is where M.B. Foster’s support capability passed my tests: its newly enhanced web site contained a note in the FAQ section on how to get MS-Query from Office 97 to work. Telephone technical support is provided from 8:00 a.m. to 8:00 p.m. Eastern time Monday through Friday.

Why pay thousands
when ODBCLink/SE is free?

The question you should probably ask yourself instead is: Am I going to use ODBC for anything more than casual, occasional access to a handful of IMAGE databases? If the answer is “yes,” then you need to consider the hidden costs of the “free” ODBCLink/SE:

• The overhead of IMAGE/SQL, both processing and management. While I have admired the technical effort that went into creating IMAGE/SQL, I’ve never liked the management and resource overhead it imposed. Until recently, the SQL optimizer did not even recognize IMAGE keys and still may not recognize TPI keys. While various tool vendors have helped ease the management load, it is still considerable. If you are not already familiar with RDBMSs in general and how to “attach” an IMAGE database to a DBE, you should definitely practice before committing to using ODBCLink/SE in any kind of production environment.

• ODBCLink supports access to KSAM and MPE files and Oracle databases. ODBCLink/SE does not. If you are like most sites that have developed their own software, you probably have some KSAM and/or MPE files that contain data needed by a client-server application.

• ODBCLink supports serial direct and modem connections. ODBCLink/SE does not.

• With the ODBCLink/DataExpress combination you can run batch jobs to extract data from databases which then can be easily accessed from the client via ODBCLink.

• ODBCLink supports remote procedure call for the development of client applications. ODBCLink/SE does not.

• ODBCLink supports DataExpress’ Data Views. ODBCLink/SE does not. Do you really want to have to explain database structure and the concept of keyed access versus serial access to end-users? And then pray someone doesn’t initiate a serial read of a million records as part of a join? Or would you rather define a simple view of the data that hides all the confusing underlying complexity from end-users who neither need nor care to know?

• Security. There isn’t much with ODBCLink/SE. Need I say more?

Conclusion

If you are serious about using ODBC as the middleman to access IMAGE data on the HP 3000 from PC applications in a client-server mode, then DataExpress with the ODBCLink option is definitely worth considering. The ODBCLink option to DataExpress runs on all supported versions of MPE/iX.

ODBCLink option
for DataExpress

M.B. Foster Associates Ltd.
82 Main Street South
P.O. Box 580
Chesterville, ON Canada
K0C 1H0

Tel: 613.448.2333
800.ANSWERS (267.9377)
Fax: 613.448.2588
E-mail: sales@mbfoster.com

The ODBCLink option of DataExpress interfaces PC applications with data on the HP 3000 using Microsoft’s ODBC protocol. ODBCLink can directly access TurboIMAGE databases as well as IMAGE/SQL, Oracle and ALLBASE/SQL. Using DataExpress’s file definition dictionary (FDGEN), access is also provided to KSAM and MPE files. DataExpress’ DataCatalog provides features such as Data Views and field level security in a client-server ODBCLink environment.

The host portion of the ODBCLink option to DataExpress runs on all supported versions of MPE/iX. The client portion runs on Windows 3.1, 3.11, 95 and NT. Pricing for the ODBCLink Bundle which includes Core DataExpress ranges from $3,000 to $16,000. A time limited trial version of DataExpress is available upon request.




Click here for WRQ sponsor message
	DataExpress ODBCLink: The Express Link to Data Mining and Extraction

		Review by John Burke Second of two parts DataExpress allows casual, unsophisticated users to rapidly select and extract data from HP 3000 data structures, so they can manipulate, calculate, summarize and reformat information and then download it to PCs or Macs. One tool DataExpress employs to do this magic is its optional ODBCLink interface. Last month I covered the host-based, core portion of DataExpress. This month I will concentrate on the ODBCLink option and note some of its major differences from ODBCLink/SE. A Level 2-compliant implementation of Microsoft’s Open Database Connectivity (ODBC) protocol, ODBCLink lets Windows-based applications and tools access TurboIMAGE natively, without relying on any Allbase/SQL overhead. It also connects clients to IMAGE/SQL, Oracle, and Allbase/SQL databases, as well as KSAM and MPE files on the HP 3000. It does all this in a client server environment secured and controlled at the server level by DataExpress. ODBCLink is a more feature-rich version of stripped down middleware called ODBCLink/SE, which is distributed in the Express 3 version of MPE/iX 5.5 as part of an agreement with Hewlett-Packard. ODBCLink/SE ships without the ODBCLink control/security features and without the ability to access TurboIMAGE directly, or use KSAM or MPE files. What does ODBCLink do and how does it work? ODBCLink is an implementation of Microsoft’s Open Database Connectivity (ODBC) protocol for communication with the HP 3000. ODBC was designed to enable Microsoft Windows-based applications and tools to access RDBMS databases over a LAN in a client-server environment. In M.B. Foster’s implementation of ODBCLink, application developers and end-users can take advantage of the PC’s strengths and the server’s strengths at the same time. The PC is great at presentation (the graphical user interface or GUI) and at dedicating processing power for applications such as spreadsheets. The server/host is great at raw storage capacity, security, integrity and database management. ODBCLink on the server (the listener process) runs on all supported versions of MPE. On the client, the ODBCLink driver is compatible with Windows 3.1, 3.11, Windows 95 and Windows NT. Both a 16-bit and 32-bit driver come with the package. The 16-bit driver connects via Winsock, PPL, serial direct connect and serial modem. The 32-bit driver provides connection via Winsock over a LAN. (The latest version of the ODBCLink 32-bit client now also supports serial direct and serial modem connections.) A 32-bit client application such as Office95 or Office97 needs the 32-bit ODBCLink; a 16-bit client application needs the 16-bit ODBCLink. You cannot run 16-bit ODBCLink from a 32-bit client or 32-bit ODBCLink from a 16-bit client. The server side of ODBCLink is the same regardless of which client is used. M.B. Foster has been shipping 16-bit ODBCLink for over four years and 32-bit ODBCLink for over two years. ODBCLink can directly support TurboIMAGE databases because it has its own SQL syntax parser that supports most SQL commands, including outer joins between tables in the same or different databases. DataExpress’ file definition generation utility (FDGEN) provides access to KSAM and MPE files and can also be used to “redefine” TurboIMAGE datasets. ODBCLink can also access DataExpress’ DataCatalog which, if you remember from last month, provides features such as Data Views (sort of pre-defined tables that may be made up of fields from multiple datasets and even databases) and field level security. For PowerHouse users, ODBCLink also supports the PowerHouse dictionary, including subfiles. The server listener process can use IMAGE and TPI keys to optimize queries on a database. This optimization is transparent to the user. Of course, depending upon the query, the optimization could result in a serial read of a very large number of records. With ODBCLink you can limit the amount of time your users spend doing serial reads by entering a small number (for example 1 minute or 60000ms) in the Sort Timeout field in the data source setup screen (Figure 1). If this time is exceeded, the server will generate a “Host database timeout” error and the query will fail. Supported client software includes: • Cognos Axiant and Impromptu • Crystal Reports • Jetform • Lotus 123 • MS-Access, Excel/Query • Paradox • PowerBuilder • Visual Basic • Visual C++ • Visual FoxPro For this review, I tested ODBCLink with MS-Access and MS-Query. The Host Configuration Utility (Figures 2-4) is a client-server program that uses ODBCLink. It is written in Visual Basic and the source is included as an example of using ODBCLink programmatically. A special note for those who may want to write client-side applications using ODBCLink: with remote procedure call support, anything you can stuff into an XL can be called from your client application. This gives the developer tremendous flexibility and power. Installation, Documentation and Support Assuming you have already installed DataExpress (with the ODBCLink option), then to use ODBCLink you need only: • Stream a job to create the configuration database on the server • Start the listener job on the server if using Winsock (note that more than one listener job can be running) • Install the client software on each PC that will be using ODBCLink and configure data sources (Figure 1) • Install the Host Configuration software on one or more PCs that will be used for administration purposes • Configure users and grant access to tables and datasets using the client-server Host Configuration program (Figures 2-4) It is generally easier than it sounds. The only difficulty I had was with changes Microsoft has made in its products since the documentation was prepared. The ODBCLink documentation is only adequate, but that is not M.B. Foster’s fault. Installing ODBCLink is not the problem - the documentation is fine for this. The problem comes in documenting how it works with various Microsoft and other manufacturer’s products. I had problems with different versions of Microsoft products. Even though ODBC is a Microsoft promoted “standard,” various versions of Microsoft software behaved differently. For example, initially I could not get the version of MS-QUERY that comes with Office 97 Professional to work, though MS-Access from the same package worked just fine (Figures 7-8). An older version of MS-QUERY also worked just fine (Figures 5-6). Here is where M.B. Foster’s support capability passed my tests: its newly enhanced web site contained a note in the FAQ section on how to get MS-Query from Office 97 to work. Telephone technical support is provided from 8:00 a.m. to 8:00 p.m. Eastern time Monday through Friday. Why pay thousands when ODBCLink/SE is free? The question you should probably ask yourself instead is: Am I going to use ODBC for anything more than casual, occasional access to a handful of IMAGE databases? If the answer is “yes,” then you need to consider the hidden costs of the “free” ODBCLink/SE: • The overhead of IMAGE/SQL, both processing and management. While I have admired the technical effort that went into creating IMAGE/SQL, I’ve never liked the management and resource overhead it imposed. Until recently, the SQL optimizer did not even recognize IMAGE keys and still may not recognize TPI keys. While various tool vendors have helped ease the management load, it is still considerable. If you are not already familiar with RDBMSs in general and how to “attach” an IMAGE database to a DBE, you should definitely practice before committing to using ODBCLink/SE in any kind of production environment. • ODBCLink supports access to KSAM and MPE files and Oracle databases. ODBCLink/SE does not. If you are like most sites that have developed their own software, you probably have some KSAM and/or MPE files that contain data needed by a client-server application. • ODBCLink supports serial direct and modem connections. ODBCLink/SE does not. • With the ODBCLink/DataExpress combination you can run batch jobs to extract data from databases which then can be easily accessed from the client via ODBCLink. • ODBCLink supports remote procedure call for the development of client applications. ODBCLink/SE does not. • ODBCLink supports DataExpress’ Data Views. ODBCLink/SE does not. Do you really want to have to explain database structure and the concept of keyed access versus serial access to end-users? And then pray someone doesn’t initiate a serial read of a million records as part of a join? Or would you rather define a simple view of the data that hides all the confusing underlying complexity from end-users who neither need nor care to know? • Security. There isn’t much with ODBCLink/SE. Need I say more? Conclusion If you are serious about using ODBC as the middleman to access IMAGE data on the HP 3000 from PC applications in a client-server mode, then DataExpress with the ODBCLink option is definitely worth considering. The ODBCLink option to DataExpress runs on all supported versions of MPE/iX.
ODBCLink option for DataExpress M.B. Foster Associates Ltd. 82 Main Street South P.O. Box 580 Chesterville, ON Canada K0C 1H0 Tel: 613.448.2333 800.ANSWERS (267.9377) Fax: 613.448.2588 E-mail: sales@mbfoster.com The ODBCLink option of DataExpress interfaces PC applications with data on the HP 3000 using Microsoft’s ODBC protocol. ODBCLink can directly access TurboIMAGE databases as well as IMAGE/SQL, Oracle and ALLBASE/SQL. Using DataExpress’s file definition dictionary (FDGEN), access is also provided to KSAM and MPE files. DataExpress’ DataCatalog provides features such as Data Views and field level security in a client-server ODBCLink environment. The host portion of the ODBCLink option to DataExpress runs on all supported versions of MPE/iX. The client portion runs on Windows 3.1, 3.11, 95 and NT. Pricing for the ODBCLink Bundle which includes Core DataExpress ranges from $3,000 to $16,000. A time limited trial version of DataExpress is available upon request.