Wednesday, January 9, 2013

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.

16 comments:

  1. Robert, wouldn't it better to have the shop's DB2 for LUW writers of sophisticated SQL and SQL Procedure Language SPs and functions, do it across platforms?

    Or have Computer Science undergrads who know SQL and have been part of IBM mainframe training programs in the universities do the programming during the summer or as interns? An excellent way for both sides to "test drive" and for shops to more effectively recruit.

    Maybe the "more senior mainframe application developers" should focus their time understanding the user and business requirements and maintaining the legacy systems.

    ReplyDelete
    Replies
    1. That's a good point. At some organizations, the initial thought may be that because DB2 for z/OS native SQL procedures execute on the mainframe platform, they should be written by people who have historically developed DB2 for z/OS applications. In fact, as you've suggested, when it comes to DB2 for z/OS native SQL procedures, the more valuable experience is SQL coding experience, versus mainframe coding experience in general. Someone who has been proficient at writing SQL procedures on a DB2 for LUW platform should indeed be effective in writing DB2 for z/OS SQL procedures -- especially if they've done that SQL procedure development work using IBM's no-charge Data Studio tool (and the same goes for user-defined functions written in SQL Procedure Language). On top of that, I am a big fan of having developers of SQL procedures and SQL UDFs work across DB2 platforms. Your suggestion of having new hires just out of college, with SQL knowledge, work on native SQL procedure development is also worthy of consideration - it could be a great way to get such people into mainframe application development relatively quickly.

      With all this said, I'm hoping that veteran mainframe COBOL developers with good SQL skills will embrace DB2 for z/OS native SQL procedure development. This could be a way for these individuals to take their careers in a new direction, and to get more into new-application development, versus maintenance of "legacy" applications.

      Robert

      Delete
    2. Robert, thanks for your comments. I think the typical issue is that understanding requirements and dealing with legacy applications take up so much time and energy that a lot of people don't have the time and focus to build up the advanced SQL skill, especially if they haven't already moved that way or already become cross-platform or already moved to LUW.

      Quite a number of good LUW people started on mainframe DB2 (and IMS and CICS before that, for some ;-)).

      Delete
    3. "Quite a number of good LUW people started on mainframe DB2 (and IMS and CICS before that, for some)."

      True enough, but there's no reason that a veteran mainframe developer who wants to code in SQL Procedure Language should need to work on the DB2 for LUW platform in order to do so. Do it on the "big iron." I believe that SQL PL will be more and more widely used in DB2 for z/OS environments as time goes by.

      Robert

      Delete
    4. Robert, thank you for sharing your insights and experiences.

      Delete
    5. I did not have the chance to see this post until recenlty and wish to have some comment about "Or have Computer Science undergrads who know SQL and have been part of IBM mainframe training programs in the universities do the programming during the summer or as interns? An excellent way for both sides to "test drive" and for shops to more effectively recruit.

      Maybe the "more senior mainframe application developers" should focus their time understanding the user and business requirements and maintaining the legacy systems." I had seen this kind of approach which there were only negative outcomes. College grads demanded high salary where they do not have the work and business experience while the existing employees feel being mistreated for just doing the "support of existing application" instead of having the chance to advance their technical knowledge and skill. The college grads would then leave the positions to obtain a higher salary offer else where afte they gained the work experience. I must say this idea is very bad base on my past experience. Simply put "Show your existing employees a career path. This is the cheapest way for companies/organizations to reward the existing employees for they have been there through the thick and thin. Also, existing employees can do a much better job than the new hire for they already have the business knowledge and understand the company's culture"

      Delete
    6. I very much agree with your point about organizations providing a career path for IT professionals that enables advancement without requiring a move to management. In fact, that was the topic of my first-ever blog entry, written shortly after I started a DB2 consulting business back in 2007 (I rejoined IBM in 2010). The URL for that long-ago blog entry is http://catterallconsulting.blogspot.com/2007/08/wanted-better-technical-professional.html.

      Your comment raises an interesting possibility: suppose there were a "middle way?" Could good results come from having young SQL experts team with veteran application developers? The new guys could transfer skill in modern application development techniques (such as developing DB2 stored procedures and user-defined functions in SQL PL), while the veteran IT people could pass on their knowledge of the organization's business operations, as well as helpful information on getting things done in the organization's application development environment -- lessons learned through years of navigating through processes and getting to know key players, in IT and on the business side of things. Who knows? Perhaps such arrangements would boost morale and esprit de corps, in addition to lifting programmer productivity. The long-serving developers might get a kick out of working with the high-energy newbies, and the new folks might feel more "at home" with their new employer, thanks to the camaraderie provided through working with people who "know the ropes."

      Anyway, something to think about.

      Robert

      Delete
  2. Robert, in order for SQL PL cpu to be offloaded to a zIIP processor does it still have to be a remote DRDA call? For some reason I was thinking that all SQL PL cpu was offloaded to zIIP regardless of the call.

    ReplyDelete
    Replies
    1. Yes - a native SQL procedure has to be called by a DRDA requester in order to be zIIP-eligible. Why is that? Because a native SQL procedure, unlike an external DB2 for z/OS stored procedure, runs under the task of the program that calls the procedure (an external stored procedure runs under a TCB in a WLM-managed stored procedure address space). If the caller is a DRDA requester, its task in the z/OS system will be a preemptable SRB in the DDF address space, and the called native SQL procedure will be zIIP-eligible because it will run under that preemptable SRB.

      Robert

      Delete
  3. I think another reason distributed developers don't use procedures, (just send SQL), is because they like the control and dont have to cooridinate the process. With SQL, if a report needs an additional column, it's added to the dynamic SQL. If procedures are used, the procedure will have to be migrated by Change Management procedures, and scheduled.

    ReplyDelete
    Replies
    1. In the particular case of report generation, I often find that a traditional SQL-issuing application program is not used. Instead, reports are often generated by way of vendor-supplied report-generation software products (IBM's Cognos BI is an example), and those products generate dynamic SQL data retrieval statements based on criteria provided by users through a GUI. In such situations stored procedures generally are not applicable.

      You're point is valid. There is typically, in a DB2 for z/OS environment, a place for client-issued dynamic SQL data manipulation statements and a place for static SQL packaged in server-side stored procedures. The use of one versus the other depends on the application requirement and on the organization's application development standards.

      Robert

      Delete
  4. We access IMS databases from external cobol db2 stored procedures using ODBA interface. How do we do this in SQL/PL? Can an SQL/PL stored procedure call an external cobol stored procedure (that is running in WLM address space) that has IMS calls though ODBA interface(AERTDLI)?

    ReplyDelete
    Replies
    1. Having a native SQL procedure call an external stored procedure that accesses IMS data should not be a problem. If you try that and it doesn't work, let me know.

      Robert

      Delete
  5. Hi Robert, Does Native Stored procedures support Multi row fetch also , which is most power full feature of DB2 Z/OS?

    ReplyDelete
    Replies
    1. I am not aware of any restrictions concerning multi-row FETCH in a DB2 for z/OS native SQL procedure context.

      Robert

      Delete