Summary

This chapter has shown some techniques for handling exceptions and completion conditions within DB2 SQL procedures. Basic definitions of SQLCODE and SQLSTATE were provided, followed by a description of how to use them in conjunction with condition handlers. Different types of handlers can be declarednamely EXIT, CONTINUE, and UNDO handlers (the UNDO handler is not supported in DB2 UDB for zSeries). The scope of a condition handler is the compound statement in which the handler is declared.

The concept of naming specific conditions was also described. In addition to making the stored procedure code more readable, the ability to name conditions is useful for specifying user-defined conditions. Additionally, the SIGNAL and RESIGNAL statements were discussed to show how you could force a condition in the SQL procedure body by specifying an SQLSTATE or a named condition.

Finally, passing parameters back to calling procedures and retrieving return values from called procedures were considered. The RETURN statement can be used for returning values, and the GET DIAGNOSTICS statement is used to receive the returned value. The GET DIAGNOSTICS statements in DB2 UDB for iSeries and zSeries are more comprehensive, which allows you to obtain information about a statement, a connection, and a condition.

If non-integer or multiple values need to be passed back to a calling application, OUT parameters can always be used.