DB2 for z/OS SQL PL Routines: REBIND PACKAGE vs. ALTER REGENERATE

I have long been a big fan of DB2 for z/OS native SQL procedures, but I also recognized early on that putting this technology to use would require DB2 DBAs to learn some new things. Much of this newness has to do with the fact that for a native SQL procedure (and for its more recently arrived cousin, the "native" SQL scalar function, also known as a compiled SQL scalar function or a non-inline SQL scalar function), there is no external-to-DB2 executable -- no object module, no load module. There is just the SQL PL routine's package (SQL PL is the language in which native SQL procedures and non-inlne SQL scalar functions are written). The package is the executable, period. That being the case, managing these packages is a different game versus managing a package that is tied to (for example) the external-to-DB2 load module of a COBOL stored procedure program. Think about making a package "active" in a DB2 for z/OS system. In the context of a load module-tied package, that's familiar territory: you bind the package into the target DB2 environment, and it doesn't get "activated" (i.e., it's not used) until the associated load module (batch job, CICS transaction, external stored procedure, whatever) is executed in that environment. But what if the package itself is the sole executable (the case, as previously mentioned, for a native SQL procedure or non-inlined SQL scalar function)? What then? Well, that's what the DEPLOY option of BIND PACKAGE, and the ACTIVATE VERSION option of ALTER PROCEDURE and ALTER FUNCTION, are for, as I pointed out in a blog entry (written while I was working as an independent consultant) that I posted back in 2009. In the blog entry I'm writing right now, I want to provide you with information about another new wrinkle associated SQL PL routines: "full" versus "partial" package bind.

"Say what?" you might be thinking. "You don't 'partially' bind a package. You bind the whole thing." That WAS true before SQL PL routines came along (and with "SQL routines" I am NOT referring to external SQL procedures, which are written in SQL PL but become C language programs with embedded SQL DML statements in the course of being prepared for execution). When the package in question is related to a native SQL procedure or a non-inline SQL scalar user-defined function (UDF), a REBIND PACKAGE operation in fact rebinds only part of the package. To understand why this is so, consider that the package for a SQL PL routine has a section that corresponds to the SQL "control" statements in the routine (i.e., the logic flow control statements such as IF, WHILE, ITERATE, and LOOP), and another section that pertains to the SQL DML statements (e.g., SELECT, INSERT, UPDATE, DELETE) in the native SQL procedure or non-inline SQL scalar UDF. Generally speaking, a REBIND PACKAGE is executed so as to drive reoptimization for SQL DML statements. If, say, a new index is added to a table to provide a better-performing access path for a static SQL statement embedded in a transaction program, DB2 will not use the new index in executing the statement unless the package of which the statement is a part is rebound. But what if the package is, essentially, the compiled form of a native SQL procedure or a non-inline SQL scalar UDF? In that case, if you want to drive reoptimization of DML statements included in the SQL procedure or UDF, you don't necessarily want the control statements in the SQL PL routine reworked, and so REBIND PACKAGE for a SQL PL routine leaves the control-statement section of the package alone and rebinds only the DML section.

What if you DO want a SQL PL routine's package to be rebound in its entirety? That can be done by way of an ALTER PROCEDURE (or ALTER FUNCTION) statement with the REGENERATE option specified. The effect of that statement's execution will be a rebinding of the whole of the package -- the SQL control statements as well as the DML statements. Why might you want to use ALTER PROCEDURE (or ALTER FUNCTION) with REGENERATE versus REBIND PACKAGE for the package associated with a SQL PL routine? One reason would be to rebind the control statements of a routine so as to get the advantage of a SQL PL performance enhancement (an example of such an enhancement would be the reduced path length of IF statement execution delivered with DB2 10 for z/OS). Here's another reason to use ALTER PROCEDURE (or FUNCTION) with REGENERATE: in a DB2 10 (or later) environment, it gets the bulk of the control section of the package, as well as the DML section, copied into above-the-bar virtual storage in the DB2 database services address space (DBM1) when the package is executed (i.e., when it's allocated to a thread). If a native SQL procedure created in a DB2 9 system is not either regenerated (via ALTER PROCEDURE with REGENERATE) or recreated in a DB2 10 (or later) system, the control section of the procedure's package will occupy more below-the-bar virtual storage when allocated to a thread.

Note that the REBIND PACKAGE option APREUSE, which tells DB2 to reuse the existing access path (when possible) for SQL DML statements when a package is rebound, is not an option that can be specified in an ALTER PROCEDURE (or FUNCTION) statement; so, when you execute ALTER PROCEDURE (or FUNCTION) with REGENERATE, access path changes for SQL DML statements in the procedure or function are a possibility.

Now you know (if you didn't already): with respect to a package associated with a native SQL procedure or a non-inline SQL scalar UDF, use REBIND PACKAGE when all you want to do is rebind the part of the package pertaining to the SQL DML statements in the procedure or function. To rebind all of the package's statements -- control statements as well as DML statements -- use ALTER PROCEDURE or ALTER FUNCTION with the REGENERATE option.