What's New for SQL PL in the IBM DB2 Universal Database "Stinger" Release

Tuesday Jun 1st 2004 by DatabaseJournal.com Staff

Part two of our Stinger preview series examines the new architecture for native SQL PL stored procedures that no longer require a C compiler.

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:

by Paul C. Zikopoulos and Roman B. Melnyk

How Things Work in DB2 UDB "Stinger"

When an SQL PL stored procedure is created, DB2 UDB "Stinger" creates a package for the stored procedure the same way it did before. The difference is that it will also create an array (which represents the procedural control flow) along with the accompanying byte code, which is used to drive section execution in the associated package.

If you ever need to rebind the package associated with an SQL procedure, you can use the REBIND_ROUTINE_PACKAGE procedure by passing it the name of the SQL procedure. The REBIND_ROUTINE_PACKAGE procedure will find the package for you and rebind it.

As previously noted, in DB2 UDB "Stinger," there is no need for a C compiler to create an SQL PL stored procedure.

For example, the following example shows a stored procedure coded in the DB2 Development Center:

Compiling this stored procedure on an installation of DB2 UDB before "Stinger," without a C compiler, results in the following error:

In "Stinger," the same stored procedure built on the same workstation would yield the following results (well, you wouldn't get the fancy bee and his message, but you get the point):

What About the Performance of SQL PL Stored Procedures in the New "Stinger" Architecture?

SQL PL stored procedures that use the new architecture, for the most part, will perform better than their predecessors did.

When an SQL PL stored procedure is called, "Stinger" does not have to switch between the hosted virtual machine and the C code. It is all done in the virtual machine inside the database engine. Thus, "Stinger" can take advantage of things such as initial assignments, caching within the virtual machine, collapsing SET sequences into a single statement, and so on.

For SQL PL procedures that are mainly SQL, you are not likely to see a lot of performance improvement, because there was not a lot of context switching between the virtual machine and the C code. However, the more scalar the operations coded inside your SQL PL logic containers are, the better the performance over the older architecture you are likely to see.

What About Stored Procedures You've Already Created using the Old Architecture?

Because a lot of business logic is built on the older SQL PL model, DB2 UDB "Stinger" is designed to handle the execution and deployment of stored procedures built on the older architecture.

We already mentioned the DB2 UDB commands (GET ROUTINE and PUT ROUTINE) that can be used to deploy SQL PL stored procedures to similar workstations that do not have a compiler. To support customers who have heavy scripting investments and use these routines as part of their deployment strategies, these commands are still supported in DB2 UDB "Stinger." Obviously, the execution of SQL PL stored procedures that were built before DB2 UDB "Stinger" is supported as well.

Any new SQL PL stored procedures created in "Stinger" and beyond will use the new architecture. You will not be able to create stored procedures that are compiled down to C code. If you want to migrate your procedures to the new architecture, you have to drop and recreate them (they will automatically rebind at CREATE time, and you will be able to find the code for these procedures in the DB2 UDB catalog views).

You may still need a compiler for your environment after installing DB2 UDB "Stinger". For example, if your user exits for Tivoli Storage Manager (TSM) archiving need to be compiled, you will still need a compiler. (It is worth noting, however, that the integrated log management in DB2 UDB "Stinger" eliminates the need for a user exit to archive log files to TSM - but that is another article.)


Although native SQL PL procedures are by far the biggest enhancement to stored procedures in the DB2 UDB "Stinger" release, there are some other enhancements that promote their usability in a DB2 UDB environment. For example, you can now specify the WITH HOLD clause when using FOR cursor loops without destroying the underlying cursor and you can also compute run-time SQLSTATE codes. However, by and large, the removal of the prerequisite for a C compiler is the most significant change to DB2 UDB SQL PL stored procedures in years.

We hope that we have given you yet another reason to check out the "Stinger" release. There are lots of other great new features; we'll cover some of them in future articles.

About the Authors

Paul C. Zikopoulos, BA, MBA, is with IBM Canada Ltd. Paul has written numerous magazine articles and books about DB2. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at: paulz_ibm at msn.com.

Roman B. Melnyk, PhD, is with IBM Canada Ltd., specializing in database administration, DB2 utilities, and SQL. Roman has written numerous DB2 books, articles, and other related materials. Roman co-authored DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, and DB2 for Dummies. You can reach him at roman_b_melnyk at hotmail.com.


The contents of this article represent those features that may or may not be available in the technology preview or beta. IBM reserves the right to include or exclude any functionality mentioned in this article for the "Stinger", or a subsequent release. As well, any performance claims made in this article are not official communications by IBM; rather the result observed by the authors is un-audited testing. The views expressed in this article are those of the authors and not necessarily those of IBM Canada Ltd. or the IBM Corporation.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved