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 of type VARCHAR(32672) in the specified
locale. If the input locale is not supported by the server, the message
is returned in English.
Syntax
>>-SQLERRM--(--msgid--, -tokens--, -token_delimiter--,---------->
>--locale--, -shortmsg--)--------------------------------------><
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
EXECUTE privilege on the
SQLERRM scalar function.
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 example is a sample 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 example is a sample 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 example is a sample 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
SQLERRM scalar function
This SQLERRM scalar
function takes an SQLCODE as the only input and returns the short
message of type VARCHAR(32672) for the specified SQLCODE in English.
Syntax
>>-SQLERRM--(--sqlcode--)--------------------------------------><
The schema is SYSPROC.
Scalar function parameter
- sqlcode
- An input argument of type INTEGER that represents an SQLCODE.
Authorization
EXECUTE privilege on the
SQLERRM scalar function.
Example
Retrieve the short message for
SQLCODE SQL0551N.
VALUES (SYSPROC.SQLERRM (551))
The following example is a sample output returned.
1
--------------------------------------------------------------...--
SQL0551N "" does not have the privilege to perform operation
"" on object "".