User-defined errors are certain conditions that a developer can define as errors in an application's business logic. For instance, your company may have a business rule specifying that total compensation for an employee can't exceed that of the employee's manager. The database routine, such as a stored procedure, used to modify the compensation must check whether the new value complies with this company regulation. If the new value exceeds the limit, the routine signals an error to the calling process to make it aware of this business rule violation.
In V5R1, DB2 Universal Database for iSeries (DB2 UDB) lets you handle user-defined errors that can occur in stored procedures and other database routines so that user-specified SQL states and messages are returned to the client application.
By adopting a consistent approach for user-defined error handling for use across various stored procedure types, you can completely isolate the client application from the implementation details of the stored procedure. For example, you can rewrite an existing RPG stored procedure in SQL or Java with no implications to the client code. This technique is also much more reliable than using the output parameters
for error feedback because DB2 UDB may not pass back the output parameter values when an error (user-defined or system) is encountered during the execution of a procedure.
Here, I explain the coding techniques you can use to implement user-defined errors in different stored procedure types.
Stored Procedure Types
Generally, DB2 UDB supports two types of stored procedures: SQL stored procedures and external stored procedures.
SQL stored procedures. You write these stored procedures entirely in SQL. The DB2 UDB implementation of SQL stored procedure language is based on the ANSI/ISO Persistent Stored Module (SQL/PSM) standard specification. DB2 UDB also makes SQL/PSM available for the development of SQL user-defined functions (UDFs) and SQL triggers.
External stored procedures. You write these stored procedures in one of the iSeries-supported programming languages (e.g., C/C++, Cobol, Java, RPG). To create an external stored procedure, you must compile the source code for the host language so that a program object (or class) is created. You then use the Create Procedure SQL statement to register the stored procedure and to point to the program object that contains its implementation.
Starting with V4R5, you can also write an external stored procedure in Java. Java stored procedures have several language-specific features that you can use for effective user-error handling (more about Java stored procedures later).
Each stored procedure type offers certain programming constructs that you can use to handle user-defined errors. All code snippets discussed here use a sample database schema (shipped with OS/400 V5R1 or later) that you can create with the following SQL statement:
User-Defined Errors in SQL Stored Procedures
To help you handle user-defined errors, SQL/PSM database language supports two programming constructs: Signal and Resignal. The Signal statement signals an error or warning condition and causes an error or warning to be returned with the specified SQLState and message text. The Resignal statement resignals an exception condition; you can code it only as part of the SQL/PSM condition handler. Note that any SQLState value not reserved for use by the database manager can be used in the Signal statement.
Let's look at some practical examples. We start with an SQL stored procedure implemented in SQL/PSM. We'll use the ModSal routine in Figure 1 to modify an employee's salary. The personal data for employees (e.g., employee number, compensation details, department number) is stored in the Employee table. The Department table contains the department information, including the department manager's employee number. The rows in Employee and Department are related by the department number. The ModSal SQL stored procedure implements a business rule specifying that an employee's total compensation must not exceed that of his or her manager. The routine's logic checks whether or not the rule is compromised, and if so, the routine signals an error condition to the calling process. The Signal and Resignal statements pass the user-defined errors to the calling process. The routine accepts two parameters: employee number of type Char(6) and salary change of type Decimal(9,2).
At A in Figure 1, the error handler defined for the '38S01' SQLState signals the user-defined error condition. The Resignal statement resets the return SQLState to '38S01' and sets the diagnostic message. Once Resignal is fired, the stored procedure immediately returns the specified error to the caller in the SQL Communications Area (SQLCA).
The SQLCA is a set of variables that is updated at the end of every SQL statement's execution. The SQLCA contains, among other things, an SQLCode variable (which is set to SQL return code) and an SQLErrMc variable (which is set to the message text associated with a given SQLCode). The SQLCA is included in the SQL procedure body by the SQL precompiler.
In the case of Signal/Resignal, SQLCode is set to -438 upon return. The entire SQLErrMc element of the SQLCA area is available for the customized message. In other words, no truncation of the user-defined error message text occurs with SQL stored procedures.
At B, the SQLState '02000' is returned to the SQL stored procedure if no data exists for the employee number passed as the first parameter. This condition may be thrown by either the Fetch or searched Update statement. The error handler handles this condition by signaling SQLState '38S02' to the caller.
At C, if the business rule is compromised, SQLState '38S01' is signaled. The control is transferred to the error handler defined for this state. Note that Signal could have included the message text and been signaled directly to the invoker.
User-Defined Errors in External Stored Procedures
To be able to return a user-defined SQL state and error message, an external stored procedure must specify the SQL parameter style. DB2 UDB supports several different param-eter styles (e.g., General, General with nulls, Java, SQL). When the external stored procedure is invoked, DB2 UDB passes several parameters to the procedure in addition to those specified on the parameter list. The number of additional parameters passed depends on the parameter style, which you specify on the Create Procedure statement.
The following list shows the parameters received by an external stored procedure defined with the SQL parameter style:
INOUT Argument null indicator
[repeated - one for each argument],
IN Procedure name,
IN Specific name,
OUT Diagnostic message
The SQLState and Diagnostic message parameters are of special interest for user-defined error handling.
SQLState. The external stored procedure can set this output parameter to signal a successful execution, warning, or error condition. SQLState should be set to one of the following values:
- '00000': successful execution
- '01Hxx': Warning — the trailing xx characters can be any two digits or uppercase letters; this value results in SQLCode +462 from the SQL runtime
- '38yxx': Error condition — y is an uppercase letter between I and Z, and xx is any two digits or uppercase letters; this value results in SQLCode -443 from the SQL runtime (for consistency with external procedures, you may also want to use the '38yxx' SQLState pattern for SQL stored procedures)
Note that you can't set SQLState to the value returned to the stored procedure by the SQL runtime to pass it on to the calling client. You can set SQLState only to these three values; otherwise, the calling program receives SQLState '39001', which indicates an invalid SQLState.
Diagnostic message. You can set this output parameter to a customized error message, which is limited to 70 characters in length. In the case of an external stored procedure, the full message text probably won't be accessible to the calling process. Generally, the SQL runtime uses the SQLErrMc field of the SQLCA area to return the customized error message.
However, if you specify the SQL parameter style, the SQL runtime must use a portion of the SQLErrMc to return other information (such as the procedure's name and schema). The message text itself is placed in the SQLErrMc field as the sixth token. Because of this truncation, the message that's passed back contains no more than the first 30 characters of the user-defined error message. You can maximize the returned message length by using short procedure names. This way, more bytes in the SQLErrMc element are left for the diagnostic message.
The alternative approach is to use the user-defined SQLState value returned to the client to determine (e.g., through a table lookup) what error message end users should see. In this case, the client application performs the error-message mapping.
Now we'll create the stored procedure program object for a C version of the ModSal routine using the following CL commands:
CRTPGM PGM(DB2USER/MODSALC) ACTGRP(*CALLER)
Once you've created the program object, you must register it as a stored procedure with this SQL statement:
( IN i_empno char(6),
IN i_salary dec(9,2))
EXTERNAL NAME db2user.modsalc
modifies SQL DATA
PARAMETER STYLE SQL
Figure 2 shows the C-embedded SQL implementation of the ModSal SQL stored procedure described in the previous section. The routine accepts two parameters: employee number of type Char(6) and salary change of type Decimal(9,2).
At A, the procedure checks whether the employee number passed as the first parameter is valid. If no data is found for a given employee number, SQLState is set to '38S02', and an appropriate diagnostic message is returned to the caller.
At B, if the business rule is compromised, the stored procedure sets SQLState to '38S01' and sets the diagnostic message to indicate the reason for the error condition.
At C is a "catch-all" error handler for native SQL errors. Bear in mind that SQL exceptions not handled by the external procedure's logic are not passed back to the calling process. Upon return, SQLState will be set to '00000', which means successful completion. Therefore, the external procedure needs to take an appropriate action. In our case, we set SQLState to '38S00' to ensure that the procedure call fails with SQLCode -443. The diagnostic message returns native SQLCode and SQLState.
User-Defined Errors in Java Stored Procedures
Java allows a great deal of flexibility in defining and throwing user-defined exceptions. Specifically, you can use an SQLException object to define an exception with virtually any SQLCode and SQLState. However, upon return from a Java stored procedure, DB2 UDB runtime handles only certain return codes. To ensure the error condition is properly recognized by the database runtime and passed back to the calling process, you can set SQLCode to -438 and SQLState to a state of class '38yxx' in SQLException. For consistency, I recommend that you adopt an approach similar to the one I presented in the previous section, User-Defined Errors in SQL Stored Procedures.
To create the stored procedure class for the Java implementation of the ModSalC external stored procedure, use the following Qshell command:
You must load the compiled code into the function directory QIBM/UserData/OS400/SQLLib/Function on the iSeries. To improve Java stored procedure performance, use the CRTJVAPGM (Create Java Program) command:
CRTJVAPGM CLSF( ModSalJ.class) OPTIMIZE(40)
Once you've created the program object, you need to register the stored procedure with this SQL statement:
IN i_empno char(6),
IN i_salary dec(9,2))
EXTERNAL NAME 'ModSalJ!modSalJ'
PARAMETER STYLE JAVA
The code sample in Figure 3 shows the Java ModSalJ class. Notice that the SQLException is used to pass user-defined errors to the client.
At A, if the employee number passed as a parameter is invalid, an SQLException is thrown to signal that an error condition occurred in the stored procedure.
At B, the SQLException is thrown to signal that the business rule was compromised. SQLState is set to '38S01', and SQLCode is set to -438. Because a Java procedure is an external stored procedure, you can also set SQLCode to -443, which is the error code used by the database runtime to indicate error conditions in an external routine. In our methodology, we've taken advantage of Java's flexibility to return -438 rather than -443. Both codes are properly handled by the database runtime, but it's better to set SQLCode to -438 because that error code prevents the truncation of the user-defined error message.
The Catch block at C catches and throws all potential SQL runtime errors and user-defined errors conditions so that they are returned to the caller.
A Consistent Approach
Using the described method of handling user-defined error conditions in stored procedures promotes a consistent approach across all supported stored procedure types. The major benefit of this method is that it's transparent to client applications. Because user-defined errors are returned as regular SQL errors, no special provisions are required in the client code to handle these conditions. Furthermore, you can use this solution successfully in UDFs. In the case of external UDFs, you should use the SQL parameter style that's equivalent to the one used for external stored procedures. For more information about DB2 UDB and stored procedures, see Further Reading, at left.
Jarek Miszczyk is a senior software engineer at PartnerWorld for Developers in Rochester. He provides consulting services on DB2 UDB for iSeries-related issues to independent software vendors and IBM customers. He also writes extensively and teaches IBM classes in all areas of the iSeries database. Before joining PartnerWorld, Jarek worked for three years at the ITSO Rochester, where he was the leading author of several popular database Redbooks. You can e-mail him at Jarek@us.ibm.com.
DB2 UDB for iSeries
Online Publications for iSeries
Redbooks at http://www.redbooks.ibm.com
DB2 UDB for AS/400 Object Relational Support SG24-5409)
Stored Procedures and Triggers on DB2 Universal Database for iSeries (SG24-6503)