What's New for SQL PL in the IBM DB2 Universal Database "Stinger" Release - Page 2
June 1, 2004
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.