Custom Errors and Error Messages


So far, you've only seen examples where condition handlers are automatically invoked for a specific SQLSTATE. What if you want to force the invocation of a handler? For example, a column value returned from a select statement may indicate an application error for which you may want to invoke a handler. This section shows you various ways to customize errors and error messages.

Using SIGNAL to Force the Invocation of a Handler

The SIGNAL statement allows you to force an error or warning through the SQLSTATE setting. You also have the option of customizing a message associated with the SQLSTATE. If a handler is declared for that SQLSTATE, it will be invoked. If no handler is declared for that SQLSTATE, then the handler for SQLEXCEPTION or SQLWARNING is invoked as necessary. If no handlers are declared at all, the exception is returned to the application as a database exception.

SQLSTATES beginning with the characters 01 or 02 are warnings and "not found" conditions, respectively. All other SQLSTATEs (not beginning with the characters 00) are considered exceptions. Any warnings and "not found" conditions issued via a SIGNAL statement will be assigned an SQLCODE of +438, and any exceptions will be assigned an SQLCODE of -438.

In fact, the SIGNAL statement even allows you to issue a customized condition or SQLSTATE. If a condition for an SQLSTATEcustomized or otherwiseis declared, then it can be referenced in a SIGNAL statement. Figure 6.8 shows the syntax diagram for the SIGNAL statement in LUW.

Figure 6.8. The syntax diagram for the SIGNAL statement for DB2 UDB for LUW and zSeries.
 >>-SIGNAL------------------------------------------------------->                .-VALUE-. >--+-SQLSTATE--+-------+--+-sqlstate-string-constant-+-+-------->    |                      '-variable-name------------' |    '-condition-name------------------------------------' >--+------------------------+----------------------------------><    '-| signal-information |-' signal-information: |--+-SET MESSAGE_TEXT-- = --diagnostic-string-expression-+------|    '-(--diagnostic-string-expression--)------------------' 

Note

A SIGNAL statement in iSeries does not support the variable name to be used as the SQLSTATE value; a string constant must be used. On the other hand, you can obtain a much richer set of condition information items from the SIGNAL statement in iSeries. A complete syntax diagram of the SIGNAL statement for DB2 UDB for iSeries is captured in the section "DB2 UDB for iSeries Consideration" of this chapter.


To raise an SQLSTATE, you can explicitly specify it with the SQLSTATE value such as 90000. Alternatively, the use of a variable containing an SQLSTATE is also allowed. Some rules need to be followed when defining your own SQLSTATE. The following excerpt is from the DB2 SQL Reference pertaining to custom SQLSTATES:

SQLSTATE values are comprised of a two-character class code value, followed by a three-character subclass code value. Class code values represent classes of successful and unsuccessful execution conditions. Any valid SQLSTATE value can be used in the SIGNAL statement. However, it is recommended that programmers define new SQLSTATES based on ranges reserved for applications. This prevents the unintentional use of an SQLSTATE value that might be defined by the database manager in a future release.

  • SQLSTATE classes that begin with the characters '7' through '9', or 'I' through 'Z' may be defined. Within these classes, any subclass may be defined.

  • SQLSTATE classes that begin with the characters '0' through '6', or 'A' through 'H' are reserved for the database manager. Within these classes, subclasses that begin with the characters '0' through 'H' are reserved for the database manager. Subclasses that begin with the characters 'I' through 'Z' may be defined.

As shown in the syntax diagram of Figure 6.8, you can also specify the message text for the particular SQLSTATE or condition being raised. If message text is provided, it must be enclosed by two single quotation marks. However, you should note that for LUW and zSeries the diagnostic text must be fewer than 70 characters.

Figure 6.9 shows an example that employs the SIGNAL statement. To illustrate, the previous example from Figure 6.7 is expanded to declare a condition for a customized SQLSTATE. The logic is changed such that if more than 10 rows are found in the department table, an exception is raised.

Figure 6.9. An example of the SIGNAL statement.
 CREATE PROCEDURE insert_update_department2 ( IN p_deptno        CHAR(3)                                             ,IN p_deptname      VARCHAR(29)                                             ,IN p_mgrno         CHAR(6)                                             ,IN p_admrdept      CHAR(3)                                             ,IN p_location      CHAR(16)                                             ,OUT p_sqlstate_out CHAR(5)                                             ,OUT p_sqlcode_out  INT )     LANGUAGE SQL     SPECIFIC ins_upd_dept2                       -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                        -- applies to zSeries iud: BEGIN     -- Declare variables     DECLARE SQLSTATE CHAR(5) DEFAULT '00000';     DECLARE SQLCODE INT DEFAULT 0;     DECLARE v_duplicate INT DEFAULT 0;     DECLARE v_num_rows INT DEFAULT 0;     -- Declare condition     DECLARE c_duplicate CONDITION FOR SQLSTATE '23505';     DECLARE c_too_many_rows CONDITION FOR SQLSTATE '99001';     -- Declare handlers     DECLARE EXIT HANDLER FOR SQLEXCEPTION         SELECT SQLSTATE               ,SQLCODE           INTO p_sqlstate_out               ,p_sqlcode_out           FROM sysibm.sysdummy1;     DECLARE CONTINUE HANDLER FOR c_duplicate         SET v_duplicate = 1;     -- Initialize output parms     VALUES (SQLSTATE, SQLCODE)       INTO p_sqlstate_out           ,p_sqlcode_out;     -- See how many rows are already in the DEPARTMENT table     SELECT COUNT(1)       INTO v_num_rows       FROM department;     -- Signal an error if more than 10 rows exist     IF v_num_rows > 10 THEN         SIGNAL c_too_many_rows SET MESSAGE_TEXT =         'Too many rows in table DEPARTMENT'; -- (1)     END IF;     -- Try insert, if duplicate, then update     INSERT INTO department ( deptno                             ,deptname                             ,mgrno                             ,admrdept                             ,location )                     VALUES ( p_deptno                             ,p_deptname                             ,p_mgrno                             ,p_admrdept                             ,p_location);     IF v_duplicate = 1 THEN         -- only update if non-null value provided as input parameter         UPDATE department            SET deptname = coalesce(p_deptname, deptname)               ,mgrno    = coalesce(p_mgrno, mgrno)               ,admrdept = coalesce(p_admrdept, admrdept)               ,location = coalesce(p_location, location)          WHERE deptno = p_deptno;     END IF; END iud 

If you examine Figure 6.9, you will see that if fewer than 11 rows exist in the department table, the SQL procedure will function as it did in Figure 6.7. However, if more than 10 rows are encountered, an exception with an SQLSTATE of 99001 is raised by the SIGNAL statement on Line (1). Because there is no specific handler for the specified SQLSTATE, the EXIT handler for the SQLEXCEPTION is invoked. The control returns to the caller after the handler finishes execution. In this case, 99001 will be returned as the SQLSTATE value, and -438 will be returned as the SQLCODE.

Using RESIGNAL to Force the Invocation of a Handler

Along the same lines of the SIGNAL statement, a similar statementRESIGNALwarrants some discussion. The RESIGNAL statement can only be invoked from inside a condition handler. It is used for one of two things:

  • It can be used to issue a warning or error in the same way as the SIGNAL statement but from within a handler. The SQLSTATE and SQLCODE that caused the handler to be invoked are overridden.

  • It can be used to reissue the same condition that caused the handler to be invoked. The reason for this case will become more apparent in the next section.

Figure 6.10 shows the syntax diagram for the RESIGNAL statement in LUW.

