APAR PM29226 and DB2 for z/OS Native SQL Procedure Source Code Management

Sometimes an APAR (in IBM-ese, a description of a software functional enhancement or defect correction effected via application of a fix) is not what it -- at first -- seems. Take DB2 for z/OS APAR PM29226, for example. The opening sentence of the brief (just one paragraph) APAR text reads as follows: "This APAR provides sample job DSNTEJ67 which initiates the process of converting source for an external SQL procedure into source for a native SQL procedure." A mainframe DB2 person reading those words might understandably conclude that the APAR is not relevant to his environment if either a) his organization has not made use of external SQL procedure functionality (external SQL procedures being what stored procedures written in SQL had to be prior to the introduction of native SQL procedures with DB2 9 for z/OS), or b) external SQL procedures have been deployed, but converting those to native SQL procedures is not currently a priority. In other words, you might think that PM29226 is about external-to-native SQL procedure conversion, period.

Folks, I have news for you: REGARDLESS of whether or not your organization has developed external SQL procedures, if you are using -- or thinking about using -- native SQL procedures in your DB2 for z/OS system, you really ought to take a look at the changes wrought by PM29226. External-to-native SQL procedure conversion is NOT the primary raison d'etre of this APAR; rather, the process of converting an external SQL procedure to a native SQL procedure is used to ILLUSTRATE a more important aspect of PM29226 -- that being the introduction of a number of services intended to facilitate source code management (SCM) in a DB2 for z/OS SQL procedure context.

Some background here: native SQL procedures are a big deal -- for my money, easily the most important enhancement since stored procedure functionality was introduced in the mid-1990s with DB2 Version 4 (I've blogged a lot on the subject, starting with an entry posted to the blog I maintained while working as an independent DB2 consultant). Native SQL procedures are advantageous in a number of ways, but they also introduce some new issues regarding development and deployment. One of these has to do with source code management. Here's why: for years, SCM as it pertains to stored procedures was an external-to-DB2 matter because stored procedure programs were external-to-DB2 objects. People would develop stored procedure programs in languages such as COBOL and Java, and they would use their SCM tool of choice to manage the associated source code (even an external SQL procedure has an outside-of-DB2 source code component, as the procedure is turned into a C-language program with embedded SQL statements as part of the execution preparation process). Along came native SQL procedures, and for the first time (as one DBA I met put it), "there's only one piece." In other words, there's not the DB2 piece and its corresponding external-to-DB2 piece -- the latter being the COBOL or Java (or whatever) source module (or the load module if you're talking about the executable). Instead, for a native SQL procedure there's just the DB2 piece: the CREATE PROCEDURE statement is the source code, period (and the stored procedure's package is the executable, period). DB2 people (DBAs and developers of DB2-accessing programs) have been accustomed to dealing with programs, written in various languages, that contain SQL statements. When the SQL statement (in this case, CREATE PROCEDURE) is the program, that introduces a new wrinkle with respect to SCM.

As native SQL procedures are still somewhat new in the mainframe DB2 world (they are available in a DB2 9 new-function mode environment, or with DB2 10 in CM9 mode, or DB2 10 NFM for organizations migrating to DB2 10 from DB2 V8), leading SCM tools tend not to provide explicit support for the management of program source when the source language is SQL Procedure Language (aka SQL PL, the language used to create DB2 native SQL procedures). That situation will be addressed as more and more organizations utilize native SQL procedures and request support in this area from their SCM tool vendors. In the meantime, the fix for PM29226 (UK71501 for DB2 10, and UK71502 for DB2 9) provide a means of bridging the SQL PL SCM gap.

To specifics: as previously mentioned, PM29226 modifies DB2 sample job DSNTEJ67. With the application of the fix for the APAR, DSNTEJ67 makes available to you a number of services, in the form of sample REXX routines, that support important SCM tasks applicable to the development and use of native SQL procedures. These tasks include source extraction, source transformation, and source deployment. [The new services, initially packaged in a macro library, are much easier to read when they have been expanded into members of a PDS. Step three of the PM29226-modified DSNTEJ67 job does this, using a temporary PDS. You might want to expand the REXX services into your own PDS, outside of the DSNTEJ67 job stream.] Among the services provided by PM29226 are the following:

• SQLPLSRC extracts the SQL PL source of a procedure from the DB2 catalog. Two other services enable one to save extracted SQL PL source code to either a RECFM F data set (fixed-length records, one logical record per physical block on disk) or a RECFM V data set (one variable-length logical record, stored as one physical block). The choice of record format might depend on the type of input that is suitable for an existing SCM tool or process: in some cases that could be a file; in others, a string.
• CHKSQLPL inspects and validates SQL PL source code, using the precompiler with the HOST(SQLPL) option.
• SQLPLTRN transforms a procedure's SQL PL source -- changing, adding, or removing options (e.g., schema, QUALIFIER, SQL PATH, DEBUG MODE, etc.) as specified by a user.
• CRSQLPL deploys a SQL procedure, with the SQL PL input coming from a file or a string.

Check these services out, and see if they can be of use to you as an aid in performing source code management tasks for your DB2 native SQL procedures.