What Are Your Plans for DB2 for z/OS Stored Procedures?
If your organization runs DB2 on a mainframe server, are you using stored procedures? If so, good - plan on using them more extensively. If you're not using DB2 stored procedures, get moving with respect to putting an implementation plan in place.
As you've probably surmised, I'm very bullish on DB2 for z/OS stored procedures. I believe that they are the future of server-side SQL on the mainframe platform. I'll get to why in a moment. First, I'd like to address a question to those of you employed by DB2 for z/OS-using organizations that have either not started using stored procedures or are utilizing them only in a very limited way: what is holding you back?
Are you concerned that stored procedures - particularly when invoked by off-mainframe client programs through DB2's Distributed Data Facility (an application model that I favor) - simply can't handle a high-volume transactional workload? In fact, they can. There are organizations out there right now that are driving more than 1000 transactions per second using stored procedures and DDF. I'm currently working with a company that has a production DB2 DDF workload (much of it involving stored procedures) that regularly peaks at over 700 transactions per second, and they are nowhere near done with respect to bringing more application work of this kind online. This should not come as a surprise to anyone. DB2 for z/OS stored procedure programs, after all, can be written in a variety of languages, including tried-and-true (and CPU-efficient) COBOL. Execution of these programs can be spread across multiple stored procedure address spaces managed by Workload Manager (aka WLM, a component of the z/OS operating system). Not only that, but WLM in goal mode can dynamically fire up additional instances of a given stored procedure address space to better handle swings in application volume.
Are you concerned that DDF means dynamic SQL, especially when you're talking about database access interfaces such as Java Database Connectivity (JDBC) and ADO.NET that are frequently used with programs written for UNIX-, Linux-, and Windows-based application servers? Worry not. Yes, a stored procedure call issued via one of these database access interfaces will end up being processed as a dynamic SQL statement on the DB2 for z/OS system, but that's just the call. Static SQL embedded in a mainframe server-side stored procedure program is still static SQL, even if the associated stored procedure program is invoked dynamically. DB2 stored procedures, therefor, enable an organization to leverage the benefits of static SQL with respect to performance (pre-optimized SQL) and security (application authorization IDs require only the EXECUTE privilege on stored procedures and related packages, as opposed to direct table access privileges), while enabling off-mainframe client-side programmers to code in ways that come naturally to them.
Worried that you won't be able to monitor your DB2 for z/OS environment as you can the other parts of your mainframe system? Don't be. There are multiple products available from several vendors of DB2-related tools that can provide you with detailed information about your stored procedure workload. Stored procedures are definitely not some kind of black-box technology.
I mention these concerns sometimes on the minds of mainframe systems people because in my experience, programmers - whether mainframers themselves or distributed systems people - are already sold on the benefits of DB2 stored procedure usage. Mainframe-side developers like to be able to leverage their COBOL (or C or whatever) and SQL skills for projects that involve a lot of off-mainframe code written in Java (or C# or whatever). The off-mainframe coders like being able to use a familiar syntax when accessing DB2 stored procedures and associated query result sets (use of stored procedures is an established best practice in relational database environments that run on Linux, UNIX, and Windows servers).
What about the future of DB2 stored procedures? Lookin' brighter all the time, baby. I'm particularly pumped about what IBM is doing with respect to SQL procedures. These are stored procedures for which the program source is contained within the CREATE PROCEDURE statement (this versus a CREATE PROCEDURE statement that references an external-to-DB2 load module). Yeah, they're easy to develop, but to me the appeal was somewhat limited because, under the covers, DB2 would end up generating a C language program to be the SQL procedure executable, and the thing ended up running as a traditional external stored procedure does. That all changes with DB2 for z/OS V9 in New Function Mode: a SQL procedure executable will not be a C language program. Instead, the executable code will be stored in the DB2 directory (conceptually similar to the storage of DB2 package sections in the directory), and it will execute in the DB2 Database Services address space. That's cool enough (no need for a C compiler), but here's what has me really jazzed: these SQL procedures, when invoked via DDF (i.e., by off-mainframe client programs), will be able to use the processing power of a zIIP engine. zIIPs, as you may well know, are zSeries processors that can do real work (especially when enclave SRBs - a certain type of z/OS control block representing a dispatchable piece of work - are involved, as they are in the case of DDF threads), with the bonus being that they don't figure into the pricing of mainframe software. Boom! Instant TCO (total cost of ownership) benefit for mainframe-using organizations.
Great, huh? Are you as excited about this technology as I? Am I just over-caffeinated (I am enjoying a good-sized cup with my Wi-Fi)? Just check it out, OK? And if you're already realizing the advantages of DB2 stored procedures, tell some other folks about it. As we in the American South might say, give stored procedures a call, y'all.
As you've probably surmised, I'm very bullish on DB2 for z/OS stored procedures. I believe that they are the future of server-side SQL on the mainframe platform. I'll get to why in a moment. First, I'd like to address a question to those of you employed by DB2 for z/OS-using organizations that have either not started using stored procedures or are utilizing them only in a very limited way: what is holding you back?
Are you concerned that stored procedures - particularly when invoked by off-mainframe client programs through DB2's Distributed Data Facility (an application model that I favor) - simply can't handle a high-volume transactional workload? In fact, they can. There are organizations out there right now that are driving more than 1000 transactions per second using stored procedures and DDF. I'm currently working with a company that has a production DB2 DDF workload (much of it involving stored procedures) that regularly peaks at over 700 transactions per second, and they are nowhere near done with respect to bringing more application work of this kind online. This should not come as a surprise to anyone. DB2 for z/OS stored procedure programs, after all, can be written in a variety of languages, including tried-and-true (and CPU-efficient) COBOL. Execution of these programs can be spread across multiple stored procedure address spaces managed by Workload Manager (aka WLM, a component of the z/OS operating system). Not only that, but WLM in goal mode can dynamically fire up additional instances of a given stored procedure address space to better handle swings in application volume.
Are you concerned that DDF means dynamic SQL, especially when you're talking about database access interfaces such as Java Database Connectivity (JDBC) and ADO.NET that are frequently used with programs written for UNIX-, Linux-, and Windows-based application servers? Worry not. Yes, a stored procedure call issued via one of these database access interfaces will end up being processed as a dynamic SQL statement on the DB2 for z/OS system, but that's just the call. Static SQL embedded in a mainframe server-side stored procedure program is still static SQL, even if the associated stored procedure program is invoked dynamically. DB2 stored procedures, therefor, enable an organization to leverage the benefits of static SQL with respect to performance (pre-optimized SQL) and security (application authorization IDs require only the EXECUTE privilege on stored procedures and related packages, as opposed to direct table access privileges), while enabling off-mainframe client-side programmers to code in ways that come naturally to them.
Worried that you won't be able to monitor your DB2 for z/OS environment as you can the other parts of your mainframe system? Don't be. There are multiple products available from several vendors of DB2-related tools that can provide you with detailed information about your stored procedure workload. Stored procedures are definitely not some kind of black-box technology.
I mention these concerns sometimes on the minds of mainframe systems people because in my experience, programmers - whether mainframers themselves or distributed systems people - are already sold on the benefits of DB2 stored procedure usage. Mainframe-side developers like to be able to leverage their COBOL (or C or whatever) and SQL skills for projects that involve a lot of off-mainframe code written in Java (or C# or whatever). The off-mainframe coders like being able to use a familiar syntax when accessing DB2 stored procedures and associated query result sets (use of stored procedures is an established best practice in relational database environments that run on Linux, UNIX, and Windows servers).
What about the future of DB2 stored procedures? Lookin' brighter all the time, baby. I'm particularly pumped about what IBM is doing with respect to SQL procedures. These are stored procedures for which the program source is contained within the CREATE PROCEDURE statement (this versus a CREATE PROCEDURE statement that references an external-to-DB2 load module). Yeah, they're easy to develop, but to me the appeal was somewhat limited because, under the covers, DB2 would end up generating a C language program to be the SQL procedure executable, and the thing ended up running as a traditional external stored procedure does. That all changes with DB2 for z/OS V9 in New Function Mode: a SQL procedure executable will not be a C language program. Instead, the executable code will be stored in the DB2 directory (conceptually similar to the storage of DB2 package sections in the directory), and it will execute in the DB2 Database Services address space. That's cool enough (no need for a C compiler), but here's what has me really jazzed: these SQL procedures, when invoked via DDF (i.e., by off-mainframe client programs), will be able to use the processing power of a zIIP engine. zIIPs, as you may well know, are zSeries processors that can do real work (especially when enclave SRBs - a certain type of z/OS control block representing a dispatchable piece of work - are involved, as they are in the case of DDF threads), with the bonus being that they don't figure into the pricing of mainframe software. Boom! Instant TCO (total cost of ownership) benefit for mainframe-using organizations.
Great, huh? Are you as excited about this technology as I? Am I just over-caffeinated (I am enjoying a good-sized cup with my Wi-Fi)? Just check it out, OK? And if you're already realizing the advantages of DB2 stored procedures, tell some other folks about it. As we in the American South might say, give stored procedures a call, y'all.
posted by Robert Catterall at
7:04 PM
8 Comments:
For applications which have been already deployed in a Dynamic SQL via JDBC environment, and where it's not feasible to rewrite the application, using Stored Procedures, which of the following will best address DB2 cost issues associated with distributed, dynamic, JDBC applications?
1) moving Websphere application to mainframe platform (so that database calls are not roundtripping over the network, and are local)?
2) employing zIIP processor to offload some DB2 work from main CPU?
3) optimizing dynamic SQL, through prepared statments, reducing size of result sets, optimized queries, etc.
4) specific DB2 v8 or v9 features to be implemented, specifically addressing dynamic SQL cost?
5) other?
thank you for you input on this question...
Excellent question. My comments:
1) I doubt that this would have much of an impact on the mainframe DB2 cost of the application. Once an SQL statement (dynamic or not) gets to the DB2 for z/OS Database Services address space, it's going to be executed the same way regardless of the point of origin (local app or off-mainframe app). Yes, going local would reduce mainframe DDF costs, but that reduction would be offset by the increase in mainframe resource utilization caused by moving the SQL-issuing programs to the mainframe. Similarly, going local with the app should not have much of an impact on response time, as long as you're using the DB2 DDF block fetch capability when multi-row result sets are returned to off-mainframe programs.
2) Use of a zIIP processor should indeed improve the price/performance profile with regard to the mainframe DB2 server. As I recall, a zIIP can run some work associated with enclave SRBs (versus TCBs). DDF threads use enclave SRBs. I believe that the zIIP benefit might be most applicable to the CPU resource consumed in sending result sets to off-mainframe requesters.
3) One way to take a bite out of dynamic SQL statement preparation costs is to use the dynamic statement caching capability of DB2 for z/OS. Dynamic statement caching is of greatest benefit when the SQL statements sent in from the requesting program use parameter markers versus having literal values coded in predicates. Also, for query optimization, check out IBM's new Data Studio product (http://www-306.ibm.com/software/data/studio/).The developer piece of data studio can help you analyze SQL statements to perhaps change some poor performers for the better. The pureQuery Runtime part of Data Studio could improve the performance of your dynamic Java query workload.
4) All that comes immediately to mind is the virtual storage constraint relief provided by DB2 for z/OS Versions 8 and 9. Extensive use of DB2 dynamic statement caching can chew up a lot of EDM pool space, and it's nice to not have to be so concerned about the 2GB level within a z/OS address space.
5) That's about all that comes immediately to mind. You know, as a shift towards server-side SQL that wouldn't require coding call statements in the Java programs, consider using the aforementioned Data Studio product to enable mainframe queries and stored procedures to be exposed as Web services. The Java coders might like that programming model.
We have been trying to use WLM managed stored procedures for one of our taxing applications. The tax engine itself is a C++ module on the mainframe and we issue calls to the engine in our stored procedure. The engine logs certain information to vsam files and would it be possible for multiple concurrent runs of the stored procedure writing data to the same vsam files. I understand WLM starts new address spaces as and when required and if there are multiple address spaces running concurrently, can they log to this same vsam file?
Good question. I don't know that overlapping units of work can have concurrent read/write access to the same VSAM file. They might be serialized, and depending on your transaction volume that could be a problem. One thought would be to have the stored procedures place their information on a WebSphere MQ (often just referred to as "MQ") queue instead of the VSAM file. At the other end of the MQ queue (logically speaking), a process could read the messages from the queue and put the information into the VSAM log file. One nice thing about this approach is that during times of very heavy stored procedure execution volume, messages would just temporarily build up on the queue if the VSAM log-insert function couldn't keep up with the stored procedures. Also, if the VSAM log file were unavailable for some reason, again the stored procedures could do their MQ PUTs and be done, as opposed top failing because of the log file being unavailable.
can sql stored procedure run in application address space intead of running in seperate WLM address space.
Sorry about the delayed response.
If you are wanting to know whether or not a DB2 SQL stored procedure can run in a z/OS address space such as a CICS application-owning region or a batch initiator, the answer is no. A SQL stored procedure can run in a WLM address space or (and this is new with DB2 for z/OS V9 native SQL procedures) in the DB2 database services address space. Native SQL procedures provide more functionality and (typically) better performance versus external SQL procedures (i.e., the kind that run in a WLM address space).
Hello Robert,
what would you say about using MQ from stored procedures, triggers.... have you ever tried it? do you have any kind of a people-friendly documentation about it?
I'm a big fan of MQ usage in a DB2 environment. MQ and DB2 work very well together, and the use of MQ to increase the level of asynchronous activity (and decrease the level of synchronous activity) in an application system can enhance the system's robustness and even its performance from an end-user's perspective. [I wrote an article on DB2 and MQ for IBM Database Magazine, and you can view it at http://www.ibmdatabasemag.com/db_area/archives/2002/q3/db2dba.shtml. Though I wrote the article almost 7 years ago, most of the information is still relevant. I also wrote about DB2 and MQ from an SOA perspective in a more recent article in that magazine: http://www.ibmdatabasemag.com/showArticle.jhtml?articleID=207801123 - check the part on "Asynchronous Processing" under the heading "SOA Performance.").
A few things to note about DB2 and MQ:
1) DB2, through the Recoverable Resource Services Attach Facility (used for DB2 for z/OS stored procedures), provides coordinated commit and rollback for stored procedure programs that make MQ and DB2 changes (e.g., update a row in a DB2 table and put a message on an MQ queue) in a single unit of work.
2) MQ Listener functionality can enable the automatic calling of a DB2 stored procedure in response to a message arriving on a queue.
3) DB2 provides multiple built-in functions that facilitate interaction with MQ using the SQL interface.
As for triggers, using an MQ queue can be beneficial if the triggered action is somewhat involved (i.e., more than a simple action), because without the queue in between the firing trigger and the complex action, the SQL statement causing the trigger to fire will have to wait on the triggered action to complete. If the trigger just places a message on a queue, the downstream processing (accomplished, perhaps, by a stored procedure that gets the message from the queue) becomes asynchronous with respect to the trigger, and the triggering SQL statement can complete more quickly.
Some good references for you:
1) The IBM "red book" titled "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond," available at http://www.redbooks.ibm.com/abstracts/sg247604.html?Open. Lots of MQ-related stuff, for example, section 2.2, which takes you through the processing flow of a stored procedure that interacts with MQ.
2) The DB2 for z/OS (if that is your platform of interest - MQ can of course also be used in tandem with DB2 for Linux, UNIX, and Windows) Application Programming and SQL Guide (the DB2 for z/OS V9 "bookshelf" is at http://www-01.ibm.com/support/docview.wss?rs=64&uid=swg27011656, and note that you can go from this page to the bookshelves for prior releases of DB2 for z/OS).
3) The DB2 for z/OS SQL Reference, for information on the built-in functions that can be used to access MQ queues (available in the bookshelf cited in item 2 above).
A tip for searching electronic copies of these manuals for MQ info: do a case-sensitive search for MQ that does NOT "search for whole words only" (this because MQ is sometimes referred to only as MQ, and other times as WebSphere/MQ, and other times as MQ series).
Hope this helps.
Post a Comment
Subscribe to Post Comments [Atom]
<< Home