DB2 for z/OS: An Interesting "Flip Side" to Native SQL Procedures

I heard the other day from a DB2 for z/OS DBA who communicated to me an interesting story. His organization had recently gotten into development and deployment of native SQL procedures on the mainframe DB2 platform. The native SQL procedures were working as advertised, but the move to utilize this DB2 technology had engendered grumbling on the part of some of the organizations' application programmers. In this entry I'll explain the basis of the developers' complaints, and I'll describe an approach that other organizations have taken to proactively address similar situations.

It's no secret that I've been bullish on DB2 for z/OS native SQL procedures since the get-go. I've done a lot of presenting and writing on the topic, starting with an entry that I posted back in 2008 to the blog I maintained while working as an independent consultant. Native SQL procedures were introduced with DB2 9 for z/OS in new-function mode (or with DB2 10 in new-function mode, for companies that migrated directly from DB2 Version 8 to DB2 10), and they deliver multiple benefits: significant processing offload to less-expensive zIIP engines on System z servers (when called by network-connected DRDA requesters), execution efficiency (they run in the DB2 DBM1 address space, versus an external-to-DB2 stored procedure address space), and simplified stored procedure management (there are no program object modules or load modules to manage -- a native SQL procedure's "executable" is its DB2 package). A lot to like.

So, what caused the aforementioned discontent among some of the application development colleagues of the DBA who shared his experience with me? In a nutshell, it was this: developing native SQL procedures ups the value of a programmer's ability to write sophisticated SQL statements, and a number of the more senior mainframe application developers at the organization to which I've referred felt that they were not prepared for this circumstance (the DBA explained that this underlying tension came to the fore when it was found that a SQL statement in a native SQL procedure performed more efficiently when it included a common table expression versus relying on a declared global temporary table).

I absolutely get this, and it very much does not reflect poorly on the skills and smarts of the programmers in question. Several of these individuals had been engaged for a long time in writing COBOL programs that included very simple SQL statements. I don't just mean simple in terms of an absence of things like common table expressions, CASE expressions, and CAST specifications. I mean simple as in not involving multi-table access via JOINs and/or UNIONs. Why is it that these folks generally wrote simple statements that didn't come close to leveraging the power of SQL? In large part -- and I've certainly seen this before -- it was because many of the programs running on the mainframe system had originally been written to access data in a non-relational DBMS and had been converted to access DB2 data. The programming interface to the non-relational DBMS was record-oriented, as opposed to being set-oriented. A program would access a record in a file, and if that record needed to be matched with one in another file then another single-record request would target that second file, using selection criteria obtained from the first retrieved record. When the database was migrated to DB2, the structure and logic of the programs that accessed the non-relational database were preserved, with very simple, single-row and single-table SQL statements taking the place of statements used to access data prior to the DB2 migration. This is not an uncommon approach for an organization to take when migrating data from a non-relational database to DB2: for the sake of expediency, existing database-accessing programs are converted to access DB2 with as little change as possible, and in terms of more fully exploiting SQL -- and DB2 for z/OS technology in general -- the focus is put on development of new applications. Consequently, you can end up with a group of programmers -- otherwise highly skilled -- who don't have much SQL knowledge beyond coding very simple statements because they haven't been asked to do more than that. Tell these folks to start developing native SQL procedures that are written entirely in SQL, and you might find that some of them feel under-prepared for that task. It's a recipe for frustration.

There are mainframe DB2-using companies that have anticipated this challenge and responded by creating a new role focused on facilitating the development and deployment of DB2 routines such as stored procedures (which could be native SQL procedures) and user-defined functions (which, starting with DB2 10 for z/OS in new-function mode, can be written using SQL Procedure Language). At one such company the name "procedural DBA" was given to the new role (at that company, some of the new "procedural DBAs" worked previously as traditional DB2 DBAs, while others were former application programmers who were keenly interested in building their SQL coding skills). The idea is to have a center of DB2 SQL excellence through which SQL coding knowledge (including development of native SQL procedures) can be diffused to the wider group of people in the organization who write programs that access DB2 data. I wrote about this new DB2-related role in an article that appeared in 2011 in IBM Data Management Magazine, and I blogged about the value to an organization of SQL coding skills in an entry posted to this blog, also in 2011.

Besides a role aimed at helping people to develop DB2 routines using SQL Procedure Language, make sure that programmers have access to helpful documentation. SQL Procedure Language statements (including statements that control logic flow in a routine) are documented in the DB2 for z/OS SQL Reference for DB2 9 and DB2 10. Information about creating native SQL procedures can be found in the DB2 for z/OS Application Programming and SQL Guide for DB2 9 and DB2 10. Another very useful source of information is the IBM "red book" titled, "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond." And, make sure that people are aware of forums through which DB2 people help other DB2 people with all kinds of questions, including those pertaining to native SQL procedure development. A personal favorite of mine is the DB2-L forum sponsored by the International DB2 Users Group (just go to www.idug.org, request -- for FREE -- a login if you don't already have a member ID, and click on the "DB2-L & Forums" link near the top of the IDUG home page).

SQL Procedure Language (SQL PL, for short) is important and getting more so in a DB2 for z/OS context (as previously mentioned, SQL PL can be used to create user-defined functions in a DB2 10 new-function mode environment, and SQL PL got a nice performance boost with DB2 10). People in your organization who have not heretofore written routines entirely in SQL can do so, effectively and efficiently, but that ability doesn't magically arise. Let people know that SQL coding help is available -- through SQL-knowledgeable individuals in your organization, in the DB2 documentation, and via DB2 forums -- and get ready to be impressed by what they can do. People like to learn new things. Give them that opportunity.