Wed, Apr
5 New Articles

Comparing IBM's and Microsoft's OLE DB Providers

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

OLE DB is a very hot topic all across the world of client/server development, as well as in the Internet and intranet spheres. Many programmers are searching the Internet, reading magazines, and buying books trying to understand this new technology. However, there’s a lot of misunderstanding about OLE DB, especially how it relates to ODBC. Quite simply, OLE DB is a Microsoft technology that allows users to access any form of data, including graphics and relational data. OLE DB provides a unified method of access to all types of data in any supported form of data store, eliminating the need to use ODBC to access relational databases and a second method to data stored in other ways. OLE DB has the potential to make client/server development far easier than it was in the past.

Both IBM and Microsoft will soon have OLE DB providers available for the AS/400. IBM is beta testing its provider in a project called Lightning, and Microsoft is getting ready to ship its OLE DB Provider for AS/400 and VSAM, which was developed as a part of a project called Thor. Although both OLE DB providers are based on the same standard and both make AS/400 data available to client systems, there are some significant differences between the two products. These differences, which are the focus of this article, affect how and if you’ll implement and use OLE DB in your development projects.

I’ll start by giving you a short overview of OLE DB, in case you’re not already familiar with it. However, the focus of this article is to compare and contrast the two soon- to-arrive OLE DB providers so you’ll be better prepared to decide which is right for your use. Let’s get started with the OLE DB overview.

I suggest you read through it even if you feel you’re already comfortable with the concepts behind OLE DB because there is a lot of misinformation floating around.


Overview of OLE DB


OLE DB is a standard that Microsoft created to allow access to data from a wide variety of data sources. It’s a part of what Microsoft calls Universal Data Access (UDA), which is designed to address some of the shortcomings of ODBC. ODBC is fine for

accessing data in relational databases, but these days not all data is in a relational database. Graphics files from a CAD application can be in a special “database” used only by that application. Network directory information is in a hierarchical, but often nonrelational, database. These types of data are unavailable to an application using ODBC.

