Q and A

Asked and Answered

Q. I want to use stored procedures as the communication mechanism to a mainframe, but I have no experience with them. So we have a lot of questions about the best way to implement stored procedures. For example, what should the logical unit of work be? Our front-end is processing bills of material containing hundreds of items that we need to associate together. What are the performance considerations of a longer-running stored procedure? Can a stored procedure do COMMITs and loops?

Robert Catterall responds:

You can choose not to issue an explicit COMMIT from a stored procedure program — you either have an implicit COMMIT when the stored procedure completes (if the stored procedure was defined with COMMIT ON RETURN YES specified), or you have the calling program issue the COMMIT. In either case, the implications for a long-running stored procedure with no COMMITs are the same as they are for other DB2-accessing programs that run for a long time without committing (assuming the stored procedure would access DB2 data — a stored procedure doesn't have to). X-locks are held for a long time (not good for concurrency), and claims are held for a long time (not good even for a read-only stored procedure, because unreleased claims on DB2 database objects can interfere with the successful execution of DB2 utilities such as online REORG).

If a business unit of work is going to take a long time to process, the application accomplishing the work should commit relatively frequently. You could, therefore, have a long-running stored procedure program issue periodic COMMITs (and, yes, a stored procedure program can contain a "do loop"). Note, however, that this option isn't universally available when stored procedures are used. The DB2 for z/OS V7 SQL Reference mentions that "the COMMIT statement cannot be used in a stored procedure if the procedure is in the calling chain of a user-defined function or a trigger or if the caller is using a two-phase commit." I think this means that a stored procedure called by an IMS or CICS transaction program can't issue an explicit COMMIT. If the calling program is a batch program linked with the TSO attach or the Call Attach Facility (CAF), a COMMIT in a stored procedure is probably okay. It's probably okay, too, if the CALL comes through the DB2 DDF from a non-mainframe client, assuming that two-phase commit isn't being used in that situation.

If you don't want to go with a long-running "do loop" and explicit, periodically-issued COMMITs in your stored procedure program (or if your environment is such that an explicit COMMIT isn't allowed in your stored procedure program), you can go with lots of calls to the stored procedure: Call it the first time, and have it process 10 items (for example) in the bill of materials, then call it again for the next 10, and so on until the work is done. In this case, you could either have the calling program issue COMMITs between stored procedure calls, or you can have the COMMITs issued implicitly via COMMIT ON RETURN YES in the definition of the stored procedure. Of course, in that case, you obviously need to have logic in the calling program that can keep track of where you are in processing the bill of materials (so that you can restart in the right place if a failure occurs before the work is all done), or you need to code the application so that the pieces of work done through the repeated stored procedure calls are idempotent (in other words, in case of a failure in the middle of processing a bill of materials, you simply start again at the beginning, and no errors occur in retrying data-changing operations that had already been committed, because your application can tolerate this situation).

Suppose both alternatives are available (long-running stored procedure with explicit COMMITs issued periodically, or many calls to a stored procedure with a small chunk of the work done with each call)? In that case, the performance edge would probably go to the COMMIT-issuing stored procedure, because that approach would avoid the work done by DB2 every time you issue a stored procedure CALL. (That delta would be bigger, of course, if the long-running stored procedure were to run for quite a long time and if the little-chunk stored procedures were to be called lots of times.)

See a complete archive of reader/author Q&As .