SQLERRM scalar functions - Retrieves error message information

There are two versions of the SQLERRM scalar function. The first allows for full flexibility of message retrieval including using message tokens and language selection. The second takes only an SQLCODE as an input parameter and returns the short message in English.

SQLERRM scalar function

This SQLERRM scalar function takes a message identifier, locale and token input and returns the short or long message in the specified locale. If the input locale is not supported by the server, the message is returned in English.

Syntax

The schema is SYSPROC.

Scalar function parameters

msgid: An input argument of type VARCHAR(9) that represents the message number for which the information should be retrieved. The message number is the application return code prefixed with 'SQL', 'DBA' or 'CLI'. For example, 'SQL551', 'CLI0001'. The message number can also be an SQLSTATE, for example, '42829'.
tokens: An input argument of type VARCHAR(70) that represents the error message token list. Some messages might not have tokens. If this parameter is null, then no token replacement occurs in the returned message. Token replacement only occurs when returning the default short messages. If the long message option is selected, no token replacement occurs.
token_delimiter: An input argument of type VARCHAR(1) that represents the token delimiter. This delimiter must be unique and not contained in any tokens passed to the scalar function. If no delimiter is supplied, the default delimiter used is the semicolon.
locale: An input argument of type VARCHAR(33) that represents the locale to pass to the server in order to have the error message retrieved in that language. If no locale is specified, or the server does not support the locale, the message is returned in English and a warning is returned.
shortmsg: An input argument of type INTEGER that is used to indicate if the long message should be returned instead of the default short message. To return long messages, this value must be set to 0 or CAST(NULL as INTEGER).

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Information returned

The data type of the result is VARCHAR(32672).

Examples

Example 1: Retrieve the English short message for SQL0551N with tokens "AYYANG", "UPDATE" and "SYSCAT.TABLES".

VALUES (SYSPROC.SQLERRM 
   ('SQL551', 'AYYANG;UPDATE;SYSCAT.TABLES', ';', 'en_US', 1))

The following is an example of output returned.

1
--------------------------------------------------------------...--
SQL0551N "AYYANG" does not have the privilege to perform operation 
   "UPDATE" on object "SYSCAT.TABLES"

Example 2: Retrieve the English error message associated with SQLSTATE 42501.

VALUES (SYSPROC.SQLERRM ('42501', '', '', 'en_US', 1))

The following is an example of output returned.

1
---------------------------------------------------------------...--
SQLSTATE 42501: The authorization ID does not have the privilege to 
   perform the specified operation on the identified object.

Example 3: Retrieve the English long error message for SQL1001N.

VALUES (SYSPROC.SQLERRM ('SQL1001', '', '', 'en_US', 0))

The following is an example of output returned.

1
------------------------------------------------------------...--
SQL1001N "<name>" is not a valid database name.

Explanation:

The syntax of the database name specified in the command is not
valid.  The database name must contain 1 to 8 characters and all
the characters must be from the database manager base character
set.

 The command cannot be processed.

User Response:

Resubmit the command with the correct database name.

 sqlcode :  -1001

 sqlstate :  2E000