DB2 for z/OS and SNA: Breaking the Ties that (Used to) Bind

I don't know if there are any industries in which things change as quickly (and constantly) as they do in the realm of information technology. Just a few years ago, clouds were what produced rain, streams were watercourses, and Ruby on Rails might have been a reference to your aunt taking the express train to New York City. Today, those are all IT terms. It can be kind of dizzying, keeping up with all this new stuff.

Fortunately (especially for us old-timers), even in IT there are those technology rocks that endure, providing a sense of stability amidst the swirl of change. Magnetic disk drives are still the dominant data storage media. Various server operating systems -- z/OS (descendant of MVS), UNIX variants, Linux, and Windows -- have stood the test of time. Relational database management systems are still very much mainstream, as challengers that have arisen over the years have faded away or become niche technologies. And then there's SNA, right? Good old Systems Network Architecture, that robust, feature-rich communications protocol that reliably gets data to and from mainframes. We'll always have SNA, won't we? Well, maybe not...

I remember when TCP/IP, the now-pervasive data communications protocol, was first getting a lot of attention in z/OS circles. It was the 1990s. Client-server computing was on the rise, and lots of organizations wanted their mainframes to be the servers on client-server configurations. With TCP/IP being the lingua franca of PCs and distributed systems servers, z/OS had to be able to support this protocol, and in time it did. Still, SNA remained heavily in use in mainframe environments, owing largely to the fact that TCP/IP, at the time, lacked some of the high-availability and diagnostic features of SNA -- features that were held in particularly high regard by the folks responsible for operating data centers that housed mainframe servers. Years passed, and TCP/IP became a much more robust communications protocol, to the extent that organizations could utilize TCP/IP instead of SNA without having to compromise on network robustness and availability. Thus, use of SNA, even in mainframe environments, steadily declined. But you still need SNA, and VTAM (the mainframe communications subsystem that enables the use of SNA), for some aspects of DB2 for z/OS processing, right?

WRONG. Starting with DB2 9 for z/OS running in New Function Mode (NFM), you can actually cut the cord between DB2 and VTAM (and SNA), and go 100% TCP/IP. Technically, this is accomplished by modifying the DDF record in the bootstrap data set (BSDS). How do you do this? The way you make any changes to the BSDS: via the change log inventory utility, also known as DSNJU003 (given what you can do with DSNJU003, it would be nice if it were rechristened as the "change BSDS" utility). Specifically, you submit a DSNJU003 job with a DDF statement. In that DDF statement, specify an IPNAME for the DB2 subsystem (or for the data sharing group, if the DB2 subsystem is a member of a group). IMPORTANT: do NOT add an IPNAME to the DDF record in the BSDS if you need your DB2 subsystem to process DRDA requests coming in over both TCP/IP and SNA. If you need to support both TCP/IP and SNA for DRDA requests, have an LUNAME in the DDF record in the BSDS and ensure that there is NOT an IPNAME in that record. Once the DDF record contains an IPNAME value, DB2 will not even try to activate its SNA communications support, even if some remote client sends a DRDA request to the subsystem using SNA. So, don't add an IPNAME to the DDF record in the BSDS until you are sure that you no longer need SNA support for a DB2 subsystem. Note that in a data sharing group, you could have one or more members supporting only TCP/IP communications, while other members support both TCP/IP and SNA. In that case, the IPNAME in the BSDS DDF record of the TCP/IP-only members must match the GENERIC LUNAME used by the members that are supporting both TCP/IP and SNA communications.

Once you have supplied an IPNAME for a subsystem in its BSDS DDF record (i.e., once you go to TCP/IP-only communications for DRDA requests), you can subsequently execute the DSNJU003 utility with the NOLUNAME option in the DDF statement. This will remove the LUNAME value from the DDF record in the BSDS (if you run DSNJU003 with NOLUNAME, no other DDF parameter specification is allowed -- NOLUNAME has to appear by itself in the DDF statement of the utility).

And there you have it: no LUNAME, no SNA, no association with VTAM. What'll change next? We'll see. At least the IBM logo still has those striped letters. Hold onto that, Big Blue!

An Important DB2 10 for z/OS Stored Procedures Enhancement

I've said it before, and I'll say it again: DB2 10 for z/OS is packed with new functionality. Along with the really high-profile features of the latest release of DB2 for the mainframe platform (temporal data support, enhanced CPU efficiency, row and column access control, etc.), you'll find several goodies that will likely be very useful to your organization. One of my favorites of these is the ability to declare a cursor WITH RETURN TO CLIENT. Not familiar with this feature? Read on.

The discussion of cursors in this blog entry, by the way, is relevant to cursors declared and opened in a DB2 for z/OS stored procedure (any kind -- e.g., COBOL, external SQL, native SQL). Here's the deal: for a stored procedure in a DB2 9 or DB2 V8 environment, a cursor declared with the WITH RETURN option (indicating that the cursor's result set is to be retrieved by a program other than the stored procedure) is actually declared with the specification WITH RETURN TO CALLER (the TO CALLER part is the default -- and only -- behavior allowed for a cursor declared WITH RETURN). The result set of a cursor declared with the WITH RETURN TO CALLER specification can ONLY be retrieved by the program that invokes the stored procedure in which said cursor is declared. Another way to say the same thing: the result set of a cursor declared with the WITH RETURN TO CALLER specification can only be returned "one level up." In this context, "one level up" refers to the hierarchy of a chain of stored procedure calls. In the simplest case, what I'd call a "top-level caller" -- a non-stored procedure program -- invokes a stored procedure that does not call any other stored procedures. That would be a two-level call chain: level 1 is the top-level calling program, and level 2 is the called stored procedure.

You could also have a situation in which a top-level caller (which I'll refer to as PROG_TOP) calls stored procedure A (PROC_A), which in turn calls stored procedure B (PROC_B). That's a three-level call chain. In a DB2 9 (or V8) system, a result set generated by PROC_B could ONLY be returned (in a direct sense) to PROC_A, because PROC_A is "one level up" from PROC_B.  -- not to the PROG_TOP program. What if the PROG_TOP program needed the result set generated by PROC_B? You basically had three ways of addressing that requirement:

• You could change PROG_TOP to invoke PROC_B directly. That could be a non-trivial effort.

• You could have PROC_B insert the result set rows associated with the cursor in question into a temporary table. Then, PROC_A could declare and open a WITH RETURN TO CALLER cursor targeting the temporary table, and PROG_TOP, being one level up from PROC_A, could fetch the result set rows via the cursor declared and opened in PROC_A (by the way, created temporary tables, as opposed to declared temporary tables, tend to be recommended for this purpose).

• As noted above, you could have PROC_B insert the result set rows associated with the cursor in question into a temporary table. You could then modify the PROG_TOP program to directly access the rows in the temporary table via a cursor declared and opened in PROG_TOP.

All do-able, but wouldn't it be nice if the result set generated by PROC_B's cursor could be directly returned two levels up to the PROG_TOP program (or three levels up if PROG_TOP calls PROC_A which then calls PROC_A2 which then calls PROC_B, or four levels up if PROG_TOP calls PROC_A which then calls PROC_A1 which then calls PROC_A2 which then calls PROC_B, or...)? In a DB2 10 new-function-mode system, you can do just that.

The important DB2 10 change comes in the form of the new WITH RETURN TO CLIENT option of the DECLARE CURSOR statement. When a cursor in a DB2 10 stored procedure is declared WITH RETURN TO CLIENT, a result set generated by that cursor can be directly returned to the top-level program (the program that initiated a chain of stored procedure calls) no matter how many call chain levels separate the cursor-declaring stored procedure and the top-level program (and a chain of DB2 for z/OS stored procedure calls can go 16 levels deep); furthermore, within a call chain the result set of a cursor declared WITH RETURN TO CLIENT will be invisible to stored procedures between the top-level calling program and the cursor-declaring stored procedure.

The delivery of the WITH RETURN TO CLIENT option for DECLARE CURSOR in DB2 10 for z/OS is a very good thing, for two reasons:

• It provides stored procedure developers with an architecture option they didn't have before. That means more flexibility and an easier way to deliver required application functionality.

• It brings DB2 for z/OS into conformity with DB2 for LUW, which already had the RETURN TO CLIENT option for DECLARE CURSOR. This is a big deal in my eyes, because lots of organizations run both DB2 for z/OS and DB2 for LUW, and I believe that we'll see, to an increasing degree going forward, individuals engaged in developing stored procedures for both DB2 platforms. These people's lives are made easier by cross-platform conformity within the DB2 Family (this has been an IBM DB2 development priority for some years now, and SQL differences between DB2 platforms -- referring to SQL in application programs versus the DDL that is used primarily by DBAs -- have been reduced nearly to non-existence).

So, there you have it -- another reason to add to your long list of incentives to get to DB2 10. If your organization is already running DB2 10, check out the new RETURN TO CLIENT option of DECLARE CURSOR. Good stuff.  

Great DB2 Learning Opportunities in Prague this November

Greetings, folks. I know that an unusually long time has passed since I lasted posted an entry to this blog -- my apologies for that. I was out of commission during the second half of July and into early August, dealing with a medical issue. I'm much better now, and things are getting back to normal. I'm still on medical leave (until October 3), but I plan on posting several blog entries between now and then).

This first-in-a-month post is of the For Your Information variety. Many of you may already know that the International DB2 Users Group (IDUG) will be holding its 2011 DB2 Tech Conference EMEA (Europe/Middle East/Africa) on November 13-18 Prague, Czech Republic (one of my all-time favorite cities: modern conference and hotel facilities convenient to the subway, which can take you in minutes to the historic -- and large and beautiful -- city center). Attend the conference, and you'll learn a ton about DB2, whether you're new to the DBMS or you've been working with it for 20 years. In this blog entry, I want to let you know about two opportunities to pick up even more DB2 knowledge during IDUG EMEA week:

The one-day seminars offered on November 18

Most of the sessions comprising the agenda of the 2011 IDUG DB2 Tech Conference EMEA are 60 minutes in duration -- enough time for a high-level overview of a broad topic or an in-depth drill-down into a specific DB2 feature or function. Want to have breadth together with depth? If so, the one-day seminars are for you. This year's one-day seminar line-up, like those of previous IDUG EMEA conferences, features a who's who of top-shelf DB2 experts known for both their great technical expertise and their outstanding teaching skills. Check it out:

• DB2 10 for z/OS -- In Depth, delivered by Phil Grainger of Cogito and Grainger Database Solutions
• DB2 Intermediate and Advanced SQL, taught by Dan Luksetich of Yevich Lawson & Associates
• I Didn't Know DB2 did THAT!, presented by Bonnie Baker of Bonnie Baker Corporation
• Optimizing DB2 for z/OS System Performance Using DB2 Statistics Trace, presented by John Campbell of IBM's DB2 for z/OS development organization
• Rocket Science: DB2 for Linux/Unix/Windows Performance Analysis and Tuning Workshop, taught by Scott Hayes of DBI

You can register for one of these one-day seminars when you register for the 2011 DB2 Tech Conference EMEA (online registration is available). Seminar sign-up involves a fee that is in addition to the conference registration fee, but I will tell you that you won't find a better value in terms of in-depth DB2 technical knowledge gained relative to euros spent.


A free DB2 10 for z/OS Migration Planning Workshop on November 13

IBM will be providing a complimentary DB2 10 for z/OS Migration Planning Workshop on the Sunday of IDUG EMEA Conference week. This will be a great opportunity for you to pick up valuable information on migrating to the latest version of DB2 for z/OS, which delivers significant performance enhancements and lots of great new features (among my favorites are temporal data support, implicit data type casting, and the ability to store a time zone together with a timestamp in a DB2 table column). Register online for this seminar and enjoy learning about DB2 10 migration from IBM experts and leading DB2 consultants.


There you have it: two ways to get more DB2 knowledge in one of the world's most beautiful cities. Take advantage of these offerings and go to Prague. It will be a treat for the left and right sides of your brain.