DB2 for z/OS: Non-Disruptively Altering a Native SQL Procedure

Not long ago, I was contacted by a DB2 for z/OS DBA who wanted to run a situation by me. On one of his organizations' mainframe DB2 systems -- a system with very high transaction volumes and very stringent application availability requirements -- there was a native SQL procedure for which the ASUTIME specification had to be changed. The DBA wanted to see if I could help him to find a non-disruptive way to effect this stored procedure modification.

[Background: a native SQL procedure is a DB2 stored procedure, written in SQL procedure language (SQP PL), for which the associated package is the the procedure's only executable (a native SQL procedure does not have an external-to-DB2 load module). ASUTIME is an option that can be included in a CREATE or ALTER statement for a DB2 stored procedure, and it indicates the maximum amount of mainframe processor time, in CPU service units, that can be consumed in one execution of the stored procedure. The default value for ASUTIME is NO LIMIT, and in this case the DBA needed to set an ASUTIME limit for a stored procedure because it was sometimes running too long.]

One stored procedure change mechanism that you really want to avoid, if at all possible, is drop and re-create. At some DB2 for z/OS sites, particularly when use of stored procedures is at an early stage, it is not uncommon for stored procedure changes to be accomplished through a drop and re-create process. That approach, aside from being relatively disruptive, can become unfeasible once an organization starts using nested stored procedures (referring to stored procedures that are called by other stored procedures). Native SQL procedures, in particular, make the drop-and-re-create means of stored procedure modification problematic, because an attempt to drop a stored procedure (whether native or external) will fail if that stored procedure is called by a native SQL procedure.

So, ALTER PROCEDURE is the way to go, versus drop and re-create, unless the change you want to make cannot be accomplished with ALTER (several parameter-related changes come to mind here -- ALTER PROCEDURE can be used to change the names of a native SQL procedure's parameters, but not to change the number of parameters for a stored procedure, or a parameter's usage (e.g., from IN to OUT), or a parameter's data type). You need to keep in mind, however, that changing some options of the current version of a native SQL procedure via ALTER PROCEDURE will cause the packages of programs that call the altered SQL procedure to be invalidated. Other ALTER PROCEDURE changes -- again, when it is the current version of the procedure that is modified -- cause the package of the native SQL procedure itself to be invalidated (and some current-version changes do both: they invalidate the SQL procedure's package and the packages of programs that call the SQL procedure). A table in the DB2 for z/OS SQL Reference shows the package invalidation effects of changing various options of the current version of a native SQL procedure via ALTER PROCEDURE. Here is the URL for information on ALTER PROCEDURE for a native SQL procedure, from the DB2 10 for z/OS Knowledge Center on IBM's Web site:

http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_sql_alterproceduresqlnative.dita

When you go to the Web page pointed to by the above URL, scroll down to Table 2, and there you will find the information pertaining to the package invalidation effects of ALTER PROCEDURE changes applied to the current version of a native SQL procedure. You will see in that table that changing ASUTIME via ALTER PROCEDURE does not invalidate (i.e., does not drive an implicit rebind or regeneration of) the package of the target native SQL procedure. This change does, however, invalidate the package of any program that calls the altered native SQL procedure. The DBA who brought his situation to my attention could have identified dependent packages (i.e., packages of programs that invoke the native SQL procedure for which the ASUTIME would be changed) via a query of the SYSIBM.SYSPACKDEP table in the DB2 catalog, and then could have issued the ALTER PROCEDURE statement and rebound the affected packages, given a brief window of time during which the related programs could be offline. That approach was not attractive to the DBA, given the fact that scheduled application outages at his site were hard to come by.

Putting our heads together, the DBA and I came up with an alternate, non-disruptive process for effecting the desired change in the definition of the native SQL procedure. This approach relies on two, not one, ALTER PROCEDURE statements to get the job done. The first is an ALTER PROCEDURE with the ADD VERSION clause, along with the new ASUTIME value (and this ALTER PROCEDURE statement would also include the list of parameters for the procedure, any options that will have non-default values, and the body of the procedure). Because the ASUTIME change applies to the new, added version of the SQL procedure, packages associated with callers of the current version of the stored procedure are not invalidated when the ALTER PROCEDURE with ADD VERSION is executed. A second ALTER PROCEDURE statement with the ACTIVATE VERSION clause is issued to make the just-added procedure version (the one with the updated ASUTIME value) the current version, and the change process is complete. Callers of the native SQL procedure are not disrupted by the ALTER PROCEDURE statement with ACTIVATE VERSION, because that statement just causes subsequent CALLs naming the SQL procedure to use the version of the procedure that you previously added with step 1 of this 2-step process.

The DBA tested this 2-step native SQL procedure change process during a period of heavy activity on a DB2 for z/OS system, and as expected, no contention issues were seen (even so, his plan is to use this process during periods when the system is less busy, and that is probably a good idea).

So, if you're willing to issue two ALTER PROCEDURE statements versus one, you can non-disruptively change ASUTIME and most other characteristics of a native SQL procedure. An extra ALTER PROCEDURE statement seems to me to be a small price to pay for enhanced application availability.