RAISE_ERROR Function - SQLSTATE values

[JohnLewis]

Hello,

when using the scalar function RAISE_ERROR to cause an error when executing an SQL statement, what would be the considerations for choosing the value for the first parameter SQLSTATE?

The IBM documentation says that it must be a 5-character value containing only characters in ranges 0-9 or A-Z and the first two characters cannot be 00, 01 or 02, but are there any other rules or conventions to follow or is the choice completely arbitrary?

Thanks in advance.

[JonBoy]

I'm not aware of any "rules" but when assigning user defined error codes I have always looked at the officially defined range and then started at the far end. So if the official codes range from (say) 001 to 555 then I would probably got for the 900 range for my own codes.

[TedHolt]

I always use a value that starts with 8 and I've never had any trouble. I heard several years ago that IBM had some recommended practices, but I couldn't find them.

[B.Hauser]

Excerpt from the Online Line Documentation: https://www.ibm.com/docs/en/i/7.4?to...state-concepts
SQLSTATE values are designed so that application programs can test for specific conditions or classes of conditions.

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 completion conditions. If you want to use SQLSTATE as the basis of your application's return codes, you can define your own SQLSTATE classes or subclasses using the following guidelines:

• SQLSTATE classes that begin with the characters 7 through 9 or I through Z can be defined. Within these classes, any subclass can 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 can be defined.

The class code of an SQLSTATE value indicates whether the SQL statement was executed successfully (class codes 00 and 01) or unsuccessfully (all other class codes).

Birgitta