System and method for virtualization of relational stored procedures in non ...

SYSTEM AND METHOD FOR
VIRTUALIZATION OF RELATIONAL
STORED PROCEDURES IN NON-NATIVE
RELATIONAL DATABASE SYSTEMS

BACKGROUND OF THE INVENTION [0001] 1. Technical Field

[0002] The present invention relates in general to a system and method for accessing remote databases. More particularly, the present invention relates to a system and method that virtualizes stored procedures to allow the use of nonnative stored procedures from a database environment. [0003] 2. Computer Program Listing Appendix [0004] A Computer Program Listing Appendix has been filed concurrently with the present application and is incorporated by reference in its entirety, herein. Listings of four computer programs in the Java programming language are included on the CD-ROM in compliance with CFR § 1.96 and MPEP § 608.05. Two identical copies of the CD-ROM, labeled "Copy 1" and "Copy 2," have been included. [0005] The files "INIT", "CREATEJAVASOURCECODE.TXT", and "DB20RACLEDATATYPEMAPPING. TXT," were created on or before Jul. 21, 2005, and the program named "DB2SPNAME.TXT" was created on or before Jun. 7, 2006.

[0006] 3. Description of the Related Art [0007] Relational database applications have the ability to run "stored procedures" that are essentially, software applications with SQL statements and other control-flow language instructions. While stored procedures usually include SQL statements, they differ from SQL statements in that stored procedures are generally pre-compiled by the database management system (DBMS). Stored procedures are widely used by relational database application developers. Some of this popularity stems from the pre-compilation aspects of stored procedures, as mentioned above. [0008] As indicated above, stored procedures are used with a particular database management system (DBMS). A DBMS is a collection of programs that enables the user to store, modify, and extract information from a database. There are many different types of DBMSs, ranging from small systems that run on personal computers to huge systems that run on mainframes. There are also many different vendors that supply DBMSs, such as International Business Machines Corporation (IBM), Oracle Corporation, Microsoft Corporation, Teradata, Sybase, Inc. and others. These DBMS vendors often offer multiple DBMS products to serve different needs and markets. Consequently, each DBMS has its strengths and weaknesses when compared with other DBMSs.

[0009] Stored procedures in most modern database environments have many similarities to other programming languages. In particular, stored procedures are often able to accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch; and contain programming statements that perform operations in the database, including calling other procedures; return a status value to a calling procedure or batch to indicate success or failure (and the reason for the failure). [0010] While stored procedures are widely used, there are some shortcomings to their use. One shortcoming faced by relational database application developers is that stored procedures are specific to a particular DBMS. In other words, a stored procedure native to a first type of DBMS

cannot inherently call stored procedures that are native to a second, different, type of DBMS.

[0011] What is needed, therefore, is a system, method, and program product that virtualizes a stored procedure native to a remote DBMS so that it can be integrated with a local remote procedure. What is further needed is a system, method, and program product that runs the remote stored procedure on the remote DBMS and returns the resulting data to the local stored procedure in a format usable by the local stored procedure.

SUMMARY

[0012] It has been discovered that the aforementioned challenges are resolved using a system, method, and program product that identifies a remote stored procedure stored in a remote database management system, and automatically generates a local stored procedure stored in a local database management system. To automatically generate the local stored procedure, local and remote metadata are gathered corresponding, respectively, to a local database management system and a remote database management system. The remote metadata is used to create a call statement to the remote stored procedure. The created call statement maps input values, input to the local stored procedure, to input parameters of the remote stored procedure. Values in the local stored procedure are set by mapping the results from the remote stored procedure to the local stored procedure values.

[0013] In one embodiment, the system, method, and program product searches a database catalog corresponding to the remote database management system before automatically generating the local stored procedure. A search result is received and processed in order to generate a call signature that corresponds to the remote stored procedure. [0014] In another embodiment, the system, method, and program product generates a connection string corresponding to the remote database management system. Data handling code is then generated to handle the remote stored procedure results. In one embodiment, the data handling code includes instructions for changing an output data type of the remote database management system to an data type that is acceptable by the local database management system. [0015] In another embodiment, the system, method, and program product executes the generated local stored procedure which calls the remote stored procedure using the created call statement. The local stored procedure then receives results from the remote stored procedure. The values that are returned can be transformed to correspond to a data type that is acceptable to the local database management system. In one embodiment, the transformed values are displayed to a user of the local stored procedure. [0016] The foregoing is a summary and thus contains, by necessity, simplifications, generalizations, and omissions of detail; consequently, those skilled in the art will appreciate that the summary is illustrative only and is not intended to be in any way limiting. Other aspects, inventive features, and advantages of the present invention, as defined solely by the claims, will become apparent in the non-limiting detailed description set forth below.

