VALIDATE_SELF scalar function

The VALIDATE_SELF scalar function is intended to be used with the SQL Error Logging Facility (SELF). The function can be used to confirm the syntactic validity of a string to be assigned to the SYSIBMADM.SELFCODES global variable.

The input to VALIDATE_SELF is a comma or space delimited list of positive or negative integers. Positive integers are denoted with a single leading plus sign (+), or no preceding sign. Negative integers are denoted with a single leading negative sign (-).

The function validates the syntax of the string. It reorders the list of values in ascending sequence within a comma separated list.

A successful completion returns a string value that is syntactically valid to be used with SELF for setting the SYSIBMADM.VALIDATE_SELF global variable.

While the list of values is intended to be comprised of valid SQLCODE values, the function does not verify that the integer values correspond to defined SQLCODEs. For a list of SQLCODE values supported by Db2® for i, see SQL messages and codes.

Start of change To specify all SQLCODEs that are error conditions (negative values), use the special value of *ERROR. End of change

Start of change To specify all SQLCODEs that are warning conditions (positive values), use the special value of *WARN. End of change

Start of change To specify all SQLCODEs that are error or warning conditions, use the special value of *ALL. End of change

To turn off SELF processing, specify 0 anywhere in the list of values or the special value of *NONE.

Authorization: None required.

The schema is SYSIBMADM.

self-sqlcodes: A character or graphic string expression that contains one or more SQLCODE values, separated by commas or blanks.

The string must conform to the following rules:

An error SQLCODE must be preceded by a single minus sign ('-').
A warning SQLCODE can be preceded by an optional plus sign ('+').
Multiple SQLCODE values in the string can be separated by any number of blanks and can have one comma between values.
Up to 32 SQLCODE values can be provided in the string.
A value of 100 or +100 is not allowed.
A special value or *ERROR, *WARN, *ALL, or *NONE must be the only value in the string.
If the string contains the value *NONE or zero (0), the character zero (0) is returned, which can be used to turn off SELF.

The result of the function is VARCHAR(32700).

The function returns the input string as an ordered list of SQLCODE values, separated by a comma and a space. The positive SQLCODEs are listed first followed by the negative SQLCODEs.

Examples

Validate a string of SQLCODE values before assigning it to the SELFCODES global variable.
```
VALUES SYSIBMADM.VALIDATE_SELF('-913,     -104,,+406,802');
```
Returns: '406, 802, -104, -913'
Turn on SELF for the current session, capturing any instances of SQL statements which complete with an SQLCODE = 406, 802, -104, or -913.
```
SET SYSIBMADM.SELFCODES = 
   SYSIBMADM.VALIDATE_SELF('-913,     -104,,+406,802');
```
Configure SELF to capture detail for all jobs started in the future, for any SQL statements that fail with SQLCODE = -913.
```
CREATE OR REPLACE VARIABLE SYSIBMADM.SELFCODES 
      VARCHAR(256) DEFAULT (SYSIBMADM.VALIDATE_SELF('-913'));
```