Browse Prior Art Database

Efficient Support of INHERIT/DEFAULT Special Register Semantics for Routine Invocations in a Partitioned Relational Database System

IP.com Disclosure Number: IPCOM000013816D
Original Publication Date: 2001-Feb-01
Included in the Prior Art Database: 2003-Jun-18
Document File: 2 page(s) / 59K

Publishing Venue

IBM

Abstract

Disclosed is a method of efficiently supporting INHERIT and DEFAULT semantics for SQL special register values referenced by routines invoked within a partitioned relational database. In a partitioned relational database system, a routine containing SQL statements can be invoked at any database partition, remote or local, at any time by another SQL statement that originated on a different partition. A performance problem arises in providing the correct set of initial register values to the new routine invocation in a prompt and efficient manner. Much time can be lost if a request must be sent back to the original partition to fetch the register values. And yet, pushing the values of registers out to all database partitions incurs a transaction overhead that is potentially unnecessary, in the event that no invocations occur, as well as requiring that any future updates to the register values be pushed out to all partitions as well. This disclosure proposes a method to ensure prompt, accurate delivery of the special register values for both INHERIT and DEFAULT behaviours to a routine containing SQL. This is achieved by determining during compilation that an SQL statement could invoke a routine containing SQL and using that knowledge to control the need and timing of sending the special register information. When a routine containing SQL, such as a User Defined Function (UDF) or a Stored Procedure (STP), is invoked by an SQL statement, the SQL statements within the invoked routine can reference any one of the updatable special registers defined within the SQL Language. The initial values used for these registers within a routine invocation can be controlled via the specification of the INHERIT or DEFAULT clause in the SQL statement used to define the routine to the relational database. INHERIT states that the current values of the special registers should be inherited from the caller of the routine as they existed at the time of the parent statement's start of execution while DEFAULT states that the default, or original, connection values should be used. The approach to be taken requires the following actions: When a connection is first established, the current register values for all updatable special

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

Page 1 of 2

  Efficient Support of INHERIT/DEFAULT Special Register Semantics for Routine Invocations in a Partitioned Relational Database System

Disclosed is a method of efficiently supporting INHERIT and DEFAULT semantics for SQL special register values referenced by routines invoked within a partitioned relational database. In a partitioned relational database system, a routine containing SQL statements can be invoked at any database partition, remote or local, at any time by another SQL statement that originated on a different partition. A performance problem arises in providing the correct set of initial register values to the new routine invocation in a prompt and efficient manner. Much time can be lost if a request must be sent back to the original partition to fetch the register values. And yet, pushing the values of registers out to all database partitions incurs a transaction overhead that is potentially unnecessary, in the event that no invocations occur, as well as requiring that any future updates to the register values be pushed out to all partitions as well. This disclosure proposes a method to ensure prompt, accurate delivery of the special register values for both INHERIT and DEFAULT behaviours to a routine containing SQL. This is achieved by determining during compilation that an SQL statement could invoke a routine containing SQL and using that knowledge to control the need and timing of sending the special register information.

When a routine containing SQL, such as a User Defined Function (UDF) or a Stored Procedure (STP), is invoked by an SQL statement, the SQL statements within the invoked routine can reference any one of the updatable special registers defined within the SQL Language. The initial values used for these registers within a routine invocation can be controlled via the specification of the INHERIT or DEFAULT clause in the SQL statement used to define the routine to the relational database. INHERIT states that the current values of the special registers should be inherited from the caller of the routine as they existed at the time of the parent statement's start of execution while DEFAULT states that the default, or original, connection values should be used.

The approach to be taken requires the following actions:

When a connection is first established, the current register values for all updatable special

registers are stored in application memory for later reference as DEFAULT VALUES. During the compilation of any SQL statement, the SQL compiler will detect whether a

routine containing SQL could be invoked during the execution of the statement and what type of special register behaviour has been specified for that routine. If an invocation is detected, the exec...