Figure 6.10. The syntax diagram for the RESIGNAL statement in LUW and zSeries.
 >>-RESIGNAL----------------------------------------------------------------> >--+---------------------------------------------------------------------+-><    |             .-VALUE-.                                               |    '-+-SQLSTATE--+-------+--+-sqlstate-string-constant-+-+-+-------------+-'      |                      '-variable-name------------' | '-|signal-info|-'      '-condition-name------------------------------------' signal-information: |--SET--MESSAGE_TEXT-- = --+-variable-name--------------+---------------|                            '-diagnostic-string-constant-' 

Note

The RESIGNAL statement syntax in iSeries is different from what is presented in Figure 6.10. Refer to the section "DB2 UDB for iSeries Considerations" toward the end this chapter for its syntax diagram.

The RESIGNAL statement syntax in zSeries is the same as shown in Figure 6.10 with the exception that the MESSAGE_TEXT can only be set to a diagnostic string expression.


The syntax diagram shows that the SQLSTATE value and signal-information specifications are optional. If they are omitted, the statement will reissue the same SQLSTATE and SQLCODE that caused the handler to be invoked.

This is a good time for another example. Again, this example is for illustration purposes only to show a crude way to override the SQLSTATE that invoked the handler. The example in Figure 6.9 is expanded to contain a handler for the condition forced by the SIGNAL statement. The handler in turn issues a RESIGNAL statement.

Figure 6.11. An example of a RESIGNAL statement.
 CREATE PROCEDURE insert_update_department3 ( IN p_deptno        CHAR(3)                                             ,IN p_deptname      VARCHAR(29)                                             ,IN p_mgrno         CHAR(6)                                             ,IN p_admrdept      CHAR(3)                                             ,IN p_location      CHAR(16)                                             ,OUT p_sqlstate_out CHAR(5)                                             ,OUT p_sqlcode_out  INT )     LANGUAGE SQL     SPECIFIC ins_upd_dept3                       -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                        -- applies to zSeries iud: BEGIN     -- Declare variables     DECLARE SQLSTATE CHAR(5) DEFAULT '00000';     DECLARE SQLCODE INT DEFAULT 0;     DECLARE v_duplicate INT DEFAULT 0;     DECLARE v_num_rows INT DEFAULT 0;     -- Declare condition     DECLARE c_duplicate CONDITION FOR SQLSTATE '23505';     DECLARE c_too_many_rows CONDITION FOR SQLSTATE '99001';     DECLARE c_error CONDITION FOR SQLSTATE '99999';     -- Declare handlers     DECLARE EXIT HANDLER FOR SQLEXCEPTION         SELECT SQLSTATE, SQLCODE           INTO p_sqlstate_out               ,p_sqlcode_out           FROM sysibm.sysdummy1;     -- Handler with RESIGNAL     DECLARE EXIT HANDLER FOR c_too_many_rows      -- (1)         RESIGNAL c_error SET MESSAGE_TEXT = 'Too many rows in table DEPARTMENT';     DECLARE CONTINUE HANDLER FOR c_duplicate         SET v_duplicate = 1;     -- Initialize output parms     VALUES (SQLSTATE, SQLCODE)     INTO p_sqlstate_out         ,p_sqlcode_out;   -- See how many rows are already in the DEPARTMENT table   SELECT COUNT(1)     INTO v_num_rows     FROM department;   -- Signal an error if more than 10 rows exist   IF v_num_rows > 10 THEN       SIGNAL c_too_many_rows;   END IF;   -- Try insert, if duplicate, then update   INSERT INTO department ( deptno                           ,deptname                           ,mgrno                           ,admrdept                           ,location )                  VALUES  ( p_deptno                           ,p_deptname                           ,p_mgrno                           ,p_admrdept                           ,p_location);   IF v_duplicate = 1 THEN       -- only update if non-null value provided as input parameter       UPDATE department          SET deptname = coalesce(p_deptname, deptname)             ,mgrno    = coalesce(p_mgrno, mgrno)             ,admrdept = coalesce(p_admrdept, admrdept)             ,location = coalesce(p_location, location)        WHERE deptno = p_deptno;   END IF; END iud 

