Every SQL application program must provide for error handling, by declaring an SQL Communications Area (or alternatively, just the SQLCODE variable, as described later in this section). This area receives messages that the database manager sends to the program. By testing certain fields of this area, you can test for certain conditions during the program's execution.
Error handling helps protect the integrity of the database when a program fails. For example, consider the two-step operation needed to transfer $500 from one account to another in a bank:
If the system or your program fails after the first statement is executed, some customer has just "lost" $500. This type of incomplete update is said to leave the database in an inconsistent state.
To avoid creating an inconsistent state, use a logical unit of work (LUW). An LUW is a group of related SQL statements, possibly with intervening host language code, that you want treated as a unit. The two steps in the previous example would make up a single LUW. SQL requests within an LUW can be made against a remote application server; such an LUW is called a remote unit of work.
LUWs prevent inconsistencies caused by system errors or SQL statement errors. For system errors, the system automatically restores all changes made during the LUW where it encountered the error. This rollback of the LUW is identified by a negative SQLCODE and a W in the SQLWARN6. When a non-severe SQL error occurs, the system restores all changes made by the statement in error. This statement rollback is identified by a negative SQL code and a blank in SQLWARN6. For work done in the LUW before execution of the statement in error, do the following:
To declare the SQL Communications Area (SQLCA), code this statement in your program:
INCLUDE SQLCA
When you preprocess your program, the system inserts host language variable declarations in place of the INCLUDE SQLCA statement, and SQL communicates with your program using this group of variables. The system uses the variables for warning flags, error codes, and diagnostic information. All these variables are discussed in the DB2 Server for VSE & VM SQL Reference manual.
The system returns a return code in SQLCODE after executing each SQL statement.
When a statement is executed successfully, SQLCODE is set to 0 (SQLSTATE is '00000'). The system indicates error conditions by returning a negative SQLCODE. A positive SQLCODE indicates normal or warning conditions experienced while executing the statement.
The system also returns a return code in SQLSTATE after executing each SQL statement. SQLSTATE provides common return codes for IBM's relational database products. SQLSTATE values comply with the SQL92 standard. For a discussion of return codes in SQLSTATE, refer to the DB2 Server for VSE & VM SQL Reference manual.
The system supports a stand-alone SQLCODE. If you request this support, you must not include the SQLCA definition in your program. You must, however, provide the integer variable SQLCODE (SQLCOD in FORTRAN). Refer to Parameters for SQLPREP EXEC for Single and Multiple User Modes or Defining the Preprocessing Parameters for information on the preprocessor parameters that provide NOSQLCA support.
The following WHENEVER statement specifies a system action that is to occur when an SQL error (that is, a negative SQLCODE) is returned:
WHENEVER SQLERROR GO TO ERRCHK
That is, whenever an SQL error (SQLERROR) occurs, program control is transferred to code which follows a specific label, such as ERRCHK. This code should include logic to analyze the error indicators in the SQLCA. Depending on how ERRCHK is defined, action may be taken to execute the next sequential program instruction, to carry out some special functions, or, as in most cases, to roll back the current LUW and end the program.
As mentioned previously, the database manager returns a return code in the SQLCA after almost every SQL statement. The only statements that do not return SQLCODEs are SQL declarative statements, which are not executed; therefore, no SQLCODE can be returned. (Never test for an SQLCODE after a declarative statement.) The following are examples of declarative statements:
When a nondeclarative statement is in error, the system reverses any changes to the database caused by that statement. For any previous work done in the LUW, you have to tell the system what action to take.
Figure 56 shows a representation of the SQLCA structure with
host-language independent data type descriptions. (Refer to the
appendixes for the SQLCA data types of a particular programming
language.)
Figure 56. SQLCA Structure (in Pseudocode)
SQLCA -- a structure composed of:
SQLCAID -- character string of length 8
SQLCABC -- 31-bit binary integer
SQLCODE -- 31-bit binary integer
SQLERRM -- varying character string of maximum length 70
SQLERRP -- character string of length 8
SQLERRD -- an array composed of:
SQLERRD(1) -- 31-bit binary integer
SQLERRD(2) -- 31-bit binary integer
SQLERRD(3) -- 31-bit binary integer
SQLERRD(4) -- 31-bit binary integer
SQLERRD(5) -- 31-bit binary integer
SQLERRD(6) -- 31-bit binary integer
SQLWARN -- a sub-structure composed of:
SQLWARN0 -- single character
SQLWARN1 -- single character
SQLWARN2 -- single character
SQLWARN3 -- single character
SQLWARN4 -- single character
SQLWARN5 -- single character
SQLWARN6 -- single character
SQLWARN7 -- single character
SQLWARN8 -- single character
SQLWARN9 -- single character
SQLWARNA -- single character
SQLSTATE - character string of length 5
|
The DB2 Server for VSE & VM SQL Reference manual explains the structure of the SQLCA, and describes each field in detail. Some tips about SQLERRM and SQLWARN fields are provided below.
The message texts associated with particular SQLCODEs (which can be found in the DB2 Server for VM Messages and Codes or the DB2 Server for VSE Messages and Codes manual), often include variables which are returned in the SQLERRM field of the SQLCA. In some situations, the format of the last variable in the SQLERRM field is 'FOnn', which specifies the format number of the SQLCODE message text. The 'FO' is an abbreviation for format, and 'nn' represents the number that identifies the version of the message text that applies. If there is more than one variable returned through SQLERRM, the variables are separated by X'FF'.
The first two bytes of SQLERRM (which is varying-length) contain the total length of the string.
See Handling Numeric Conversion Errors for the values of this field when a numeric conversion occurs in an outer select and Handling Errors in a Select-List for the values when an error occurs while evaluating expressions in an outer select.
This field contains characters that warn of various conditions encountered during the processing of your statement. Alternatively, specific warnings may be indicated by positive values in the SQLCA field, SQLCODE. For example, a warning indicator is set when the system ignores null values in computing an average. When the system encounters a particular condition, it sets the corresponding warning character to a designated value, such as W, N, or Z. When the system encounters two different warning conditions and must set the warning character to either W or N, the system randomly chooses one value. If the system encounters three different warning conditions and must set the value of the warning character to W, N, or Z, the system sets the value of the warning character to W or N, but not Z. The warning character Z is, therefore, overridden by W or N. One or more warning characters may be set to W regardless of the code returned in SQLCODE. The meanings of the warning characters are listed in the DB2 Server for VSE & VM SQL Reference manual.
Because there is only one return code structure in each program, you should copy out of the structure any information that you wish to save before the next SQL statement is executed. Of particular note are the SQLCODE and the warning indicators (SQLWARN).
The WHENEVER statement is a nonexecutable statement that assists you in reacting to unusual conditions, based on data returned in the SQLCA.
The following three conditions can be addressed with WHENEVER statements:
Each SQL statement is within the scope of one WHENEVER statement for each of the three conditions. A WHENEVER statement for an already specified condition can be overridden at any time by coding another WHENEVER statement for the same conditions.
One of three actions can be taken for a WHENEVER statement:
If a WHENEVER statement is not coded for a condition, it is processed as if the condition were CONTINUE.
For a full discussion of the WHENEVER statement, see the DB2 Server for VSE & VM SQL Reference manual.
The scope of a WHENEVER statement is determined by its position in the source program listing, not by its place in the logic flow. (This is because WHENEVER is a declarative statement.) For example:
|
In the pseudocode program fragment above, the scope of the first WHENEVER is only the SELECT INTO statement. The second WHENEVER applies to the DROP INDEX statement (and to all SQL statements that follow it until another WHENEVER is encountered). The CREATE INDEX and DELETE statements are not covered by a WHENEVER (there is no preceding WHENEVER); therefore, the default CONTINUE action applies for WHENEVER conditions.
The SQLCA structure can be examined using the WHENEVER statement. You can test for both general (SQLCODE < 0 | SQLWARN0 <> blank) and specific (SQLCODE = -911 | SQLWARN6 = 'W') warning or error conditions. To do this, use a WHENEVER statement with a GOTO somewhere in the source program before the SQL statements for which you want to directly examine the SQLCA.
For example, Figure 57 shows pseudocode for an error handling routine:
Figure 57. Pseudocode Error-Handling Routine
EXEC SQL WHENEVER SQLERROR GOTO ERRCHK
.
.
.
ERRCHK: * Prevent further errors from branching here
EXEC SQL WHENEVER SQLERROR CONTINUE
* Handle severe errors first
IF SQLWARN0 = 'S'
DISPLAY('A SEVERE ERROR HAS OCCURRED.')
DISPLAY('SQLCODE = ' SQLCODE)
.
.
.
STOP
END-IF
* Describe the error
DISPLAY('AN ERROR HAS OCCURRED.')
DISPLAY('SQLCODE =' SQLCODE)
.
.
.
EXEC SQL ROLLBACK WORK
* Check for errors
IF SQLCODE < 0
DISPLAY('ROLLBACK WORK FAILED. SQLCODE = ')
DISPLAY(SQLCODE)
.
.
.
* Recovery from error is complete.
ELSE
DISPLAY('ROLLBACK WORK SUCCEEDED.')
.
.
.
END-IF
|
When an error occurs, control is passed to the ERRCHK label. Then, in order to prevent a program loop in this routine, a WHENEVER SQLERROR CONTINUE statement is issued. (It is safe to do this because WHENEVER statements never return an SQLCODE.) Next, the severity of the error is determined. If a severe error occurs, the execution of any SQL statements on this application server (except a CONNECT statement) terminates the application abnormally. The pseudocode example reports the error and ends.
If the error is not severe, the pseudocode example displays an informational message giving the SQLCODE, and an attempt is made to undo any changes. The pseudocode example determines whether the ROLLBACK successfully completed, by checking the SQLCODE after the ROLLBACK statement.
After a severe error, only a CONNECT statement is permitted. If the application program reconnects to the application server in which the severe error occurred, two possibilities exist. If the application server has been restarted or has otherwise recovered, the application may continue; otherwise, another severe error will result. If your application program is accessing multiple application servers, you can enter a CONNECT statement to switch to another application server and continue processing.
When an SQL error occurs, you can examine the SQLCA in order to determine the problem. To reduce the time taken to do so, you can issue a call from the application to either a DB2 Server for VSE module, a DB2 Server for VM TEXT file ARISSMF (for FORTRAN programs), or a TEXT file ARISSMA (for all other programs). The pseudo formats of these calls in each of the languages are:
CALL ARISSMA,(SQLCA,S1,S2,S3,S4,S5),VL /* Assembly Language */ ARISSMA(SQLCA,S1,S2,S3,S4,S5) /* 'C' */ CALL 'ARISSMA' USING SQLCA S1 S2 S3 S4 S5. /* COBOL */ CALL ARISSMF(SQLCA,SQLERP,S1,S2,S3,S4,S5) /* FORTRAN */ CALL ARISSMA(SQLCA,S1,S2,S3,S4,S5); /* PL/I */
In this example, S1, S2, S3, S4, and S5 are character strings declared
within the program and according to the rules of the specific language.
Each string will contain information on specific SQLCA fields, after the call
to ARISSMA/ARISSMF. Figure 58 shows the parameter name for the strings, their SQL name,
their lengths, and the corresponding SQLCA fields.
Figure 58. SQLCA Error Information Strings
| Parameter Name | SQL Name | Length | SQLCA Field |
|---|---|---|---|
| S1 | SQLCSTR1 | 13 | SQLCODE |
| S2 | SQLCSTR2 | 13 | SQLERRD1 |
| S3 | SQLCSTR3 | 13 | SQLERRD2 |
| S4 | SQLCSTR4 | 12 | SQLERRP (part 1) |
| S5 | SQLCSTR5 | 14 | SQLERRP (part 2) |
| DB2 Server for VSE |
|---|
|
Notes:
|
| DB2 Server for VM |
|---|
|
Notes:
|
ARISSMA/ARISSMF returns information in the strings to your program. This information can be displayed or can be written to a file. The format in which the information is returned is shown below.
Suppose the SQLCA fields have the following values when the error occurred:
SQLCODE = -901 SQLERRD1 = -160 SQLERRD2 = -33 SQLERRP = ARIXOEX
then the values of the strings will be:
SQLCSTR1 ==> PRCS/00000901 SQLCSTR2 ==> PRCS/00000160 SQLCSTR3 ==> PRCS/00000033 SQLCSTR4 ==> FLDS/SQLERRP SQLCSTR5 ==> VALU/CARIXOEX
These values may be displayed as shown in the pseudocode in Figure 59:
Figure 59. Pseudocode to Display Error Information
EXEC SQL WHENEVER SQLERROR GOTO ERROR
.
.
.
ERROR: * Display string information
CALL ARISSMA
DISPLAY('SQLCSTR1 ='SQLCSTR1)
DISPLAY('SQLCSTR2 ='SQLCSTR2)
DISPLAY('SQLCSTR3 ='SQLCSTR3)
DISPLAY('SQLCSTR4 ='SQLCSTR4)
DISPLAY('SQLCSTR5 ='SQLCSTR5)
|
| DB2 Server for VSE |
|---|
|
Another reason an application might want to process the SQLERROR condition is for graceful cleanup and termination. An example of this is a CICS/VSE conversational transaction. Rather than abnormally terminating on SQLERROR, a conversational transaction might do the following:
The ISQL transaction handles SQLERROR in a similar manner. That is, rather than terminating the ISQL session, the user is given an error message and allowed to proceed. |
| DB2 Server for VM |
|---|
|
The processing of the SQLERROR condition not only allows an application to terminate normally, but also permits easy recovery from errors. An example of this is the ISQL application. Rather than terminating the ISQL session, the user is given an error message and allowed to proceed. In fact, the application could give the user the opportunity to indicate whether backout is necessary. ISQL does this when you omit the WHERE clause in an UPDATE or DELETE statement by checking SQLWARN4. That way, you have the chance to confirm that all the rows in the table are to be deleted or updated. Additional code could be added to the pseudocode example to check for this situation. |