whereby 

 is considered to be a 

single 

statement even if a set of 

statements is grouped between 

begin 

and 

end.

 Unlike the usual parameter style, 

SQL stored procedures use a mode name type triplet for argument declaration, 

where 

mode 

can be:

IN: Input value is not changed by procedural code

OUT: Procedural code modifies argument but is not required as input 

parameter. In the CALL statement OUT parameters need to be supplied as 

parameter markers.

INOUT: Value is supplied by the caller, returned to the caller and may be 

modified in between

In 7.2.6,  Condition handling in DB2  on page 213,

you can find additional code 

snippets of stored procedures dealing specifically with condition handling.

Stored procedure considerations

While stored procedures are often implemented to achieve performance 

improvements and often yield such benefits, it is not necessarily guaranteed that 

your queries run faster after moving them to a stored procedure. Improvements 

are not as easily achieved as moving a statement from the application to a stored 

procedure. 

Indeed, there are circumstances under which the use of stored procedures can 

actually cause performance to degrade. For example, if you create a stored 

procedure that simply issues one INSERT statement and calls this procedure 

from a remote application, network traffic will not be reduced. Your application 

still has to make a network call to invoke the procedure, just as it would to issue 

an INSERT statement. 

Furthermore, the DBMS may need to load the procedure and incur inter process 

communications overhead to execute it. Thus, your application may actually run 

slower by using such a stored procedure. 

If you are planning on creating new stored procedures to support, or if you are 

trying to tune existing stored procedures, you should be aware that language 

issues and creation options can significantly influence your results. For example, 

in some DBMS products, procedures written in Java may perform more poorly 

than an equivalent procedure written in C or SQL. In addition, procedures that 

run in a separate address space from the DBMS ( fenced  procedures) perform 

more poorly than procedures that run in the same address space as the DBMS 

( unfenced  procedures).

 Chapter 10. Advanced DB2 UDB features 

323