The example shows that when more than 10 rows exist in the department table, an error is SIGNALed with an SQLSTATE of 99001. The condition handler for this condition RESIGNALs with an SQLSTATE of 99999. Thus, when there are too many rows in the department table, the calling application will receive an SQLSTATE of 99999, not 99001 as in the example from Figure 6.9.

The real value of the RESIGNAL statement is shown when you have multiple handlers with multiple levels of compound statements, which you will see in the next section.

Scope of Handlers

Recall that condition handlers are declared within a compound statement. In LUW and iSeries, an SQL procedure by definition is a compound statement, which can contain other compound statements, and so on. Hence, it follows that each of these nested compound statements can contain condition handlers as well. What is the behavior of the stored procedure when nested compound statements with multiple handlers are used?

Tip

Nested compound statements are not currently supported in zSeries, Figure 6.12 does not apply to this platform. Therefore, the scope of the handlers will always be for the entire stored procedure.

Figure 6.12. Pseudo-code for nested handlers.
 CREATE PROCEDURE s: BEGIN     s1: DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL     s2: SQL Procedure Statement     s3: SQL Procedure Statement     s4: BEGIN         s4-1: DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL         s4-2: DECLARE EXIT HANDLER FOR '99999' BEGIN END         s4-3: SQL Procedure Statement         s4-4: SQL Procedure Statement         s4-5: BEGIN             s4-5-1: DECLARE EXIT HANDLER FOR SQLSTATE '23503'             RESIGNAL SQLSTATE '99999'             s4-5-2: SQL Procedure Statement             s4-5-3: SQL Procedure Statement             s4-5-4: IF <cond> THEN SIGNAL SQLSTATE '23503' END IF         END s4-5         s4-6: BEGIN ATOMIC            s4-6-1: DECLARE UNDO HANDLER FOR SQLSTATE '23503'            RESIGNAL SQLSTATE '99998'            s4-6-2: SQL Procedure Statement            s4-6-3: SQL Procedure Statement            s4-6-4: IF <cond> THEN SIGNAL SQLSTATE '23503' END IF            END s4-6            s4-7: IF <cond> THEN SIGNAL SQLSTATE '23503' END IF        END s4        s5: SQL Procedure Statement    END s 


Figure 6.12 shows some pseudo-code to help paint a scenario for discussion.

Based on the previous example, the scope of each of the handlers is the compound statement in which they are declared, specifically

  • The scope of the handler declared in line s1 is the s compound statement (lines s1 through s5).

  • The scope of the handler declared in line s4-1 is the s4 compound statement (lines s4-1 tHRough s4-7).

  • The scope of the handler declared in line s4-2 is also the s4 compound statement (lines s4-1 through s4-7).

  • The scope of the handler declared in line s4-5-1 is the s4-5 compound statement (lines s4-5-2 through s4-5-4).

  • The scope of the handler declared in line s4-6-1 is the s4-6 compound statement (lines s4-6-2 through s4-6-4).