In response to this problem, Microsoft created UDA and OLE DB. (If you want to find out more about UDA, use the URL http://www.microsoft.com/data/. UDA and its data-access component, OLE DB, provide access to all forms of data from almost any data source possible using a common interface. Data from the AS/400, from a network directory, and from a CAD application would all be accessed in the same way.

Just as a program needs an ODBC driver to be in place before it can use ODBC, a program must have an OLE DB provider available. IBM’s OLE DB requires Client Access/400 for Windows 95/NT, and Microsoft’s OLE DB Provider for AS/400 and VSAM is dependent on SNA Server.


ADO: An Easier Interface for OLE DB


OLE DB, like ODBC, has a C-style interface that can be very challenging to use. Fortunately, like ODBC, there is a higher-level abstraction of OLE DB that is a lot more manageable. For OLE DB, this abstraction is called ADO (ActiveX Data Objects). ADO makes the use of OLE DB in languages such as Visual Basic, Java, and Delphi easier (read: more practical).

As you might guess from its name, ADO uses objects and methods to access data using an OLE DB provider. For example, to connect to an AS/400, you create an instance of a “connection” object that points to an ODBC data source connected to an AS/400. There are also collection, command, field, and recordset objects in ADO. To use ADO in most languages, you set properties for the object, then use methods to affect the object. For example, a recordset object would point to a specific file using properties of the recordset object. You would use methods such as MoveNext and MoveFirst to navigate through the recordset. (Figure 1 shows some code from a Visual Basic program. This code connects to the AS/400 and a specific table using ADO.)

You can use the basic OLE DB interfaces to access data on the AS/400. You can also use ADO from languages such as Visual Basic or Delphi. Another option uses the Active Server Pages (ASP) support for ADO in Microsoft’s Internet Information Server (IIS). This IIS integration with the AS/400 would give you a server-based option for integrating data from your AS/400 with your Windows NT-based Web site.

I’ve only outlined OLE DB and ADO at a conceptual level. If you’d like more hands-on details, check out another article I wrote. In “Lightning Strikes the AS/400,” Midrange Computing, , I walked through a working example of how IBM’s Lightning OLE DB provider in Visual Basic is used to provide record-level access. Now, let’s start looking at some of the differences between the OLE DB providers coming from IBM and Microsoft.


Depth of Support


One of the important benefits of OLE DB is that it provides a consistent interface to different types of data from different data stores. However, there are significant differences between the support for AS/400 data types in the two OLE DB providers for the AS/400. Here are some of the AS/400 data sources that the Lightning OLE DB provider supports:

• Stored Procedures
• Tables
• Commands
• Programs
• Data Queues Some of you might wonder how a command or a program could be considered a data source. The designers of OLE DB understood that in some cases you need to be able to run commands in order to prepare data before it can be used, so support for commands

is designed into the standard for OLE DB. (Commands and programs are treated as the same type of object in OLE DB.)

Here’s the list of AS/400 data sources that Microsoft’s OLE DB Provider for AS/400 and VSAM supports:

• Tables Quite a bit of difference, as you can see. I suspect the reason for the difference lies in the way that Microsoft’s OLE DB provider accesses the system. Microsoft uses Distributed Data Management (DDM) to access data, so it is limited to supporting record- level access because that’s all that DDM supports. If Microsoft wants to support the other AS/400 objects that Lightning supports, such as data queues and commands, it will have to use routes other than DDM. That’s not to say that IBM’s product is always the best choice. Client Access/400 only works with the AS/400. Microsoft SNA Server and the upcoming OLE DB Provider for AS/400 and VSAM work withAS/400s and mainframes. So if you’re in a mixed AS/400-and-mainframe environment, Microsoft’s OLE DB pro-vider might be a better choice for your use.


Client “Thickness”


There’s one difference between the OLE DB providers from IBM and Microsoft that I would like to point out. The two products have very different footprints. When I use that term, I mean the resources that you need on the client system in order support the OLE DB provider. Although the ongoing debate about whether thin clients are better than thick clients usually centers around network computers versus PCs, PC clients also come in thick and thin varieties. Thin client software usually has more of the processing done on the server than thick client software, so the hardware requirements for thick client software are often greater than the requirements for thin client software. You usually need more RAM and disk space for a thick client.

If all you need is OLE DB connectivity, then there’s a difference in the “thickness” of the two OLE DB providers. Client Access/400 includes a 5250 emulator and other utilities. SNA Server only provides the basic connectivity to the AS/400. If you choose Microsoft’s product but you still need a 5250 emulator, then you’ll have to purchase a package such as Wall Data’s RUMBA. However, if you’re only going to be using the connectivity to the AS/400 from client/server applications (including browser access), then the footprint of SNA Server is much smaller than Client Access/400.

A PC running Client Access/400 for Windows 95/NT should have at least 50 MB of disk space and 16 MB of RAM for Windows 95 or 32 MB of RAM for Windows NT. In addition, IBM recommends an Intel 486 processor for Windows 95 and 100 MHz (or faster) Intel Pentium processor for Windows NT. These are, as a practical matter, fairly low-end requirements. In most cases, you’ll want both additional memory and faster processors to get acceptable performance.

If you’re not going to need 5250 emulation or the other utilities included in Client Access/400, the hardware requirements for SNA Server are much lower. In many cases, the hardware required is not much above the basic hardware requirements for Windows 95 or Windows NT. This is because much of the work in supporting connectivity to the AS/400 is done by the server instead of by the client, as is the case with Client Access/400.


Making the Right Choice for You


As you’ve seen, there are some significant differences in the architectures of the two OLE DB providers. Some of the differences stem from the fact that one product is based on direct AS/400 connectivity while the other is based on SNA gateway connectivity to the AS/400. I can’t tell you which solution is right for you. You’re the only person who has the necessary knowledge of your company to make that decision. The points that I’ve raised will only help you answer some of the questions you’ll need to address in order to select the best OLE DB provider.

Are you using data queues in your applications? If so, then the Client Access/400 OLE DB provider might be the best answer because the Microsoft OLE DB provider can’t

access data queues. Are you in a mixed mainframe and AS/400 environment? If so, the Microsoft’s OLE DB provider might be the better answer because you won’t have two different vendors to turn to for support of your OLE DB connectivity. Finally, is client footprint a concern of yours? If so, and if you don’t need 5250 connectivity or the other utilities in Client Access/400, then Microsoft’s provider might be the better answer because it doesn’t have the overhead required to support all those applications that you don’t need.

If you want more information about IBM’s OLE DB provider, check out IBM’s Web site at http://www.as400.ibm. com/lightning/homepg.htm. If you want more information about Microsoft’s OLE DB Provider for AS/400 and VSAM, go to http://www.microsoft.com/ sna/thor.htm.

Public Sub OpenLinks()

Dim varRcds As Variant Dim varParms As Variant Const DBPROPVAL_UP_DELETE = 2 Const DBPROPVAL_UP_INSERT = 4

‘{{DA400_TABLE_OPENS_BEGIN}} Set CUSTOMER.ActiveConnection = MCRISC CUSTOMER.Properties(“Updatability”) = DBPROPVAL_UP_INSERT + DBPROPVAL_UP_DELETE CUSTOMER.CommandText = “LIGHTNING.CUSTOMER” CUSTOMER.Parameters.Append CUSTOMER.CreateParameter(“P1”, adChar, adParamInput, 1) Set CUSTOMER_DATA = CUSTOMER.Execute(varRcds, varParms, adCmdTable) ‘{{DA400_TABLE_OPENS_END}}

End Sub

Figure 1: Sample of code used to connect to an AS/400 table for read, insert, and delete access



Support MC Press Online

$0.00 Raised:

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: