Introduction
The requirement to share data between PC systems and IBM DB2 relational databases has grown significantly in the last several years. Businesses understand the advantages of sharing data to bring products to market sooner, develop applications and make critical business decisions on all levels.
This document discusses why sharing DB2 data in the PC environment has become critically important. For comparison purposes, traditional data access methods are covered. Current trends for direct data sharing are discussed, as well as the enabling technologies that make these solutions fast, effective and scalable. Finally, we'll review how StarQuest and Microsoft products work together to provide complete, easy-to-implement data sharing solutions.
As a practical demonstration, appendices show samples of the components discussed in the document and examples of actual configurations and code segments for implementation. Each of the solutions discussed in the document are constructed by combining various elements in the appendices.
Fulfilling The Need For Data Sharing
Organizations are increasingly relying on IBM's DB2 for database storage and on Window-based products for desktop operations.
The PC desktop is the fastest growing segment of the computer industry and more users are leveraging Microsoft Technology than ever before - over one million copies of Windows are sold each month. Organizations are using Microsoft operating systems on PC desktops to run productivity enhancing software packages: word processors, spreadsheets, decision support tools, report writers or their own custom applications.
At the same time, many organizations rely on IBM mainframe and midrange systems as the repository of their data assets. IBM's relational database DB2 has become the most popular method for storing and protecting corporate data in the IBM environment. With more than 400,000 licenses worldwide, DB2 makes up roughly 70 percent of the relational databases in use today - more than all other RDBMS combined (Source: IDC).
As more organizations push decision-making throughout all ranks of the corporate hierarchy, fulfilling the need to share data between the PC desktop and DB2 environment has become a critical business requirement. The general consensus is that by enabling data sharing, organizations become more effective and more profitable by making better use of the components of their information services infrastructure.
TRADITIONAL APPROACHES TO SHARING DATA
In illustrating where improvements have been made, it is useful to consider how organizations have traditionally enabled data sharing. Until recently, vendors providing PC-to-host database access have relied upon a variety of indirect methods:
Traditional, indirect data access methods, which include screen scraping, file transfer, and program-to-program communications, are considered cumbersome, inefficient and complex.
- Screen Scrapers
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 updates.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.
- 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 accessed often. Moreover, time and resources are wasted transferring whole files when the user needs only a few pieces of information. - Advanced Program-to-Program Communication (APPC) 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. 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 at advanced IBM connectivity protocols.
All of these methods have some inherent drawbacks, making them, when considered together, cumbersome, inefficient and complex. It is for these reasons that organizations continue to demand a seamless, transparent access method.
DIRECT DATA SHARING MODELS
There are three primary data sharing models for direct access: two-tiered, three-tiered and distributed architecture.
- Two-tiered Model
An example of the two-tiered solution is a user connecting directly to a RDBMS with an application. The application could be a common spreadsheet tool or some custom developed application.
Various tiered architectures provide options for organizations to access data directly.
- Three-tiered Model
In the three-tiered model, a user is connected to an intermediary system that interfaces with the RDBMS as the result of some action taken by the user or at some regular interval. The primary three-tiered implementations include accessing RDBMS through a web browser, data marts and transaction processing models.
- Distributed Architecture
The number of systems between the RDBMS and the user becomes implementation dependent. With Distributed Architecture, the number of intervening systems can be essentially unlimited. Hence the more generalized model of Distributed Architecture has arisen.
For direct access using any of these models, Microsoft and StarQuest provide a solution that combines the power and reliability of Microsoft SNA Server with the high performance of StarQuest's StarSQL database access software.
THE ENABLING TECHNOLOGIES FOR DIRECT ACCESS
Enabling technologies that utilize open standards, provide users with stability and flexibility, independent of their technology migration path. All of the direct data sharing models for PC systems and DB2 shown are implemented with the following enabling technologies.
- 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.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 Pro), 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. Hundreds of application software suppliers, including nearly all major Windows vendors, are supporting ODBC in their packages.
StarSQL bridges Microsoft's ODBC and IBM's DRDA to provide transparent data access
- 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 Network Architecture (SNA) 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.1 and above for AIX, HP-UX, Solaris and Windows NT.
- SNA Server
Microsoft SNA Server is an 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.Communication to the host takes place over link protocols and media supported by Microsoft SNA Server, (the hardware on which SNA Server runs), and the hardware of the host. For example, links may be Token Ring, Ethernet, Synchronous Data Link Control (SDLC), or channel attachment.
StarSQL maps ODBC calls into DRDA requests for transmission to the IBM host.
Connection to the host normally is through standard SNA facilities of IBM host processors. In MVS systems, this is typically through front-end processors running Network Control Program (NCP) and through Virtual Telecommunications Access Method (VTAM) facilities. Using channel attachment hardware, users can bypass front-end processors.
(See Appendix 1: Sample SNA to VTAM Configuration)
- StarSQL Data Access Middleware
StarSQL is a client-based data access middleware driver that translates ODBC calls into DRDA-formatted requests for use with local or remote DB2 databases. No special hardware or host software is needed because StarSQL runs on the client.Acting as the bridge between ODBC-enabled Windows applications and DB2 data, StarSQL brings host data directly into any ODBC-enabled application, column-by-column, row-by-row, and record-by-record. StarSQL implements the latest ODBC API, Version 3 Level 2 and DRDA Level 3.
The StarSQL ODBC driver supports Windows 3.X, Windows for Work Groups, Windows 95 and Windows NT. StarSQL is bundled with MS SNA Server. The bundled version allows for 10 concurrent connections from a single CPU. Users purchasing additional licenses from StarQuest Software, get additional StarSQL Pro features and direct technical support.
(See Appendix 2: Sample Data Source for Connecting to DB2 on MVS)
Sample Implementations For Data Sharing
Direct database access provides a faster, more convenient solution when using any Windows application.
IMPLEMENTING TWO-TIERED SOLUTIONS
In the two-tiered model, client applications are connected directly to DB2. With direct database access, a Windows application such as Excel can access any IBM relational database.
This has many benefits. Local users can work in the applications they are familiar with to do analysis on the data they need. Only the necessary data is transferred to and from systems. This means the solution is fast and efficient. In addition, the queries and updates are using the database system rules, guaranteeing data integrity.
The applications used to do analysis on the data run on the PC, which reduces CPU loads on the host and leverages the lower cost desktop CPU. Software development costs are much lower on the PC and end-users prefer point-and-click applications over traditional green screen applications.
In a two-tiered solution, direct data access makes data available in a seamless, one step operation. Using this simple and low cost method with spreadsheets, word processors, decision support tools and report writers, users are empowered with the information they need.
Queries executed remotely via DRDA utilize less CPU than the identical query submitted locally via the IBM Query Management Facility (QMF), a host-based query tool. In addition to the CPU savings, providing a direct connection to clients eliminates the multi-step processes of executing queries on the host, then translating and transferring the data to the client system.
This figure illustrates the efficiency of the two-tiered model from the host CPU perspective.
There are hundreds of applications available for decision support, reporting, data mining, and On-Line Analytical Processing (OLAP) that can be used effectively in the two-tiered model. The most important factors for choosing a two- tiered solution are the need for up-to-the-minute data or the need to update the database.
The two-tiered solution can be implemented with Microsoft SNA Server, the bundled StarSQL driver and any ODBC-enabled application, such as Excel, Visual Basics or PowerBuilder.
IMPLEMENTING THREE-TIERED SOLUTIONS
Three-tiered solutions are represented by the following implementation models: Web Server Publishing, Data Marts and Transaction Processing.
Each of these solutions offer specific application values that make them among the most useful and sought after systems today. Through a collaboration with StarQuest, Microsoft has provided the ability to implement all three of these models possible to implement with Microsoft's BackOffice suite.
- Publishing DB2 Data on the Web
Many first generation Web Servers published static Web pages. In this model the data displayed is unchanged regardless of who is viewing it. One of the first challenges for creating more useful web sites is to provide users with access to dynamic data specific to the viewer.Using the standard features of the Microsoft Internet Information Server (IIS), SNA Server users can execute queries and publish DB2 data directly into Web pages. Microsoft IIS provides an ODBC interface through their Active Server Pages (ASP) interface. Once access is provided to ODBC, StarSQL can be used to provide a connection to DB2 via SNA Server.
-
Microsoft Back Office components IIS, SNA Server and StarQuest's StarSQL connect a Web server to an IBM host for publishing dynamic data on a browser client.
- Data Marts
The result is a Web server that can combine static web pages with dynamic data from the company's corporate DB2 database. For example, the Web page of an air freight company might get HTML pages from a local file and dynamic data from a remote DB2 database. This would allow a customer to enter an air bill number in a Web page to request up-to-the-second information on their package.
An IIS Web server has the ability to access relational databases using the Active Server Pages. The ASP uses ODBC drivers to access relational databases and can use StarSQL or other ODBC-to-DRDA drivers to access DB2. The Web server connects to the IBM host through SNA Server.
(See Appendix 3: Sample HTML and Active Server Page)
A data mart is a subset of data collected at a site for the purposes of department- specific analysis. Data is copied from the central data repository and refreshed on a periodic basis. The local data mart is then used for resource intensive activities not suitable for execution on the production system responsible for managing the day-to-day data processing.
SNA Server connects Web server to IBM host providing replicate data.
-
Microsoft BackOffice makes setting up data marts fast and easy. Microsoft SQL Server and StarQuest Data Replicator (SQDR), used in conjunction with SNA Server and StarSQL make up the components of the data mart solution. Client systems then use the tools of their choice to perform analysis on replicated data.
Through the use of graphical administration tools and easily customizable options, StarQuest Data Replicator allows you to accomplish robust and efficient bi-directional snapshot replication of data between Microsoft SQL Server and multi-platform DB2 databases.
One of the frequently used applications for data replication is decision support. Typically, these applications do not need the most up-to-date information. For example, it usually does not matter when studying monthly and annual business trends to have data that is up-to-the-second.
Often, production data processing systems do not store historical data in DB2 that may be valuable for trend analysis and similar decision support applications. One solution is to schedule regular SQDR replications from DB2 to SQL Server thereby accumulating needed historical data. Over time, this replication creates a data warehouse that is valuable for historical analysis and other purposes.
(See Appendix 4: Sample Replication Subscription)
Once selected, the database subset in SQL Server is easier to manage and use than the huge database superset in DB2.
- Microsoft Transaction Server
In this three-tiered model, users can keep connections open, schedule work loads and service many clients.
MTS has many powerful features that facilitates three-tiered and Distributed Architecture client/server solutions.
-
The Microsoft Transaction Server (MTS) allows developers to create efficient applications that provide high speed transaction processing services. MTS is designed for three tiered application development. It provides a multi-threaded capability that reduces the time it takes to create and execute instances of server-based objects. This connection pooling improves performance by reducing the number of steps required for execution of a resource.
(See Appendix 5: Sample MTS Connection Pooling Registry Entry)
A multi-tiered solution incorporates Web Server, Transaction Services and a Data Mart.
IMPLEMENTING DISTRIBUTED ARCHITECTURE SOLUTIONS
By using components from the basic three-tiered model, new solutions can be crafted that exploit the benefits from each individual component In the example, a multi-tiered solution incorporates a Web Server, Transaction Services and a Data Mart. Users replicate data to SQL Server, use Transaction Server for efficient access to data and use IIS to publishing data. The result is a fast, broad bandwidth method for publishing data to the Web with minimal impact on the host system.
Conclusion
The StarSQL/SNA Server combination fulfills the need for data sharing– providing 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 environment, 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.
In the past, building client server and distributed architecture solutions for DB2 has been a difficult undertaking. Now, through a partnership between Microsoft and StarQuest, organizations can quickly construct powerful data sharing solutions.
All of the solutions in this document are constructed with components of Microsoft BackOffice. They all share a common connection to DB2 via StarSQL and SNA Server. In the appendices is a sample configuration for connecting SNA server to MVS, instructions to enable DB2 for remote access and a matching configuration for a StarSQL data source. Sample code for IIS integration, SQDR configurations and MTS Connection Pooling are also included. The examples are matched and designed to be step-by-step implementation instructions.
StarQuest and Microsoft have made sharing DB2 data as easy as sharing data from any other RDBMS. All of the solutions are standards-based and will provide immediate value for almost any organization. If you have Microsoft BackOffice, you have everything you need today to begin enjoying the benefits of sharing DB2 data in your organization.
For the future, StarQuest Software foresees adding access to more DRDA databases 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.
For more information:
Contact your local Microsoft office or a Microsoft Certified Solution Provider near you. In the United States, call 1-800-426-9400 for product information or to locate a Microsoft Solution Provider. In Canada, call 1-800-563-9048. Outside the United States and Canada, call your local Microsoft subsidiary or 206-936-8661. Via the Internet, www.microsoft.com.
Contact StarQuest Software in Berkeley, California, USA at 1-800-763-0050 from anywhere in the United States or Canada. From elsewhere call 510-528-2900. Via the Internet, www.starquest.com or email: sales@starquest.com.
© 1998 Microsoft Corporation and StarQuest Corporation. All rights reserved. This document is for informational purposes only.
MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Microsoft, Visual Basic, Windows, MS-DOS are registered trademarks and Windows NT is a trademark of Microsoft Corporation. IBM, OS/2, OS/400, AS/400, and NetView are registered trademarks and MVS/ESA, ACF/VTAM and ES/9000 and SAA are trademarks of International Business Machines Corporation. UNIX is a registered trademark in the United States and other countries, licensed exclusively through X/Open Co. Ltd. All other trademarks or registered trademarks are the properties of their respective owners.
