© 2003 International Business Machines Corporation. All rights reserved.

You can also read Peggy's answers to questions that came in from DB2DD readers.

DB2DD: Peggy, what would you say is the most common question that you hear when talking with system architects designing for stored procedures?

Peggy: One question that seems to be very common is whether stored procedures should be invoked locally as common modules, like I/O routines. I'd say be very careful of the performance implications of doing this. Stored procedures were really designed to save network transmission time for logic and for multiple SQL statements that are invoked from a remote client. This isn't to say that it's never a good idea to invoke stored procedures locally, just be aware that there are performance implications that you need to consider when determining whether to use stored procedures in a non-distributed application to obtain the code reuse benefits.

DB2DD: You know what the next question is. Can you expand a bit on what type of performance implications we can expect from a stored procedure invocation?

Peggy: On z/OS, each execution of a stored procedure will be around 30K instructions. This still means the potential for multiple thousands of stored procedure invocations per second, so this is not a show stopper for all applications, it's just something to be considered carefully for your most performance-sensitive non-distributed applications. For distributed applications accessing DB2 for z/OS, I strongly recommend using stored procedures to reduce network transmissions and to obtain the benefits of static SQL.

DB2DD: OK, so if I don't want to make a stored procedure call but I do want to use the same z/OS code for local and remote invokers, what are my options?

Peggy: Some installations link-edit the code multiple times so that it can be invoked in the WLM-SPAS [the WLM-managed stored procedure address space] using the RRS attach DSNRLI code, and also locally under batch using DSNELI.

DB2DD: I know that it is possible nest stored procedures and user-defined functions, starting with DB2 for OS/390 V6, but are there any drawbacks to doing so?

Peggy: You should realize that each invocation of a stored procedure or UDF requires a service task in the WLM-SPAS, and must go through queuing and scheduling code. So the performance overhead for an application coded to use a nested stored procedure call is double that of using a single-level call. Also, if you see any hang situations, especially after communication failures or cancellations of invoking threads, you should make sure to be current on maintenance (PQ67764 and PQ63548) as we did find some problems in 'un-nesting' during termination.

DB2DD: There have been some new stored procedures languages introduced recently in DB2 for OS/390 and z/OS. Does this mean that using COBOL is being discouraged ?

Peggy: No, by no means! From what I can tell, most stored procedures on z/OS are still coded in COBOL. The new languages, SQL and Java, were introduced to meet the needs of folks who do not have COBOL skills and to provide compatibility with DB2 on the other platforms.

DB2DD: Well, that sounds good, but what is being done to ensure compatibility of SQL and Java stored procedures on the DB2 platforms?

Peggy: We have a team dedicated to ensuring that our language syntax for SQL procedures is consistent, and there have been many enhancements for both V7 and V8 in this area. Java stored procedures on the z/OS and Linux, UNIX®, and Windows® platforms will all be using the same DB2 Java driver , what we call the "Java combined client" code, which means the driver names, datasource support and SQLJ support are exactly the same, so there is no longer a need to manage the customized SQLJ profiles. (This Java combined client is available on the DB2 V8 for Linux, UNIX, and Windows and is currently in beta on the z/OS and OS/390 platform.) We also simplify re-deployment of SQL and Java stored procedures among different platforms using our GUI stored procedure builder tool which can be used to cut and paste stored procedures to heterogeneous servers. In V8, this tool is called the DB2 Development Center.

DB2DD: Speaking of SQL procedures, have there been any other changes to make them easier to build and use on z/OS?

Peggy: Yes there have been several enhancements. You can now specify what external name (load module) is to be created, and we also added the ability to return uncaught SQL errors on the SQL call statement. For more information on these, see APARs PQ58235 and PQ56323.

DB2DD: So SQL procedures have been enhanced to return errors to the SQL CALL statement; is there any way I can do this for COBOL or other native languages like C or PL/I?

Peggy: We recommend defining your stored procedures to be PARAMETER STYLE DB2SQL. This causes some extra parameters to be sent to the stored procedure program, one of which is an output SQLSTATE. If the stored procedure program sets this, the DB2 will set the SQLCODE for the invoking call statement to a corresponding negative value. This is a good solution for stored procedures that are invoked by a trigger.

DB2DD: Let's talk about availability of the stored procedures environment on z/OS. Do you have any advice for environments that tend to get spikes in the stored procedures requests that come in to make sure that the stored procedure requests don't time out?

Peggy: We have worked closely with the z/OS Workload Manager (WLM) team and as a result have identified some changes and common mistakes. First, make sure that WLM APARs OW55360 and OW54195 are applied. Also, you need to look at what the goals are for the service class the enclaves are running in (each calling thread is assigned to a WLM enclave). If DDF is creating the enclaves then you should review the goals for the DDF subsystem. The goals of the WLM-SPAS should be at least those of the other DB2 address spaces, but remember that WLM is managing these address spaces to the goals of the transactions they are serving. So in the DDF case, it would be the goals for subsystem DDF. If no goals were specified or if the work is running in a lower importance service class, this could impact the starting of new address spaces.

DB2DD: Thanks for those tips. Have there been any other availability improvements in DB2 for OS/390 and z/OS?

Peggy: We made a change to the DB2 engine code in APAR PQ55581 that allows a stored procedure to be dropped and recreated and not invalidate the calling package. Instead, the next time the calling package is invoked, DB2 will try to autobind that section, much like it would for a table that had been dropped and recreated.

DB2DD: Is there anything coming up in Version 8 that we need to be prepared for?

Peggy: The biggest impact is going to be that we will no longer allow creation of DB2-managed stored procedures. Those that exist prior to Version 8 will continue to run, but all new stored procedures must run in a WLM-managed stored procedures address space. This is really our strategic, high-availability solution, and we feel this is the best time to start phasing out usage of the DB2-SPAS, since the required release of z/OS 1.3 makes WLM goal mode mandatory.

DB2DD: Do you have any closing thoughts for our readers?

Peggy: DB2 stored procedures are in high-volume production in many, many companies. This technology has proven to be stable, flexible and if possible, even a bigger part of e-business architectures than we anticipated when we first introduced it in Version 4. It has been really exciting to see all of the places that stored procedures are being used.

Notices

All statements regarding IBM's future direction or intent are subject to change without notice, and represent goals and objectives only.

Resources

• Information on DB2 for z/OS and OS/390 at http://www.ibm.com/software/data/db2/os390/
  
  
• Support information and FAQs at http://www.ibm.com/software/data/db2/os390/support.html
  
  
• "Java Stored Procedures, WebSphere, and DB2 on the IBM eServer zSeries" at http://www.ibm.com/developerworks/db2/library/techarticle/0206baylor/0206baylor.html
  
  
• "DB2 Development Center - The Next-Generation AD Tooling for DB2" at http://www.ibm.com/developerworks/db2/library/techarticle/0207alazzawe/0207alazzawe.html
  
  

About the author

Peggy Abelite is the lead developer and recognized industry expert on IBM DB2 for z/OS and OS/390 and stored procedures. In addition to her development work, she works closely with customers and business partners to resolve problems and questions related to stored procedures, UDF execution, network computing, and other Java topics. Peggy speaks frequently at user conferences around the world.

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers