Structured query language to IMS transaction mapper

Details Structured query language to IMS transaction mapper Structured query language to IMS transaction mapper

1997-12-02

2001-06-26

Kulik, Paul V. (Department: 2177)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C709S241000

Reexamination Certificate

active

06253200

ABSTRACT:

BACKGROUND OF THE INVENTION
1. Field of the Invention
The present invention is directed to a middleware tool to allow access to information management system (IMS) transaction based computer systems from a structured query language (SQL) application program. In particular, the present invention is directed to a system wherein the SQL application can be used to communicate and operate with IMS transaction based systems without revising or migrating the transaction based system.
2. Prior Art
Relational databases have been used extensively over the past few years. Relational databases store all data in tables. In a relational system, tables have horizontal rows and vertical columns (sometimes referred to as files, records and fields, respectively) A set of related tables forms a database. The tables in a relational database are separate but equal with no hierarchial ranking of tables.
More recently, relational databases have standardized on a language called structured query language (SQL), which first appeared in the mid-70's. SQL employs a set of commands in a declarative language which specifies which data is to be accessed. SQL statements consist of four basic verbs: insert, delete, update and select. SQL is set theory oriented and is a complete data language which supports data definition, data control, and transaction management. SQL has become the standard language of networked database servers.
Open database connectivity (ODBC) is becoming a defacto standard for Windows™ platform client applications. ODBC defines an application programmer interface (API) for access to multiple database management systems.
Databases that organize the data into an hierarchy are called hierarchical systems. IMS transaction-based systems often employ such systems in mainframe environments. The IMS database system comprises tree structured entities from which logical data files are defined. Each class of data is located at different levels along a particular branch that stems from the root. Under the IMS database system, there is often more than one method to retrieve any one piece of data.
Another trend is the migration from mainframe computer systems to PC based client/server systems. Migration requires an expensive translation which may take years to accomplish.
Accordingly, it would be desirable to provide an interface between a mainframe transaction based system and the SQL ODBC application. Heretofore, it has not been possible to map a non- mainframe SQL request with a mainframe based IMS transaction using client-based middleware. It is desirable for both a client ODBC application in SQL language and a mainframe IMS transaction-based system to communicate and operate with each other.
It is also desirable not to alter the IMS transaction based system for a number of reasons. Typically, a great amount of investment has been made to the IMS transaction based systems. Additionally, there is a level of reliability and security with existing IMS based systems. The layers of protection built into the mainframe system programs control the input and output of the databases. Accordingly, it would be advantageous to provide a system that will communicate and interact with an IMS transaction based system without revising or migrating the IMS transaction based system.
Thus, it would be advisable to use the mainframe transaction based system as a server for the client SQL applications.
Presently, there are a number of approaches available that will provide access from an ODBC client application to mainframe based databases. Screen scrapers will allow for custom client applications to be written that will access mainframe system screens directly by mapping client access variables directly to screen row and screen column positions. These tools do not allow a client application to issue SQL statements to satisfy application queries. Rather, they are dependent on direct mappings to data contained in the mainframe screens. Consequently, client side application development is restricted since the screen scraping middle layer cannot translate SQL statements to mainframe transactions.
Direct database access tools will allow client applications to access mainframe databases directly using SQL statements. The direct database access tools do not allow access to the often vast base of existing transaction based mainframe systems. Although the client application has direct access to the mainframe database using SQL, it must re-implement business rules needed to interface to that database.
By way of example, Song (U.S. Pat. No. 5,432,930) discloses a system to access COBOL data files with an SQL database language by creating dictionaries for each COBOL data file and assigning attributes to them. Song, however, uses CCSQL (COBOL SQL) which is a native COBOL language different from ODBC SQL and embeds CCSQL (COBOL SQL) statements in the COBOL. Song does not allow one to translate an ODBC request to retrieve data from the mainframe IMS transaction based system using existing mainframe programs.
Feather (U.S. Pat. No. 4,205,371) provides a method for executing a first system request in a second system environment. Feather, however, requires conversion of database files from one environment to the other.
Accordingly, it is a principal object and purpose of the present invention to provide access to information management system (IMS) transaction based computer systems from a structured query language (SQL) application without revising or migrating the transaction based system.
It is an additional object and purpose of the present invention to provide an ODBC to APPC interface to access IMS transactions.
It is an additional object and purpose of the present invention to provide an ODBC to APPC interface to access IMS transactions that evaluates the efficiency of access paths in order to decide which path would be the most efficient.
SUMMARY OF THE INVENTION
The present invention provides a process and a system to access an IMS transaction based computer system from a structured query language (SQL) application program.
An SQL statement would be entered by a user through a computer utilizing an ODBC software application program. The SQL statement will be delivered to an ODBC driver manager. The driver manager is a self-contained software module designed to provide software services to the ODBC application program.
The ODBC driver manager is in communication with an SQL mapper engine through an ODBC interface. The SQL statement will be parsed to determine how the SQL statement can be satisfied by executing one or more mainframe transactions. The statement will be checked for syntactical and semantical correctness and for its lexicon.
The mapper engine will read a binary file containing virtual table and column mappings to determine a virtual database scheme. The mappings are built by an analyst or analysts in advance to define virtual tables and columns representing a logical view of and path to the hierarchical or relational database of the IMS system.
The mapper engine translates the SQL statement into possible transaction execution sets using the binary mappings file. The mapper engine reads the mappings and determines, using a non- deterministic algorithm, that a transaction based solution exists for a given SQL statement issued by the application program.
The mapper engine may determine that a particular combination of transactions can satisfy the SQL statement requests. Costs are computed for each of the solution series. Various factors are considered in the cost computation, including (1) the number of different transactions in the series; (2) an “extra cost” figure associated with each transaction; (3) the number of times each transaction will be executed; (4) the operation being performed by the transaction; and (5) the number of records expected to be returned from invocation of each inquiry.
Thereafter, a lowest cost transaction set is chosen and executed via a transaction data stream through a virtual circuit communications interface to an APPC server.
Finally, the APPC server is in communication with and

Affiliated with

Also associated with

Rating

Structured query language to IMS transaction mapper does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Structured query language to IMS transaction mapper, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Structured query language to IMS transaction mapper will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-2470342