Connecting Windows Applications to IBM Databases-Contents
A Joint White Paper by StarWare, Inc. and Microsoft Corporation
Overview
Approaches to IBM Database Access through SNA
SNA Server and the Benefits of Using SNA Gateways
SNA Server Integrates Heterogeneous Environments
Benefits of Using StarSQL
Summary
With the widespread acceptance of the Personal Computer in the
corporate world, more desktop computer users are leveraging Microsoft®
Windows® programs than ever before - today, more than one
million copies of Windows are sold each month. While computer
users have embraced Windows as the de facto desktop standard,
information systems managers continue to rely on IBM's DB2 relational
databases to store and protect corporate data. With more than
300,000 licenses worldwide, DB2 makes up roughly 70 percent of
the relational databases in use today - more than all other RDBMSs
combined (Source: IDC). As more organizations push decision-making
throughout all ranks of the corporate hierarchy, access to these
databases from Windows applications becomes a significant challenge.
Microsoft and StarWare provide a solution to this challenge that
combines the power and reliability of Microsoft SNA Server with
the speed and low cost of StarWare's StarSQL database access software.
This paper describes this solution and provides an explanation
of the technology components that enable it.
Organizations are increasingly relying on IBM's DB2 for database
storage and on Windows-based products for desktop operations.
Document Contents
Leveraging Systems Network Architecture (SNA)
As with most IBM host software, IBM relational databases are accessible
through IBM's Systems Network Architecture (SNA) technology, a
comprehensive set of protocols for interconnecting IBM systems.
As every IBM host system has SNA protocols built in, all other
systems wishing to interoperate with IBM machines - including
desktop PCs seeking database access - usually present themselves
as SNA devices in order to gain access. There are currently two
ways to connect PC users with IBM databases via SNA: indirect
and direct.
Traditional, Indirect Approaches
Until recently, vendors providing PC-to-host database access through
SNA protocols have relied upon a variety of indirect methods:
Traditional, indirect data access methods include terminal
emulation, file transfer, and program-to-program communications.
- Terminal Emulation. Terminal emulators are software
programs that run on the PC client, transforming the PC screen
into a replica of an IBM terminal screen and making the PC look
like a slave terminal, such as an IBM 3270 or 5250, to the IBM
host. Although terminal emulators provide the ability to see
and even update corporate data, they do not allow users to pull
that data into local Windows applications such as Microsoft Excel
or Lotus 1-2-3. In addition, they require an application resident
on the host for accessing data there.
- File Transfer. File transfer utilities take entire
data files from the host and deliver them to the local client
or LAN database. Although this method enables data to be leveraged
by local applications, it can consume inordinate amounts of network
bandwidth and local CPU cycles, especially for data which are
accessed often. Moreover, time and resources often are wasted
transferring whole files when the user needs only a few pieces
of information.
- Program-to-Program. Cooperative processing applications,
such as those that use IBM's APPC or LU 6.2 technologies, enable
a PC application to communicate with an application on the host
via SNA protocols. While this method provides a more direct way
to access corporate data, it requires new, custom applications
on both the client and the host. These custom applications, in
turn, call for programmers who are adept with advanced IBM connectivity
protocols.
- Screen Scraping. The most recent of the indirect
techniques, screen scrapers, provide an application programming
interface (API) by which an application on the PC may access selected
fields of an IBM host screen image, for example, a 3270 or 5250
screen image. An application can issue commands via the API that
either read or replace data in screen fields, thereby getting
information from the host or sending data to the host for update.
This approach solves the problem of providing corporate data to
local applications, but it still requires an application resident
on the host for the terminal emulation operations. Another problem
is that users receive a limited view of data, restricted to the
fields selected by the host application.
The New IBM Database Access Approach: Direct Access
The limitations of indirect database access have prompted a movement
toward direct access to DB2 data from desktop Windows applications.
Currently there are two such solutions: proxy software and standards-based
software.
- Proxy Software. Recently, several vendors have introduced
a direct method of IBM database access from Windows applications.
These vendors, including Micro DecisionWare, Inc. (MDI) and ShowCase
Corporation, offer products that enable desktop
applications to access DB2 data directly on the host system, using
proprietary software on the IBM host. This software serves as
a ÒproxyÓ in that it issues SQL commands to DB2
databases on behalf of the client application. The approach does
not require custom applications on either the client or the host,
and data are delivered directly to standard Windows applications.
However, the benefits of direct access to IBM data often are
offset by the high cost of installing and maintaining proprietary
systems software on the host.
- Standards-Based Software. The standards-based approach
leverages Microsoft's Open Database Connectivity (ODBC) technology
on the client platform, along with IBM's Distributed Relational
Database Architecture (DRDA) specification on the host, to provide
a direct link between ODBC-enabled Windows applications and IBM
relational data sources, namely DB2 databases. (These standards
are detailed below.) This method presents a more direct and elegant
solution for database connectivity than either traditional indirect
data access or direct, proxy access. Requiring no application
or other software on the host, ODBC-to-DRDA drivers, such as StarWare's
StarSQL, enable desktop users to pull data, record-by-record,
directly from the host database, and view, manipulate, and update
the data, all from within their familiar Windows applications.
Using this approach, any ODBC-enabled software - including more
than 100 commercial Windows applications and custom programs built
using popular application development tools such as PowerSoft
PowerBuilder or Microsoft Visual Basic® - can tap into DB2
data quickly, easily, and cost-efficiently.
Elements of Direct Database Access
The Microsoft/StarWare ODBC-to-DRDA solution is enabled by the
convergence of several key technologies, including the following:
- Distributed Relational Database Architecture (DRDA)..
Defined by IBM for managing relational data within and among IBM
systems, DRDA is a set of rules or protocols for accessing, requesting,
transmitting, and managing distributed relational data. DRDA
utilizes portions of IBM's Systems Application Architecture framework
to ensure data integrity and security, enabling multiple requesters
to access IBM's databases through DRDA without disruption to the
core data. DRDA governs such functions as user sign-in and table
access, among others. IBM released the first levels of DRDA in
1992, and industry support has steadily gathered momentum since
then. Today, DRDA-enabled databases include DB2 for MVS mainframes,
DB2 for VSE mainframes (formerly known as SQL/DS), DB2/400 for
AS/400 midrange systems, and DB2/6000 for UNIX systems (including
IBM, HP and Sun). In addition, DRDA support is forthcoming for
DB2 for PCs, Oracle and Sybase.
- Open Database Connectivity (ODBC). Defined by Microsoft
and adopted by the SQL Access Group, ODBC is a standard application
programming interface (API) for database access that is completely
data source- and vendor-neutral. As such, ODBC enables desktop
applications to access most major relational databases through
a single, standard interface, thereby eliminating the need to
implement software drivers for each data source. ODBC is most
frequently used to access local relational databases. However,
with the advent of products like StarSQL, users can leverage ODBC
to access remote relational databases. In addition to incorporating
SQL Access Group standards, ODBC leverages ANSI-standard SQL and
ISO standards for interoperability. As a result, ODBC enables
desktop users to access one or more relational database management
systems concurrently, including DB2 (using StarSQL), and Oracle,
Sybase, or Informix (using other ODBC drivers). Since its adoption
by the SQL Access Group and X/Open, ODBC has generated broad support
throughout the industry. To date, more than 100 application software
suppliers, including nearly all major Windows vendors, are supporting
ODBC in their packages.
StarSQL translates ODBC StarSQL maps ODBC calls into DRDA requests
for transmission to the IBM host.
- StarSQL. StarSQL is a client-based software driver
that translates ODBC calls into DRDA-formatted requests for use
with local or remote DB2 databases. StarSQL acts as the glue
between ODBC-enabled Windows applications and DB2 data, pulling
host data directly into any ODBC-enabled application, column-by-column,
row-by-row, and record-by-record. The StarSQL driver supports
Windows 3.X, Windows for Workgroups and Windows NT™. StarSQL
consists of three software layers: ODBC, translator, and DRDA.
Because ODBC commands do not directly map into DRDA requests
(i.e., multiple ODBC calls may result in a single DRDA
request), StarSQL handles the translation of ODBC SQL calls into
DRDA requests. StarSQL enables connection to multiple data sources
concurrently, allowing users to select data from mainframe, midrange,
and UNIX hosts for use in their local applications.
- Microsoft SNA Server. High-performance, low-cost
communication servers for SNA connectivity provide the final piece
of the database access puzzle. Microsoft SNA Server for Windows
NT is ideal for operation with StarSQL and has a wide range of
features and benefits that apply to many additional areas of
LAN-to-IBM host connectivity.
Document Contents
SNA Server's client-server architecture allows for transport-independent
host access using all popular LAN protocols.
SNA Server Overview
Microsoft SNA Server is a LAN-to-SNA gateway that provides SNA
communications for LAN-based services and multiple platform PC
workstations running a variety of network protocols. SNA Server
employs a client-server architecture that is tightly integrated
with, and leverages the strengths of, Microsoft Windows NT Server.
For operation with StarSQL, SNA Server is configured as an IBM
PU 2.0, PU 2.1, or APPN LEN node providing LU 6.2 services. SNA
Server complements StarSQL not only by providing SNA communications
service but also by providing traditional access to IBM systems
through TN3270 access to host applications and through high speed
file transfer using APPC File Transfer Protocol ( AFTP). SNA
Server allows for comprehensive, bi-directional SNA access while
maintaining LAN protocol transport independence. For clients,
all popular PC operating systems are supported including Windows
NT, Windows, Windows for Workgroups, Windows 95, MS-DOS®,
OS/2, UNIX, and Apple Macintosh. StarSQL runs on clients under
Windows NT, Windows for Workgroups, and Windows 95 (when available)
and will run on several other SNA Server-supported clients in
the future. For connection between the client and server, SNA
Server supports multiple network protocols including Novell IPX/SPX,
TCP/IP, Banyan Vines IP, Named Pipes, AppleTalk and RAS.
Benefits of Using an SNA Gateway
The StarSQL/SNA Server configuration is a classical gateway configuration
which provides a number of benefits compared with solutions that
connect clients directly to IBM hosts without an intervening gateway.
These advantages accrue to the desktop user, the LAN administrator,
and the host administrator.
Benefits to the Desktop User
SNA Server increases stability, saves memory, and reduces complexity
at the desktop by allowing the choice of only one protocol per
desktop.
- Increased stability and memory savings.
- The DLC protocol is a terminate-and-stay-resident (TSR)
program and thus requires a real mode NDIS or ODI driver. Eliminating
real mode TSRs and drivers not only saves real memory, but can
improve the stability of the desktop.
- Reduced complexity.
- IBM's DLC protocol (LAN Support program) only works with
certain adapters and protocol stacks. Selecting compatible products
and then creating and maintaining configurations that require
concurrent SNA and LAN protocol support is excessively complex
compared to an SNA Gateway.
- Increased LAN protocol choice.
- Desktop users select the LAN protocol that best suites their
needs.
Benefits to the LAN Administrator
Branch configuration - SNA Server provides the host link for
remote clients. No configuration is required at the client desktops.
SNA Server provides easier administration, greater security, and
configuration flexibility. When connecting direct, each desktop
must be configured individually with the host's network address,
LU name, and XID. Whenever there's a change on the host side,
such as the XID is updated, all desktops must be reconfigured.
In large organizations, where thousands of desktop systems need
access to host applications, reconfiguration of large desktop
populations can be an overwhelming task. With SNA Server, there's
nothing to configure at the client; all host changes can be tracked
centrally by the LAN administrator through the use of intuitive
Windows-based administration tools.
- SNA Server eases administration with built-in performance
and troubleshooting tools. When all host-bound traffic is
concentrated through SNA Server, the LAN administrator can use
the performance monitoring, event logging, and tracing capabilities
of the server platform.
- SNA Server provides integrated security for all users.
The LAN administrator can control access to the host by using
the integrated security features of Windows NT Server and SNA
Server. In contrast, direct connection allows each desktop user
to access the host by simply knowing the applicable host parameters.
A gateway adds another level of flexible security.
- SNA Server provides exceptional configuration flexibility.
SNA Server allows LAN administrators to configure a variety
of host connectivity options. SNA Server can be located at remote
sites and can use leased line or dialup SDLC, X.25, ISDN or Frame
Relay connectivity to the host. This scenario is depicted in the
branch configuration diagram on this page. Optionally, SNA Server
can be located at or close to the host by employing direct channel
attachment, Twinax, DFT, Token Ring, or Ethernet connections.
Benefits to the Host Administrator
SNA Server provides several benefits to the host administrator:
- SNA Server reduces frequency of VTAM gens. SNA Server
dramatically reduces (re)definition work on the host, which reduces
support costs and system downtime. With SNA Server, hundreds of
users can be supported by defining a single PU or controller,
instead of having to define one for each end user.
Centralized configuration - All host access is routed through
SNA Server. SNA traffic is isolated to the data center while native
LAN protocols are used on the corporate WAN.
- SNA Server reduces host memory requirements. In
the direct connection model, the hundreds of end user definitions
typically are stored in resident memory on the host system, consuming
expensive host resources. By assigning individual user accounts
to host resources on the SNA Server machine, SNA Server allows
memory savings to be realized on the host system, which can result
in hardware savings and in improved host performance.
- SNA Server reduces host CPU utilization Each direct
connection to a host must be managed individually by the host's
control software, consuming expensive CPU cycles. Some studies
have shown that you can free up as much as 30% of an AS/400's
CPU simply by switching from direct connections to a gateway approach.
Similar savings occur in reducing the load on front-end processors
(FEPs).
- SNA Server saves network bandwidth. Instead of the
host having to poll all desktops individually to maintain direct
connections (even when there's no activity), the host has to maintain
only one connection when using a gateway. This can dramatically
reduce network traffic and allow better network performance as
well as reduce session time-out problems.
- SNA Server provides higher reliability. SNA Server
overcomes the typical objections to using gateways through its
excellent stability, hot backup, and load balancing features.
In addition, because it is integrated with Windows NT Server,
SNA Server can provide the scalability of symmetric multi-processing
and offer more raw CPU power than any other SNA gateway.
Document Contents
Many enterprise environments include desktops running Windows,
Windows for Workgroups, Windows NT Workstation, Windows 95, MS-DOS,
Apple Macintosh, OS/2 and UNIX, with a networking mix that contains
IPX/SPX, TCP/IP, NetBEUI, Banyan VINES IP, AppleTalk in addition
to SNA protocols. Microsoft SNA Server provides the solution
for this heterogeneous reality, while offering the extensibility
demanded by the ever-changing computing environment. Thus, StarSQL
clients can operate with most of the SNA Server-supported client
environments and LAN protocols and will support others over time.
Today, StarSQL supports clients running Windows NT, Windows for
Workgroups, and Windows 3.X and all the LAN protocols supported
by these clients.
Scaleable Platform from 1 to 10,000 Host Sessions
As SNA Server is an application integrated with Windows NT Server,
it can grow with organizations as they increase the number of
users connecting through the gateway, the number of host connections,
or the types of applications that use gateway services.
- Server capacity. SNA Server provides the highest
capacity of any SNA gateway
- Each SNA Server supports up to 2,000 users and 10,000 sessions.
- Each SNA Server connect
- SNA Server is designed for growth. SNA Server takes
advantage of the newest hardware platforms for best performance.
- Intel x86
- Pentium™
- MIPS®
- Alpha AXP™
- PowerPC™
- Operational Tools and Server Security. SNA Server
takes advantage of the advanced features in Microsoft Windows
NT Server.
- NT domain security
- Performance monitoring
- Event logging and auditing
- Multi-threading
- Virtual memory management
Hot Backup and Load Balancing
In any large IS environment, an individual gateway can be viewed
as a single point of failure. For mission-critical use, Microsoft
recommends a minimum of two SNA Servers to provide fault tolerance.
Up to 50 SNA Servers can be grouped together to provide load
balancing and hot backup, allowing sessions to be automatically
rerouted to a backup gateway should the primary gateway or host
link fail. The load balancing and hot backup features work both
for mainframe connections and AS/400 connections-and both for
dependent and independent LUs. For highest speed mainframe connectivity,
SNA Server works with direct channel attached technology such
as Barr System's Channel Adapter or Bus-Tech Inc.'s 3172-NT.
Each of these devices can provide host access at speeds many times
greater than the fastest direct LAN attachments.
Administration
A single Windows NT Workstation or Windows NT Server, located
anywhere in the corporate network can configure and manage all
of the SNA Servers in the network using Windows NT graphical tools
that are included with the product. If the servers reside at
a central location, the IS staff can be responsible for the administration
and monitoring of all host access. This can also be accomplished
from the IBM host perspective because of the integration of SNA
Server with NetView facilities.
- IBM NetView Alerts. SNA Server can be configured
to provide custom alerts to NetView in addition to informational
messages and alerts to any Windows NT Server in the domain .
- IBM NetView RunCmd. NetView RunCmd support enables
the NetView operator to directly control all functions on the
Windows NT Server running SNA Server. The SNA Server RunCmd service
is a daemon service running on the server which extends access
of the Windows NT command line to NetView.
Licensing Flexibility
For maximum acquisition and implementation flexibility, SNA Server
licenses can be purchased using two different models.
- Client-based.
- In this model, you purchase as many server licenses as you
want SNA Servers, and a client license for each desktop which
can access any of the SNA Servers in your network.
- On a large network with multiple servers, this model provides
substantial cost savings. Any additional cost is accrued through
the addition of the server license- other gateway vendors require
twice the total cost of the first server to gain fault tolerance.
- As in the "per server" model, increasing your
capacity is as easy as purchasing additional client licenses.
You can move at your own financial pace as users are added and
your network grows.
- Server-based.
- In this model, you purchase an SNA Server license and as
many client licenses as there are concurrent desktops accessing
the server.
- To add additional desktops, simply purchase additional client
licenses.
- There is no requirement to purchase "packs" of
licenses beyond what you need.
Each of these models has specific advantages when implementing
SNA Server solutions. The client based approach is more desirable
in multiple server environments requiring extra capacity, load
balancing or hot backup for direct database access to DB2.
Document Contents
Direct access to DB2 data from local Windows applications provides
a number of obvious advantages: convenience, low cost, and elegance
of design. But a number of other benefits accrue from the use
of a well-designed ODBC-to-DRDA driver.
Security and Data Integrity
IBM's DB2 relational database continues to be popular among information
systems managers for one overriding reason: data integrity.
DB2, in concert with IBM system environments such as MVS, VM and
VSE, ensures that data are continually protected and updated,
through comprehensive rollback and recovery operations, and through
controls on user access and updating privileges enforced by DB2
and DRDA.
Performance
A well-conceived and -implemented direct access driver can optimize
the performance of ODBC-to-DRDA requests, making data access fast
and easy for end users. First and foremost, direct access drivers
utilize a client/server configuration to optimize performance.
In this configuration, client workstations perform the bulk of
data processing, accessing the host only for data retrieval.
In standard host or terminal emulation environments, by contrast,
all the processing occurs on the host, which can create a bottleneck.
By using significant computing resources available on the desktop,
the client/server model eliminates this bottleneck.
In addition to employing a client/server configuration, StarSQL
leverages four design innovations to optimize performance:
- Intelligent ODBC-to-DRDA conversions. ODBC calls
and DRDA requests do not share a one-to-one relationship. As
a result, the ODBC driver must gather and translate ODBC calls
to maximize use of DRDA requests and minimize consumption of network
bandwidth. A thorough understanding of the DRDA architecture
and its Distributed Data Management (DDM) and Form Data Object
Content Architecture (FD:OCA) subsets has allowed StarWare to
construct an ODBC-to-DRDA translator that leverages DRDA's unique
data management capabilities.
- Enhancements to SNA data transport. Unlike one major
competitive product, StarSQL utilizes very little system resources
in the movement of data from one SNA protocol stack to the next.
This is because StarSQL stores ODBC calls and the resulting data
retrieved in a buffer, then allows algorithms in each protocol
stack to operate on the data there. This is called Òcollapsing
the stack,Ó and early benchmark tests in Microsoft laboratories
suggest it helps StarSQL log a 50 percent performance advantage
over competitive ODBC-to-DRDA drivers.
- Dedicated memory management. StarSQL features its
own memory manager, which optimizes the driver's use of memory
resources. In all, the StarSQL driver consumes less than 150K
of memory in the Windows system.
- Local data conversion. In addition to performing
intelligent ODBC-to-DRDA conversions, StarSQL minimizes bandwidth
and saves expensive host resources by performing all conversions
locally. This includes translating text, floating point and other
data formats into IBM-accepted formats.
Document Contents
The StarSQL/SNA Server combination provides a compelling solution
to PC-to-host data access requirements today. By delivering a
simple and elegant means for Windows users to pull corporate data
directly into their familiar desktop environments, without compromising
the security and integrity of the data, StarSQL and SNA Server
have leveraged today's standards to provide a real-world solution
to a pressing data access problem. For the future, StarWare foresees
adding access to more DRDA databases, including Oracle and Sybase,
as well as offering that access across a wider variety of client
platforms, including UNIX and Macintosh systems. In addition,
as ODBC continues to gain momentum as the industry's de facto
standard data access API, more applications will be able to leverage
StarSQL in 1995 and 1996. Lastly, new related products from StarWare
and Microsoft will broaden and deepen the range of functions StarSQL
and SNA Server can provide.
For more information:
Contact your local Microsoft office or a Microsoft Solution Provider
near you. In the United States, call (800) 426-9400 for product
information or to locate a Microsoft Solution Provider. In Canada,
call (800) 563-9048. Outside the United States and Canada, call
your local Microsoft subsidiary or (206) 936-8661. Via the Internet,
use: http://www.microsoft.com.
Contact StarWare in Berkeley, California, USA at (800) 763-0050
from anywhere in the United States or Canada. From elsewhere
call (510) 704-2000, or contact StarWare in the United Kingdom
at 44 1734 508 900.
Document Contents
http://www.microsoft.com/TechNet/boes/bo/sna/ypages/starware.htm (PC Press Internet CD, 03/1996)
Go To TechNet Home Page
Go To Microsoft Home Page