Tips for developing DB2 stored procedures

 

Tips on creating stored procedures

The following techniques will help you to develop more efficient and useful stored procedures.

• Pass all input data and parameters from the client application to the stored procedure when you invoke. You can also pass table names as input data, which can provide additional parameter input values.

• Return all result data and output parameters to the client application only when the stored procedure is complete. Example: A client application might accumulate a collection of database updates and pass them to a stored procedure to be applied as a batch, with a return code that indicates whether the entire batch was applied successfully or rolled back due to a failure.

• Define input and output parameters in both the client application and the stored procedure. The stored procedure must expect the passed parameters. A stored procedure is not required to receive input parameters or return output parameters. Also, a stored procedure is not required to return a result set, and might return one or more result sets.

See Authorities for DB2 stored procedures and user-defined functions for a list of DB2 privileges and authorities that are required to create and work with stored procedures. See the DB2 Application Development Guide, SQL Reference, and other DB2 documentation for additional restrictions and information about developing stored procedures.

 

Parent topic

DB2 stored procedures

 

Related concepts

Overview of DB2 stored procedures tasks

 

Related tasks

Creating DB2 stored procedures
Preparing to debug a DB2 SQL stored procedure

Related reference
Stored procedure task restrictions
Stored procedure and user-defined function support by DB2 Universal Database server type