So it follows that if an exception is encountered or SIGNALed in line:

  • s4-5-4, then the procedure continues execution after executing a few handlers. First, the EXIT handler declared at line s4-5-1 is invoked with SQLSTATE 23503 and SQLCODE -438. It RESIGNALS an exception with SQLSTATE 99999 and SQLCODE -438 and EXITS the s4-5 compound statement. At this point, the EXIT handler declared at line s4-2 is invoked. This handler executes and EXITS the s4 compound statement upon completion and continues to execute line s5. Note that compound statement s4-6 is skipped. Because the EXIT handler s4-2 did not RESIGNAL a condition, the s1 handler was not invoked upon EXIT of the s4 compound statement.

  • s4-6-4, then the procedure continues execution after executing a few handlers. First, the UNDO handler declared at line s4-6-1 is invoked with SQLSTATE 23503 and SQLCODE -438. Any statements within the s4-6 compound statement are rolled back. Thus, statements s4-6-2 and s4-6-3 are rolled back. The UNDO handler RESIGNALS an exception with SQLSTATE 99998 and SQLCODE -438 and EXITS the s4-6 compound statement. At this point, the EXIT handler declared at line s4-1 is invoked, because there is no specific handler declared for SQLSTATE 99998. This handler RESIGNALS with the same SQLSTATE and SQLCODE and EXITs the s4 compound statement. Here the handler declared at line s1 takes over, and the handler RESIGNALS the same SQLSTATE and SQLCODE, and EXITS the procedure.

  • s4-7, then SQLSTATE 23503 and SQLCODE -438 are returned to the calling application. First, the EXIT handler declared at line s4-1 is invoked with SQLSTATE 23503 and SQLCODE -438. This handler RESIGNALS with the same SQLSTATE and SQLCODE, and EXITS the s4 compound statement. Here, the handler declared at line s1 takes over, and this handler RESIGNALs the same SQLSTATE and SQLCODE, and EXITS the procedure.

  • s4-5-3, then the SQLSTATE and SQLCODE causing the exception is returned to the calling program. In this case, there is no handler within the s4-5 compound statement to handle this exception, so a handler in the outer scope is invokednamely, the handler declared at line s4-1. This handler RESIGNALS with the same SQLSTATE and SQLCODE, and EXITS the s4 compound statement. Here, the handler declared at line s1 takes over, and this handler RESIGNALS the same SQLSTATE and SQLCODE, and EXITS the procedure.

Note the use of the RESIGNAL statement in the scenario described. It is sometimes useful to have a handler that executes some SQL statements, and then reissue the same condition. This is especially useful when nested scoping is used. In Figure 6.12, the handler in line s4-5-1 could easily be modified to contain one or more SQL statements prior to the RESIGNAL statement (as long as they are enclosed with a BEGIN and END).

The pseudo-code in Figure 6.12 is not a working example, but does serve to show you the flow of logic under some of the more complex situations. The "Bringing It All Together" section will build a working example of such a scenario.

RAISE_ERROR Function

Besides the SIGNAL and RESIGNAL statements, DB2 UDB for LUW and zSeries also provide a built-in function, RAISE_ERROR, which causes a statement to return an error. The function contains two arguments that specify the SQLSTATE and error message to be returned. Figure 6.13 shows the syntax diagram of the function.

Figure 6.13. The syntax diagram for the RAISE_ERROR function.
 >>-RAISE_ERROR--(--sqlstate--,--diagnostic-string--)----------->< 

When defining your own SQLSTATE, the same rules discussed for the SIGNAL statement in the "Using SIGNAL to Force the Invocation of a Handler" section also apply here. SQLCODE -438 is returned after executing the statement that includes the RAISE_ERROR. The error message text or diagnostic string as indicated in Figure 6.13 is a string that's 70 bytes long. If the message is longer than 70 bytes, it will be truncated.

The RAISE_ERROR function is found to be most useful in a CASE expression. Refer to Figure 6.14 for an example.

Figure 6.14. An example of the RAISE_ERROR function for LUW and zSeries.
 CREATE PROCEDURE app_raise_error ( IN p_admrdept      CHAR(3)                                   ,OUT p_sqlstate_out CHAR(5)                                   ,OUT p_sqlcode_out  INT )     LANGUAGE SQL     SPECIFIC app_raise_eror                    -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                      -- applies to zSeries ae: BEGIN    -- Declare variables    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';    DECLARE SQLCODE INT DEFAULT 0;    -- Declare handlers    DECLARE EXIT HANDLER FOR SQLEXCEPTION                              -- (1)       SELECT SQLSTATE, SQLCODE         INTO p_sqlstate_out             ,p_sqlcode_out          FROM sysibm.sysdummy1;   UPDATE department      SET LOCATION =            (CASE WHEN p_admrdept = 'A00' THEN 'Boston'                -- (2)                  WHEN p_admrdept = 'B01' THEN 'Chicago'                  WHEN p_admrdept = 'C01' THEN 'Dallas'                  WHEN p_admrdept = 'D01' THEN 'San Mateo'                  ELSE RAISE_ERROR('70001', 'ADMRDEPT does not exist') -- (3)            END); END ae 

