http://www.microsoft.com/TechNet/boes/bo/sna/technote/sn205.htm (PC Press Internet CD, 03/1996)
| Updated: March 14,1996 |  Go To TechNet Home Page |
Presented by: Jim Raisio
Nick Nicholes
James E. Raisio is vice president, OEM and Strategic Alliances,
at Wall Data, responsible for developing relationships with strategic
accounts and North American OEMs.
Nick Nicholes, Product Developer for RUMBA for Database Access.
Introduction
Accessing and Using Host Information
Accessing host data
Building and executing queries
RUMBA for Database Access
Developing Using RUMBA For Database Access
RUMBA for Database Access and OLE 2.0
Building Business Objects
Data Access Architecture
Example Business Data Container
Example Object Configurator
OLE 2.0 Object Creation
Mainframe Data Access
Using Business Objects
Conclusion
This session is designed to introduce technical decision makers and developers to an easy, cost-effective solution for accessing and using business information hosted on IBM mainframes or AS/400s. The technologies involved include:
&183;RUMBA for Database Access
&183;Microsoft® SNA Server
&183;OLE 2.0 Automation
&183;ODBC and DRDA Drivers
The ease of use of this solution is evident to the end user, who is shielded from the underlying technology. As you will see in the demonstration, the complexities of the network, communications protocols, database access, and query construction are hidden from the business user, who can focus on using the information in their job. Wall Data also takes advantage of two industry standards: Open Database Connectivity (ODBC) and Object Linking and Embedding (OLE) to provide an efficient way to bridge the gap between legacy systems and client-server computing. This allows developers to use the skills they've learned using Microsoft's client/server development tools, business object development paradigm, and object technology standards to build business solutions accessing IBM host and midrange databases as easily as local or Microsoft SQL Server™ sources.
The shift to client-server computing in the enterprise must provide a means of accessing the mission critical data residing securely on corporate mainframes and AS/400s. To optimize the benefits that client-server computing brings to an enterprise, the end user must be able to get the information he or she needs to the desktop where it can then be used. There is pressure today for IT managers to provide end-users with a consistent graphical user interface (GUI) for both their PC personal productivity applications and corporate applications resident on mainframe or midrange computers.
IBM database management systems (DBMS), especially DB2, are the corporate data repositories for many companies. Users, however, cannot reach the information in these host databases because of the difficulties in developing new host-based reports and applications. So-called screen-scraping techniques solve the problem by putting a GUI in front of the existing character-based 3270 or 5250 terminal interface, driving the terminal session in the background as the user uses the mouse to drive the new GUI application. Unfortunately, this approach often suffers from poor performance and requires extensive development skills in the area of screen manipulation and traversal. Moreover, it is often limited by the information that can be gathered from the screens of the existing character-based host application, while the business requirements may dictate access to data that is in the database but not accessible through the existing screens. Finally, this approach can be very maintenance intensive, requiring client application code changes to be made whenever screens on the host are altered.
Products like Wall Data's RUMBA for Database Access solve these problems by providing the end-user and client application developer direct access to host databases. This is accomplished by providing a client side-only software solution that presents an OBDC API interface on the client while using IBM's Distributed Relational Database Access (DRDA) standard to access host databases. Thus, developers and end-users can treat IBM host databases in exactly the same manner as any other OBDC-compliant client/server database, including Microsoft SQL Server and Microsoft Access.
This capability allows developers and application architects to treat IBM host databases as client/server enabled. Important benefits include developer skill-set preservation, reduced application development cycles, reduced maintenance costs, preservation of the investment in existing systems, and a smooth migration path between IBM hosts and LAN-based systems.
The easy-to-use query building and executing capabilities inherent in RUMBA for Database Access allow many programming tasks to be moved from systems and database administrators to the people who actually use the data. End-users can then use their favorite spreadsheets to access and analyze data in timely ways. The business object approach outlined in this paper facilitates this, allowing the developer to provide a powerful and flexible host database query capability while reducing complexity for the end-user.
RUMBA for Database Access builds on the overall RUMBA strategy of allowing users access to information anywhere in their company. The RUMBA for Database Access family of products takes full advantage of the systems network architecture (SNA) and advanced program-to-program communications (APPC) expertise for which WALL Data is noted.
RUMBA for Database Access provides business users with transparent access to IBM DRDA compliant databases, which include DB2, SQL/DS, and OS/400 directly without the added overhead of LAN gateways or host components. RUMBA for Database Access unlocks corporate databases so that business users and developers can quickly and easily build and execute queries, while maintaining the security and integrity of corporate information.
PC developers can use any graphical development tool supporting ODBC to develop client applications. This includes Microsoft Visual Basic for Applications, Microsoft Access, and the Visual Basic for Applications family of desktop application products, which significantly reduce development cycles for applications that need access to host databases. By using ODBC-compliant applications you can get access to not only the IBM DRDA compliant databases but also LAN and server DBMSs, combining data from many sources for the end-user. Thus, applications can be split between old host systems and new servers, allowing tremendous flexibility in building new applications.
Using RUMBA for Database Access in conjunction with OLE 2.0 allows business objects to be built easily and quickly that automate the data access functions for the end user and the application developer.
This approach allows end-users to focus on the logical unit of business information that they are concerned with, rather than the granular database constructs required to get the required information from one or more databases. From a user-interface standpoint, OLE 2.0 also allows for drag and drop query execution, and the rapid movement of the resulting data between applications.
As an ODBC-compliant driver, RUMBA for Database Access also allows for many of the constructs available in FMC, Visual Basic, and Visual Basic. to be used to perform the data access functions, and then present the results through an OLE automation interface.
While the complete discussion of building reusable business objects is beyond the scope of this paper, a brief discussion helps set the stage for the development topics outlined below.
The concept behind business object development is to build reusable objects that present business-level property and method interfaces to the developers of business applications. This is in contrast to typical data access development where the business application developer must have good skills in both user interface/data manipulation and data acquisition. By building an object that hides the data access and provides a business-level data interface, business application developers can simply call the object to retrieve or set a particular data value, or execute a business transaction.
This approach is particularly valuable when approaching host data and business rules that exist in the typical corporate enterprise network today. The business object approach shields the business application developer from dealing with the complexities of data access, security verification, protocol knowledge, and different access methods between systems.
Building a business object means first defining the business values and functions of the business. For example, let's assume that the task is to build an OLE 2.0 business object that represents a customer in the life insurance industry. In the past, the business application developer would typically have to deal with accessing a mainframe DB2 database for a variety of customer information, and implement logic that correctly executes business functions like beneficiary change, death benefit change, medical history update, and so on.
Using a business object approach, we can instead easily construct an OLE 2.0 business object that represents that customer and their associated business functions, and provide the business application developer with a very high-level business object interface to that host information. Using RUMBA for Database Access and a tool like Visual C++, an object can be constructed that provides properties like:
name, address, social_security_number, age, beneficiary, non_smoker,...etc.
Likewise we can expose methods that represent the database functions already available on the host in the form of stored procedures. In this example, these methods would include functions like:
beneficiary_change, calculate_premium, create_policy,...etc.
As a result, the business application developer can now focus on developing an application that meets the end-user's requirements, and not worry about the complexities of data access and transactions, or even where and how that data is stored. Instead of complex data access code, these developers can now write code that looks like this (using Microsoft Visual Basic®, Applications Edition):
dim customer as object
set customer = createobject ("insurance_customer")
customer.name = name.text
customer.beneficiary = beneficiary.text
return_code = customer.beneficiary_change
Thus the amount of code written by the application developer is vastly reduced, and no understanding of the underlying systems, protocols, and data access is required. Moreover, when anything on the back end changes, the object is simply updated to accommodate the change and the business application code remains completely unmodified.
Let's turn now to the process of building a reusable business object that accesses host data, using Wall Data's RUMBA for Database Access and Microsoft Visual C++™.
The particular business environment used as an example in this white paper is one where the target data resides on an IBM AS/400. The database itself is the ever-popular PUBS database, which contains information on Authors, Titles, Royalties, and so forth.
The overall connection between the client PC and the AS/400 is shown in the following diagram.
There are at least three important items to note in the preceding diagram:
1. There is no terminal emulation application on the client PC. The application depicted here is an OLE 2.0 client object.
This client object activates an OLE 2.0 server object that provides a shell around an ODBC API. The ODBC subsystem, in turn, communicates with the Microsoft SNA Server's APPC DLL.
2. The ODBC subsystem (RUMBA for Database Access) translates the ODBC functions into IBM's DRDA (Distributed Relational Database Access) SQL protocol. This is the protocol required to talk directly to the AS/400's relational database, which is named DB2/400.
3. The DRDA server transaction program is already present on the AS/400. There is no additional software to buy or install. The DRDA server is a multithreaded application that processes SQL statements delivered to it. Unlike a 5250 application, where the "terminal emulator" must navigate the host's screens to access the required information, the DRDA server provides direct access to the desired data.
The AS/400 data system is a relational database. This ODBC-DRDA system allows the client PC to take advantage of that fact.
Again, the topic of this paper is accessing business data using OLE 2.0 objects. The previous diagram shows the physical connection, and the discussion addresses some of the elements of the physical connection.
The following sections address how this ODBC-DRDA technology can be used in an OLE 2.0 environment such that a developer can create objects that can be distributed to end-users for activation in their own day-to-day work.
There are two major elements in this OLE 2.0 application--the client and the server. The client is represented by an object container application. An example of this type of client is shown in the following figure..
The structure of the application itself is that of multiple folders. Only the "OBJECTS" folder is shown in the above figure. Other folders could be an Excel-type grid to receive numerical data, an object property configuration folder, and so forth.
The two objects shown here, the Author By State object and the Sales By Store object, when activated, link into the OLE 2.0 server and retrieve the appropriate data from the AS/400 database.
The properties of each object would define the ODBC data source as well as the libraries, tables, and rows to be returned when the object is activated. Other properties could include the appropriate security operations, the maximum rows returned per activation, and so on. The CONFIGURATION tab would provide for the definition of these properties.
In this particular example, the container application is a Database Access client. Thus the configurator is linked into the RUMBA for Database Access configuration elements. These RUMBA for Database Access configuration parameters become properties of the objects in the container itself.
For example, in this case we are going to access an AS/400 database. Important parameters are:
1. The particular communications systems to be used. In this case it will be the Microsoft SNA Server. This information is stored in the ODBC.INI file. This file also contains the name of the target AS/400 and the Relational Database name (RDB name).
A sample ODBC.INI file is shown below.
This file was created using RUMBA for Database Access's ODBC Data Source configurator. This configurator panel is shown below.
2. Other important parameters are the queries that are to be activated by the objects. These queries are stored in a file named RUMBASQL.TXT. The configuration initialization routine goes into this file, retrieves the query names, and creates objects for each of the queries it finds.
It also attaches the SQL query itself to the object. The SQL statements are also stored in RUMBASQL.TXT.
A sample RUMBASQL.TXT file is shown below.
If one chooses to change properties, for example, it is very straightforward to execute the Query Configurator program to change the SQL statement. This panel is shown below.
You may also use this panel to point to a specific destination for the results set. If you leave it at the default, Grid, the OLE 2.0 object will determine where to place the results set.
The initial query was constructed using the RUMBA for Database Access point-and-click query builder. This panel is shown below.
In this query, the desired Library, Table, and Columns are highlighted. When you do highlight them, the SQL Query Statement is created dynamically. When you are finished with this point-and-click query build (including conditions), you merely save it. The default save file is RUMBASQL.TXT. Newly-created queries are appended to the file.
Because it is a plain-text query, you may also choose to edit it with a text editor as long as you are familiar with SQL.
In the previous sections we have examined the connectivity and database access technology to be used by the OLE 2.0 objects. The following sections will outline the methodology for executing these elements as OLE 2.0 objects.
As discussed previously, this application consists of an OLE 2.0 Client and Server. The client application was shown previously with two objects.
The server does not have a visual representation. It is activated by the client and delivers the requested data back to the client.
In this particular case the server is an OLE 2.0 wrap around the RUMBA for Database Access ODBC API. In essence, the ODBC functions are linked to the OLE 2.0 object using a straightforward Visual C++ program. The source code for this server OLE 2.0 wrap for the ODBC functions in included on the CD ROM for the Tech Ed conference. The source is too voluminous to include in hard-copy in this paper. However, a small section showing some of the OLE 2.0 to ODBC function calls is shown in the following section.
This is an excerpt of the source code only. The full OLE 2.0 Server source code is on the CD. It is important to note that the OLE 2.0 server created with this source code controls the RUMBA for Database Access product. Source code for the RUMBA for Database access is not required, nor included on the CD. It is a standard Wall Data product.
Source code for the Business Data Container object shown previously is not included on the CD ROM. Source code is included, however, for a sample OLE 2.0 client that exercises the above server. This client is based on the CPP Demo included in the ODBC software development kit (SDK) (Version 2.01). This sample client merely issues ODBC calls and posts the results.
The example OLE 2.0 application discussed in this paper describes data access on an IBM AS/400. It is equally straightforward to access DB2 and SQL/DS data on an IBM Mainframe. Both RUMBA for Database Access and the Microsoft SNA Server support Mainframes and AS/400s. All of the diagrams, figures, and listing segments would look only slightly different for Mainframe access. This OLE 2.0 business object method of accessing mainframe data can change the way corporations view and use their mainframes. They are becoming giant file servers--this technology can accelerate that trend.
The end user activates the objects in a variety of ways. He can double-click on the object, in which the results set will go to the destination specified in the configuration process (default is Grid). Or, he can drag the object into an OLE 2.0-enabled application, such as Microsoft Excel or Microsoft Word.
Dragging the "Sales By Store" object into an Excel spreadsheet is depicted in the figure below.
Or, the user may imbed the Business Data Container in Microsoft Excel or Microsoft Word (for example) and execute one of the objects. The results set will be delivered to the host application.
Most users will probably use the drag and drop method, which is usually the most intuitive.
Business users require convenient access to corporate data. Much of this data resides in relational databases on IBM hosts, including DB2, SQL/DS, and DB2/400. Terminal-based applications can be awkward to use as needs change and, especially, as the desktop system changes.
This white paper has described methods to integrate data that lives on IBM host systems into today's client/server environment, including OLE 2.0 access to this data.
There no longer needs to be a barrier between LAN-based databases and IBM host-based databases.
© 1995 Microsoft Corporation.
THESE MATERIALS ARE PROVIDED "AS-IS," FOR INFORMATIONAL
PURPOSES ONLY.
NEITHER MICROSOFT NOR ITS SUPPLIERS MAKES ANY WARRANTY, EXPRESS
OR IMPLIED WITH RESPECT TO THE CONTENT OF THESE MATERIALS OR THE
ACCURACY OF ANY INFORMATION CONTAINED HEREIN, INCLUDING, WITHOUT
LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS
FOR A PARTICULAR PURPOSE. BECAUSE SOME STATES/JURISDICTIONS DO
NOT ALLOW EXCLUSIONS OF IMPLIED WARRANTIES, THE ABOVE LIMITATION
MAY NOT APPLY TO YOU.
NEITHER MICROSOFT NOR ITS SUPPLIERS SHALL HAVE ANY LIABILITY FOR
ANY DAMAGES WHATSOEVER INCLUDING CONSEQUENTIAL INCIDENTAL, DIRECT,
INDIRECT, SPECIAL, AND LOSS PROFITS. BECAUSE SOME STATES/JURISDICTIONS
DO NOT ALLOW THE EXCLUSION OF CONSEQUENTIAL OR INCIDENTAL DAMAGES,
THE ABOVE LIMITATION MAY NOT APPLY TO YOU. IN ANY EVENT, MICROSOFT'S
AND ITS SUPPLIERS' ENTIRE LIABILITY IN ANY MANNER ARISING OUT
OF THESE MATERIALS, WHETHER BY TORT, CONTRACT, OR OTHERWISE SHALL
NOT EXCEED THE SUGGESTED RETAIL PRICE OF THESE MATERIALS.
 |
Click Here to Search TechNet Web Contents | TechNet CD Overview | |
Microsoft TechNet Credit Card Order Form At this time we can only support electronic orders in the US and Canada. International ordering information. |
| Go To TechNet Home Page |
©1996 Microsoft Corporation |  Go To Microsoft Home Page |