Ask the Experts: Peggy Abelite on DB2 for z/OS and OS/390 Stored Procedures

DB2 UDB developer Peggy Abelite answers readers' questions about stored procedures on DB2 for OS/390 and z/OS.


Peggy Abelite, Lead Developer and Recognized Industry Expert , IBM, Software Group

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.

27 March 2003

© 2003 International Business Machines Corporation. All rights reserved.

Peggy Abelite's previous column on stored procedures has generated a lot of questions from our readers. Here are some of the questions Peggy has received:

Q: The SP WLM address spaces shut down after one hour of inactivity at our shop. Is there a way to have them stay up and avoid the overhead of startup when someone runs a stored procedure? And why do they do this?

A: WLM shuts down the address spaces to avoid using system resources for nothing. All but the last one for an application enviornment/service class will be shut down after around 5 minutes of inactivity, with the last one shut down after around an hour of inactivity. The expectation is that stored procedure requests that require high performance will execute often enough to keep the address space active. There is no way to override this behavior for DB2 server address spaces.

Q:: Can a call-attach program be used in lieu of writing a stored procedure? We have a need to modify a large quantity of non-DB2 programs that will all need to call a common routine for DB2 based information. We would rather not have to transform all these programs to DB2 in order to call a stored procedure?

A: Yes, a program can be written to attach to DB2 using call-attach. See the DB2 Application Programming and SQL Guide for more information.

Q: We have a routine that we call in batch and CICS® to access data in a dataspace. This dataspace is shared across regions and so is protected by enqueues and private locks. To ensure that locks and enqueues are properly cleaned up, we receive control at both normal and abnormal end of task/transaction.

We are planning to call this routine from DB2 stored procedures. Of course, trapping end of task had to be modified in this environment. Right now we are testing with OS/390 R2.8 (admittedly a bit old) with a DB2-managed stored procedure address space with DB2 6.1. We are planning to test with WLM-managed stored procedure address spaces.

Our questions:

  • Should we expect significant differences between the DB2 and WLM-managed SPASs concerning their creation and usage of TCBs?
  • Should we expect significant differences between DB2 6.1 and DB2 V7 WLM-managed SPASs?

A: For your processing, there really aren't big differences between the way TCBs are created and used between DB2-managed and WLM-managed address spaces. Tasks that terminate abornormally are replaced in both environments. And no, there is really no difference between DB2 V6 and V7 in this area. In V8, we will exploit WLM server task management, which means that server tasks may be created and detached while the address space stays up, but again, this should not affect your code.

Q: Can the RAISE_ERROR function be used in a COBOL stored procedure to return an error?

A: Yes, it can be used, but it will only raise the error on the statement in the COBOL stored procedure. It will not affect the SQLCODE for the SQL CALL statement used to invoke the stored procedure.

Q: Are triggers fired by dynamic SQL statements?

A: Yes.

Q: Are there any thoughts on supporting the SQLDA interface for the z/OS stored procedures? We want to exchange all stored procedures between platforms, and we want to utilize our current lroutines as stored procedures. Our current interface exchanges a hugh number of variables (in structures), which we can't convert to result sets, and it seems like the z/OS stored procedure interface is an data element interface and not a data structure interface. Today the CREATE PROCEDURE statement length in SPUFI prevents us from defining the current number of variables exchanged.

Also the COBOL compiler complains on the number of variables used on the PROCEDURE DIVISION statement in the stored procedure.

A: The SQL CALL statement on z/OS does support the USING DESCRIPTOR syntax for specifying parameters when invoking a stored procedure. If the SPUFI limit is not as big as the 32K limit for an SQL statement, then consider using a different method to get to the 32K statement length. The limit for an SQL statement in V8 is raised from 32K to 1MB. You may want to contact the COBOL team to see whether there is a way to increase the compiler limit.

Q: My shop is developing SQL stored procedures using the Stored Procedures Builder on a Windows NT® workstation and using DB2 Connect to "post" the SQL stored procedure on a DB2 subsystem running on OS/390.

