DB2 9 Native SQL Procedures: One Address Space is OK

I've been doing a lot of presenting lately on the topic of DB2 for z/OS stored procedures. In these presentations, I've emphasized the benefits of native SQL procedures, introduced for the mainframe platform via DB2 9 for z/OS (I blogged on the importance of this development in an entry I posted late last year). During two different sessions held recently in two different cities, two different people asked me the same question pertaining to native SQL procedures versus external stored procedures (the latter being what you might think of as "traditional" stored procedures in a DB2 for z/OS environment). In this entry, I'll share with you that question and my response.

First, the common question: "When DB2 for z/OS Version 5 provided support for stored procedure address spaces managed by the Workload Manager (WLM) component of z/OS, we were told that an advantage of this enhancement was the ability to have multiple stored procedure address spaces, versus the one DB2-managed stored procedures address space (SPAS). With different stored procedures assigned to different WLM application environments and their associated address spaces, if a stored procedure program misbehaved in such a way as to bring down the address space in which it was running, the other stored procedure address spaces would not be impacted. Now, with DB2 9 native SQL procedures, we're back to one address space for stored procedure execution (native SQL procedures execute in the DB2 database services address space, also known as DBM1). Doesn't that mean that we now have the same risk we faced when using the old DB2-managed SPAS, namely, that one errant stored procedure could take down the one stored procedure address space (and this time, we're talking about losing DBM1)?"

My response: It's true that having multiple WLM-managed stored procedure address spaces can reduce the impact of an address space failure caused by an external stored procedure program, but that kind of failure has to do with stored procedure program code executing outside of DB2. Multiple DB2-accessing stored procedures running in in multiple stored procedure address spaces are all executing code in DBM1 when they issue SQL statements (as is true of multiple DB2-accessing CICS transaction programs running in multiple CICS AORs), and that doesn't cause DBM1 to crash. Native SQL procedures running in DBM1 execute as packages. It's all DB2-generated and DB2-managed code. This means that the exposure mitigated by having multiple WLM-managed stored procedure address spaces - that user-written stored procedure program code running outside of DB2 could cause a problem that would lead to the failure of a WLM-managed address space - does not exist for native SQL procedures. To put it another way, having native SQL procedures executing in one address space - DBM1 - is no more risky than having multiple packages invoked by external callers all running in DBM1, and that's been standard operating procedure for DB2 since day one (execution of an embedded SQL statement involves, under the covers, a call to DB2 and a reference to a section of a package).

So, take advantage of the enhanced performance and simplified lifecycle management offered by DB2 9 native SQL procedures, and don't worry about not having multiple address spaces in which to run these stored procedures - you don't need them. Native SQL procedures are made up of SQL statements, and SQL statements - as always - run in DBM1.