If you want to use Microsoft ActiveX Data Objects (ADO) to access AS/400 information, the best way to do it is by using an OLE DB provider. IBM supplies its own IBM OLE DB provider for accessing AS/400 tables and data queues with Client Access for Windows 95/NT (the Windows 95/NT client) and AS/400 Client Access Express for Windows (the Express client). Given this, why would any other company create an OLE DB provider and attempt to compete with a free product, and why would you want to consider paying for this functionality?
In this article, I’m going to examine some of the common problems with IBM’s OLE DB provider and look at some competitive alternatives for providing OLE DB support within your PC applications. Even though the IBM OLE DB provider is free with the Windows 95/NT client and Express client, there are always certain situations in which you are going to need an alternative. I’ll examine your alternatives and explain why you may want to consider them for your shop.
What’s Wrong with IBM’s OLE DB Provider?
Most people want an OLE DB provider so they can use PC-based reporting tools, program in Visual Basic, or develop Active Server Page (ASP) applications that talk to the AS/400. It is in the last two applications where IBM’s OLE DB provider falls down.
If you have Client Access for Windows 95/NT, the first headache lies in obtaining support for the Microsoft OLE DB 2.0 specification. If you’re running Client Access for Windows 95/NT V3R1M3, IBM’s OLE DB provider supports ActiveX Data Objects (ADO) versions 1.0 in the base version. ADO version 1.5 and 2.0 can also be supported by the V3R1M3 product if you install service pack SF56517 or later. However, if you’re planning on using Microsoft’s ADO 2.1 or 2.5 specifications, you will need to upgrade your V3R1M3 Windows 95/NT client software to Client Access for Windows 95/NT V3R2M0 or Express client. The V3R2M0 Windows 95/NT client supports ADO 1.0 in its
base version and adds support for ADO 1.5 and 2.0 when you install service pack SF55446 or later. In addition, the product can support ADO 1.5 and ADO 2.0 when you download and install the files in the IBM AS/400 SDK for ActiveX and OLE DB Service Pack 2 (which is available at www.as400.ibm.com/clientaccess/oledb/ServicePacks.htm). In addition to providing additional ADO support, service pack 2 provides fixes for the Visual Basic 6.0 (VB) incompatibility problems that have plagued the SDK and OLE DB provider since it was first introduced, including the ability to run the AS/400 SDK plug-in
inside the VB 6.0 Integrated Development Environment (IDE). There is a catch, however. Service pack 2 will only display the AS/400 SDK VB menu items in English, no matter what language was originally installed. Also, you may get an error message the first time you start VB after you install the plug-ins. This message may say that VB cannot load the old add-in and it may ask you if you want to remove the old add-in. If this occurs, IBM recommends that you answer "Yes." For instructions on downloading and installing the patch, see the Readme.txt file on that site. Also note that the SDK and OLE DB service packs are not being folded into the next Windows 95/NT V3R2M0 service pack (which was scheduled to be released on October 29, 1999). The AS/400 SDK for ActiveX and OLE DB and the OLE DB provider in the Windows 95/NT client have always been provided as "as-is" products that are not officially supported by IBM. As such, the patches provided by IBM for these products are considered separate service packs that are not covered under standard support. IBM offers a Client Access OLE DB support Web page (www.as400.ibm.com/clientaccess/oledb/comment.htm) where you can post question and comments, but you are not guaranteed a response.
As you can see, there are plenty of twists and turns with using IBM's OLE DB provider under Windows 95/NT V3R2M0.
For the latest information on VB 6.0 and SDK and OLE DB compatibity, see APAR II11869 "SDK for ActiveX and OLE DB-Compatibility with Visual Basic 6.0," at the Client Access for Windows 95/NT Informational APAR site.
For Express client, the picture is a lot brighter as IBM supports all current versions of ADO (versions 1.0, 1.5, 2.0, 2.1, and 2.5) as long as service pack SF57098 or later is installed on your PC. In Express client, the OLE DB provider and SDK for ActiveX and OLE DB have been folded into the Client Access Express Toolkit and they are supported by IBM. This means you can make support calls for the Express client version and all fixes will now be included in Express client service packs. The most significant problem with the Express client version is that it does not support VB 4.0. In addition, since IBM has frozen development on the Windows 95/NT client in favor of extending Express client’s capabilities, it appears that your best bet for maintaining complete compatibility with Microsoft’s ADO specification is to upgrade your PCs to Express client. However, not everyone is able or willing to upgrade to Express client at this moment so, if you want to use ADO 2.1 or above, you must at least be at V3R2M0 of the Windows 95/NT client. For the latest information on using IBM’s OLE DB Provider with ADO, see Information APAR II11872, “OLE DB Provider—Support for Microsoft Products and ADO Versions,” at the Client Access for Windows 95/NT Informational APAR Web site (www.as400.ibm.com/clientaccess/caiixd1.htm).
As to the ASP issue, it has been reported on Usenet (the public Internet newsgroups that can be found at comp.sys.ibm.as400. misc) that the IBM OLE DB provider has scalability problems, a fact that I have also noted in my testing. The OLE DB provider works fine during development, but it has problems in production, particularly when a large number of users access the Web server simultaneously. It almost seems as if the Client Access OLE DB provider serializes SQL requests rather than executing them in a multithreaded model; hence many requests appear to be pushed into a buffer and executed one at a time. My experience has been that there is a noticeable performance lag when many users are connected via IBM’s OLE DB provider and pounding a Web server vs. one or two users pummeling the server.
If you’re affected by any of these problems, you’re probably wondering who else makes OLE DB providers for the AS/400. In the rest of this article, I will discuss vendors and products that provide competitive solutions to the IBM OLE DB provider and how they can benefit your shop.
Some Alternatives
Amalgamated Software of North America, ASNA
You might be familiar with ASNA (www.asna.com) because of its Visual RPG product, but ASNA also makes an AS/400 gateway product called DataGate/400. DataGate/400 is a client/server product that allows for high-speed record-level access to AS/400 data. The DataGate/400 server piece sits on your AS/400 and is accessible via TCP/IP or Advanced Program-to-Program Communications (APPC) from the Windows client machine. Currently the company has an OLE DB provider for the DataGate/400 product in beta and it should be released by first quarter of 2000.
DataGate/400 is more than a gateway product as it has a companion product called Acceler8DB. Acceler8DB is a Windows implementation of the AS/400 DB2/400 file system. Using Acceler8DB and DataGate400 together allows a program to seamlessly talk to AS/400 file systems no matter where they are located, on a PC or on the AS/400. If propagating AS/400 databases into PC-land interests you, you should check these products out.
HiT Software, Inc.
HiT Software, Inc. (www.hit.com) makes two OLE DB access products for the AS/400: HiT OLEDB Server/400 and HiT OLEDB/400. HiT OLEDB Server/400 is an OLE DB provider optimized for Windows NT Server applications, such as Active Server Pages development. The provider supports multithread-safe execution and is able to communicate with your AS/400 via TCP/IP or APPC.
The package is available in a developer toolkit form, HiT OLEDB Server/400 Developer Edition, which contains a lot of example code for Active Server Pages and Visual Basic development. The code makes extensive use of the OLE DB provider and should serve first-time OLE DB users well by providing a host of examples.
When you buy the developer editions of HiT’s products, you also get powerful tracing and debugging tools and a neat SQL Package manager. The package manager makes life nice for the programmer by making it easier to deal with AS/400 package files that are created by the query optimizer.
The HiT OLEDB/400 product is an OLE DB provider for any 32-bit Windows platform. The only real difference between the HiT OLEDB Server/400 edition and this edition is that the OLEDB/400 product is not optimized for running on a Windows NT Server. HiT OLEDB/400 is also available in a Developer Toolkit form that, again, contains code samples for ASP or VB and the neat toolbox with tracing and debugging tools, just like the Server/400 edition.
For both products, HiT took the approach of communicating with the AS/400 Optimized Server (the QSERVER subsystem that Client Access also uses). The main difference between HiT and IBM is that HiT offers a more complete implementation of the OLE DB specification than the IBM OLE DB provider. According to HiT, most people buying their providers are either developing ASP scripts that need to access the AS/400 or need to connect Microsoft SQLServer to their AS/400(s).
For the security-conscious, HiT’s providers also offer support for encrypted communications via the Secured Sockets Layer (SSL) if you also purchase the HiT SSL Server product.
MetaWise Computing, Inc.
MetaWise Computing, Inc. (www.metawise.com) provides access to the AS/400 via its MetaWise DAO product. The OLE DB connectivity is provided in the MetaWise DP feature of MetaWise DAO. MetaWise DP allows for OLE DB access to the AS/400 and the execution of remote commands. Its one big drawback is that MetaWise DP only supports the APPC protocol for communications with an AS/400 host (no TCP/IP connections).
MERANT
MERANT (www.merant.com/datadirect/products/index.asp, the company formerly known as Intersolv), offers OLE DB access via its SequeLink OLE DB Edition product.
SequeLink OLE DB is server-based middleware that allows for connectivity with any level of Microsoft Windows operating systems via SNA or TCP/IP. The server component accesses the AS/400 file system directly, which cuts down on access to the system catalogue functions, thus improving performance.
Both the server and the client will automatically try to optimize network communications. If the client asks the server for 10 rows of data and the server determines that 20 will fit in the packet buffer, the server will automatically fill the buffer with the 10 additional rows and the client will cache them in case more rows are requested. The server also supports data compression for faster communications with dialup/internet-connected clients. In addition, the client will automatically defer communications with the server as long as possible, then bundle all requests in order to minimize required communications. Both the server and client also support encrypted communications for better security and they are firewall-enabled.
What is neat about their implementation is that the OLE DB provider is the same software—no matter what database you are connecting to. And MERANT offers support for 25 databases with this product. Also, if the SequeLink server is installed on your AS/400, connectivity with ODBC, JDBC or OLE DB is just a matter of client licensing as the one server piece supports all of MERANT’s connectivity options.
Microsoft
If you need SNA in your organization, you might already have SNA server installed. In addition to the other SNA networking services that Microsoft (www.microsoft.com/sna) provides, you also gain an OLE DB provider that provides access to AS/400 and VSAM files. Microsoft’s OLE DB provider for AS/400 and VSAM gives you record-level access and SQL access to both physical and logical files on the AS/400.
All access is managed through the Distributed Data Management (DDM) model and can only be achieved via an APPC connection to the AS/400. If this is not a limitation, it may be the way for you to go as the platform also sports other features for integrating PC technology with the AS/400 world. However, there is hope for TCP/IP shops. Microsoft’s new Enterprise InterOp Server (code-name Babylon, which is scheduled to currently be in beta testing) is built on Microsoft SNA Server and it is designed to provide integration with legacy servers, including AS/400. One of Babylon’s design specifications is that it will be able to run over TCP/IP as well as SNA, so the APPC-only limitation may soon disappear. Babylon is scheduled to be shipped with BackOffice 5.0, reportedly in the second quarter of 2000 (but this is a Microsoft shipping date, so there’s a very good possibility it will be much later).
IBM Ain’t the Only Choice
As you can see, there are options for AS/400 OLE DB connectivity other than Client Access. Some of these providers take a unique approach or implement features that are not available with IBM’s solution. Look at the features available, your project requirements, and the cost of the solution to determine which provider will meet your needs. As usual, healthy competition has provided the data consumer with a wealth of good, and, many times, superior alternatives to the IBM company line.
LATEST COMMENTS
MC Press Online