Dismiss
InnovationQ will be updated on Sunday, Oct. 22, from 10am ET - noon. You may experience brief service interruptions during that time.
Browse Prior Art Database

Management of Cached Dynamic SQL Integrity in a Multiple Node RDBMS

IP.com Disclosure Number: IPCOM000123493D
Original Publication Date: 1998-Dec-01
Included in the Prior Art Database: 2005-Apr-04
Document File: 2 page(s) / 91K

Publishing Venue

IBM

Related People

Bird, PM: AUTHOR

Abstract

This invention describes an overall design for handling the interaction between dynamic SQL and actions that attempt to modify a database object (e.g. DDL SQL statements) in such a way as to protect the validity of any affected dynamic SQL statements that are currently executing and invalidate, or remove, any cached dynamic SQL statements which may be affected.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 51% of the total text.

Management of Cached Dynamic SQL Integrity in a Multiple Node RDBMS

   This invention describes an overall design for handling
the interaction between dynamic SQL and actions that attempt to
modify a database object (e.g. DDL SQL statements) in such a way as
to protect the validity of any affected dynamic SQL statements that
are currently executing and invalidate, or remove, any cached dynamic
SQL statements which may be affected.

   A fundamental component of this design requires that, at
each node in the database, every dynamic SQL statement that is
currently being executed or that is cached at that node have an
entry in a segment of shared database memory referred to as that
node's global SQL cache.  The global SQL cache is created when a
node becomes active, fills as SQL statements are executed at the
node, and is flushed when the node becomes inactive.  The global SQL
cache is shared among all database processes at a particular node.

   Each entry in the global SQL cache must contain a list of
the database objects upon which relies the validity and integrity of
the executable format, or section, of the dynamic SQL statement;
this list is referred to as the dependency list and contains an
entry for each database object that the section directly, or
indirectly, requires to remain as it was at the time that the
section was created.  Every database process, or agent, that is
currently using a dynamic SQL statement at a node must have a shared
lock, acquired at that node, upon the specific entry representing the
SQ statement.  Multiple agents performing work for the same
application at the same node that are using the same dynamic SQL
statement need only acquire one shared lock amongst them.  Diagram 1
shows an overview of the Global SQL Cache from the perspective of the
relationship between dynamic SQL statements and their list of
dependencies.  Note that the list are integrated into one common set
of unique dependency objects from which individual SQL statement
dependencies can be derived; an example would be the table TEST.T1
which has only one entry in the dependency lists but has two dynamic
SQL statements dependent upon it.

   The second major component of this design is the creation
and maintenance of a list of all nodes that are currently active for
the database and could potentially have SQL statements in their
global SQL cache; this list is referred to as the Participating Nodes
List.  The Participating Nodes List is maintained at the Catalog
Node, which is the database node where the system catalog tables
reside, as that node is guaranteed to be active wh...