This example accepts an input parameter that is evaluated in the CASE expression on Line (2). If the input does not match any value specified, an error is raised. On Line (3), SQLSTATE 70001 and an error message ADMRDEPT does not exist are raised. This error will be captured by the EXIT handler declared on Line (1) and handled in the exact same fashion as discussed in the previous sections.

Note

Although the RAISE_ERROR function is not supported in iSeries, most applications of the function can be handled by the SIGNAL or RESIGNAL statement. An example showing how to use the SIGNAL statement in iSeries is presented in the "DB2 UDB for iSeries Considerations" section in this chapter.


GET DIAGNOSTICS

When presenting scenarios for condition handling, it is necessary to introduce the use of the GET DIAGNOSTICS statement. The GET DIAGNOSTICS statement is used to obtain information relating to the statement that was just executed. For example, many tests for error conditions rely on knowing how many rows are affected as a result of an INSERT, UPDATE, or DELETE statement. The GET DIAGNOSTICS statement helps you get this information. The syntax diagram for this statement in LUW is shown in Figure 6.15.

Figure 6.15. The syntax diagram for the GET DIAGNOSTICS statement in LUW.
 >>-GET DIAGNOSTICS-+-SQL-variable-name--=----+-ROW_COUNT-----+--+-><                    |                         '-RETURN_STATUS-'  |                    '-condition-information----------------------' condition-information               .-,-----------------------------------------------.               V                                                 | |-EXCEPTION-1-+--SQL-variable-name--=--+--MESSAGE_TEXT-------+--+---|                                        '--DB2_MESSAGE_STRING--' 

Note

DB2 UDB for iSeries and zSeries offer a more comprehensive set of information than what is available in LUW shown in Figure 6.15. Refer to the considerations section in the chapter for detailed discussions and examples.


You will notice that it has three alternatives. When invoked as the first statement after an INSERT, UPDATE, or DELETE statement, ROW_COUNT will retrieve the number of rows that were affected by the previous statement. If this statement follows a PREPARE statement, then ROW_COUNT will return the number of estimated rows expected as a result of the PREPAREd statement. Note that it is a common misconception that the GET DIAGNOSTICS statement will also give the number of rows retrieved by a SELECT. This is not true.

Figure 6.16 shows a simple example of using the statement with the ROW_COUNT keyword. It deletes from the employee table rows that have values of empno beginning with the parameter value passed in, and returns the number of rows deleted.

Figure 6.16. An example of GET DIAGNOSTICS with ROW_COUNT.
 CREATE PROCEDURE get_diag ( IN p_empno VARCHAR(6)                            ,OUT p_rows INT )     LANGUAGE SQL     SPECIFIC get_diag                            -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                        -- applies to zSeries gd: BEGIN     DECLARE v_rows INT DEFAULT -1;     DELETE FROM employee      WHERE empno like p_empno || '%';     GET DIAGNOSTICS v_rows = ROW_COUNT;     SET p_rows = v_rows; END gd 

GET DIAGNOSTICS supports another option called RETURN_STATUS that is used after a CALL statement to another procedure. It returns the value specified in the RETURN statement of the called procedure. The RETURN statement and working with results from called procedures is the subject of the next section, "Processing Results from Called Procedures."

