Browse Prior Art Database

Method for generating interconnected sets of access path elements in an unfenced SQL PL Procedure

IP.com Disclosure Number: IPCOM000127887D
Original Publication Date: 2005-Sep-13
Included in the Prior Art Database: 2005-Sep-13
Document File: 3 page(s) / 107K

Publishing Venue

IBM

Abstract

Method for generating interconnected sets of access path elements in an unfenced SQL PL Procedure

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 38% of the total text.

Page 1 of 3

Method for generating interconnected sets of access path elements in an unfenced SQL PL Procedure
Background:As SQL (Structured Query Language) statements are passed into a database system Parse Tree Nodes (PTNs) and Access Path Elements (APEs) are built to represent the flow the database system should take to evaluate the given SQL statement. Traditionally, each SQL statement within an application program is processed independently. A database system converts PTNs into APEs. PTNs are built once and discarded once the APEs are constructed. Once the APE is built it may be stored in a container for the database system to execute over and over again. Since each SQL statement is typically processed independently, the APEs are not dependent on other SQL statements. An exception to this type of SQL statement independence would be for a positioned update or positioned delete where the positioned update/delete is dependent upon a cursor. SQL Procedures may be fenced or unfenced. If the SQL Procedure is fenced, the procedure will run in an external address space to prevent user programs from corrupting the database system storage. Likewise, in a fenced program, the SQL statements are linked together with application program logic and host variables. If the SQL Procedure is unfenced, the SQL body executes within the database's address space and therefore eliminates API crossings between the application program and the database system. Likewise, in an unfenced model the SQL statements are held together via an outermost set of APEs, which is not traditional application logic. For an unfenced SQL PL procedure that executes within a database system, multiple sets of PTNs and APEs may be generated for a given set of SQL statements. These SQL statements may be dependent upon other information and other SQL statements within the unfenced SQL PL procedure. As APEs are built, references to other APEs are needed, however those other APEs might not have been generated yet.

Problem:As APEs are built to represent the flow the database system should execute to evaluate an SQL statement, references to other APEs that might not have been generated yet are needed in the building of a particular APE. A method was developed to correctly build the APEs that reference other APEs. An example where this method was utilized was for continue handlers and GOTO statements within an unfenced SQL PL Procedure.

Solution:A two pass method was developed to correctly build sets of APEs for an unfenced SQL PL Procedure where APEs are dependent on other APEs. During the first pass as the database system builds PTNs and APEs for a given SQL statement, the database system recognizes a need to reference another PTN or APE and therefore stores a reference to the PTN and APE being built in a PTN/APE Reference Table. Once all sets of APEs are built for every SQL statement in an unfenced SQL PL procedure, the database system makes a pass over the PTNs registered in the PTN/APE R...