CREATE OR REPLACE: Agile Deployment of Db2 for z/OS Stored Procedures

A relatively recent Db2 for z/OS enhancement that might have escaped your notice may well merit your consideration. Here's what I'm talking about: Db2 12 function level 507 (available since June of 2020, related to APAR PH24371) introduced CREATE OR REPLACE syntax for stored procedures. In a Db2 for z/OS context, CREATE OR REPLACE syntax was initially provided for Db2 12 advanced triggers, and it eliminated what had been a real irritant with regard to modifying a trigger. Extending CREATE OR REPLACE to stored procedures delivers a big benefit that I can sum up in two words: deployment agility.

Agility in development and deployment of new application functionality has very much grown in importance over the past few years, to no one's surprise. In an age of increasing enterprise digitalization, getting new capabilities and services into the hands of your customers and users in a timely and frequent way can be major competitive differentiator (or, in the case of a public sector organization, a source of citizen delight). Anyone who works with and advocates for Db2 for z/OS wants that DBMS to be fully plugged into an enterprise's DevOps pipeline, and that makes agility of deployment of the Db2 for z/OS objects associated with an application a priority. Suppose a Db2 for z/OS-accessing application utilizes some Db2 stored procedures, and a new iteration of the application is going to require modification of some of those stored procedures. Does that create any issues that could cause some friction on the deployment front? Quite possibly, yes, prior to Db2 12 function level 507.

How could a need to modify some Db2 stored procedures put some sand in the deployment gears? It comes down to the pre-function-level-507 options for deploying a new or modified stored procedure in "the next-higher-level" environment (e.g., dev to test, test to prod). Let's say that we're deploying to the production Db2 for z/OS environment, and let's say further that the stored procedures are of the native SQL variety (i.e., they're written in SQL PL), because it's for such procedures that CREATE OR REPLACE syntax is particularly helpful. OK, how do you get those modified native SQL procedures deployed to production in a pre-function-level-507 environment? You could get the job done using the BIND PACKAGE command with the DEPLOY option, but that approach is less than ideal from an agility perspective. Why? Because it relies on a Db2 command (BIND PACKAGE) versus a SQL statement. A Db2 for z/OS DBA might say, "What's the diff? I can use a Db2 command as easily as I can use a SQL statement." Sure you can, if you're using a tool like SPUFI (the TSO/ISPF-based interactive interface that comes with Db2), but DevOps engineers are not Db2 DBAs, and they're not SPUFI users. DevOps engineers like to use tools that work with all of the systems and servers across which an application's components are deployed. In other words, they have in mind a unified DevOps pipeline that can be used for all of an enterprise's applications. The tools generally used for such a pipeline are much more suited to issuing SQL statements than DBMS commands.

OK, BIND PACKAGE with DEPLOY is not your only option in a pre-function-level-507 Db2 system. You could go the drop and re-create route, couldn't you? Drop the already-there stored procedures in the production environment, and re-create them with the desired modifications. DROP PROCEDURE and CREATE PROCEDURE are SQL statements, not Db2 commands, so this approach satisfies the agility requirement, right? Eh, not really. Why not? Well, here's one problem: after you drop and re-create the Db2 stored procedures, you have to re-grant associated authorizations (referring to the EXECUTE privilege that one or more IDs need on the stored procedures). That can be a hassle. Another problem with the drop and re-create approach: if a stored procedure that you want to drop is called by a SQL PL routine (such as a native SQL procedure), the DROP will fail. You could drop the calling SQL PL routine that was blocking the drop of the stored procedure, but then you'd have to re-create that routine in addition to creating the dropped stored procedure. And, what if the DROP-blocking SQL PL routine is itself called by another SQL PL routine? You'd have to drop and re-create that routine, too. As you can see, the situation can get pretty messy pretty fast.

Enter CREATE OR REPLACE functionality, and things get easy peasy. I'll illustrate with an example. Let's say that we have this native SQL procedure already in the production Db2 environment:

