What’s New for SQL PL in the IBM DB2 Universal Database “Stinger” Release

by Paul C. Zikopoulos and Roman B. Melnyk

The next release of IBM DB2 Universal Database (DB2 UDB) for Linux, UNIX , and Windows is codenamed “Stinger.” As of the publication date of this article, DB2 UDB “Stinger” is available as an open beta for download at: www.ibm.com\data\stinger.’

In a previous article, we detailed some of the new options available to generated columns in the DB2 UDB “Stinger” release. In this article, we will detail the new architecture for native SQL PL stored procedures that no longer requires a C compiler.

Other SQL-related “Stinger” enhancements that we will cover in future articles include:

  • Enhanced nested savepoint functionality that lets you nest more than one level in a unit of work.
  • An increase in the maximum supported SQL statement size, from 64 KB to 2 MB.
  • Support for the CALL statement in the body of a trigger and other functions.
  • New binding options that let you define when a package should be optimized.
  • More granular lock timeout for applications that require varying lock wait times, depending on the business function.

The Old SQL PL Architecture – Get Your Compilers Out

DB2 UDB used to have a dependency on a C compiler for the creation of SQL PL procedures. Customers and partners have expressed their interest in eliminating this dependency in their DB2 UDB environments, for the following reasons.

First, there is a cost associated with requiring a C compiler on each DB2 UDB workstation where you want to create stored procedures. DB2 UDB is not packaged with a C compiler, so customers have to go out and purchase one. Hardware vendors used to ship C compilers with their workstations, but this is no longer the case. (Typically, customers already have one through some sort of developer’s subscription with their application development tool vendor, but the dependency still represents an added cost.) Of course, once you had a compiler installed, DB2 UDB had to be configured to use it (which involved setting the DB2_SQLROUTINE_COMPILER_PATH registry variable, linking to the compiler and its libraries and so on). Each of these factors impacted a user’s “out-of-the-box” experience.

Second, DB2 UDB is used as the “backbone” for many of the world’s most popular applications, and independent software vendors (ISVs) tend to encapsulate a good proportion of their application logic in stored procedures. This, in turn, means that customers who purchase these applications for a DB2 UDB platform must have a C compiler and ultimately face the same issues described in the previous paragraph.

Finally, because SQL PL procedures before “Stinger” were compiled down to C code, they have associated dynamic link libraries (DLLs) that are not handled as part of the database restore process. This means that database administrators (DBAs) need to pay special attention to these DLLs when performing a restore operation after a database crash; otherwise, they could end up with a recovered database that their applications could not use.

Before the “Stinger” release, the process of creating SQL PL procedures looked something like this:

C language constructs were used to represent the procedural control flow (IF/THEN/ELSE loops, conditional handling, and so on), to allocate space for host variables, and to implement variable assignments.

In this environment, when an SQL PL procedure was called, DB2 UDB had to go back-and-forth between the SQL running in a virtual machine within DB2 UDB, and the hosted C code, which was external to the engine.

In the DB2 UDB V7.2 release, the GET ROUTINE and PUT ROUTINE commands were introduced so that SQL PL stored procedures could be compiled and moved to other machines without the need for a compiler on those target machines; however, the target machines had to have the exact same architecture (DB2 UDB maintenance level, operating system, and so on). DB2 UDB V8.1 introduced the DB2 Development Center, which wrapped up these functions in an easy-to-use graphical user interface (GUI) that made deployment easy. Whereas these solutions provided an acceptable workaround for some customers, some concerns were not fully addressed.

The New SQL PL Architecture in “Stinger” – Compiler Who?

In DB2 UDB “Stinger,” the dependency on a C compiler to create SQL PL stored procedures has been eliminated. All of the operations that the C compiler was responsible for (outlined in the previous section) are now performed by DB2 UDB-generated byte code that is hosted in a virtual machine.

When an SQL PL stored procedure is called, its byte code is loaded from the DB2 UDB catalog into memory (it is stored in the SYSIBM.SYSCODEPROPERTIES table, in the SQL_COMPILED_CODE column). The interpreter is invoked and receives the stored procedure’s arguments and a pointer to the byte code array as parameters. At this point, DB2 UDB allocates the required memory for host variables before finally executing the byte code. Currently, a tool is being developed that will extract the byte code from this table and display its contents in a readable format.

Moving the SQL PL stored procedures code closer to the DB2 UDB engine allows for better integration between run-time environments. Better integration means better performance, as well as significant opportunities for future enhancements with respect to performance, the tools used to create stored procedures, profiling, debugging, and so on. (In other words, look for more good things to come.)

Compare the new SQL PL stored procedure architecture (shown below) with the old architecture (shown above) – it is substantially more straightforward:

Latest Articles