The SIGNAL statement signals an error or warning condition. It causes an error or warning to be returned with the specified SQLSTATE and optional condition-information-items.
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. It must not be specified in REXX.
None required.
.-VALUE-. >>-SIGNAL--SQLSTATE--+-------+--+-sqlstate-string-constant-+----> '-sqlstate-string-variable-' >--+--------------------+-------------------------------------->< '-signal-information-' signal-information .-,-----------------------------------------------------------. V | |--SET----+-MESSAGE_TEXT-------+-- = --+-variable-------------------+-+--| +-CONSTRAINT_CATALOG-+ '-diagnostic-string-constant-' +-CONSTRAINT_SCHEMA--+ +-CONSTRAINT_NAME----+ +-CATALOG_NAME-------+ +-SCHEMA_NAME--------+ +-TABLE_NAME---------+ +-COLUMN_NAME--------+ +-CURSOR_NAME--------+ +-CLASS_ORIGIN-------+ '-SUBCLASS_ORIGIN----'
If the SQLSTATE does not conform to these rules, an error is returned.
The sqlstate-string-variable must be a character
or Unicode graphic variable. It cannot be a global variable. The actual
length of the contents of the variable must
be 5.
If an SQLCA is used,
It cannot
be a global variable. 
SQLSTATE values: 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 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.
For more information about SQLSTATEs, see the SQL Messages and Codes topic collection.
Assignment: When the SIGNAL statement is executed, the value of each of the specified string-constants and variables is assigned to the corresponding condition-information-item. However, if the length of a string-constant or variable is longer than the maximum length of the corresponding condition-information-item, it is truncated without a warning. For details on the assignment rules, see Assignments and comparisons. For details on the maximum length of specific condition-information-items, see GET DIAGNOSTICS.
Processing a SIGNAL statement: When a SIGNAL statement is issued, the SQLCODE is based on the SQLSTATE value as follows:
Example 1: Signal SQLSTATE '75002' with a descriptive message text.
EXEC SQL SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'Customer number is not known';
Example 2: Signal SQLSTATE '75002' with a descriptive message text and associate a specific table with the error.
EXEC SQL SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'Customer number is not known', SCHEMA_NAME = 'CORPDATA', TABLE_NAME = 'CUSTOMER';