CREATE PROCEDURE MYPROC1

   ( IN  P1 CHAR(5),  

     OUT P2 DECIMAL(15,2) ) 

  BEGIN 

     SELECT AVG(SALARY) INTO P2 

       FROM DSN8C10.EMP

      WHERE WORKDEPT = P1;

  END

OK, now I'm going to deploy a slightly modified form of this stored procedure (slight modification highlighted in orange). Function level 507 (or higher) is activated in my Db2 12 environment, and the package through which I'm issuing SQL statements (an IBM Data Server Driver package, or whatever) has an APPLCOMPAT value of V12R1M507 (or higher), so I can use CREATE OR REPLACE syntax, as shown below.

CREATE OR REPLACE PROCEDURE MYPROC1

   ( IN  P1 CHAR(5),  

     OUT P2 DECIMAL(15,2) ) 

  BEGIN 

     SELECT AVG(SALARY + 1000) INTO P2 

       FROM DSN8C10.EMP

      WHERE WORKDEPT = P1;

  END

What will Db2 do in this case? Db2 will check to see if a stored procedure named MYPROC1 already exists in the production system. One does (we created it earlier), so Db2 replaces the existing MYPROC1 procedure with the modified MYPROC1. After that replace action has been accomplished, do I need to re-grant authorizations for the MYPROC1 procedure? No! Why not? Because you didn't drop MYPROC1 (the word "DROP" appears nowhere in the SQL statement above). What if MYPROC1 is called by a SQL PL routine? Will that block the replace action? No! Why not? Because the SQL PL caller of MYPROC1 would block a DROP of MYPROC1, and you didn't drop MYPROC1 (you replaced MYPROC1, and a procedure replace action is not blocked by a SQL PL routine that calls the procedure being replaced).

Go forward in time a bit more, and we need to deploy yet another modified form of MYPROC1. This time, I want to add a version V2 of MYPROC1, as opposed to replacing the existing version of the procedure, so I specify VERSION V2 in my CREATE OR REPLACE statement (I can do that if the stored procedure is a native SQL procedure - can't if it's an external stored procedure written in a language such as COBOL or C):

CREATE OR REPLACE PROCEDURE MYPROC1

   ( IN  P1 CHAR(5),  

     OUT P2 DECIMAL(15,2) ) 

  VERSION V2

  BEGIN 

     SELECT AVG(SALARY + 5000) INTO P2 

       FROM DSN8C10.EMP

      WHERE WORKDEPT = P1;

  END

What will Db2 do this time? It will check to see if there is already a version V2 of MYPROC1 in this production system. There isn't, so Db2 creates version V2 of MYPROC1.

One more illustrative change: I want to modify version V2 of MYPROC1. To do that, I issue a CREATE OR REPLACE PROCEDURE statement for MYPROC1 that includes a reference to version V2 of the procedure:

CREATE OR REPLACE PROCEDURE MYPROC1

   ( IN  P1 CHAR(5),  

     OUT P2 DECIMAL(15,2) ) 

  VERSION V2

  BEGIN 

     SELECT AVG(SALARY + 9000) INTO P2 

       FROM DSN8C10.EMP

      WHERE WORKDEPT = P1;

  END

Db2 will check to see if there is already a version V2 of MYPROC1 in the production system. There is, so Db2 will replace version V2 of the procedure.

And there you have it: a SQL statement-driven (not command-driven) means of creating or replacing stored procedures in "the next-higher-level" environment that avoids the hassles associated with a drop and re-create approach. Big plus. Organizations have already been doing agile things with Db2 for z/OS stored procedures (especially the native SQL kind), like using their source code management tool of choice (GitLab, for example) to manage stored procedure source code (which, for a native SQL procedure, is the procedure's CREATE PROCEDURE statement), and using the IBM Db2 for z/OS Developer Extension for Visual Studio Code to give developers an assist in creating and debugging native SQL procedures. Add CREATE OR REPLACE PROCEDURE to that picture, and you take development and deployment agility for Db2 stored procedures (especially the native SQL kind) to a whole new level. Check it out.