|
DB2 UDB for iSeries supports all of the condition handling techniques discussed in this chapter except for the RAISE_ERROR built-in function. Some other statementsnamely SIGNAL, RESIGNAL, and GET DIAGNOSTICSoffer a much richer set of functionality. SIGNAL and RESIGNAL StatementsFigure 6.24 shows the syntax diagrams for the SIGNAL and RESIGNAL statements in DB2 UDB for iSeries. Figure 6.24. Syntax diagrams for SIGNAL and RESIGNAL statements in DB2 UDB for iSeries..-VALUE-. >>-SIGNAL----+-SQLSTATE--+-------+--sqlstate-string-constant--+-------------> '-condition-name---------------------------------' .-,-----------------------------------------------------. V | >---+-SET----+--MESSAGE_TEXT-------+-- = --+-SQL-variable-name---+-+-+----->< | |--CONSTRAINT CATALOG-| | SQL-parameter-name--| | | |--CONSTRAINT SCHEMA--| '-diagnostic-str-const' | | |--CONSTRAINT NAME----| | | |--CATALOG NAME-------| | | |--SCHEMA NAME--------| | | |--TABLE NAME---------| | | |--COLUMN NAME--------| | | |--CURSOR NAME--------| | | |--CLASS ORIGIN-------| | | '--SUBCLASS ORIGIN----' | | | '-(--diagnostic-string-constant--)-------------------------------' .-VALUE-. >>-RESIGNAL--+-SQLSTATE--+-------+--sqlstate-string-constant--+-------------> '-condition-name---------------------------------' .-,----------------------------------------------------. V | >---+-SET----+--MESSAGE_TEXT-------+-- = --+-SQL-variable-name--+-+-------->< |--CONSTRAINT CATALOG-| | SQL-parameter-name---| |--CONSTRAINT SCHEMA--| '-diagnostic-str-const-' |--CONSTRAINT NAME----| |--CATALOG NAME-------| |--SCHEMA NAME--------| |--TABLE NAME---------| |--COLUMN NAME--------| |--CURSOR NAME--------| |--CLASS ORIGIN-------| '--SUBCLASS ORIGIN----' As you can see, the SET clause allows you to assign several values related to the condition. All of these values can be accessed by the richer iSeries GET DIAGNOSTICS statement. Table 6.1, taken from the DB2 UDB for iSeries SQL Reference, gives a brief explanation for each condition information value that can be used in the GET DIAGNOSTICS statement.
The (diagnostic string constant) option of the SIGNAL statement is used in the CREATE TRIGGER statement only, and is provided for compatibility with other products. As a best practice, this syntax should not be used, because it does not conform to ANSI and ISO standards. RAISE_ERROR FunctionRecall that the RAISE_ERROR function is not supported in DB2 UDB for iSeries. Figure 6.25 demonstrates how you can rewrite the SQL procedure in Figure 6.14 by using the SIGNAL statement to provide the same behavior. Figure 6.25. An example of an alternative in iSeries to the RAISE_ERROR function.CREATE PROCEDURE app_raise_error2 ( IN p_admrdept CHAR(3) ,OUT p_sqlstate_out CHAR(5) ,OUT p_sqlcode_out INT ) LANGUAGE SQL SPECIFIC app_raise_error 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; IF p_admrdept NOT IN ('A00', 'B01', 'C01', 'D01') THEN SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'ADMRDEPT does not exist'; END IF; 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' -- (3) END); END ae The main difference is that RAISE_ERROR is a function and can be embedded in a statement, whereas SIGNAL is a separate statement altogether. GET DIAGNOSTICSThe GET DIAGNOSTICS statement in DB2 UDB for iSeries has been greatly enhanced. Figure 6.26 shows the syntax diagram for the statement. Figure 6.26. A simplified GET DIAGNOSTICS syntax diagram for iSeries..--CURRENT--. >>-GET--+-----------+--DIAGNOSTICS-+--statement-information--+----->< '--STACKED--' |--condition-information--| '--combined-information---' statement-information .-,-------------------------------------------------------------. V | |----+--SQL-variable-name----+-=--+--statement-information-item--+--| '--SQL-parameter-name---' condition-information |-CONDITION---+--+--SQL-variable-name----+--+------------------------> | '--SQL-parameter-name---' | '--integer---------------------' .-,-----------------------------------------------------------. V | >----+--SQL-variable-name----+-=--+--connection-information-item--+--| '--SQL-parameter-name---' '--condition-information-item---' combined-information |--+--SQL-variable-name----+-- = ------------------------------------> '--SQL-parameter-name---' >-ALL--+-----------------------------------------------------------------+--| | .-,--------------------------------------------------------. | | V | | '---'----+--STATEMENT------------------------------------------+--+--' '--+--CONDITION---+--+------------------------------+--' '--CONNECTION--' |--+--SQL-variable-name----+--| | '--SQL-parameter-name---' | '--integer--------------------- The GET DIAGNOSTICS statement can be used to obtain a lot of information about the SQL statement that was just executed. The CURRENT keyword retrieves information about the last SQL statement (not a GET DIAGNOSTIC statement) that executed. The STACKED keyword can only be used from inside a handler, and obtains information on the last SQL statement prior to the handler invocation. The type of information available is divided into three parts: statement information, connection information, and condition information. It is accessed using the appropriate clause. A clause to obtain the combined information is also provided. The examples in this chapter have shown how to obtain condition-related information. The keyword CONDITION is a synonym for EXCEPTION. Hence, the statement GET DIAGNOSTICS EXCEPTION 1 p_error_message = MESSAGE_TEXT is the same as GET DIAGNOSTICS CONDITION 1 p_error_message = MESSAGE_TEXT Table 6.2 lists all the keywords for information that can be obtained using the GET DIAGNOSTICS statement.
Figure 6.30 in the next section provides an example of how the GET DIAGNOSTICS statement can be used to obtain several pieces of diagnostic information in one statement. Refer to the DB2 UDB for iSeries SQL Reference manual for a detailed description for each of these options. |
|