The condition-information clause allows the retrieval of error or warning information related to the SQL statement that just executed. The MESSAGE_TEXT keyword will retrieve the actual error or warning message associated with the SQL statement, or a blank or empty string if the statement completed successfully without warnings. This is useful in cases where you would like to return an error message from the procedure instead of just the SQLSTATE and/or SQLCODE.

The example in Figure 6.17 shows the simple_error procedure from Figure 6.4 that's been changed to return a message instead of SQLCODE and SQLSTATE.

Figure 6.17. A revised example of simple error checking with MESSAGE_TEXT.
 CREATE PROCEDURE simple_error_message ( IN p_midinit        CHAR                                        ,IN p_empno          CHAR(6)                                        ,OUT p_error_message VARCHAR(300) )    LANGUAGE SQL    SPECIFIC simple_error_msg                    -- applies to LUW and iSeries -- WLM ENVIRONMENT <env>                        -- applies to zSeries sem: BEGIN    -- Declare condition handlers    DECLARE EXIT HANDLER FOR SQLEXCEPTION         GET DIAGNOSTICS EXCEPTION 1 p_error_message = MESSAGE_TEXT;    -- Procedure logic    SET p_error_message = '';    UPDATE employee                                  -- (1)       SET midinit = p_midinit     WHERE empno = p_empno; END sem 

The procedure in Figure 6.17 will return a message when an exception occurs, instead of SQLCODE and SQLSTATE, as shown in Figure 6.4. On LUW, the error message will contain both the SQLCODE and SQLSTATE inside it. Note that the MESSAGE_TEXT is not bound by 70 bytes as in the customized error messages.

To see an example of an error message, assume that the employee table is dropped after the procedure in Figure 6.17 is created. A subsequent execution of the stored procedure would result in the following message being passed in the output parameter:

Figure 6.18. Examples of an error message.
 On LUW: SQL0727N  An error occurred during implicit system action type "1". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "DB2ADMIN.EMPLOYEE".  SQLSTATE=56098 On iSeries: EMPLOYEE in DB2ADMIN type *FILE not found. On zSeries: DB2ADMIN.EMPLOYEE is an undefined name. 