Our problem is change management of the SQL stored procedure. Recreating the SQL stored procedure with Stored Procedure Builder that has been successfully tested in one DB2 subsystem on another DB2 subsystem is not true promotion of tested code. What is the best way to promote SQL stored procedures through a life cycle?

A: This question comes up often, and could be the subject for another complete article. Some confusion on this topic results because different companies have different requirements for promoting code. There are basically two options that I know of:

  • One is used when a recompile is required, which can happen if the source and target systems are running incompatible levels of Language Environment, or, in your situation, where you are promoting between two completely different operating systems that require different C compilers. In this case, the best method is to use the stored procedure builder / DB2 Development Center client program to copy and paste the program and rebuild it to the target system.
  • If a recompilation is not required or not allowed, then you can use similar methodology to other programs, which is to copy the load module, copy the DBRM, bind the package, and issue a CREATE PROCEDURE statement, which you can extract from the source and just execute as an SQL statement. This will not save the source or the build options on the target system, but the assumption is that that is not required because builds are never done against the production system, only the test or development system. Some of the popular change management tools are adding support for stored procedures, so if you use that type of tool for other programs, you are encouraged to contact them about SQL procedures support.

Q: When granting authority to run a stored procedure, does execute authority need to be granted to both the stored procedure object and the package associated with the procedure, or just the stored procedure object?

A: If the owner of the stored procedure has execute authority on the SQL package for the procedure, then this authority does not need to be granted to the user ID calling the stored procedure. If the owner of the stored procedure does not have execute authority on the SQL package, then execute authority on both the procedure and the SQL packakge must be granted to the user ID calling the stored procedure.

Q: Is there a limit to the size of a COBOL stored procedure? Do all COBOL stored procs need to be compiled and linked as re-entrant (RENT) and re-usable (REUSE)?

A: am not aware of any restrictions on module size, other than what is available in the address space. RENT and REUSE are not required, but these options are recommended so that a single copy of the load module can be used for multiple invocations, which will improve performance.

Q: I'm trying to introduce internal stored procedures at my shop. I'm having problems with the syntax. I would appreciate it if you would send me a sample of a simple stored procedure with language SQL.

A: Please see the IBM Redbook Cross-Platform DB2 Stored Procedures: Building and Debugging, SG24-5485-01 for examples.

Q: Where can I find detailed documentation about how to use the DB2 Stored Procedure Builder? The manuals for DB2 on distributed platforms only cover this tool at a high level.

A: Please see the IBM Redbook Cross-Platform DB2 Stored Procedures: Building and Debugging, SG24-5485-01. There are also links to information from the Stored Procedure Builder Web site. There are several articles on both the Stored Procedure Builder and the DB2 Development Center on the DB2 Developer Domain.

Q: Can I call mainframe stored procedures from SQL Server?

A: This is not my area of expertise. I do see that there is some information about accessing mainframe databases using ODBC on the Microsoft Web site. I recommend that you start there.

Q: Hi, I work for a bank. Currently our internet/intranet systems are built using MQ messages to CICS programs running on zSeries. These CICS programs then in turn have all the application logic to manage the data that is stored in DB2. That is, when we update a table we also write out a row to a history type table.My limited understanding of stored procedures suggests that the writing to the second table could easily be done using a stored procedure.Is my understanding correct? Given that we are not in a distributed environment, all our DB2 calls a made from Cobol programs running on the mainframe, are we better to stay with application logic or move to stored procedures?

A: As I mentioned in my original article (first question), I get this question a lot. I just think you need to be careful about the performance implications of doing this. See my original answer for more information. In addition, in your case, it sounds like a trigger could be used for the update to the history table. For more information on using triggers, see the SQL Reference.


developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into Information management on developerWorks

Zone=Information Management
ArticleTitle=Ask the Experts: Peggy Abelite on DB2 for z/OS and OS/390 Stored Procedures