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.
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:
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 xe2x80x9csectionxe2x80x9d 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 xe2x80x9cshippedxe2x80x9d to the remote nodes. At each of the nodes xe2x80x9cagentsxe2x80x9d 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.
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 portion storing dynamic SQL statements and associated executables; each the executable being designed for a particular environment; the SQL cache includes a dependency portion which lists SQL objects and links them to the corresponding executable entries that depend on the objects; the SQL cache includes a shipped variation portion, each entry of the shipped variation portion refers to one executable entry and identifies the node at which the SQL statement associated with the executable was originally compiled and identification of such executable in the node at which it was compiled; the SQL 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 yet a further aspect of the invention the data processing system the global SQL instruction cache includes a statement entry which includes:
SQL instruction statement text and a list of associated environment entries;
each the environment entry includes a description of the environment and a list of variations, each the variation includes: an identification number; the executable itself; a list of required privileges; a reference available to the entry in the shipped variation list portion of the cache.
Another aspect of the invention provides a method of executing an SQL request issued by an application from a node (a coordinator node) comprising: searching in a global SQL cache at the coordinator node for an executable that is associated with an SQL statement and environment corresponding to the SQL request; if the executable is not found in the global cache then the database management system compiles the SQL request at the coordinator node then inserts an executable entry into the global cache at the coordinator node; searching at the coordinator node in the global SQL cache for an SQL statement entry, the text of which matches the text of the SQL request; if the entry is not present at the coordinator node, inserting, by the database management system, a new SQL statement entry, the text of which matches the SQL request; if the entry is present then the database management system searches for environment entries associated with the SQL statement entry for an environment entry that matches the environment for which the SQL request was issued; if the entry is not present then the database management system inserts into the cache a new environment entry matching the environment of the SQL request; the database management system searches for a variation entry comprising a valid executable corresponding to the SQL request, which includes the SQL statement and environment entry, and, if a valid executable is not found then the database management system compiles the SQL request, referring to the appropriate environment, into an executable (a set of one or more executable instructions) to carry out the SQL request; inserting into a variation entry the compiled executable, including a list of required privileges, a list of dependent objects, an identification number of the variation entry, and identification of the validity of the entry; and, for each object in the list of dependent objects searching in the dependency portion of the global SQL cache for an object entry with a matching object name and object type, and if it does not exist in the cache, inserting a new entry.
The invention also provides for a data processing system having a plurality of nodes at which processing may be performed, a method of identifying an executable comprising identifying the executable by node identification and entry identification associated with the first node of the data processing system that shipped the executable to any other node in the data processing system.
Preferably the executable is identified by node and entry identification associated with the first node of the data processing system that compiled and shipped the executable to any other node in the data processing system.
Another aspect of the invention provides a method of indexing data and instructions for storage and retrieval for a multi-node data processing system comprising:
storing identification of the data and instructions in a list wherein the data and instructions are identified by node identification and entry identification associated with the first node that shipped the data and instructions to any other node.
Preferably the node list comprises an entry portion and a lookup portion wherein:
the entry portion stores the following:
SQL statements;
executables;
environment parameters;
entry identification for that entry in the list; and,
wherein the lookup portion includes the following:
the entry identification;
the identification of the node that first shipped or compiled the executable;
entry identification of the executable at the node that first shipped or compiled the executable.
In still another aspect of the invention which may be used in a data base system, having a plurality of interconnected nodes, managed by a database management system, each of the nodes having an unique identification, the nodes being capable of storing or processing information, each node having a cache, the cache having a statement portion and a shipped variation portion;
the statement portion of the cache at each node for storing SQL statements, executables associated with the SQL statements, and environments with which the executables are associated, wherein the executables are each identified by an unique entry identification;
the shipped variation portion (list) for storing identification entries for the executables including:
identification (ID number) of the executable which corresponds to the identification (ID number) of the associated entry in the statement portion of the cache;
the node identification (ID number) of the node from which the executable was originally shipped from or compiled; and,
the identification (ID number) of the executable at the node from which the executable was first shipped;
a method of executing a data processing request from a node (the coordinator node) in a manner adapted to avoid redundant shipping of executables comprising:
finding a suitable executable for carrying out the request by:
searching the statement portion of the cache at the coordinating node for an executable that is associated matching the request and environment in which the request was issued; and,
if the matching statement and environment were not found in the statement portion of the cache of the coordinating node, then compiling the request and inserting the request into the cache;
sending a request to all other nodes of the data base management system that are necessary to process the request (subordinate nodes of the coordinating node) wherein the request to all other such nodes includes: the identification of the coordinator node and the entry identification of the executable, and if there is a entry in the shipped variation list associated with the executable on the coordinator node cache then the database management system also ships identification of the node and entry.
In yet another aspect the invention provides for use in a database system having a plurality of nodes, and being managed by a database management system, each node having a storage cache for indexing and storing instructions including statements, executables and environment information, a method of executing a request by an application at a first node wherein the application identifies the executable that is desired to be executed, searches at the first node for the executable, and, if the executable is not found at the node compiling the executable, then, using identification of the node by the node and entry identification of the executable on the node from which it was first shipped or compiled, requesting execution by all other nodes required for executing the executable;
the other nodes searching in their respective caches by the node and entry identification of the executable assigned by the node from which it was initially shipped or compiled; and,
if the executable is found at each the other node executing it at each node at which it is found; and,
if it is not found at one or more of the other nodes requesting that the executable be shipped from the first node to the nodes where the executable was not found; and, then executing the executable at each of the other nodes receiving it.
Another aspect of the invention also provides a method of performing data processing in a multi-node data processing system by sharing dynamic compiled (SQL) executable data processing statements between nodes of the data processing system comprising:
identifying each compiled SQL statement with a (numerical) identifier;
at a node receiving statements (a receiving node) storing a list of statements received at the receiving node listing the originating node and identifier for each of the received statements;
upon receiving a request for processing with a specified statement having the receiving node check its list of statements received for the specified statement by its originating node and identifier;
comparing the identification of the specified statement with the statements in the list of the receiving node, and if a match if found then accessing the statement stored at the receiving node;
if a match is not found, having the receiving node request the specified statement from another node, and when it is received then loading it into its local cache;
having the receiving node update its list of statements;
executing the statement at the receiving node.
In another aspect the invention provides a method for use in a data base management system having a plurality of interconnected nodes, each of the nodes having an unique identification, the nodes being capable of storing or processing information,
one of the nodes comprising a catalogue node having metadata describing tables and other SQL objects and relationships between them in the data processing system;
each node having a cache, the cache having a statement portion and a dependency portion;
the statement portion of the cache at each node for storing SQL statements, executables associated with the SQL statements, and environments with which the executables are associated, wherein the executables are each identified by an unique entry identification;
the dependency portion for storing object entries for the SQL objects including:
identification of the object which corresponds to the object name and object type;
a list of entries referring to executable entries in the statement portion of the cache whose execution relies on the definition and existence of the object;
a method of updating entries in the cache at each node when object definition entries in the catalogue are changed comprising:
updating the catalogue node by removing all invalid entries and broadcasting instructions to other known nodes of system to remove invalid entries from their respective caches, by:
making a first broadcast of instructions to the nodes to secure entries in the dependency portion of each cache by first:
finding an object entry associated with the object that is being changed in the dependency portion of the cache;
if such an object entry in the dependency portion of the cache is not found, inserting a new object entry;
securing the object entry in the dependency portion of caches of the other nodes; marking all the executables currently listed as being associated with the object as invalid;
reporting to the database management system at the catalog node that the object entry in each of the caches have been marked as secured;
examining any executable newly arriving in a cache of a node and determining if it depends on an object that has been secured, and if so, marking it as invalid;
making a second broadcast from the catalog node to all known nodes to:
mark all entries in the caches which are dependent on a secured object or an invalid object as being invalid; and,
removing the secured marking of all secured objects;
removing all invalid entries and objects.
In a further aspect of the immediately above method entries are updated in the cache at each node when a node is added to the database comprising:
when a new node is added to the database system, sending from the catalogue node a list of all secured objects to the new node;
at the new node, for all the objects in the list of the objects secured at the catalog node, creating and securing a new object entry in the dependency portion of the cache of the new node;
at the new, nodes examining any executable newly arriving in the cache of the new node and determining if the executable depends on an object that has been secured, and if so, marking it as invalid.
A further aspect of the invention provides a computer program product comprising computer readable storage for storing statements or instructions for use in execution in a computer in accordance with one or more aspects of the invention described above.