On LUW, the message starts with a message identifier and ends with the SQLSTATE. The message identifier for SQL messages begins with a prefix (SQL) followed by a message number. The message number can be easily translated to an SQLCODE. It contains an unsigned number representing the SQLCODE and one of three letters: N, W, or C, indicating notifications, warnings or critical errors, respectively. For the SQLCODE translation, warnings imply that the sign of the SQLCODE is positive, and notifications and critical errors imply the sign of the SQLCODE is negative. The rest of the message is informative and may contain one or more tokens which are enclosed in double quotes ("). Not all messages will contain tokens.

On iSeries and zSeries, the value returned is just the informative message and may contain one or more tokens which are enclosed in double quotes ("). Similar to LUW, not all messages will contain tokens.

The DB2_TOKEN_STRING keyword of the condition-information clause in the GET DIAGNOSTICS statement of LUW and iSeries can be used to retrieve just the string of tokens without the error message. Why would you want to do this? In some cases, you may know what the message is and the action you need to take in a handler is based on the value of one of the tokens. Thus getting just the tokens would be sufficient. Alternatively, you may just want to return the SQLCODE or SQLSTATE and the string of tokens to your calling application and have your calling application's error-handling process use the DB2 APIs (sqlaintp(), sqlogstt()) to receive the error message. In this case, when the calling application retrieves the error message, the message will have placeholders for the tokens because the context of the original message is no longer known. This is where the string of tokens can be used for a manual substitution. This second approach seems like a lot of work for the calling application, but is often desired to limit the size of the parameter being passed back.

Note

The mechanism for obtaining the DB2 error token string is not the same in zSeries. Instead of using the DB2_TOKEN_STRING keyword, other keywords such as DB2_RETURNED_SQLCODE and RETURNED_SQLSTATE can be used to obtain the same information. To see this in an SQL procedure example, refer to the section "DB2 UDB for zSeries Considerations."


It may not be obvious, but being able to return both the SQLCODE or SQLSTATE and the DB2_TOKEN_STRING presents a dilemma. Recall that if you want to retrieve the SQLCODE or SQLSTATE, it needs to be accessed on the first statement after an SQL statement is executed, or be the first statement in a handler. The same rules apply for the GET DIAGNOSTICS statement. So how can we have both values related to a single statement? For LUW and iSeries, the answer is to use nested handlers and the RESIGNAL statement. As for zSeries (and as an alternate solution on iSeries), you can simply access SQLCODE, SQLSTATE, and MESSAGE_TEXT using a single GET DIAGNOSTICS statement. Refer to Figure 6.30 for an example.

The example in Figure 6.19 will show the procedure from Figure 6.17 rewritten to return SQLCODE, SQLSTATE, and the string of message tokens.

Figure 6.19. An example of simple error checking with DB2_TOKEN_STRING and nested handlers for LUW and iSeries.
 CREATE PROCEDURE simple_error_token ( IN p_midinit       CHAR                                      ,IN p_empno         CHAR(6)                                      ,OUT p_sqlcode_out  INT                                      ,OUT p_sqlstate_out CHAR(5)                                      ,OUT p_token_string VARCHAR(100) )     LANGUAGE SQL     SPECIFIC simple_error_token                 -- applies to LUW and iSeries  setk: BEGIN     -- Declare variables     DECLARE SQLSTATE CHAR(5) DEFAULT '00000';     DECLARE SQLCODE INT DEFAULT 0;     -- Declare condition handlers     DECLARE EXIT HANDLER FOR SQLEXCEPTION                               -- (1)        BEGIN          DECLARE CONTINUE HANDLER FOR SQLEXCEPTION                      -- (4)             GET DIAGNOSTICS EXCEPTION 1 p_token_string = DB2_TOKEN_STRING;          SELECT SQLCODE                                                 -- (2)               ,SQLSTATE           INTO p_sqlcode_out               ,p_sqlstate_out           FROM sysibm.sysdummy1;         RESIGNAL;                                                       -- (3)       END;    -- Procedure logic    SET p_token_string = '';    VALUES (SQLCODE, SQLSTATE)      INTO p_sqlcode_out          ,p_sqlstate_out;    UPDATE employee       SET midinit = p_midinit     WHERE empno = p_empno; END setk 

Notice the EXIT handler on Line (1) contains a CONTINUE handler within it. If an error occurred, the first statement to execute within the handleron Line (2)will receive the SQLCODE and SQLSTATE values. The next statement to execute, on Line (3), will reissue the same error. At this point, the nested handler on Line (4) will kick in to retrieve the DB2_TOKEN_STRING. Because this handler is a CONTINUE handler, execution will continue after the RESIGNAL statement on Line (3), which is the end of the EXIT handler; thus control is returned to the calling program.

Again, to see an example of DB2_TOKEN_STRING, assume that the EMPLOYEE table is dropped after the procedure in Figure 6.19 is created. A subsequent execution of the procedure would result in the following string being passed in the p_token_string parameter:

 LUW: 1ÿ-204ÿ42704ÿDB2ADMIN.EMPLOYEE iSeries: DB2ADMIN.EMPLOYEETABLE 

The tokens in the string are delimited by the 'ÿ' character, 0xFF in hexadecimal, or ASCII character code 255. The LUW string contains four tokens that are substitution values for the corresponding message. The actual message was shown in Figure 6.17 with the tokens substituted. Note the order of the tokens is the substitution order.



    DB2 SQL PL. Deployment and Advanced Configuration Essential Guide for DB2 UDB on Linux., UNIX, Windows, i5. OS, z. OS
    Intelligent Enterprises of the 21st Century
    ISBN: N/A
    EAN: 2147483647
    Year: 2003
    Pages: 205

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net