DB2 9 for z/OS - A Stored Procedure Game-Changer

Stored procedures have long been a mainstay of enterprise-class applications built on a DB2 for Linux/UNIX/Windows foundation. They provide for robust security (among other things, keeping the SQL DML statements server-side helps to restrict what people with ill intent can learn about your database schema, thereby reducing exposure to hack attacks), scalability (stored procedures can significantly reduce network traffic), and flexibility (packaging table-touching SQL statements in stored procedures is a nice way of abstracting DBMS particulars from client-side developers, allowing these folks to focus more of their coding efforts on business logic).

On the mainframe side, the story's been a little different, with stored procedure usage increasing since delivery of the feature with DB2 for z/OS Version 4, but not quite achieving what I'd call "escape velocity" (with the notable exception of some organizations that have embraced mainframe DB2 stored procedures in a major way). I believe that DB2 9 for z/OS, available since March of 2007, will change that situation.

It is not surprising that DB2 stored procedure usage didn't skyrocket from the get-go on the mainframe platform. For one thing, z/OS users already had some robust and reliable solutions for the server-side packaging of DB2 SQL statements, namely, the CICS and IMS/TM transaction management subsystems. Additionally, DB2 for z/OS stored procedures had some functionality holes early on that needed filling before some organizations would commit resources to leveraging the technology. With that in mind, I'd like to review some of the stored procedure enhancements that have come out in various releases of DB2 over the past dozen years:

• DB2 V4: Stored procedure functionality introduced.
• DB2 V5: Program calling a stored procedure can FETCH rows from a result set (previously, data could only be returned via output parameters - a problem for varying-size and many-row result sets).
• DB2 V6: CREATE/ALTER/DROP PROCEDURE statements added to DB2 DDL (before that, DBAs had to manually insert/update/delete rows in the SYSPROCEDURES catalog table).
• DB2 V7: SQL Procedure Language introduced, enabling programmers (and development-oriented DBAs) to code stored procedure programs in SQL, versus having to embed SQL statements in stored procedure programs written in COBOL or C (to make this work, DB2 SQL was extended to include logic flow-control statements such as GOTO, IF, ITERATE, LEAVE, LOOP, REPEAT, and WHILE).
• DB2 V8: Better synergy between DB2 and the z/OS Workload Manager in terms of optimizing the number of tasks in a WLM-managed stored procedure address space, plus the ability to specify an abend limit (a number of execution failures after which a stored procedure is placed in stopped status) at the individual stored procedure level (especially handy in a development environment).
• DB2 V9: Native SQL procedures, meaning stored procedures that are written in SQL and which execute in the DB2 Database Services address space (aka DBM1).

DB2 V9 native SQL procedure support is what I see as being the game-changer with regard to stored procedure usage on the mainframe platform. Why? Two reasons. First, it provides for a more streamlined stored procedure invocation process, as pointed out by Peggy Zagelow, one of IBM's senior software developers, in a blog entry earlier this year. With external procedures (and SQL procedures of the non-native variety end up executing as external stored procedures coded in C), the underlying program needs a language environment in which to execute. This is provided by a WLM-managed address space, and when the external procedure is called the caller's DB2 thread is suspended while it's switched from the caller's task (SRB or TCB) to the TCB associated with the external procedure program. In some real-world cases, this thread suspension has led to processing delays and increased DBM1 virtual storage consumption. Such problems can be dealt with through adjustments in WLM policy goals and/or DB2 thread limits, but with native SQL procedures they are eliminated entirely. A native SQL procedure exists in the form of a package, which is - as packages always have been - a "runtime structure" generated from the SQL statements to be executed. When a native SQL procedure is called, DB2 just switches from the caller's package to the stored procedure package. No thread-suspension-and-task-switching, and therefore no delay in stored procedure execution. Another benefit of the run-in-DB2 model is the elimination of instruction pathlength associated with crossing back and forth between DBM1 and a stored procedure address space for each SQL statement issued by an external procedure (I don't have numbers, but I expect that native SQL procedures are quite competitive with COBOL external procedures with respect to CPU consumption).

The second game-changing aspect of native SQL procedures is exploitation of zIIP processing resources (referring to the z9 Integrated Information Processor). zIIPs, as you may know already, are specialty engines on a mainframe that can run eligible workloads and which do not factor into mainframe software pricing (as general-purpose engines do). A native SQL procedure is zIIP eligible if it is invoked via a remote call through the Distributed Data Facility component of DB2 (commonly called DDF). Tests have shown that the amount of CPU processing directed to a zIIP can exceed 50% for some stored procedures. These zIIP MIPS are as inexpensive as they get on the mainframe platform, and native SQL procedures offer a great way to use 'em. Why the restriction of remote versus local calls regarding the zIIP eligibility of native SQL procedures? I believe that it reflects IBM's long-term vision of the role of DB2 on System z: a super-reliable, super-scalable, high-ratio-of-capacity-to-footprint data server supporting various application servers (e.g., WebSphere/Java, WebLogic/Java, Windows/.NET, Ruby on Rails, etc.). Basically, IBM with its zIIP initiative is giving you a financial incentive to move towards that architecture.

In addition to those Big Two native SQL procedure pluses, you get some advantages on the programming front versus external SQL procedures. Important in this regard is support for nested compound statements. With the ability to use more than one compound statement in a SQL procedure, you can code compound statements within condition handlers, thereby providing for a native SQL procedure much more sophisticated error-handling capabilities versus an external SQL procedure.

I feel pretty strongly that native SQL procedures are the way of the future as far as DB2 for z/OS is concerned. If you are already using external SQL procedures, plan on migrating these to native SQL procedures in a DB2 V9 New Function Mode environment (not a difficult process). If you are not yet using SQL procedures, I would encourage you to start using them. To help you on your way, take advantage of the recently published IBM "red book" titled DB2 9 Stored Procedures: Through the CALL and Beyond (a complete update of an outstanding red book originally published in 2004 for DB2 V7).

The mainframe DB2 stored procedure wave is now a big one. Catch it.