Patent Application
20070055644
The present invention relates to database systems, and more particularly to a method and system for improving execution of procedures residing in the database system.
The database request portion 34 is used to perform operations on the database 18, for example writing to the database 18 and reading from the database. The database request portion 34 includes database request statement(s), which are used to perform the operations. For example, database request statements may typically be SQL statements. Such database request statements include but are not limited to the insert statement 36, selects statement, and other database request statements. The database request portion 34 typically utilizes the variables in the logic portion 32.
Although the procedure 30 may be implemented, one of ordinary skill in the art may readily recognize that the implementation may be inefficient. In particular, processes involved in utilizing the variables defined by the procedure 30 may be costly in terms of time and/or resources. For example, bind-in and bind-out operations performed in steps 48 and 50, respectively, involve data movement and validation of data types and are thus costly. This is true even for a procedure 30 that resides entirely within the conventional database system 10. A procedure 30 which resides entirely within the conventional database system 10 may still require bind-in and bind-out operations to be performed despite that fact that the conventional database system 10 should have information relating to all of the variables.
Accordingly, what is needed is a method and system for more efficiently executing procedures, particularly procedures that reside and are executed entirely within the database system. The present invention addresses such a need.
The present invention provides a method and system for using a procedure residing and executed entirely within a database system. The procedure utilizes at least one variable, which has a plurality of attributes. The method and system comprise defining the at least one variable as at least one global variable prior to calling of the procedure. Defining the global variable(s) includes informing the database system of the plurality of attributes of the at least one variable and allowing the database system access to the at least one variable. The method and system also comprise tracking the at least one global variable.
According to the method and system disclosed herein, the present invention provides a method and system for more efficiently implementing procedures within a database system.
The present invention relates to database systems. The following description is presented to enable one of ordinary skill in the art to make and use the invention and is provided in the context of a patent application and its requirements. Various modifications to the preferred embodiments and the generic principles and features described herein will be readily apparent to those skilled in the art. Thus, the present invention is not intended to be limited to the embodiments shown, but is to be accorded the widest scope consistent with the principles and features described herein.
The present invention provides a method and system for using a procedure residing and executed entirely within a database system. The procedure utilizes at least one variable, which has a plurality of attributes. The method and system comprise defining the at least one variable as at least one global variable prior to calling of the procedure. Defining the global variable(s) includes informing the database system of the plurality of attributes of the at least one variable and allowing the database system access to the at least one variable. The method and system also comprise tracking the at least one global variable.
The present invention will be described in terms of a particular procedure. However, one of ordinary skill in the art will readily recognize that the method and system may be used with other procedures having other and/or additional portions such as variables and database request statements. The present invention is also described in the context of particular methods and database systems. However, for ease of explanation steps in the method and portions of the database system may be omitted or combined. One of ordinary skill in the art will readily recognize, therefore, that the method and system in accordance with the present invention may include other and/or additional steps or portions.
To more particularly describe the method and system in accordance with the present invention, refer to
The variable(s) for the procedure are defined as global variables prior to the procedure being called, via step 102. The variables are considered to be global because the variables are preferably accessible by all database request statements in the procedure. Step 102 includes informing the database system of the attributes of each of the variables. For example, the database system may be informed of the type, length, encoding scheme, and value of the variables. In addition, the database is allowed access to the variables in step 102. As discussed above, the variables of the procedure are defined as global variables prior to the procedure being called. In a preferred embodiment, this defining occurs upon building or compiling of the procedure. Thus, step 102 is preferably performed well in advance of the procedure being called.
The global variable(s) are tracked by the database system, via step 104. In a preferred embodiment, the database engine tracks the variables. The tracking includes determining the locations as well as the status of the remaining attributes of the variables. For example, the database system may track the value of each variable in addition to the location. As a result, when the variable is called by a database request statement, the database system is capable of accessing at the location at which the variable is stored and using the variable in executing the database request statements for the procedure. In one embodiment, step 104 is performed using relative offsets and employing a relocation table or directory to convert the relative offsets to pointers to the actual location(s) of the variable(s).
Thus, using the method 100, the variables can be accessed and tracked by the database system, particularly the database engine. Consequently, a specialized structure, such as a SQLDA, is not necessary for managing the variables. In addition, bind-in and bind-out operations can be avoided. This is achieved because the variables are global variables recognized and managed by the database system. Consequently, for statements such as insert or select statements, simple read and write operations not requiring the time of bind-in or bind-out operations may be performed. Thus, using the method 100, a database system can more efficiently use a procedure that resides and is executed entirely within the database system.
Also depicted in the database system 110 are procedures 115, executable structures 116, tracking mechanisms 118, and dynamic variable storage 122. The procedures 115 reside and are executed within the database system 110. The procedure 30 depicted in
The executable structures 116 correspond to the logic portion of the procedures 115 and are generated prior to the corresponding procedures being called. In a preferred embodiment, the executable structures 116 are generated when the corresponding procedures are built, then stored in the catalog 114. The executable structures 116 describe how the database engine 112 is to execute the logic portion of the procedures 115. Consequently, the executable structures 116 effectively include executable code that describes the attributes of the variables used by the procedures 115 to which the executable structures 116 correspond. Thus, the executable structures 116 effectively define the variables in the procedures 115 to be global by informing the database engine 112 of the attributes of the variables and allowing the database engine 112 access to the variables. Thus, the variables may be used by all of the database request statements in the procedure(s) 115 to which the executable structure(s) 116 correspond. The executable structure(s) 116 allow the database engine 112 to access the variables for the procedure(s) 115.
The tracking mechanisms 118 are used to allow the database engine 112 to determine at least the locations of the variables corresponding to the executable structures 115. In a preferred embodiment, the tracking mechanisms 118 also allow the database engine 112 to track the remaining attributes of the variables. For example, the database system may track the value of each variable in addition to the location. In one embodiment, the tracking mechanisms 118 include relocation tables or directories used to convert relative offsets to pointers to the actual location(s) of the variable(s).
The dynamic variable storage 122 is used in executing the procedures 115 and is generated after the corresponding one(s) of the procedures 115 are called. The dynamic variable storage 122 has a global portion 124, which corresponds to the executable structures 116 and a local portion 126. The global portion 124 includes storage allocated for the variables described in the executable procedures 115. The local portion 126 includes storage allocated specifically for the database request statements in the procedures 115 being executed. Both the global portion 124 and the local portion 126 are preferably allocated once the corresponding one or more of the procedures 115 is invoked.
The executable structure 116′ corresponds to the logic portion 32 of the procedure 30. The executable structure 116′ includes a pointer 172 to the dynamic variable storage 122′. In addition, the executable structure 116′ provides definitions 174, 176, and 178 of the variables A and B as well as the parameter NUM, respectively. The definitions 174, 176, and 178 also point to the locations 184, 186, and 188 in the global dynamic variable storage 182 of the dynamic variable storage 122′. In addition, the definitions 172, 174, and 176 effectively define the variables A and B and the parameter NUM to be global, as discussed above. Consequently, the database engine 112 is informed of and can access the variables A and B and the parameter NUM.
The dynamic variable storage 122′ is preferably allocated after the procedure 30 is invoked and includes global storage 182 as well as local storage 189. The global storage 182 corresponds to the variables and the executable structure 116′. The global storage includes locations 184, 186, and 188 store at least the values of the variables A, B, and NUM, respectively and thus correspond to items 172, 174, and 176, respectively. The dynamic variable storage 122′ may also include local storage 189, for use when executing the logic portion of procedure 130.
The executable structure 190 corresponds to the database request portion 34 of the procedure 30 and is preferably generated prior to the procedure 30 being invoked. Also in a preferred embodiment, the executable structure 190 is generated at substantially the same time as the executable structure 122′. The database request executable structure 190 includes a mechanism for finding a location of the global variable(s) at execution time. The executable structure 190 includes a pointer 192 to the dynamic variable storage 196 (described below). In addition, the executable structure 190 allows the variables A and B to be accessed by providing pointers 192 and 193 to the appropriate definitions variables 184 and 186. Consequently, the database engine 112 can access the variables A and B and the parameter NUM.
The dynamic variable storage 194 corresponds to the executable structure 190 and is allocated after the procedure 30 is invoked. The dynamic variable storage 194 corresponds to the database request portion 34 of the database request. The dynamic variable storage 194 includes local storage 196 that is specific to the database request statement. The local storage 196 is used for storage during execution of the database request statement, the insert statement, of the procedure 130.
Referring to
Using the database system 110, particularly the executable structures 116 and tracking mechanism 118 in conjunction with the dynamic variable storage 122, the variables in the procedures 115 can be accessed by the database engine 112. A specialized structure, such as a SQLDA, is not necessary for managing the variables. Instead, the database engine 112 may access the global variables corresponding to the executable structures 116. In addition, bind-in and bind-out operations for the variables on each database requests within the same procedure can be avoided. The database system 110 may, therefore, operate more efficiently.
Thus, using the method 200, the structures 116 and 116′, 118, 122, 122′ and 194, are provided at the appropriate times. Consequently, the variables in the procedures 115 can be accessed by the database engine 112 without requiring bind-in and bind-out operations. As a result, the database system 110 can operate more efficiently.
A method and system for using a procedure in a database system more efficiently has been disclosed. The present invention has been described in accordance with the embodiments shown, and one of ordinary skill in the art will readily recognize that there could be variations to the embodiments, and any variations would be within the spirit and scope of the present invention. Software written according to the present invention is to be stored in some form of computer-readable medium, such as memory, CD-ROM or transmitted over a network, and executed by a processor. Consequently, a computer-readable medium is intended to include a computer readable signal which, for example, may be transmitted over a network. Accordingly, many modifications may be made by one of ordinary skill in the art without departing from the spirit and scope of the appended claims.
