Another Advantage of DB2 for z/OS V9 Native SQL Procedures

In an entry posted to this blog last November, I wrote about some of the benefits of using native SQL procedures (which one can create and deploy with DB2 for z/OS V9 in New Function Mode) versus external SQL procedures (these get turned into C language executables under the covers via the program preparation process). In particular, I focused on reduced computing costs (native SQL procedures are more CPU-efficient than external SQL procedures, and they can utilize zIIP engine cycles when invoked through the DB2 Distributed Data Facility) and enhanced functionality (native SQL procedures support nested compound statements which, among other things, can enable more-sophisticated condition-handling logic). Recently I've been impressed by yet another advantage of native SQL procedures: they are easier to create and manage than their external counterparts (though there is an "eye of the beholder" aspect of this assessment, as I'll explain momentarily).

Because an external SQL procedure will end up becoming (as previously mentioned) a C language program, resources outside of DB2 are involved in the program preparation process. At many mainframe DB2 sites, people use DSNTPSMP, a REXX language stored procedure that comes with DB2, to prepare external SQL procedures for execution. DSNTPSMP handles the necessary create, precompile, compile, link edit, and bind tasks, and doing those things requires access to work file, source code, DBRM, and load module data sets that are a) outside of DB2 and b) protected by RACF (or by an equivalent mainframe-based security management product). By design, DSNTPSMP is created (as part of a DB2 subsystem installation or migration) with a SECURITY specification of USER (SECURITY, one of the options on a CREATE PROCEDURE statement, indicates how access by the stored procedure to resources outside of DB2 is to be handled). This means that the authorization ID of a user who invokes DSNTPSMP has to be permitted by RACF (or equivalent) to access the aforementioned non-DB2 resources. Users invoking DSNTPSMP must also have the right level of DB2 authorization, including the EXECUTE privilege on DSNTPSMP and access privileges to some DB2 catalog tables (e.g., SELECT on SYSROUTINES and SELECT, INSERT, UPDATE, and DELETE on SYSROUTINES_SRC). Managing these authorizations can be simplified through the use of secondary authorization IDs (i.e., granting privileges to a RACF - or equivalent - group and then connecting individual IDs to said group), but for secondary auth IDs to work correctly any and all libraries containing DB2 authorization exits have to be identified in STEPLIB in the JCL of the WLM environment in which DSNTPSMP runs, and sometimes people overlook that.

The rather involved nature of external SQL procedure preparation is in contrast to the straightforward preparation of a native SQL procedure. External (to DB2) resources are not required to prepare or to execute a native SQL procedure. No load libraries: the prepared procedure is just a package (i.e., a DB2 "runtime structure" based on the SQL statements to be executed), and it's stored in the DB2 directory. No WLM application execution environment: when a native SQL procedure is called it executes in the DB2 database services address space (aka DBM1). This lack of external dependencies makes lifecycle processes simpler for native SQL procedures relative to external procedures, and in my book simple is generally good.

Now, about the "eye of the beholder" aspect of my characterization of lifecycle processes (create, maintain, etc.) as being simpler for native SQL procedures than for external SQL procedures: it's true that external procedure preparation and management is more complex for external SQL procedures, but once these processes and the attendant infrastructure are established, the environment should be stable and straightforward from an administrative perspective - as many users of DB2 for z/OS external SQL procedures will attest. I would encourage people who are currently using external SQL procedures to use native SQL procedures once they have DB2 for z/OS V9 running in New Function Mode, but that should be a deliberate move as opposed to a pell-mell rush. I like the approach advocated by Tom Miller, Senior Software Engineer at IBM's Silicon Valley Lab and a leader in the area of DB2 for z/OS stored procedure functionality. Tom's advice for current users of external SQL procedures who are looking to use DB2 V9 native SQL procedures:

1. Get familiar and comfortable with the different lifecycle processes associated with internal versus external SQL procedures.
2. Consider converting external SQL procedures to native SQL procedures when upgrading existing DB2 applications.
3. As appropriate, choose native SQL procedures for new DB2 application development.

External SQL procedures are good. Native SQL procedures are better. Users of the former should prepare to upgrade to the latter. Your investment of time in planning and testing will set the stage for SQL procedures that are more efficient, more flexible, and easier to manage.