Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
1998-09-29
2001-11-20
Black, Thomas (Department: 2171)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C707S793000
Reexamination Certificate
active
06321235
ABSTRACT:
FIELD OF THE INVENTION
This invention relates to relational database management systems and more particularly to a method of caching dynamic and static SQL statements so that these statements having been prepared once may be reused by multiple requesters.
BACKGROUND OF THE INVENTION
Structured Query Language (SQLd is the database access language most commonly used to access relational databases (such as the DB2 product sold by IBM Canada Ltd.) in an open, heterogeneous environment. Although this disclosure refers to the DB2 relational database product sold by IBM, individuals skilled in the art will recognize that the caching of database access statements is applicable to any relational database management system (RDBMS).
Within this specification including the claims, the following terms will be used:
Access Plan An access plan is the method(s) chosen by the SQL Compiler to satisfy an application request as stated in the form of an SQL statement.
Agent A process used by a RDBMS to provide services for an application request.
Node A node is a physical entity (eg. a processor and memory) that is used to process application requests to the RDBMS and contains some or all of the database. A serial version of the RDBMS contains at most 1 node, while a parallel version of the RDBMS can contain 1 or more nodes.
Package A package is associated with an application and contains the information required by the RDBMS for all SQL statements defined in that application. The information in a package consists of a collection of sections and the compilation environment settings (e.g. compile or binding options) used to compile any static SQL statements; some of these same settings are also used as the default environment for any dynamic SQL statements compiled by the application during execution.
Section A section contains all the information required by the RDBMS to execute the chosen access plan for an SQL statement. A section is the compiled version of the access plan chosen by the SQL compiler.
Section Entry A section entry contains information about a specific section as well as the SQL statement corresponding to that section.
There are two basic types of SQL statements, static and dynamic. In using static SQL the user embeds SQL requests for data in an application program. An SQL precompiler removes these statements from the application program and replaces them with function calls whose parameters indicate a specific section entry for the package corresponding to the current source file. The removed SQL statement is then sent to DB2 for compilation. Compiling (also known as preparing) a SQL statement is the process by which the DB2 SQL compiler chooses and builds an access plan to efficiently resolve the SQL statement. The access plan is saved in its executable format, a section, in the system catalogues.
The parsing of the statement and building of the access plan can be relatively long and complicated. Compilation of static SQL improves run time performance by building the access plan before the application is executed.
Dynamic SQL is generally used for ad hoc SQL requests. For example, in a database used to track sales of individual products, a dynamic SQL query may be invoked to list the top ten products sold, by sales region. Depending upon the nature of a dynamic SQL request, the time required to parse it and create an access plan to satisfy the user request can be significant. Further, if the dynamic SQL request is repeated later in the application by the same agent or perhaps by a different agent, a new access plan must be created in each instance. Thus, the creation of an identical access plan may often have to be repeated, thereby impacting performance of the application.
Solutions to improve application performance include the concept of caching all the information necessary for an agent to invoke an access plan.
IBM Technical Disclosure Bulletin volume 39 No. Feb. 2, 1996 pages 113-116 discloses a caching scenario within a client/server environment which applies to a applications running on a single client and using a single interface (the one providing the cache). The present invention is a server cache available to all application requests regardless of interface or client.
IBM Technical Disclosure Bulletin volume 39 No. Feb. 2, 1996 page 179 discusses the concept of caching dynamic SQL statements. This one page synopsis of the idea does not address the issue of multiple applications with multiple agents being able to share the cached SQL statement IBM Technical Disclosure Bulletin volume 39 No. Feb. 2, 1996 at pages 235-236 discloses a concept known in the art as “extended dynamic SQL”. Extended dynamic SQL is a method which allows users to specify that dynamic SQL statements relating to a specific package be “captured” and stored in the system catalogues, in effect converting the statements to static SQL. There is no concept of sharing these statements beyond the package with which they are associated. As users of the same package may not be aware that a dynamic SQL statement has been “captured”, program logic is required to detect multiple prepare attempts for the same statement and to map it to an existing “captured” statement. Such a scenario requires the user to pro-actively register their queries as well as requiring that all such queries be persistent, i.e. are permanently stored within the database.
In the prior art caching solutions described above, there is no facility to allow agents of multiple applications to access and execute common sections. Thus, there is a need for such a facility.
SUMMARY OF THE INVENTION
The invention comprises a global cache for SQL section in a relational database management system, the global cache being stored in a computer readable memory and being accessible to a plurality of agents, the cache having a static SQL portion and a dynamic SQL portion. The dynamic SQL portion having a statement portion and a dependency portion. The statement portion containing one or more SQL text statements, one or more compilation environments, and one or more variations within the compilation environments. The dependency portion of the global cache having lists of objects each of the lists being specific to a single object type, each object in the list containing information on variations that are dependant on the object.
A relational database management system having a plurality of interlinked heterogeneous nodes each of the nodes having associated storage and processor resources. Each node including a global cache, the cache having a number of SQL sections, means for locating the sections and means for creating new variations in the cache.
REFERENCES:
patent: 5136707 (1992-08-01), Block et al.
patent: 5283894 (1994-02-01), Deran
patent: 5544345 (1996-08-01), Carpenter et al.
patent: 5559984 (1996-09-01), Nakano et al.
patent: 5848241 (1998-12-01), Misinai et al.
patent: 5897634 (1999-04-01), Attaluri et al.
patent: 5924096 (1999-07-01), Draper et al.
patent: 5974129 (1999-10-01), Bodnar
patent: 5987499 (1999-11-01), Morris et al.
patent: 6021413 (2000-01-01), Vaduvur et al.
patent: 6073129 (2000-06-01), Levine et al.
patent: 6098064 (2000-08-01), Pirolli et al.
patent: 6115703 (2000-09-01), Bireley et al.
patent: 6233584 (2001-05-01), Purcell
patent: 6237000 (2001-05-01), Dahlen et al.
patent: 6243719 (2001-06-01), Ikuta et al.
Attaluri et al., “Concurrency Control of Large Unstructured Data,” IEEE, 1988, pp. 314-323.*
Thomas et al., “Heterogenous Distributed Database Systems for Production Use,” ACM Computing Surveys, v. 22, No. 3, Sep. 1990, pp. 237-266.
Black Thomas
International Business Machines - Corporation
Johnson Daniel E.
Johnson Prentiss W.
Rones Charles L.
LandOfFree
Global caching and sharing of SQL statements in a... does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Global caching and sharing of SQL statements in a..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Global caching and sharing of SQL statements in a... will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-2595797