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:
Post a Comment