System, method, and program for extending a SQL compiler for handling control statements packaged with SQL query statements

A system for compiling a database language statement having a procedural part packaged with at least one declarative part, the system comprising:

• a parser for separating the procedural part from the declarative part whereby the procedural part, separated from the declarative part, is a control skeleton, and for generating a first representation of the declarative part and a control flow representation of the control skeleton;
• an analyzer creating a table dependency graph from each declarative part;
• a threaded code generator for generating a code sequence representing the control flow of the control skeleton;
• a query rewrite applying rewrite rules to the declarative part based upon information from the analyzer to rewrite a query for each declarative part;
• a query optimizer optimizing the code sequence and the plan for the declarative part based upon the information from the analyzer;
• and
• a plan synthesizer synthesizing a final plan from an optimized code sequence and an optimized execution plan from the query optimizer.

A system for compiling a database language statement having a procedural part packaged together with at least one declarative part, the database language statement embedded in an application program for querying a database managed by a server, the system comprising:

• an integrated compiler having a first compilation path for compiling each declarative part apart from the procedural part and having a second compilation path for processing the procedural part;
• a control analyzer, in the second compilation path, for analyzing a control flow of said procedural part;
• populating a host variable table with local variables, and creating a symbol table of local and host variables;
• a query compiler, in the first compilation path for handling all local variables as host variables from the host variable table populated with local variables in compiling each declarative part into an executable plan;
• a threaded code generator, in the second compilation path, for generating a threaded code sequence of the control flow of the procedural part;
• a plan synthesizer for embedding each executable plan into the threaded code sequence thereby creating a meta-plan, and allocating storage for local and host variables from the symbol table during code generation time;
• a run time interpreter recursively executing each execution plan embedded within the threaded code sequence within the meta-plan wherein the meta-plan has one bind-in operator and one bind-out operator for communicating with the application input and output values of local and host variables.

A method for compiling a database language statement having a procedural part packaged together with at least one declarative part, said method comprising:

• separating the procedural part from the declarative part;
• generating, by a query compiler, an executable plan for each of the at least one declarative part;
• generating a threaded code sequence based upon control flow information from the procedural part;
• and
• embedding the executable plan for each of the at least one declarative part as a sub plan into the threaded code sequence to create an executable meta-plan.

A method for compiling a database language statement having a procedural part packaged together with at least one declarative part, the method comprising:

• separating the database language statement into the procedural part and the declarative part;
• utilizing a query compilation path, for the declarative part, for generating an executable plan for each one of the at least one declarative part;
• utilizing a second compilation path, separate from said query compilation path, for the procedural part, for generating control flow information and a threaded code sequence based upon the control flow information;
• and
• synthesizing a final execution plan for the database language statement from the threaded code sequence and the executable plan for each one of the at least one declarative part.

A method for compiling a database language statement having a procedural part packaged together with at least one declarative part, the method comprising:

• transforming a SET statement into an equivalent query statement thereby directly translating the procedural part to a pure declarative part;
• extracting a search condition of a procedural part and replacing the search condition with a compiler generated local variable and, based upon the search condition, assigning a first representation for true or a second representation for false to the local variable, thereby creating a pure procedural part;
• replacing each declarative part with a token to create a control skeleton representing the procedural part of the database language statement;
• and
• compiling each declarative part through a query compiler and processing the control skeleton separate from the declarative part.

A method of compiling a database language statement having a procedural part packaged together with at least one declarative part, the method comprising:

• separating the procedural part and the declarative part into a control skeleton and the declarative part;
• compiling, through a first compilation path through a query compiler, each declarative part into an execution plan;
• generating, separately from said first compilation path, a threaded code sequence representing a control flow of the control skeleton;
• and
• synthesizing the threaded code sequence and each execution plan into one meta-plan.

A system for compiling a database language statement having a procedural part packaged together with at least one declarative part, said system comprising:

• means for separating the procedural part from the declarative part;
• means for processing the declarative part through a query compiler to generate an executable plan for each of the at least one declarative part;
• means, separate from the query compiler, for analyzing control flow information of the procedural part and generating a threaded code sequence based upon the control flow information;
• and
• means for merging the executable plan for each of the at least one declarative part into the threaded code sequence to create an executable meta-plan.

A system for compiling a database language statement having a procedural part packaged together with at least one declarative part, said system comprising:

• means for separating the procedural part from the declarative part;
• means for integrating a compilation of the declarative part in a first compilation path and a processing of the procedural part in a second path;
• means, in the first compilation path, for compiling each declarative part into an execution plan;
• means, in the second path for generating a threaded code sequence representing a control flow of the procedural part;
• and
• means for synthesizing the threaded code sequence and each execution plan into a meta-plan.

A system for compiling a database language statement having a procedural part packaged with at least one declarative part, the system comprising:

• means for separating the procedural part from the declarative part into a control skeleton and the at least one declarative part;
• means for generating a first representation of the declarative part and generating a control flow representation of a control flow of the control skeleton;
• means for performing an analysis on the control flow representation and creating a table dependency graph from each declarative part;
• means for generating a code sequence representing the control flow of the control skeleton;
• means for applying rewrite rules to the declarative part, based upon information from the means for performing an analysis, for generating a plan for each declarative part;
• means for optimizing the code sequence and the plan for the declarative part based upon the information from the means for performing an analysis;
• and
• means for synthesizing a final plan from an optimized code sequence and an optimized execution plan from the means for optimizing.

An article of manufacture having a computer usable medium having computer readable program code means embodied therein for enabling a compilation of a database language statement having a procedural part packaged together with at least one declarative part, said article of manufacture comprising:

• means for enabling a separation of the procedural part from the declarative part;
• means for enabling an integrated compilation of the declarative part in a first compilation path and a processing of the procedural part in a second path;
• means for enabling, in the first compilation path, a compilation of each declarative part into an execution plan by a query compiler;
• means for enabling, in the second path, a generation of a threaded code sequence representing a control flow of the procedural part;
• and
• means for enabling a merging of the threaded code sequence and each execution plan into a meta-plan.

A system for compiling a database language statement having a procedural part packaged together with at least one declarative part, the system comprising:

• means for compiling each declarative part into an executable plan;
• means for generating a threaded code sequence of a control flow of the procedural part;
• and
• means for embedding each executable plan into the threaded code sequence as a single executable meta-plan.

A method for compiling a database language statement having a procedural part packaged together with at least one declarative part, the method comprising:

• compiling each declarative part into an executable plan;
• generating a threaded code sequence of a control flow of the procedural part;
• and
• embedding each executable plan into the threaded code sequence.

An article of manufacture having a computer usable medium having computer readable program code means embodied therein for enabling a compilation of a database language statement having a procedural part packaged together with at least one declarative part, said article of manufacture comprising:

• means for enabling a compilation of each declarative part into an executable plan;
• means for enabling a generation of a threaded code sequence of a control flow of the procedural part;
• and
• means for enabling an embedding of each executable plan into the threaded code sequence as a single executable meta-plan.