BRIEF DESCRIPTION OF THE DRAWINGS [0017] The present invention may be better understood, and its numerous objects, features, and advantages made apparent to those skilled in the art by referencing the accompanying drawings.

[0018] FIG. 1 is a system diagram showing the virtualization procedure querying and retrieving data from a remote database management system and creating local stored procedure files;

[0019] FIG. 2 is a system diagram showing a user interacting with a local stored procedure that calls a remote stored procedure and receives resulting values from the remote database management system; [0020] FIG. 3 is a flowchart showing the steps taken between the user, the virtualization procedure, and the database management systems to generate a local stored procedure that calls a remote stored procedure; [0021] FIG. 4 is a flowchart showing the steps taken to generate local stored procedure code that maps to a remote stored procedure;

[0022] FIG. 5 is a flowchart showing the steps taken initialize the generation of the local stored procedure code that maps to the remote stored procedure; and [0023] FIG. 6 is a block diagram of a computer system in which the present invention can be implemented.

DETAILED DESCRIPTION

[0024] The following is intended to provide a detailed description of an example of the invention and should not be taken to be limiting of the invention itself. Rather, any number of variations may fall within the scope of the invention, which is defined in the claims following the description.

[0025] FIG. 1 is a system diagram showing the virtualization procedure querying and retrieving data from a remote database management system and creating local stored procedure files. Virtualization procedure 100 is used to extract data from remote database management system 120 that has stored procedure support. In other words, remote database management system 120 is a database management system that can store and execute stored procedures. An example of a stored procedure included in remote database management system 120 would be a stored procedure that accesses and manipulates data, such as a database table, being managed by database management system 120.

[0026] At step 110, the virtualization procedure queries the database catalog maintained by remote database management system 120. In one embodiment, the user provides the virtualization procedure a name or identifier of a stored procedure managed by remote database management system 120 and this name or identifier is used when querying (searching) the remote database management system's catalog.

[0027] At step 130, the virtualization procedure receives stored procedure metadata corresponding to the remote stored procedure being managed by remote database management system 120. Remote stored procedure metadata (remote metadata) is data describing the remote stored procedure as well as the remote database management system.

[0028] At step 140, the remote metadata that was received is used to create local stored procedure 150. In one embodiment, a "data definition" file (*.ddl 160) is created with data definition statements that will register the generated local stored procedure with the local database management system. In a Java implementation, the java file(s) (*.java 170)

and class file(s) (*.class 180) include instructions, in the Java programming language for the local stored procedure that was generated.

[0029] FIG. 2 is a system diagram showing a user interacting with a local stored procedure that calls a remote stored procedure and receives resulting values from the remote database management system. After local stored procedure 150 has been generated, using the steps outlined in FIG. 1, the local stored procedure is used by user 200 using the steps outlined in FIG. 2. First, the user executes the data definition file (*.ddl). The data definition file includes a "create" statement 215 that is used, at step 220, to register the generated local stored procedure with local database management system 225. This results in local stored procedure 230 that is registered with local database management system 225.

[0030] At step 240, the user calls local stored procedure 230 that has been registered with local database management system 225. The call to the local stored procedure can either be a direct invocation by the user, such as by using a keyboard to enter the local stored procedure name, or the call can be an invocation through another stored procedure used by the user. In this manner, a single local stored procedure that accesses a remote stored procedure in remote database management system 120 can be called by a variety of local stored procedures managed by local database management system 225.

[0031] At step 250, local stored procedure 230 (registered with the local database management system) calls the remote stored procedure that is managed by remote database management system 120. Local stored procedure 230 includes code to connect to the remote database management system as well as code to call the remote stored procedure using the correct syntax needed to call the remote stored procedure. The connection code and call syntax was generated by the virtualization procedure shown in FIG. 1. [0032] At step 260, local stored procedure 230 receives results from the remote stored procedure that was called. The results that are received are mapped to local stored procedure values that conform to the local database management system. For example, if data returned by the remote database management system is monetary data in a "currency" data type, but the local database management system does not have a "currency" data type, then the local stored procedure transforms the "currency" data type to another data type, such as a decimal data type that is appropriate to the local database management system. [0033] At step 270, the values returned from the remote stored procedure and mapped to local stored procedure values, are returned to user 200. As mentioned before, local stored procedure 230 can be called from another local stored procedure, so the values returned in step 270 can be used by the stored procedure that called local stored procedure 230. The user is also able to display values from the remote database management system that were generated by the remote stored procedure. For example, the remote stored procedure can retrieve data from tables in the remote database management system and this data, once returned to user 200, can be displayed.

[0034] FIG. 3 is a flowchart showing the steps taken between the user, the virtualization procedure, and the database management systems to generate a local stored procedure that calls a remote stored procedure. User processing commences at 300 whereupon, at step 310, the user