Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2000-07-12
2003-09-02
Vu, Kim (Department: 2172)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000
Reexamination Certificate
active
06615214
ABSTRACT:
FIELD OF THE INVENTION
The present invention is directed to an improvement in computing systems and in particular to computer systems which provide for accommodation of data definition statements in the sharing of dynamic SQL (Structured Query Language) statements.
BACKGROUND OF THE INVENTION
In relational DBMSs, such as the DB2Ô database, an SQL statement is compiled into an executable object which contains the logic needed to satisfy the SQL statement. In DB2 such executable objects are referred to as sections. It is known to use an SQL.cache to store the compiled SQL statements (the sections) to permit reuse of compiled SQL statements. This permits SQL statements to be used without the need to recompile the statements, but only where the compilation environment and the system catalog information is unchanged. This reuse may be by the application initially using the SQL statement (resulting in the initial compilation of the SQL statement) or by any other application with the same statement text and compilation environment (such a reuse by another application is a sharing of the compiled SQL statement).
In general terms, the compilation environment is described as being the context of the SQL request. The compilation environment includes the information which can affect the final form of the executable objects generated by the compiler. The information potentially found in the compilation environment information includes such items as the isolation level requested, the requested query optimization level, the codepage of the requesting application, and other information which affects the executable objects and which is known to those skilled in the art. The system catalog information in a relational DBMS typically contains information about how the data in the relational database is structured. Table definition information such as the number of columns, the column type, and index information are examples of the type of information typically maintained in the system catalogue information. In relational DBMSs, it is common to have system catalogue information stored as tables in each database, these are sometimes referred to as catalogue tables or system tables.
Typically in DBMSs the compilation, including optimisation, of SQL statements is a costly step in processing the SQL statement and therefore the reuse of compiled sections has a significant impact on the efficiency of the DBMS. The caching of SQL sections (executable objects) is particularly useful in the case of dynamically generated SQL. In systems such as DB
2
, which permit the reuse of compiled SQL statements, it is important that the reused executable objects are not reused when changes have been made to the compilation environment or system catalogue tables such that the stored executable object is no longer a valid representation of the SQL statement.
To prevent such inappropriate reuse, it is known to remove from the cache those executable objects created in a unit of work after a data definition language (DDL) statement has been executed. The DDL may potentially change the system catalogue information such that the stored compiled version of the SQL statement may no longer be valid as a reusable equivalent of the SQL statement. Similarly, after a DDL statement has been issued, prior art systems prevent other applications from sharing any sections added to the cache by the application using the DDL statement until the DDL statement is committed.
It is therefore desirable to have a DBMS which permits, under the appropriate circumstances, executable objects to be shared rather than excluding them from being shared when added to the cache by an application in a unit of work after the execution of a DDL statement
SUMMARY OF THE INVENTION
According to one aspect of the present invention, there is provided an improved system for the accommodation of DDL statements in the sharing of dynamic SQL statements.
According to another aspect of the present invention, there is provided an SQL relational database management system including an SQL cache for the storage of sections corresponding to SQL statements, the relational database management system further including an identification list for a current unit of work for an application, the identification list storing database entity identifiers corresponding to database entities altered by DDL statements issued in the current unit of work, an SQL cache manager for identifying and marking the SQL sections compiled after the issuing of DDL statements in the current unit of work which are non-shareable with other applications during the current unit of work, where the shareable status of an SQL statement is determined by comparing data dependencies returned by the compiler for the SQL statement with the database entities found on the identification list. The SQL cache manager provides functionality specific to the SQL cache and may be implemented in different components of the system.
According to another aspect of the present invention, there is provided the above system, further comprising an application list for the current unit of work, the application list comprising entries corresponding to SQL sections added to the SQL cache which are non-shareable.
According to another aspect of the present invention, there is provided the above system in which each entry in the application list further comprises a reference to the current active savepoint.
According to another aspect of the present invention, there is provided the above system in which the SQL cache manager is responsive to a savepoint rollback to remove from the SQL cache all entries on the application list added after the savepoint is responsive to a unit of work rollback to remove from the SQL cache all entries on the application list, and in which the SQL cache manager is responsive to a work commit to make shareable all entries on the application list.
According to another aspect of the present invention, there is provided a computer program product for use with a computer comprising a central processing unit and random access memory, said computer program product comprising a computer usable medium having computer readable code means embodied in said medium providing for the determination of shareable sections in an SQL cache in an SQL relational database management system, the computer program product including computer readable program code means for implementing the above system.
Advantages of the present invention include the ability to share more sections found in the SQL cache and to therefore increase the efficiency of SQL relational DBMSs.
REFERENCES:
patent: 5717911 (1998-02-01), Madrid et al.
patent: 6009271 (1999-12-01), Whatley
patent: 6105033 (2000-08-01), Levine
patent: 6374257 (2002-04-01), Guay et al.
Bird Paul M.
Chan Yuk-kuen
Snowbell Michael J.
Sawyer Law Group LLP
Vu Kim
Woo Isaac
LandOfFree
Accommodation of data definition statements in the sharing... does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Accommodation of data definition statements in the sharing..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Accommodation of data definition statements in the sharing... will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3082108