SQL access to system specific data

Details SQL access to system specific data SQL access to system specific data

2000-05-09

2004-05-04

Robinson, Greta (Department: 2177)

Data processing: database and file management or data structures

Database design

Data structure types

Reexamination Certificate

active

06732089

ABSTRACT:

BACKGROUND OF THE INVENTION
1. Field of the Invention
This invention relates in general to database management systems performed by computers, and in particular to a remote data access technique utilizing a stored procedure wrapper, wherein system specific data and subroutines may be accessed using SQL.
2. Description of Related Art
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving data. RDBMS software using a Structured Query Language (SQL) interface is well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Organization (ANSI) and the International Standards Organization (ISO).
In RDBMS software all data is externally structured into tables. The SQL interface allows users to formulate relational operations on the tables either interactively, in batch files, or embedded in host language, such as C, COBOL, etc. Operators are provided in SQL that allow the user to manipulate the data, wherein each operator operates on either one or two tables and produces a new table as a result. The power of SQL lies on its ability to link information from multiple tables or views together, to perform complex sets of procedures with a single statement.
Although SQL is standard to some degree across a number of platforms, including Windows NT, Windows 95, AIX, OS/390 and AS/400, there remains platform specific information that can not be obtained using SQL. Native commands are commands that run only on a specific platform and that cannot be utilized by SQL in conventional approaches. An example of a native command is “DSPFD FILE,” which displays a file description for obtaining file member information on an AS/400 platform. Other examples include “ps -aux” for processing information running on an AIX machine and “SET” for displaying Windows environment information on a Windows NT system. These native commands obtain platform specific information that cannot be directly obtained with SQL.
One conventional approach for utilizing system specific routines utilizing SQL is Remote Procedure Call (RPC). RPC is generally a mechanism for allowing subroutines to execute on a remote system across a network.
FIG. 1
depicts the difference between a traditional direct procedure call
101
and a remote procedure call
103
.
The code that enables RPC to access remote systems is broken into four components, shown in FIG.
2
. The client stub
201
and server stub
203
are compiled C language source files specific to the procedures being sent across the network. The client runtime
205
and server mainline
207
are generic. The user of the RPC describes the interface between the main program
209
and the subroutines
211
using an Interface Definition Language (IDL). The RPC compiler then generates the client stub
201
and the server stub
203
based on the IDL description. Common interface definition languages are Sun's NIDL and the DCE IDL.
Once compiled, the client stub
201
is linked with the client runtime
205
to provide access to the remote procedures that otherwise could not have been accessed with SQL. Similarly, the server stub
203
, once compiled, is linked with the server mainline
207
to format commands prior to calling the actual user's subroutines. The RPC code must correctly translate data from the format used by the client to the format used by the server and vice versa. This requirement, in turn, yields the constraint that the client stub
201
must be compatible with the server stub
201
. Although RPC is functional in its approach for accessing system specific subroutines, it is limiting in that it requires installation of additional code on both the client and the server systems.
A different conventional approach for accessing system specific information is an SQL stored procedure. With this approach, as detailed in
FIG. 3
, specific procedure programs
301
are stored on a server
303
. The client
305
can then use an SQL statement to invoke the stored procedure on the server side. The process is cumbersome, however. For example, a procedure program
301
must be created and registered using an SQL statement create procedure. The program
301
must then be installed in the server. If, for example, the program
301
is stored on the server
303
at location MYSCH.STRPROC, the client
305
will use the SQL statement: EXEC SQL EXECUTE IMMEDIATE CALL MYSCH.STRPROC to invoke the stored procedure
301
on the server side
303
. The stored procedure
301
then executes native commands to obtain system specific data and the results are passed back. The client SQL call statement
305
must match the signature of the stored procedure
301
. This approach, much like the previously described conventional approach, requires installation of additional code on the server side every time a new client function is required.
SUMMARY OF THE INVENTION
It is an object of the present invention to provide a method and system for accessing system specific data without the installation of additional server side code. It is a further object of the invention to provide a method that does not require installation of additional client side code in cases that involve separate requests for the same system specific information. The method of the invention is less complex than conventional methods and results in lower software maintenance costs.
One embodiment of the present invention includes a method of using SQL to access system specific information and functions in a computer system. The method utilizes table formatted native command output files as well as “wrapper” stored procedures for running native commands and accessing system specific information through SQL. The method does not require installation of additional code on the server time for each new client function request.
Another preferred embodiment of the present invention is a computer system for optimizing an SQL query to system specific information, utilizing the above-described method embodiment of the present invention.
Yet another preferred embodiment of the present invention is a program storage device readable by a computer tangibly embodying a program of instructions executable by the computer to perform the above-mentioned method embodiment of the present invention.


REFERENCES:
patent: 5600833 (1997-02-01), Senn et al.
patent: 5615337 (1997-03-01), Zimowski et al.
patent: 5713018 (1998-01-01), Chan
patent: 5794229 (1998-08-01), French et al.
patent: 5842205 (1998-11-01), Brann
patent: 5899990 (1999-05-01), Maritzen et al.
patent: 5937415 (1999-08-01), Sheffield et al.
patent: 5950188 (1999-09-01), Wildermuth
patent: 6006224 (1999-12-01), McComb et al.
patent: 6102969 (2000-08-01), Christianson et al.
patent: 6135650 (2000-10-01), Goebel
patent: 6327629 (2001-12-01), Wang et al.
patent: 6370681 (2002-04-01), Dellarocas et al.

Affiliated with

Also associated with

Rating

SQL access to system specific data does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with SQL access to system specific data, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and SQL access to system specific data will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3207621