Supporting procedural constructs in existing SQL compilers

The draft of the SQL/PSM standard denes a procedural extension to the existing SQL2 language. An essential part of this extension is the support of procedural constructs such as BEGIN/END blocks, local variables, assignment statements, conditional statements, and various forms of loops.Such an extension introduces new challenges to existing SQL compilers. Most SQL compilers exiting in the marketplace today were built based on the declarativeness of SQL. The question is how these procedural extensions can be best implemented in a relational DBMS without losing the power of existing query optimization mechanisms. So far, most implementations of the SQL procedural extensions rely on the use of a separate interpreter to handle the procedural statements so that the existing SQL compiler can be left untouched. Although this approach is quite easy to implement (as it follows the paradigm currently used between SQL and host languages), it does not provide the best possible performance.In this paper, we propose an integrated approach to extend existing query compilers to support SQL/PSM-like procedural extensions. We show how the existing SQL compiler infrastructure can be generalized to accommodate the new procedural constructs, and we describe how this approach can be implemented as part of an existing DBMS. The work presented in this paper has been prototyped on the code base of IBM DB2 for Common Server Version 2 product.