Raising a condition within an SQL procedure by using the SIGNAL or RESIGNAL statements

Within an SQL procedure, you can force a particular condition to occur with a specific SQLSTATE and message text.

About this task

You can use either a SIGNAL or RESIGNAL statement to raise a condition with a specific SQLSTATE and message text within an SQL procedure. The SIGNAL and RESIGNAL statements differ in the following ways:

  • You can use the SIGNAL statement anywhere within an SQL procedure. You must specify the SQLSTATE value. In addition, you can use the SIGNAL statement in a trigger body. For information about using the SIGNAL statement in a trigger, see Creating a trigger.
  • You can use the RESIGNAL statement only within a handler of an SQL procedure. If you do not specify the SQLSTATE value, Db2 uses the same SQLSTATE value that activated the handler.

You can use any valid SQLSTATE value in a SIGNAL or RESIGNAL statement, except an SQLSTATE class with '00' as the first two characters.

The following table summarizes the differences between issuing a RESIGNAL or SIGNAL statement within the body of a condition handler. For each row in the table, assume that the diagnostics area contains the following information when the RESIGNAL or SIGNAL statement is issued: 
  RETURNED_SQLSTATE    xxxxx       
  MESSAGE_TEXT 'this is my message'
Table 1. Example RESIGNAL and SIGNAL statements
Specify a new condition? Specify message text? Example RESIGNAL statement... Example SIGNAL statement... Result
No No 
RESIGNAL
1		 Not possible

RETURNED_SQLSTATE xxxxx

MESSAGE_TEXT 'this is my message'
Yes No 
RESIGNAL '98765'
2		 
SIGNAL '98765'

RETURNED_SQLSTATE 98765

MESSAGE_TEXT 'APPLICATION RAISED ERROR WITH DIAGNOSTIC TEXT: this is my message'
No Yes Not possible Not possible NA
Yes Yes 
RESIGNAL '98765' 
SET MESSAGE_TEXT 
= 'xyz'
3		 
SIGNAL '98765' 
SET MESSAGE_TEXT 
= 'xyz'
3

RETURNED_SQLSTATE 98765

MESSAGE_TEXT 'APPLICATION RAISED ERROR WITH DIAGNOSTIC TEXT: xyz'
Note:
  1. This statement raises the current condition with the existing SQLSTATE, SQLCODE, message text, and tokens.
  2. This statement raises a new condition (SQLSTATE '98765'). Existing message text and tokens are reset. The SQLCODE is set to -438 for an error or 438 for a warning.
  3. This statement raises a new condition (SQLSTATE '98765') with new message text ('xyz'). The SQLCODE is set to -438 for an error or 438 for a warning.