Method and apparatus for cross-node sharing of cached...

Details Method and apparatus for cross-node sharing of cached... Method and apparatus for cross-node sharing of cached...

1999-09-22

2003-07-22

Corrielus, Jean M. (Department: 2172)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C707S793000, C707S793000, C707S793000, C707S793000, C707S793000

Reexamination Certificate

active

06598058

ABSTRACT:

FIELD OF THE INVENTION
This invention relates to database management systems, and has particular application to relational database management systems (RDBMS) and more particularly to a method and apparatus for efficiently sharing of instructions, especially cached dynamic SQL statements between applications connected at different nodes in a multiple node database while maintaining cache and application integrity across the nodes.
BACKGROUND OF THE INVENTION
Structured Query Language (SQL) is the database access language most commonly used to access relational databases (such as the DB2 product sold by IBM Corporation) 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.
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.
Coordinator
The coordinator node refers to the node at which an
node:
application issues SQL queries to the database.
Subordinate
Subordinate nodes are all the nodes, aside from the
node:
coordinator node, on which processing of a given SQL
request must be run.
Catalogue
The catalogue node is the single node in the database
node:
system which contains the system catalogues. These
catalogues contain meta-data about each of the table and
SQL objects in the system and the relationships between
them.
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 the database management system for compilation. Compiling (also known as preparing) a SQL statement is the process by which the database management system 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 SQL issued by an application that is not compiled until the time the application issues the query, and is often 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. Furthermore, 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.
In such a scenario, each of the nodes involved in processing the query must have a copy of the “section” in order that the query runs completely. Including all the data from all the nodes. As a basic solution to this requirement, the dynamic SQL statement is compiled at the node where the application is running. The resulting section is then sent or “shipped” to the remote nodes. At each of the nodes “agents” on behalf of the application can then execute the query using the shipped section. Similarly, applications that are running on other nodes of the database that issue dynamic SQL, require the SQL to be compiled on the resulting section be shipped to all of this remote nodes that are involved in the transaction.
Given the potentially high cost of compilation and of SQL sections, it is desirable to avoid repeated compilation of the identical SQL request, and rely on previously compiled sections.
SUMMARY OF THE INVENTION
In accordance with another aspect of the present invention there is provided a database system comprising:
a) a plurality of nodes
b) a plurality of databases stored on the nodes; and
c) a global dynamic SQL statement cache stored on at least one node; the global dynamic SQL cache being accessible to a plurality of applications in the database system.
In accordance with the present invention there is also provided a method of maintaining cache and application integrity across a relational database system comprising a plurality of nodes and a plurality of databases shared on the nodes, the method comprising the steps of: (a) providing for at least some of the databases, a global cached including information to enable agents to access the databases; (b) origination a request through a coordinating node, the coordinating node sending information on the dynamic SQL request to be executed to remote nodes: (c) having each remote node receive the request and check the global cache on the respective remote node for a current copy of the dynamic SQL entry; and (d) if the remote node does not have the current entry, the remote node requesting the coordination node to send the current version of the dynamic SQL entry and its section (e) having the global dynamic SQL cache be accessible for applications that are running at any node in the database system so that an application is able to re-use a cached copy of a previously inserted dynamic SQL entry and its section regardless of the node at which it was originally compiled.
Various aspects of the invention provide a data processing system, a method, and software for operating it where the data processing system includes a number of interconnected nodes. At least one data partition resides on one of the nodes, and one or more tables reside in a data partition. One of the nodes has a catalogue containing metadata describing tables and other objects and relationships therebetween in the data processing system. There is a global instruction cache at each node having a statement portion storing instruction statements and associated executables; each of the executable is designed for a particular environment; the instruction cache includes a dependency portion which lists objects and links them to the corresponding executable entries that depend on the objects; the instruction cache includes a shipped variation portion, each entry of the shipped variation portion refers to one executable entry and identifies the node from which the statement associated with the executable was originally shipped and the identification of such executable in the node from which it was originally shipped. The cache includes an invalidations in progress portion, each entry of the invalidations in progress portion refers to an object entry in the dependency portion.
In a further aspect of the invention the data processing system a catalogue node has a catalogue containing metadata describing tables and other SQL objects and relationships therebetween in the data processing system; wherein the SQL objects may include tables views of tables, aliases of tables, functions, and user defined types among others; a global SQL instruction cache at each node having a statement

Affiliated with

Also associated with

Rating

Method and apparatus for cross-node sharing of cached... does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Method and apparatus for cross-node sharing of cached..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method and apparatus for cross-node sharing of cached... will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3083418