SIGNAL statement

Invocation

Authorization

If a module condition is referenced, the privileges held by the authorization ID of the statement must include EXECUTE privilege on the module.

Syntax

>>-SIGNAL------------------------------------------------------->

               .-VALUE-.                                   
>--+-SQLSTATE--+-------+--+-sqlstate-string-constant-+-+-------->
   |                      '-sqlstate-string-variable-' |   
   '-condition-name------------------------------------'   

>--+------------------------+----------------------------------><
   '-| signal-information |-'   

signal-information

|--+-SET MESSAGE_TEXT-- = --diagnostic-string-expression-+------|
   '-(--diagnostic-string-expression--)------------------'   

Description

SQLSTATE VALUE

Specifies the SQLSTATE that will be returned. Any valid SQLSTATE value can be used. The specified value must follow the rules for SQLSTATEs:

• Each character must be from the set of digits ('0' through '9') or upper case letters ('A' through 'Z') without diacritical marks
• The SQLSTATE class (first two characters) cannot be '00', since this represents successful completion.

In the context of a compound SQL (inlined) statement, a MERGE statement, or as the only statement in a trigger body, the following rules must also be applied:

• The SQLSTATE class (first two characters) cannot be '01' or '02', since these are not error classes.
• If the SQLSTATE class starts with the numbers '0' through '6' or the letters 'A' through 'H', then the subclass (the last three characters) must start with a letter in the range of 'I' through 'Z'.
• If the SQLSTATE class starts with the numbers '7', '8', '9', or the letters 'I' through 'Z', then the subclass can be any of '0' through '9' or 'A' through 'Z'.

If the SQLSTATE does not conform to these rules, an error is returned.

sqlstate-string-constant

The sqlstate-string-constant must be a character string constant with exactly 5 characters.

sqlstate-string-variable

The specified SQL variable or SQL parameter must be of data type CHAR(5) and must not be the null value.

condition-name

Specifies the name of a condition that will be returned. The condition-name must be declared within the compound-statement or identify a condition that exists at the current server (SQLSTATE 42373).

SET MESSAGE_TEXT =

Specifies a string that describes the error or warning. The string is returned in the SQLERRMC field of the SQLCA. If the actual string is longer than 70 bytes, it is truncated without warning.

diagnostic-string-expression

A literal string, or a local variable or parameter that describes the error condition. If the string is longer than 70 bytes, it is truncated.

(diagnostic-string-expression)

An expression of type CHAR or VARCHAR that returns a character string of up to 70 bytes to describe the error condition. If the string is longer than 70 bytes, it is truncated. This option is only provided within the scope of a CREATE TRIGGER statement for compatibility with previous versions of DB2®. Regular use of this option is not recommended.

Notes

• If a SIGNAL statement is issued using a condition-name that has no associated SQLSTATE value and the condition is not handled, SQLSTATE 45000 is returned and the SQLCODE is set to -438. Note that such a condition will not be handled by a condition handler for SQLSTATE 45000 that is within the scope of the routine issuing the SIGNAL statement.
• If a SIGNAL statement is issued using an SQLSTATE value or a condition-name with an associated SQLSTATE value, the SQLCODE returned is based on the SQLSTATE value as follows:
  • If the specified SQLSTATE class is either '01' or '02', a warning or not found condition is returned and the SQLCODE is set to +438.
  • Otherwise, an exception condition is returned and the SQLCODE is set to -438.
• A SIGNAL statement has the indicated fields of the SQLCA set as follows:
  • sqlerrd fields are set to zero
  • sqlwarn fields are set to blank
  • sqlerrmc is set to the first 70 bytes of MESSAGE_TEXT
  • sqlerrml is set to the length of sqlerrmc, or to zero if no SET MESSAGE_TEXT clause is specified
  • sqlerrp is set to ROUTINE
• SQLSTATE values are composed 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.

Example

   CREATE PROCEDURE SUBMIT_ORDER
     (IN ONUM INTEGER, IN CNUM INTEGER,
      IN PNUM INTEGER, IN QNUM INTEGER)
      SPECIFIC SUBMIT_ORDER
      MODIFIES SQL DATA
     LANGUAGE SQL
     BEGIN
       DECLARE EXIT HANDLER FOR SQLSTATE VALUE '23503'
         SIGNAL SQLSTATE '75002'
           SET MESSAGE_TEXT = 'Customer number is not known';
       INSERT INTO ORDERS (ORDERNO, CUSTNO, PARTNO, QUANTITY)
         VALUES (ONUM, CNUM, PNUM, QNUM);
     END