DB2 UDB for iSeries Considerations

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 Statements

Figure 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.

Table 6.1. Explanation of Condition Information Items
Condition Information	Item Description
`MESSAGE_TEXT`	Specifies a string that describes the error or warning. If an `SQLCA` is used, the string is returned in the `SQLERRMC` field of the `SQLCA`; if the actual length of the string is longer than 70 bytes, it is truncated without a warning.
`CONSTRAINT_CATALOG`	Specifies a string that indicates the name of the database that contains a constraint related to the signaled error or warning.
`CONSTRAINT_SCHEMA`	Specifies a string that indicates the name of the schema that contains a constraint related to the signaled error or warning.
`CONSTRAINT_NAME`	Specifies a string that indicates the name of a constraint related to the signaled error or warning.
`CATALOG_NAME`	Specifies a string that indicates the name of the database that contains a table or view related to the signaled error or warning.
`SCHEMA_NAME`	Specifies a string that indicates the name of the schema that contains a table or view related to the signaled error or warning.
`TABLE_NAME`	Specifies a string that indicates the name of a table or view related to the signaled error or warning.
`COLUMN_NAME`	Specifies a string that indicates the name of a column in the table or view related to the signaled error or warning.
`CURSOR_NAME`	Specifies a string that indicates the name of a cursor related to the signaled error or warning.
`CLASS ORIGIN`	Specifies a string that indicates the origin of the `SQLSTATE` class related to the signaled error or warning.
SUBCLASS ORIGIN	Specifies a string that indicates the origin of the `SQLSTATE` subclass related to the signaled error or warning.

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 Function

Recall 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 DIAGNOSTICS

The 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.

Table 6.2. Information Available from the iSeries GET DIAGNOSTICS Statement
Information Type	Information Keyword
`STATEMENT`	`COMMAND_FUNCTION_CODE`
	`DB2_DIAGNOSTIC_CONVERSION_ERROR`
	`DB2_LAST_ROW`
	`DB2_NUMBER_CONNECTIONS`
	`DB2_NUMBER_PARAMETER_MARKERS`
	`DB2_NUMBER_RESULT_SETS`
	`DB2_NUMBER_ROWS`
	`DB2_NUMBER_SUCCESSFUL_SUBSTMTS`
	`DB2_RELATIVE_COST_ESTIMATE`
	`DB2_RETURN_STATUS`
	`DB2_ROW_COUNT_SECONDARY`
	`DB2_ROW_LENGTH`
	`DB2_SQL_ATTR_CONCURRENCY`
	`DB2_SQL_ATTR_CURSOR_CAPABILITY`
	`DB2_SQL_ATTR_CURSOR_HOLD`
	`DB2_SQL_ATTR_CURSOR_ROWSET`
	`DB2_SQL_ATTR_CURSOR_SCROLLABLE`
	`DB2_SQL_ATTR_CURSOR_SENSITIVITY`
	`DB2_SQL_ATTR_CURSOR_TYPE`
	`DYNAMIC_FUNCTION`
	`DYNAMIC_FUNCTION_CODE`
	`MORE`
	`NUMBER`
	`ROW_COUNT`
	`trANSACTION_ACTIVE`
	`TRANSACTIONS_COMMITTED`
	`trANSACTIONS_ROLLED_BACK`
`CONNECTION`	`CONNECTION_NAME`
	`DB2_AUTHENTICATION_TYPE`
	`DB2_AUTHID_TRUNCATION`
	`DB2_AUTHORIZATION_ID`
	`DB2_CONNECTION_METHOD`
	`DB2_CONNECTION_NUMBER`
	`DB2_CONNECTION_STATE`
	`DB2_CONNECTION_STATUS`
	`DB2_CONNECTION_TYPE`
	`DB2_DDM_SERVER_CLASS_NAME`
	`DB2_DYN_QUERY_MGMT`
	`DB2_ENCRYPTION_TYPE`
	`DB2_EXPANSION_FACTOR_FROM`
	`DB2_EXPANSION_FACTOR_TO`
	`DB2_PRODUCT_ID`
	`DB2_SERVER_CLASS_NAME`
	`DB2_SERVER_NAME`
	`DB2_USER_ID`
`CONDITION`	`CATALOG_NAME`
	`CLASS_ORIGIN`
	`COLUMN_NAME`
	`CONDITION_IDENTIFIER`
	`CONDITION_NUMBER`
	`CONSTRAINT_CATALOG`
	`CONSTRAINT_NAME`
	`CONSTRAINT_SCHEMA`
	`CURSOR_NAME`
	`DB2_ERROR_CODE1`
	`DB2_ERROR_CODE2`
	`DB2_ERROR_CODE3`
	`DB2_ERROR_CODE4`
	`DB2_INTERNAL_ERROR_POINTER`
	`DB2_LINE_NUMBER`
	`DB2_MESSAGE_ID`
	`DB2_MESSAGE_ID1`
	`DB2_MESSAGE_ID2`
	`DB2_MESSAGE_KEY`
	`DB2_MODULE_DETECTING_ERROR`
	`DB2_NUMBER_FAILING_STATEMENTS`
	`DB2_OFFSET`
	`DB2_ORDINAL_TOKEN_n`
	`DB2_PARTITION_NUMBER`
	`DB2_REASON_CODE`
	`DB2_RETURNED_SQLCODE`
	`DB2_ROW_NUMBER`
	`DB2_SQLERRD_SET`
	`DB2_SQLERRD1`
	`DB2_SQLERRD2`
	`DB2_SQLERRD3`
	`DB2_SQLERRD4`
	`DB2_SQLERRD5`
	`DB2_SQLERRD6`
	`DB2_TOKEN_COUNT`
	`DB2_TOKEN_STRING`
	`MESSAGE_LENGTH`
	`MESSAGE_OCTET_LENGTH`
	`MESSAGE_TEXT`
	`PARAMETER_MODE`
	`PARAMETER_NAME`
	`PARAMETER_ORDINAL_POSITION`
	`RETURNED_SQLSTATE`
	`ROUTINE_CATALOG`
	`ROUTINE_NAME`
	`ROUTINE_SCHEMA`
	`SCHEMA_NAME`
	`SERVER_NAME`
	`SPECIFIC_NAME`
	`SUBCLASS_ORIGIN`
	`TABLE_NAME`
	`trIGGER_CATALOG`
	`trIGGER_NAME`
	`trIGGER_SCHEMA`
	`CATALOG_NAME`

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.