Migrating DB2 9 for z/OS Native SQL Procedures

Last week, a friend who works for a big DB2-using company in the American Midwest sent me a question concerning the migration of native SQL procedures from DB2 subsystem A to subsystem B (e.g., from a development to a test subsystem, or from test to production). This person's organization had been using DB2 for z/OS stored procedures for some time, and they had an automated process - utilizing IBM's Software Configuration and Library Manager product (SCLM) - that took care of moving a mainframe application program and associated items (load module, source code, control cards, etc.) from one DB2 environment to another. The company is in the process of going to DB2 for z/OS Version 9 from Version 8, and my friend and his colleagues are very much interested in taking advantage of the native SQL procedure functionality that's available in DB2 9 New Function Mode (I've done a good bit of blogging about DB2 9 native SQL procedures, starting with an overview-type entry posted last fall). There is plenty to get excited about here, including improved CPU efficiency versus external SQL procedures and excellent utilization of cost-effective zIIP engines on System z servers (when invoked through remote CALLs coming through the DB2 Distributed Data Facility), but people focused on the infrastructure of mainframe application systems may well wonder (as did my friend): given that a DB2 9 native SQL procedure is not dependent on external-to-DB2 items such as load and source modules (the executable is a DB2 package, and the source CREATE PROCEDURE statement is stored in the SYSROUTINES table in the DB2 catalog), how does one manage the migration of these stored procedures from (for example) test to production?

It turns out that new DB2 9 functionality, which gave rise to this question, also provides the solution in the form of two enhancements:

1. The new DEPLOY option of the BIND PACKAGE command. This new option lets you migrate a native SQL stored procedure (and a specific version of same, at that) from one DB2 for z/OS subsystem to another. DEPLOY essentially extends the functionality of a remote BIND command (that is, BIND to a remote subsystem), enabling you to add or replace a version of a native SQL procedure on the target remote subsystem from the current-location subsystem. DEPLOY does NOT change the logic portion of the native SQL procedure, which is stored in a special section of the package (the sections pertaining to SQL DML statements will of course be generated anew on the target remote system, so that you'll get appropriate access paths and such). Note that when you migrate a native SQL procedure using BIND PACKAGE with DEPLOY, you can change the qualifier used to resolve references to unqualified database objects named in SQL DML statements in the procedure.
2. New options for the ALTER PROCEDURE statement. You can issue ALTER PROCEDURE with the ACTIVATE VERSION option to make a particular version of a native SQL procedure the currently active version on a subsystem, so in the event that several versions of the native SQL procedure exist on the subsystem, the one identified via ALTER PROCEDURE ACTIVATE VERSION will be the one executed when a CALL to that procedure is executed (this default active version designation can be overridden at a thread level via the new CURRENT ROUTINE VERSION special register). ALTER PROCEDURE can also be used to drop a version of a native SQL procedure.

So, yes, when IBM's DB2 for z/OS development team was working on native SQL procedures for Version 9, they thought about mundane (though very important) matters such as inter-subsystem migration, as well as more-cool things like reduced execution instruction pathlength and nested compound SQL statements (the latter enabling, among other things, more sophisticated error-handling logic versus what you can achieve with an external SQL stored procedure). If you want to read more about native SQL procedure migration, check out the IBM "red book" titled "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond," the DB2 9 for z/OS Command Reference (for BIND PACKAGE with DEPLOY), and the DB2 9 for z/OS SQL Reference (for ALTER PROCEDURE with ACTIVATE VERSION).