US20020026448A1 - Caching of distributed dynamic sql statements in a multiple node rdbms. - Google Patents
Caching of distributed dynamic sql statements in a multiple node rdbms. Download PDFInfo
- Publication number
- US20020026448A1 US20020026448A1 US09/162,165 US16216598A US2002026448A1 US 20020026448 A1 US20020026448 A1 US 20020026448A1 US 16216598 A US16216598 A US 16216598A US 2002026448 A1 US2002026448 A1 US 2002026448A1
- Authority
- US
- United States
- Prior art keywords
- node
- nodes
- variation
- cache
- database
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 238000000034 method Methods 0.000 claims abstract description 22
- 230000003068 static effect Effects 0.000 claims description 12
- 230000008859 change Effects 0.000 claims description 6
- 238000004891 communication Methods 0.000 claims description 6
- 239000003795 chemical substances by application Substances 0.000 description 22
- 230000009471 action Effects 0.000 description 11
- 238000010586 diagram Methods 0.000 description 7
- 230000006870 function Effects 0.000 description 7
- 230000001419 dependent effect Effects 0.000 description 5
- 238000005192 partition Methods 0.000 description 5
- 230000008569 process Effects 0.000 description 4
- 238000012545 processing Methods 0.000 description 3
- 239000000872 buffer Substances 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 238000003780 insertion Methods 0.000 description 2
- 230000037431 insertion Effects 0.000 description 2
- 230000004044 response Effects 0.000 description 2
- 230000006399 behavior Effects 0.000 description 1
- 230000005540 biological transmission Effects 0.000 description 1
- 238000012790 confirmation Methods 0.000 description 1
- 230000003116 impacting effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
- G06F16/24552—Database cache management
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2452—Query translation
- G06F16/24524—Access plan code generation and invalidation; Reuse of access plans
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99931—Database or file accessing
- Y10S707/99938—Concurrency, e.g. lock management in shared database
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
- Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y10S707/00—Data processing: database and file management or data structures
- Y10S707/99951—File or database maintenance
- Y10S707/99952—Coherency, e.g. same view to multiple users
Definitions
- This invention relates to relational database management systems (RDBMS) and more particularly to a method for caching dynamic SQL statements (and their sections) at different nodes in a multiple node database while maintaining cache and application integrity across all nodes.
- RDBMS relational database management systems
- SQL Structured Query Language
- IBM Canada Ltd. relational database product sold by IBM Canada Ltd.
- RDBMS relational database management system
- 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.
- 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 the same settings are also used as the default environment for any dynamic SQL statements compiled by the application during execution.
- compilation environment settings e.g. compile or binding options
- 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.
- 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 individual 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 seated in each instance. Thus, the creation of an identical access plan may often have to be repeated, thereby impacting performance of the application.
- a dynamic SQL request originates on the node running an application and may require data from one or more other nodes (the remote nodes).
- the prior art solution is to generate the section to obtain the data from the remote node(s) and send the section to the remote node(s) for execution.
- the inventors are not aware of any solution proposed to date which considers allowing caching of the same statement across multiple nodes, thus not requiring the section to be executed to be shipped. Maintaining multiple iterations on multiple nodes result in less communications traffic and faster response times for remote parts of a dynamic SQL request due to faster startup times.
- a database system comprising:
- a method of maintaining fiche 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 cache including information to enable agents to access the databases, (b) originating a request through a coordinating node, the coordinating node sending information on the variation 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 variation; and (d) if the a remote node does not have the current variation, the remote node requesting the coordinating node to send the current version of the variation.
- FIG. 1 is a conceptual diagram of a Relational Database Management System
- FIG. 2 is a conceptual diagram of a node of the Relational Database Management System of FIG. 1;
- FIG. 3 is a conceptual diagram of the global cache
- FIG. 4 is a conceptual diagram of the statement portion of the dynamic cache portion of the global mesh
- FIG. 5 is a conceptual diagram of the dependency portion of the dynamic cache portion of the global cache.
- FIG. 6 is a conceptual diagram of a catalogue node, the participating nodes list and the participating nodes.
- FIG. 1 is a conceptual diagram of a Relational Database Management system (RDBMS) 10 .
- the RDBMS of 10 of FIG. 1 illustrates only one of many configurations available in an RDBMS and is intended only to show a networked system that may utilize the present invention.
- RDBMS 10 contains a plurality of nodes ( 12 , 14 , 16 , 18 ). Each node ( 12 , 14 , 16 , 18 ) may contain a partition or all of a database.
- the RDBMS has two databases, although any number of databases can be provided, the content of which is partitioned among the plurality of nodes. The first of these databases is divided into a partition 20 and a partition 21 .
- the catalogue 22 for this first database is stored on catalogue node 18 .
- the catalogue 22 contains tables in which resides all the “meta-data” such as the structure, tables packages and functions relating to the first database.
- the second database is divided into partitions 23 , 24 and 25 . Note that in the illustrated example, the partition 24 of the second database also contains the catalogue tables for the second database.
- a catalogue node 18 may contain data as well as catalogue tables. Catalogue tables are per database and can be located at different nodes for different database. This means that the term “catalogue node” is relative per database, i.e. the catalogue could reside on any node.
- FIG. 2 illustrates one scenario of the activity that may occur on any node of the system and, by way of example, the node 16 of the RDBMS system 10 of FIG. 1 is shown.
- Node 16 may contain any number of applications 30 , each accessing data from the database(s) to which the node is connected.
- Each application 30 has a respective SQL work area 32 .
- a global cache 40 in accordance with the present invention which is shared by all applications on all the nodes.
- the global cache 40 acts as a repository for package and section information for static SQL and statement and section information for dynamic SQL as detailed below.
- the global cache is kept at the database level, and is accessible to all agents for a database at a particular node of the database (physical or logical). It is allocated from the same memory set as other database-level entities, such as the lock list or buffer pool; it is created when the database is initialized, and it remains active until the database is shut down.
- the global cache acts like a “public” library for all the agents using the database at a given node. Agents simply copy the package information and the modifiable portions of the sections (e.g. buffers and flags.)
- the global cache 40 consists of two logical areas; the static SQL cache 42 and the dynamic SQL cache 44 .
- the static cache 42 contains the information for packages 46 , section entries 48 , and static SQL sections 50 .
- Thc dynamic SQL cache 44 contains the information and sections for dynamic SQL statements.
- the dynamic SQL portion 44 of the global cache 40 is subdivided into two portions: the statement portion 60 (FIG. 4) and the dependency portion 58 (FIG. 5).
- the statement portion 60 contains SQL statement entries 62 which contain the text of the cached dynamic SQL statements 64 as well as thc different sections compiled for each SQL statement.
- the statement portion 60 of the dynamic cache 44 is used to support application requests to prepare the dynamic SQL statement 64 and obtain an executable section.
- the dependency portion 58 of the dynamic cache 44 contains entries for all the objects upon which the cached dynamic SQL sections are dependent. This information is used Lo support cached object invalidation due to data definition language statements and other database activities.
- the primary structure of the dynamic SQL portion 44 of the global cache 40 is based upon the unique text for a given SQL statement 64 , which is stored within SQL statement entry 62 .
- a SQL statement entry 62 contains characteristic information about the SQL statement 64 that is determined once the first occurence of the statement text 64 is compiled. This information is invariant since thc statement text 64 is constant and these characteristics are inherent in the statement text 64 itself, independent of the environment in which it was compiled.
- a section generated for the exact identical SQL statement text 64 is stored within the compilation environment 66 .
- the compilation environment 66 contains information on the environmentL used to generate or compile the section.
- the compilation environment 66 contains all information not already contained in the catalogue tables, that influence or control an access plan created by the SQL compiler, and thus the section.
- each compilation envionment 66 is stored individual units known as “variations” 68 .
- a variation 68 represents a unique section for the statement text 64 , where the uniqueness is specified by the compilation environment 66 used to generate the section.
- the compilation environment 66 encompasses all those elements that affect the nature and result of the section generated by the SQL compiler for a dynamic SQL statement (e.g., special registers, relevant package compilation defaults, use of default qualifiers, use of function path, etc.). Note that since privileges do not affect the actual section generated, only whether it is allowed to be executed, the prerequistite privileges for a dynamic SQL statement 64 are irrelevant to the compilation environment 66 . In the preferred embodiment, privileges are not part of the compilation environmnt 66 .
- a variation 68 is simply the representation of the fact that two applications issuing the same dynamic SQL statement 64 in the same context with the exact same compilation environment 66 should generate the exact same section, although the results of execution may differ due to actions within the individual units of work. For example, two applications using the same section for SELECT C1 FROM T1 may return different results if the first application has inserted some rows into T1 but not committed its changes. The first application will get the row returned by the section, but the second application may not.
- each variation 68 under a statement entry 62 and compilation environment 66 also contains: the required privileges list of privileges needed to execute the section, the dependency list for the section and the section generated for the specified compilation environment.
- the dependency list refers to those objects and entities in the catalogue tables required, either directly or indirectly, by the section for a variation 68 .
- the dependency list is also used to determine whether the section is no longer valid when an object on the dependency list is dropped; if the variation 68 is in use at the time, the drop request is rejected. When a section is no longer valid, the variation 68 becomes an invalid variation 70 and must be regenerated.
- the dependency list functions in much the same manner as the package dependencies that are recorded for static SQL statements in the SYSCAT.PACKAGEDEP catalogue table of DB2.
- the SYSPLANDEP table is scanned to locate packages dependent upon the affected object and the packages are invalidated. Details of the dependency list and its use to invalidate dynamic SQL sections is discussed below in the description of FIG. 5. The major exception is that the loss of privileges does not result in any variation 68 being marked as an invalid variation U since, as noted previosuly, privileges do not affect the contents of the section.
- a variation 68 represents a dynamic SQL statement, and dynamic SQL always reflects the current environment, other actions such as issuing thc RUNSTATS command against a table or creating a new User Defined Function (UDF) can cause a variation to be marked invalid.
- UDF User Defined Function
- Each dynamic SQL statement entry 62 may have one or more compilation environments 66 , but each compilation environment 66 may contain only one valid variation 68 .
- An invalid variation 70 will be flushed from the cache during cache space management.
- an ALTER TABLE statement can cause a variation 68 to become an invalid variation 70 in the dynamic cache 44 and will eventually be flushed from the dynamic cache 44 .
- Dynamic SQL always reflects the current environment, i.e. the section for a dynamic SQL statement always represents the choices the SQL compiler would make given the most current environment. This is why a dynamic SQL section is invalidated when a new index is added or statistics are updated.
- the sections are fine in the sense that they will work, but the environment has changed and the sections may not reflect new choices that thc compiler might make.
- each object 72 represents a database object upon which a dynamic SQL variation 68 is dependent. Each object 72 is associated with one or more variations 68 . Each object 7 in the dependency portion 58 of the global cache 40 is distinct and only one occurrence of the object 72 may appear at any one time.
- each object 72 is associated with a specific object type anchor point 74 by hashing the qualified object name 76 to a unique anchor point 74 . Once associated with an anchor point 74 , the object 72 is inserted into the list of objects 78 off that anchor point 74 in alphabetical order based on the object name 76 followed by the qualifier.
- Anchor points 74 are type specific (i.e. an alias anchor point only holds aliases) and only objects 72 of the type represented by the anchor point 74 are to be found off that and/or point 74 .
- objects of types view, alias, index, User Defined Functions (UDF), table, structured types, summary tables, hierarchiess and pagesize are supported.
- UDF User Defined Functions
- An essential characteristic of a dynamic SQL statement is that it reflects the current database environment and the dynamic SQL cache 44 does not alter this characteristic. If an environment change affects a dynamic SQL section, the variation 68 for that section will become an invalid variation 70 .
- the name of the affected object 76 is hashed to obtain the appropriate anchor point 74 for the same object type.
- the list of objects 78 is then scanned to find a matching object 72 . Once a matching object 72 is found, the list of dependant variations below that object is scanned and an attempt is made to invalidate them.
- a request to execute the same variation as the current variation is sent to that node by a coordinator node 92 .
- the coordinator node is responsible for coordinating all database requests made by an application on that node.
- the remote node ( 24 , 28 ) receives the request, the list of shipped variations contained within the global cache 40 is searched for a matching entry from the coordinator node 92 . If none is found, then the remote node ( 24 , 28 ) issues a request to the coordinator node 92 to send the specified variation and section to it. A long with the section, there is also sent the dependency information and required privileges information.
- this information is received at the remote node ( 24 , 28 ), it is inserted into the cache 40 at that node ( 24 , 28 ) as a complete entry (i.e. as if it had originated at the remote node ( 24 , 28 ); all information is in the cache including privileges and dependencies).
- the normal actions taken for invalidating events will process the transhipped variation just like any other in the remote nodes ( 24 , 28 ) cache 40 .
- a list of participating nodes 94 for a database 20 is, built and maintained on the catalogue node 22 .
- a participating node is a node which is active, conencted to the database 20 and has registered in the participating nodes list 92 at the database catalogue node 22 .
- the list of participating nodes 94 is used to define which nodes must be informed when a communication bulletin (described hereinbelow) is to be broadcast for a database 20 .
- the information is broadcast to all nodes in the list of participating nodes 20 other than the current one (the catalogue node 22 ) by the agent wishing to send the information. Before any broadcast, the broadcasting agent musy acquire the latch for thc list of participating nodes 92 to prevent any new nodes from being added. Processing for the cache 40 at the catalogue node 22 is handled by the broadcasting agent.
- a REGISTER request is sent to the catalogue node 22 to update the list of participating nodes 94 by adding an entry for this newly active node; the response to this request will contain the current values of all global relevancy counters (described hereinbelow) for the database 20 .
- an UNREGISTER request is sent to the catalogue node 22 to remove the node from the participating nodes list 92 .
- an event broadcast is sent from the catalogue node 22 to all active nodes using all of the nodes in the participating nodes list 94 to ensure that all affected variations at all nodes arc properly invalidated. If a node is not registered in the participating nodes list 94 , then it has no global cache 40 and thus does not need to be informed of changes until it registers.
- Dynamic SQL statements in a parallel environment require that the identical section be executed on all participating nodes.
- the coordinator agent the agent dealing directly with the application
- the remote nodes will request that this variation be loaded into their SQL Work Area ( 32 of FIG. 2, which will be in the application's common memory in most cases).
- the node's dynamic SQL cache 40 is then chocked for this variation, if it is not there, a request is made to the coordinating node for a copy of the variation from that node which is inserted into the requesting node's dynamic SQL cachce 40 upon receipt.
- this section will be used by all agents executing in parallel for this application at this node. If the variation is not found at the coordinating node (or is not valid), an error will be returned to the requesting agent.
- the shipped ID sent by thc coordinating agent with the request to execute consist of the originating node, the statement ID and variation ID at the node of origin, and the insertion time of the variation into the global SQL cache at the node of origin as well as the specific subsection to be executed.
- a subsection is a logical sub-component of a section. Requests are made to different agents specifying different subsections. The insertion time is required to ensure the identical variation is used at alI nodes as the one specified by the coordinator node in its request. No check on the validity of the variation is performed as it is assumed to be still protected by the coordinator node; however, the status of the variation inserted in to the local node's global SQL cache will be identical to its value at the node of origin (e.g. an invalid variation will be inserted as invalid and marked as such).
- the specified variation is from another node, it may or may not exist at the current node and if it does exist at the current node, it will have a different variation ID.
- a list of shipped IDs and the associated variation ID at this node is linked from a control block (not shown) within the statement portion 60 of the global cache 40 , to help agents find the desired variation quickly. Once the variation ID for this node is known, the variation is handled exactly like any other variation.
- the variation is requested from the node of origin and inserted into the dynamic SQL cache of the requesting node.
- the following information will be sent to the requesting node in order to allow complete support of the shipped variation in the cache for the requesting node:
- Relevancy counters are a simple method of determining whether past decisions or cached information are still relevant in the current environment. When an object is cached or a privilege is checked, the value of the appropriate relevancy counter is stored at that time. When the cached object or decision point is next revisited, the stored value is compared to the current value. If they are the same, then the old object or decision is still valid, else it must be revisited. Relevancy counters are only meaningful to coordinator agents. There are two types of relevancy counters; those based on global, shared database information, and those focussed on application/connection information.
- the following counters are unique to a specific application and are only referenced or updated by the coordinator agent. They are used to determine if a new compilation environment exists for the application or whether any SQL statements being compiled are possibly dependent on uncommitted Data Definition Language (DDL) performed by the application in this Unit of Work.
- DDL Data Definition Language
- a variation for a dynamic SQL statement can become invalid for a number of reasons, some of which relate to a change in the compilation environment in which the variation was created. Since dynamic SQL must reflect all changes in the compilation environment, as these changes occur a variation may become invalid and be eligible to be flushed from the dynamic SQL cache.
- Synchronization messages are used to inform the cache at a node that a change has been made to the database environment and action needs to be taken to ensure the cache at this node remains relevant to the current environment.
- the receiving node Upon receipt of a synchronization message that affects cached dynamic SQL statements, the receiving node will search the depedency portion 58 of the dynamic SQL cache 44 to determine which variations, if any, are affected by the message contents; these variations are invalidated.
- the receiving node Upon receipt of a message affecting a cached package, the receiving node will perform the indicated action on the specified package.
- a synchronization message which is invalidating a package or cached dynamic SQL statements requires that the cache at the receiving node guarantee that all relevant cached packages or variations are locked exclusively for the requesting application. Once the appropriate action has been taken for a synchronization message, the receiving node(s) must respond to the requesting node with a confirmation message with a success or failure indicator.
- the catalogue node Since all DDL actions take place at the catalogue node, the catalogue node will be the source of all synchronization messages. All messages will be sent to all participating nodes except the current one (the catalogue node) as the cache at the current node will be manipulated directly. If a communication failure occurs during a transmission to a participating node, the sending function fails and the transaction is rolled back. Any other errors will also halt processing and cause the transaction to be rolled back.
Abstract
A method and system for caching dynamic SQL statements and their sections at different nodes in a multiple node Relational Database Management System (RDBMS) while maintaining cache and application integrity across all nodes. Each node in the RDBMS having a global cache shared by all applications on that node. One of the nodes in the RDBMS being a catalogue node which controls access to a database. The catalogue node maintaining a list of participating nodes that have requested access to the database controlled by the catalogue node. The list of participating nodes being used to notify each participating node of any changes to the database that may affect the validity of a dynamic SQL statement stored in the global cache of the node.
Description
- This invention relates to relational database management systems (RDBMS) and more particularly to a method for caching dynamic SQL statements (and their sections) at different nodes in a multiple node database while maintaining cache and application integrity across all 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 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 the 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 individual 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 seated in each instance. Thus, the creation of an identical access plan may often have to be repeated, thereby impacting performance of the application.
- A dynamic SQL request originates on the node running an application and may require data from one or more other nodes (the remote nodes). In such a scenario, the prior art solution is to generate the section to obtain the data from the remote node(s) and send the section to the remote node(s) for execution. The inventors are not aware of any solution proposed to date which considers allowing caching of the same statement across multiple nodes, thus not requiring the section to be executed to be shipped. Maintaining multiple iterations on multiple nodes result in less communications traffic and faster response times for remote parts of a dynamic SQL request due to faster startup times.
- In accordance with 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 cache stored on at least one node, the global 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 fiche 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 cache including information to enable agents to access the databases, (b) originating a request through a coordinating node, the coordinating node sending information on the variation 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 variation; and (d) if the a remote node does not have the current variation, the remote node requesting the coordinating node to send the current version of the variation.
- Reference will now be made, by way of example, to the accompanying drawings which show a preferred embodiment of the present invention and in which:
- FIG. 1 is a conceptual diagram of a Relational Database Management System;
- FIG. 2 is a conceptual diagram of a node of the Relational Database Management System of FIG. 1;
- FIG. 3 is a conceptual diagram of the global cache;
- FIG. 4 is a conceptual diagram of the statement portion of the dynamic cache portion of the global mesh;
- FIG. 5 is a conceptual diagram of the dependency portion of the dynamic cache portion of the global cache; and
- FIG. 6 is a conceptual diagram of a catalogue node, the participating nodes list and the participating nodes.
- FIG. 1 is a conceptual diagram of a Relational Database Management system (RDBMS)10. The RDBMS of 10 of FIG. 1 illustrates only one of many configurations available in an RDBMS and is intended only to show a networked system that may utilize the present invention. In the configuration as shown, RDBMS 10 contains a plurality of nodes (12, 14, 16, 18). Each node (12, 14, 16, 18) may contain a partition or all of a database. In the example of FIG. 1, the RDBMS has two databases, although any number of databases can be provided, the content of which is partitioned among the plurality of nodes. The first of these databases is divided into a
partition 20 and apartition 21. Thecatalogue 22 for this first database is stored oncatalogue node 18. Thecatalogue 22 contains tables in which resides all the “meta-data” such as the structure, tables packages and functions relating to the first database. The second database is divided intopartitions partition 24 of the second database also contains the catalogue tables for the second database. Thus, acatalogue node 18 may contain data as well as catalogue tables. Catalogue tables are per database and can be located at different nodes for different database. This means that the term “catalogue node” is relative per database, i.e. the catalogue could reside on any node. - FIG. 2 illustrates one scenario of the activity that may occur on any node of the system and, by way of example, the
node 16 of theRDBMS system 10 of FIG. 1 is shown.Node 16 may contain any number ofapplications 30, each accessing data from the database(s) to which the node is connected. Eachapplication 30 has a respective SQLwork area 32. Also contained withinnode 16 is aglobal cache 40 in accordance with the present invention which is shared by all applications on all the nodes. - The Global Cache
- The
global cache 40 acts as a repository for package and section information for static SQL and statement and section information for dynamic SQL as detailed below. - In the preferred embodiment, the global cache is kept at the database level, and is accessible to all agents for a database at a particular node of the database (physical or logical). It is allocated from the same memory set as other database-level entities, such as the lock list or buffer pool; it is created when the database is initialized, and it remains active until the database is shut down. The global cache acts like a “public” library for all the agents using the database at a given node. Agents simply copy the package information and the modifiable portions of the sections (e.g. buffers and flags.)
- Referring to FIG. 3, the
global cache 40, consists of two logical areas; thestatic SQL cache 42 and thedynamic SQL cache 44. Thestatic cache 42 contains the information forpackages 46, section entries 48, and static SQL sections 50. Thcdynamic SQL cache 44 contains the information and sections for dynamic SQL statements. - The
dynamic SQL portion 44 of theglobal cache 40 is subdivided into two portions: the statement portion 60 (FIG. 4) and the dependency portion 58 (FIG. 5). The statement portion 60 containsSQL statement entries 62 which contain the text of the cacheddynamic SQL statements 64 as well as thc different sections compiled for each SQL statement. The statement portion 60 of thedynamic cache 44 is used to support application requests to prepare thedynamic SQL statement 64 and obtain an executable section. The dependency portion 58 of thedynamic cache 44 contains entries for all the objects upon which the cached dynamic SQL sections are dependent. This information is used Lo support cached object invalidation due to data definition language statements and other database activities. - The primary structure of the
dynamic SQL portion 44 of theglobal cache 40 is based upon the unique text for a givenSQL statement 64, which is stored withinSQL statement entry 62. ASQL statement entry 62 contains characteristic information about theSQL statement 64 that is determined once the first occurence of thestatement text 64 is compiled. This information is invariant sincethc statement text 64 is constant and these characteristics are inherent in thestatement text 64 itself, independent of the environment in which it was compiled. - A section generated for the exact identical
SQL statement text 64 is stored within thecompilation environment 66. Thecompilation environment 66 contains information on the environmentL used to generate or compile the section. Thecompilation environment 66 contains all information not already contained in the catalogue tables, that influence or control an access plan created by the SQL compiler, and thus the section. - Below each
compilation envionment 66 are stored individual units known as “variations” 68. Avariation 68 represents a unique section for thestatement text 64, where the uniqueness is specified by thecompilation environment 66 used to generate the section. Thecompilation environment 66 encompasses all those elements that affect the nature and result of the section generated by the SQL compiler for a dynamic SQL statement (e.g., special registers, relevant package compilation defaults, use of default qualifiers, use of function path, etc.). Note that since privileges do not affect the actual section generated, only whether it is allowed to be executed, the prerequistite privileges for adynamic SQL statement 64 are irrelevant to thecompilation environment 66. In the preferred embodiment, privileges are not part of thecompilation environmnt 66. - A
variation 68 is simply the representation of the fact that two applications issuing the samedynamic SQL statement 64 in the same context with the exactsame compilation environment 66 should generate the exact same section, although the results of execution may differ due to actions within the individual units of work. For example, two applications using the same section for SELECT C1 FROM T1 may return different results if the first application has inserted some rows into T1 but not committed its changes. The first application will get the row returned by the section, but the second application may not. - In addition to the identifying compilation environment, each
variation 68 under astatement entry 62 andcompilation environment 66 also contains: the required privileges list of privileges needed to execute the section, the dependency list for the section and the section generated for the specified compilation environment. The dependency list refers to those objects and entities in the catalogue tables required, either directly or indirectly, by the section for avariation 68. The dependency list is also used to determine whether the section is no longer valid when an object on the dependency list is dropped; if thevariation 68 is in use at the time, the drop request is rejected. When a section is no longer valid, thevariation 68 becomes aninvalid variation 70 and must be regenerated. The dependency list functions in much the same manner as the package dependencies that are recorded for static SQL statements in the SYSCAT.PACKAGEDEP catalogue table of DB2. For static SQL the SYSPLANDEP table is scanned to locate packages dependent upon the affected object and the packages are invalidated. Details of the dependency list and its use to invalidate dynamic SQL sections is discussed below in the description of FIG. 5. The major exception is that the loss of privileges does not result in anyvariation 68 being marked as an invalid variation U since, as noted previosuly, privileges do not affect the contents of the section. Since avariation 68 represents a dynamic SQL statement, and dynamic SQL always reflects the current environment, other actions such as issuing thc RUNSTATS command against a table or creating a new User Defined Function (UDF) can cause a variation to be marked invalid. - Each dynamic
SQL statement entry 62 may have one ormore compilation environments 66, but eachcompilation environment 66 may contain only onevalid variation 68. Aninvalid variation 70 will be flushed from the cache during cache space management. For example, an ALTER TABLE statement can cause avariation 68 to become aninvalid variation 70 in thedynamic cache 44 and will eventually be flushed from thedynamic cache 44. - Dynamic SQL always reflects the current environment, i.e. the section for a dynamic SQL statement always represents the choices the SQL compiler would make given the most current environment. This is why a dynamic SQL section is invalidated when a new index is added or statistics are updated. The sections are fine in the sense that they will work, but the environment has changed and the sections may not reflect new choices that thc compiler might make.
- Referring now to FIG. 5, the basic unit of the dependency portion58 of the
dynamic SQL cache 44 is theobject 72. Anobject 72 represents a database object upon which adynamic SQL variation 68 is dependent. Eachobject 72 is associated with one ormore variations 68. Each object 7 in the dependency portion 58 of theglobal cache 40 is distinct and only one occurrence of theobject 72 may appear at any one time. To facilitate access, eachobject 72 is associated with a specific objecttype anchor point 74 by hashing thequalified object name 76 to aunique anchor point 74. Once associated with ananchor point 74, theobject 72 is inserted into the list ofobjects 78 off thatanchor point 74 in alphabetical order based on theobject name 76 followed by the qualifier. Anchor points 74 are type specific (i.e. an alias anchor point only holds aliases) and only objects 72 of the type represented by theanchor point 74 are to be found off that and/orpoint 74. In the present embodiment, objects of types: view, alias, index, User Defined Functions (UDF), table, structured types, summary tables, hierarchiess and pagesize are supported. - An essential characteristic of a dynamic SQL statement is that it reflects the current database environment and the
dynamic SQL cache 44 does not alter this characteristic. If an environment change affects a dynamic SQL section, thevariation 68 for that section will become aninvalid variation 70. When invalidating adynamic SQL variation 68 due to a change in a dependent object, the name of the affectedobject 76 is hashed to obtain theappropriate anchor point 74 for the same object type. The list ofobjects 78 is then scanned to find amatching object 72. Once a matchingobject 72 is found, the list of dependant variations below that object is scanned and an attempt is made to invalidate them. - Referring now to FIG. 6, when a section needs to be executed at a remote node (24, 28), a request to execute the same variation as the current variation is sent to that node by a
coordinator node 92. The coordinator node is responsible for coordinating all database requests made by an application on that node. When the remote node (24, 28) receives the request, the list of shipped variations contained within theglobal cache 40 is searched for a matching entry from thecoordinator node 92. If none is found, then the remote node (24, 28) issues a request to thecoordinator node 92 to send the specified variation and section to it. A long with the section, there is also sent the dependency information and required privileges information. When this information is received at the remote node (24, 28), it is inserted into thecache 40 at that node (24, 28) as a complete entry (i.e. as if it had originated at the remote node (24, 28); all information is in the cache including privileges and dependencies). At this point, the normal actions taken for invalidating events will process the transhipped variation just like any other in the remote nodes (24, 28)cache 40. - The Participating Nodes List
- In order to minimize network traffic and to allow for the ignoring of nodes that are either down or have not connected to a particular database, a list of participating
nodes 94 for adatabase 20 is, built and maintained on thecatalogue node 22. A participating node is a node which is active, conencted to thedatabase 20 and has registered in the participatingnodes list 92 at thedatabase catalogue node 22. - The list of participating
nodes 94 is used to define which nodes must be informed when a communication bulletin (described hereinbelow) is to be broadcast for adatabase 20. The information is broadcast to all nodes in the list of participatingnodes 20 other than the current one (the catalogue node 22) by the agent wishing to send the information. Before any broadcast, the broadcasting agent musy acquire the latch for thc list of participatingnodes 92 to prevent any new nodes from being added. Processing for thecache 40 at thecatalogue node 22 is handled by the broadcasting agent. - Every time a node becomes active, a REGISTER request is sent to the
catalogue node 22 to update the list of participatingnodes 94 by adding an entry for this newly active node; the response to this request will contain the current values of all global relevancy counters (described hereinbelow) for thedatabase 20. When a node becomes inactive, an UNREGISTER request is sent to thecatalogue node 22 to remove the node from the participatingnodes list 92. - When an action occurs that could invalidate any variation, an event broadcast is sent from the
catalogue node 22 to all active nodes using all of the nodes in the participatingnodes list 94 to ensure that all affected variations at all nodes arc properly invalidated. If a node is not registered in the participatingnodes list 94, then it has noglobal cache 40 and thus does not need to be informed of changes until it registers. - All information broadcasts for a
database 20 must be done from thecatalogue node 22 and use the list of participatingnodes 94. If an error occurs during the broadcast, the transaction fails and is rolled back - Dynamic SQL in a Parallel Environment
- Dynamic SQL statements in a parallel environment require that the identical section be executed on all participating nodes. To ensure this behaviour, the coordinator agent (the agent dealing directly with the application) will place the needed identification for the variation to be executed in the request sent to cache remote node, where a remote node is a node working for the application that is not the coordinator node. The remote nodes will request that this variation be loaded into their SQL Work Area (32 of FIG. 2, which will be in the application's common memory in most cases). The node's
dynamic SQL cache 40 is then chocked for this variation, if it is not there, a request is made to the coordinating node for a copy of the variation from that node which is inserted into the requesting node'sdynamic SQL cachce 40 upon receipt. Once loaded into the SQL Work Area (32 of FIG. 2), this section will be used by all agents executing in parallel for this application at this node. If the variation is not found at the coordinating node (or is not valid), an error will be returned to the requesting agent. - The shipped ID sent by thc coordinating agent with the request to execute consist of the originating node, the statement ID and variation ID at the node of origin, and the insertion time of the variation into the global SQL cache at the node of origin as well as the specific subsection to be executed. A subsection is a logical sub-component of a section. Requests are made to different agents specifying different subsections. The insertion time is required to ensure the identical variation is used at alI nodes as the one specified by the coordinator node in its request. No check on the validity of the variation is performed as it is assumed to be still protected by the coordinator node; however, the status of the variation inserted in to the local node's global SQL cache will be identical to its value at the node of origin (e.g. an invalid variation will be inserted as invalid and marked as such).
- Since the specified variation is from another node, it may or may not exist at the current node and if it does exist at the current node, it will have a different variation ID. To help map from shipped IDs to valid variation IDs at a node, a list of shipped IDs and the associated variation ID at this node is linked from a control block (not shown) within the statement portion60 of the
global cache 40, to help agents find the desired variation quickly. Once the variation ID for this node is known, the variation is handled exactly like any other variation. - If a slipped ID is not found in the list, the variation is requested from the node of origin and inserted into the dynamic SQL cache of the requesting node. When the variation is requested, the following information will be sent to the requesting node in order to allow complete support of the shipped variation in the cache for the requesting node:
- Statement Length
- Statement Text
- Statement Information
- Compilation Environment
- Required Privileges List
- Dependency List
- Section Data
- Relevancy Counters
- In order to ensure that database changes are recognized and communicated to all agents working on a database in a simple yet effective manner, a number of relevancy counters are used. Relevancy counters are a simple method of determining whether past decisions or cached information are still relevant in the current environment. When an object is cached or a privilege is checked, the value of the appropriate relevancy counter is stored at that time. When the cached object or decision point is next revisited, the stored value is compared to the current value. If they are the same, then the old object or decision is still valid, else it must be revisited. Relevancy counters are only meaningful to coordinator agents. There are two types of relevancy counters; those based on global, shared database information, and those focussed on application/connection information.
- The following counters are global across the database and are maintained at ale catalogue node.
- Database Privileges Counter
- Reflects revocation actions against the SYSDBAUTH catalogue table.
- Package Privileges Counter
- Reflect revocation against the SYSPLANAUTH catalogue table.
- User Privileges Counter
- Reflects revocation actions against the SYSUSERAUTH catalogue table.
- Statistics Iteration Counter
- Reflects changes to statistical information either through an Update Statistics statement or a call to RUNSTATS
- Any change to the value of one of these counters must be broadcast to all nodes of the database in order to ensure database and application relevancy.
- The following counters are unique to a specific application and are only referenced or updated by the coordinator agent. They are used to determine if a new compilation environment exists for the application or whether any SQL statements being compiled are possibly dependent on uncommitted Data Definition Language (DDL) performed by the application in this Unit of Work.
- Compilation Ennvironment Counter
- Reflects changes in the application compilation environment for dynamic SQL that affect the compilation environment used to select variations. An example would he if the application issued a SET CURRENT SCHEMA statement which changes the default qualifier to be used for unqualified object references in any future dynamic SQL statements issued by the application.
- DDL in UOW Counter
- Reflects the sentence of DD1, actions by the application within the current unit of work.
- These counters are kept with other application information that is shared by all agents working for that application. These counters are used to determine if a new variation is required during a unit of work due to DDL or SRT statements issued by the applications since the beginning of current unit of work.
- Invalidation of a Variation
- A variation for a dynamic SQL statement can become invalid for a number of reasons, some of which relate to a change in the compilation environment in which the variation was created. Since dynamic SQL must reflect all changes in the compilation environment, as these changes occur a variation may become invalid and be eligible to be flushed from the dynamic SQL cache.
- All invalidating events are broadcast from the catalogue node to participating nodes once the invalidation event has occurred: i.e. the invalidation event is broadcast as part of the processing for an invalidation event.
- Inter-node Communication Messages
- There are a number of cache synchronization messages used to pass information between global SQL caches on different nodes. Synchronization messages are used to inform the cache at a node that a change has been made to the database environment and action needs to be taken to ensure the cache at this node remains relevant to the current environment. Upon receipt of a synchronization message that affects cached dynamic SQL statements, the receiving node will search the depedency portion58 of the
dynamic SQL cache 44 to determine which variations, if any, are affected by the message contents; these variations are invalidated. Upon receipt of a message affecting a cached package, the receiving node will perform the indicated action on the specified package. - The format of a synchronization message is,
- Node of Origin
- Application ID
- Originating Agent ID
- Event Class
- Update package privilege counters
- Flush package from cache (Exclusive Lock and remove from cache
- Invalidate package in cache (Exclusive Lock and set package status to INVALID)
- Inoperate package in cache (Exclusive Lock and set package status to INOPERATE)
- Invalidate dynamic SQL—Hard (Exclusive access on each variation affected and invalidate the variation)
- Invalidate dynamic SQL—Soft (Invalidate the variation)
- Update user privilege counters
- Update db auth privilege counters
- Temporary tablespace DDL—Soft (Invalidate the variation)
- Temporary tablespace DDL—Hard (Exclusive access on each variation affected and invalidate the variation)
- Object Type
- Object Schema
- Object Name
- Object Usage
- Object Column Information (if applicable)
- Counter information (if applicable)
- A synchronization message which is invalidating a package or cached dynamic SQL statements requires that the cache at the receiving node guarantee that all relevant cached packages or variations are locked exclusively for the requesting application. Once the appropriate action has been taken for a synchronization message, the receiving node(s) must respond to the requesting node with a confirmation message with a success or failure indicator.
- Since all DDL actions take place at the catalogue node, the catalogue node will be the source of all synchronization messages. All messages will be sent to all participating nodes except the current one (the catalogue node) as the cache at the current node will be manipulated directly. If a communication failure occurs during a transmission to a participating node, the sending function fails and the transaction is rolled back. Any other errors will also halt processing and cause the transaction to be rolled back.
Claims (11)
1. A database system comprising:
a) a plurality of nodes;
b) a plurality of databases stored on the nodes; and
c) a global cache stored on at least one node, the global cache being accessible to a plurality of applications in the database system.
2. A database system as claimed in claim 1 , wherein the global cache comprising a static portion and a dynamic portion.
3. A database system as claimed in claim 2 , wherein the dynamic portion of the global cache includes a plurality of variations.
4. A database system as claimed in claim 3 , which includes a catalogue node, wherein the database system includes, in the catalogue node, a list comprising a list of participating nodes that are active and connected to the database system.
5. A database system as claimed in claim 4 , wherein the static cache of the global cache comprises:
a) one or more packages, and one or more section entries within each of said packages; and
b) wherein the dynamic portion of the global cache comprises a statement portion and a dependency portion.
6. A method of maintaining cache and application integrity across a database system comprising a plurality of nodes and a plurality of databases stored on the nodes, the method comprising providing a global cache on one node including variations for access to data in a database, and the global cache being accessible to a plurality of applications on the database system; when a request is to be made of a database, first checking the global cache for a variation matching the current request and executing said variation if present.
7. 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:
(1) providing, for at least some of the databases, a global cache including information to enable agents to access the databases;
(2) originating a request through a coordinating node, the coordinating node sending information on the variation to be executed to remote nodes;
(3) having each remote node receive the request and check the global cache on the respective remote node for a current variation; and
(4) if the a remote node does not have the current variation, the remote node requesting the coordinating node to send the current version of the variation.
8. The method as claimed in claim 7 , the method including providing a catalogue node and maintaining, on the catalogue node, a list of participating nodes that are active and connected to the database system, and broadcasting a communication bulletin from the catalogue node to the nodes on the list of participating nodes when a change occurs to an object in the database that may invalidate a variation.
9. The method as claimed in claim 8 , the method including:
(1) the catalogue node receiving a REGISTER request when a node becomes active and updating a list of participating nodes by adding an entry for the newly entered node, and
(2) the catalogue node receiving an UNREGISTER request when a node becomes inactive and removing that node from the participating nodes list.
10. The method as claimed in claim 9 , the method including broadcasting a communication bulletin from the catalogue node to nodes on the list of participating nodes to ensure that all affected variations and all nodes are properly invalidated.
11. The method as claimed in claim 10 , the method composing the steps of:
(a) verifying if the section requested to be executed is valid and executing such section or,
(b) if such section requested to be executed is invalid, requesting a valid section from the coordinating node and executing such section upon receipt of the same from the coordinating node.
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
US09/162,165 US6353833B1 (en) | 1998-09-29 | 1998-09-29 | Caching of distributed dynamic SQL statements in a multiple node RDBMS |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
US09/162,165 US6353833B1 (en) | 1998-09-29 | 1998-09-29 | Caching of distributed dynamic SQL statements in a multiple node RDBMS |
Publications (2)
Publication Number | Publication Date |
---|---|
US20020026448A1 true US20020026448A1 (en) | 2002-02-28 |
US6353833B1 US6353833B1 (en) | 2002-03-05 |
Family
ID=22584435
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
US09/162,165 Expired - Lifetime US6353833B1 (en) | 1998-09-29 | 1998-09-29 | Caching of distributed dynamic SQL statements in a multiple node RDBMS |
Country Status (1)
Country | Link |
---|---|
US (1) | US6353833B1 (en) |
Cited By (13)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20030204504A1 (en) * | 2002-04-29 | 2003-10-30 | Stuy Michael A.F. | Access concurrency for cached authorization information in relational database systems |
US20050262103A1 (en) * | 1997-12-31 | 2005-11-24 | International Business Machines Corporation | Low overhead methods and apparatus shared access storage devices |
US7222117B1 (en) | 2003-11-14 | 2007-05-22 | Advent Software, Inc. | Segmented global area database |
US20100293140A1 (en) * | 2009-05-18 | 2010-11-18 | Shuhei Nishiyama | Distributed database system by sharing or replicating the meta information on memory caches |
US20120221768A1 (en) * | 2011-02-28 | 2012-08-30 | Bagal Prasad V | Universal cache management system |
US8886671B1 (en) | 2013-08-14 | 2014-11-11 | Advent Software, Inc. | Multi-tenant in-memory database (MUTED) system and method |
US10133667B2 (en) | 2016-09-06 | 2018-11-20 | Orcle International Corporation | Efficient data storage and retrieval using a heterogeneous main memory |
US10324809B2 (en) | 2016-09-12 | 2019-06-18 | Oracle International Corporation | Cache recovery for failed database instances |
CN110019300A (en) * | 2017-11-16 | 2019-07-16 | 中兴通讯股份有限公司 | The data access method and its system of distributed data base |
US10747782B2 (en) | 2016-09-16 | 2020-08-18 | Oracle International Corporation | Efficient dual-objective cache |
US10831666B2 (en) | 2018-10-05 | 2020-11-10 | Oracle International Corporation | Secondary storage server caching |
US11188516B2 (en) | 2018-08-24 | 2021-11-30 | Oracle International Corproation | Providing consistent database recovery after database failure for distributed databases with non-durable storage leveraging background synchronization point |
US11327887B2 (en) | 2017-09-14 | 2022-05-10 | Oracle International Corporation | Server-side extension of client-side caches |
Families Citing this family (16)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
JP3510042B2 (en) * | 1996-04-26 | 2004-03-22 | 株式会社日立製作所 | Database management method and system |
US7739308B2 (en) * | 2000-09-08 | 2010-06-15 | Oracle International Corporation | Techniques for automatically provisioning a database over a wide area network |
US7240324B2 (en) * | 2001-02-28 | 2007-07-03 | Hewlett-Packard Development Company, L.P. | Event-based scheduling method and system for workflow activities |
CA2365731C (en) * | 2001-12-20 | 2008-09-23 | Ibm Canada Limited-Ibm Canada Limitee | Support for concurrent active sql statements having shared sql context information |
US7305410B2 (en) * | 2002-12-26 | 2007-12-04 | Rocket Software, Inc. | Low-latency method to replace SQL insert for bulk data transfer to relational database |
CA2433377A1 (en) * | 2003-06-25 | 2004-12-25 | Ibm Canada Limited - Ibm Canada Limitee | Computing frequent value statistics in a partitioned relational database |
US7899797B2 (en) * | 2003-12-04 | 2011-03-01 | International Business Machines Corporation | Package resolution mechanism for database systems |
US7421562B2 (en) * | 2004-03-01 | 2008-09-02 | Sybase, Inc. | Database system providing methodology for extended memory support |
US8126873B2 (en) * | 2007-04-13 | 2012-02-28 | International Business Machines Corporation | Portable and iterative re-usable suboptimization of database queries |
US9715438B2 (en) * | 2007-06-29 | 2017-07-25 | International Business Machines Corporation | Static execution of statements in a program |
US7831569B2 (en) * | 2007-10-10 | 2010-11-09 | International Business Machines Corporation | Preserving a query plan cache |
US8458217B1 (en) | 2009-08-24 | 2013-06-04 | Advent Software, Inc. | Instantly built information space (IBIS) |
US8769350B1 (en) | 2011-09-20 | 2014-07-01 | Advent Software, Inc. | Multi-writer in-memory non-copying database (MIND) system and method |
US8332349B1 (en) | 2012-01-06 | 2012-12-11 | Advent Software, Inc. | Asynchronous acid event-driven data processing using audit trail tools for transaction systems |
US10380104B2 (en) | 2016-08-09 | 2019-08-13 | International Business Machines Corporation | Method to monitor dynamic SQL statements for automatic stabilization in a data sharing environment |
US11947568B1 (en) * | 2021-09-30 | 2024-04-02 | Amazon Technologies, Inc. | Working set ratio estimations of data items in a sliding time window for dynamically allocating computing resources for the data items |
Family Cites Families (12)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US5010478A (en) * | 1986-04-11 | 1991-04-23 | Deran Roger L | Entity-attribute value database system with inverse attribute for selectively relating two different entities |
US5136707A (en) * | 1988-10-28 | 1992-08-04 | At&T Bell Laboratories | Reliable database administration arrangement |
US5349678A (en) | 1991-08-21 | 1994-09-20 | Norand Corporation | Versatile RF data capture system |
JP3140621B2 (en) * | 1993-09-28 | 2001-03-05 | 株式会社日立製作所 | Distributed file system |
US5544345A (en) * | 1993-11-08 | 1996-08-06 | International Business Machines Corporation | Coherence controls for store-multiple shared data coordinated by cache directory entries in a shared electronic storage |
US5848241A (en) * | 1996-01-11 | 1998-12-08 | Openframe Corporation Ltd. | Resource sharing facility functions as a controller for secondary storage device and is accessible to all computers via inter system links |
US5897634A (en) * | 1997-05-09 | 1999-04-27 | International Business Machines Corporation | Optimized caching of SQL data in an object server system |
US5974129A (en) * | 1997-05-21 | 1999-10-26 | Lucent Technologies Inc. | Distributed virtual cache method for use in a database query control system |
US5924096A (en) * | 1997-10-15 | 1999-07-13 | Novell, Inc. | Distributed database using indexed into tags to tracks events according to type, update cache, create virtual update log on demand |
JPH11120048A (en) * | 1997-10-20 | 1999-04-30 | Fujitsu Ltd | Device and method for data caching of clinet-server decentralized system and medium where data caching program is recorded |
US6073129A (en) * | 1997-12-29 | 2000-06-06 | Bull Hn Information Systems Inc. | Method and apparatus for improving the performance of a database management system through a central cache mechanism |
US6115703A (en) * | 1998-05-11 | 2000-09-05 | International Business Machines Corporation | Two-level caching system for prepared SQL statements in a relational database management system |
-
1998
- 1998-09-29 US US09/162,165 patent/US6353833B1/en not_active Expired - Lifetime
Cited By (19)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20050262103A1 (en) * | 1997-12-31 | 2005-11-24 | International Business Machines Corporation | Low overhead methods and apparatus shared access storage devices |
US7299232B2 (en) * | 1997-12-31 | 2007-11-20 | International Business Machines Corporation | Low overhead methods and apparatus shared access storage devices |
US7970787B2 (en) * | 2002-04-29 | 2011-06-28 | International Business Machines Corporation | Access concurrency for cached authorization information in relational database systems |
US20030204504A1 (en) * | 2002-04-29 | 2003-10-30 | Stuy Michael A.F. | Access concurrency for cached authorization information in relational database systems |
US7222117B1 (en) | 2003-11-14 | 2007-05-22 | Advent Software, Inc. | Segmented global area database |
US20100293140A1 (en) * | 2009-05-18 | 2010-11-18 | Shuhei Nishiyama | Distributed database system by sharing or replicating the meta information on memory caches |
US8140498B2 (en) * | 2009-05-18 | 2012-03-20 | Shuhei Nishiyama | Distributed database system by sharing or replicating the meta information on memory caches |
US11755481B2 (en) | 2011-02-28 | 2023-09-12 | Oracle International Corporation | Universal cache management system |
US20120221768A1 (en) * | 2011-02-28 | 2012-08-30 | Bagal Prasad V | Universal cache management system |
US9703706B2 (en) * | 2011-02-28 | 2017-07-11 | Oracle International Corporation | Universal cache management system |
US10095619B2 (en) | 2011-02-28 | 2018-10-09 | Oracle International Corporation | Universal cache management system |
US8886671B1 (en) | 2013-08-14 | 2014-11-11 | Advent Software, Inc. | Multi-tenant in-memory database (MUTED) system and method |
US10133667B2 (en) | 2016-09-06 | 2018-11-20 | Orcle International Corporation | Efficient data storage and retrieval using a heterogeneous main memory |
US10324809B2 (en) | 2016-09-12 | 2019-06-18 | Oracle International Corporation | Cache recovery for failed database instances |
US10747782B2 (en) | 2016-09-16 | 2020-08-18 | Oracle International Corporation | Efficient dual-objective cache |
US11327887B2 (en) | 2017-09-14 | 2022-05-10 | Oracle International Corporation | Server-side extension of client-side caches |
CN110019300A (en) * | 2017-11-16 | 2019-07-16 | 中兴通讯股份有限公司 | The data access method and its system of distributed data base |
US11188516B2 (en) | 2018-08-24 | 2021-11-30 | Oracle International Corproation | Providing consistent database recovery after database failure for distributed databases with non-durable storage leveraging background synchronization point |
US10831666B2 (en) | 2018-10-05 | 2020-11-10 | Oracle International Corporation | Secondary storage server caching |
Also Published As
Publication number | Publication date |
---|---|
US6353833B1 (en) | 2002-03-05 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
US6353833B1 (en) | Caching of distributed dynamic SQL statements in a multiple node RDBMS | |
US6598058B2 (en) | Method and apparatus for cross-node sharing of cached dynamic SQL in a multiple relational database management system environment | |
US6321235B1 (en) | Global caching and sharing of SQL statements in a heterogeneous application environment | |
US7188116B2 (en) | Method and apparatus for deleting data in a database | |
US6446089B1 (en) | Method of using a cache to determine the visibility to a remote database client of a plurality of database transactions | |
US6178425B1 (en) | Method of determining the visibility to a remote database client of a plurality of database transactions using simplified visibility rules | |
US5625815A (en) | Relational database system and method with high data availability during table data restructuring | |
US7287249B2 (en) | Method and system for tracking and exchanging incremental changes to hierarchical objects | |
US7818297B2 (en) | System and method for refreshing a table using epochs | |
CN1708757B (en) | A transparent edge-of-network data cache | |
US6366901B1 (en) | Automatic database statistics maintenance and plan regeneration | |
US6216135B1 (en) | Method of determining visibility to a remote database client of a plurality of database transactions having variable visibility strengths | |
US6360214B1 (en) | Automatic database statistics creation | |
US7962481B2 (en) | Query based invalidation subscription | |
US7653665B1 (en) | Systems and methods for avoiding database anomalies when maintaining constraints and indexes in presence of snapshot isolation | |
US7685124B2 (en) | Systems and methods for performing authorization | |
CN111522631B (en) | Distributed transaction processing method, device, server and medium | |
US9171036B2 (en) | Batching heterogeneous database commands | |
US7953749B2 (en) | Providing the timing of the last committed change to a row in a database table | |
JPH113267A (en) | Method and device for combining state and behavior of object in database management system | |
US7020648B2 (en) | System and method for identifying and utilizing a secondary index to access a database using a management system without an internal catalogue of online metadata | |
WO2023124242A1 (en) | Transaction execution method and apparatus, device, and storage medium | |
US6615214B1 (en) | Accommodation of data definition statements in the sharing of dynamic SQL statements | |
CA2249059C (en) | Caching of distributed dynamic sql statements in a multiple node rdbms | |
CA2282999C (en) | Method and apparatus for cross-node sharing of instructions in a multiple node relational database management system environment |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
AS | Assignment |
Owner name: INTERNATIONAL BUSINESS MACHINES CORPORATION, NEW Y Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNORS:BIRD, PAUL M.;SNOWBELL, MICHAEL J.;REEL/FRAME:009715/0412 Effective date: 19981029 |
|
STCF | Information on status: patent grant |
Free format text: PATENTED CASE |
|
FPAY | Fee payment |
Year of fee payment: 4 |
|
FPAY | Fee payment |
Year of fee payment: 8 |
|
REMI | Maintenance fee reminder mailed | ||
FPAY | Fee payment |
Year of fee payment: 12 |
|
SULP | Surcharge for late payment |
Year of fee payment: 11 |