SQL Error Logging Facility (SELF)

The SQL Error Logging Facility (SELF) provides a mechanism that can be used to understand when SQL statements are encountering specific SQL errors or warnings. SELF is built into Db2® for i and can be enabled in specific jobs or system wide.

SELF differs from the Db2 for i SQL Performance Monitoring or Database Monitoring feature in some significant ways:

SELF is configured by the client to identify specific SQLCODE values that Db2 for i should use for SELF processing when a user-initiated SQL statement completes with a matching SQLCODE value.
SELF collects point-of-failure information which is accessible through the QSYS2.SQL_ERROR_LOG view.
SELF runs when an SQL statement completes with an SQLCODE value that matches a list of values in the SYSIBMADM.SELFCODES built-in global variable.
SELF is safe to use in production environments. It has no performance impact to SQL statements that complete successfully or for any SQL errors or warnings where the SQLCODE does not match the values listed in the SYSIBMADM.SELFCODES global variable.

By registering the SQLCODE values you are interested in monitoring at either a job or system level, each time one of the SQLCODE values is returned, an entry will be logged in the SELF table. For each SQLCODE, information such as the program name, call stack, and SQL statement text are recorded. This information is collected while the application is running. Logging only happens during SQL error processing, so it has no performance impact on SQL statements that complete successfully. Errors that are issued during a precompile of an embedded SQL program are not recorded. The QSYS2.SQL_ERROR_LOG view can be used to examine the information that has been collected. See SQL_ERROR_LOG view for the definition of the view.

Registering SQLCODEs with SELF

Users configure the SQLCODEs that should be processed by SELF through the SYSIBMADM.SELFCODES global variable. By default, this global variable is set to NULL for all jobs, which means that SELF is turned off and will not log anything. To enable SELF, the global variable needs to be set, providing one or more SQLCODE values. See SELFCODES global variable for more information.

The setting of the SELFCODES global variable applies to the SQL session where it is set. An SQL session is equivalent to an activation group. If SELFCODES has a default value, that value applies to all sessions in all user jobs. Changing the default value for the SELFCODES global variable does not affect active jobs. If, however, the user wants to enable SELF only in specific jobs, the SELFCODES global variable value can be changed in those jobs.

A string of SQLCODE values assigned to the SELFCODES global variable 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 commas between values.
Up to 32 SQLCODE values can be provided in the string. If more than 32 are specified, only the first 32 are used by SELF. No error is issued.
If a special value exists in the string, it must be the only entry in the string:
- The special value of *ERROR specifies all SQLCODEs that are error conditions (negative values).
- The special value of *WARN specifies all SQLCODEs that are warning conditions (positive values).
- The special value of *ALL specifies all SQLCODEs that are error or warning conditions.
- The special value of *NONE turns off SELF processing.
A string containing the value 0 turns off SELF processing, even if other SQLCODE values are found in the SELFCODES string.
A value of 100 or +100 is not allowed.
There is no verification that a specified SQLCODE is used by Db2 for i.
Any character other than a digit (0-9), minus, plus, blank, or comma is not valid and will remove all SQLCODEs from logging. Using the SYSIBMADM.VALIDATE_SELF scalar function to perform validation of a string to be used for SELFCODES is recommended. See VALIDATE_SELF scalar function for a description of this function.

Setting SELFCODES within a specific job

To enable SELF only within a specific job, the SELFCODES global variable must be changed within that job.

For example, to set the list of SQLCODEs within a job, use the SQL SET statement. This has no effect on other jobs. Invoke the VALIDATE_SELF function to verify the string is properly formed.

SET SYSIBMADM.SELFCODES = SYSIBMADM.VALIDATE_SELF('-514, -204, -501, +30, -199');

Each time the SELFCODES global variable is set, the new list of SQLCODE values replaces any SQLCODEs that were previously being used.

Setting SELFCODES for all user jobs

When a job first uses SQL, its value for the SELFCODES global variable is assigned using the default value specified for the SELFCODES global variable. When the default is changed, it will apply to jobs that start after the change is made.

To change the default for the SELFCODES global variable, use the CREATE OR REPLACE VARIABLE SQL statement. Note that the data type of the global variable must remain VARCHAR(256).

CREATE OR REPLACE VARIABLE SYSIBMADM.SELFCODES VARCHAR(256) 
                  DEFAULT '-514, -204, -501, +30, -199';

SELF data repository

When an SQL statement completes execution, SELF will determine whether the SQLCODE value matches the list of SQLCODE values specified within the SELFCODES global variable. If the SQLCODE is in the list that is being recorded for the session, information about the failure and the environment is collected and written to the QSYS2.SQL_ERRORT table.

QSYS2.SQL_ERRORT - This is the master repository for SELF detail. SELF inserts or updates rows in this table as SQL statements complete with an SQLCODE that matches a value specified within SELFCODES. By default, *PUBLIC is set to *EXCLUDE for SQL_ERRORT.

QSYS2.SQL_ERROR_LOG - This view is the preferred interface for accessing SELF detail. To access the data within SQL_ERROR_LOG, the caller must have *ALLOBJ special authority or be authorized to the QIBM_DB_SQLADM function usage ID.

There are five details that determine whether SELF will insert a new row or update an existing row in SQL_ERRORT:

Application library name
Application program name
Application module name
SQLCODE
SQL statement text

If these key values are the same as a row that has already been logged, the existing row is updated with information about the new occurrence of the error. The number of times the specific error has been encountered is incremented. Otherwise, a new row is inserted into SQL_ERRORT.

If any errors occur while attempting to update SQL_ERRORT, information is not recorded.

If SQL_ERRORT does not exist, it will be recreated.

Viewing the SELF log

The QSYS2.SQL_ERROR_LOG view should be used to look at the entries that are recorded by SELF. This view requires the user to be authorized to the QIBM_DB_SQLADM function usage ID or have *ALLOBJ special authority.

To find all entries recorded for a specific application program, the following query can be used.

SELECT * FROM QSYS2.SQL_ERROR_LOG
  WHERE PROGRAM_LIBRARY = 'PGMLIB' AND PROGRAM_NAME = 'PGM1'
  ORDER BY LOGGED_TIME DESC;

To find all entries where the same error has occurred for the same statement in the same program, the following query can be used.

SELECT * FROM QSYS2.SQL_ERROR_LOG
  WHERE NUMBER_OCCURRENCES > 1
  ORDER BY NUMBER_OCCURRENCES DESC;

Maintaining the SELF log

Similar to the Index Advisor table (QSYS2.SYSIXADV), the database only inserts or updates rows in the SELF log file (QSYS2.SQL_ERRORT). Rows are never deleted.

The user of SELF must decide when and if rows within SQL_ERRORT should be deleted. For example, maybe SELF was being used to review and evaluate SQL application failures before releasing a new version of an application. After an initial set of problems are understood and resolved, the application team could ask for SQL_ERRORT to be cleared of historical SELF detail before another iteration of testing occurs.

To remove rows from the table haven’t had a new occurrence logged in the last 30 days:

DELETE FROM QSYS2.SQL_ERRORT
  WHERE DATE(LOGGED_TIME) < CURRENT DATE - 30 DAYS;

SELF example

This example shows how SELF can be used to capture point-of-failure detail for SQL statements that are failing because they cannot acquire the necessary locks and for SQL statements that are failing due to lack of authority. The SQLCODEs the example will capture details for are:

SQLCODE -913 : Row or object &1 in &2 type *&3 in use.
SQLCODE +551 : Not authorized to object &1 in &2 type *&3.
SQLCODE -551 : Not authorized to object &1 in &2 type *&3.
SQLCODE +552 : Not authorized to &1.
SQLCODE -552 : Not authorized to &1.

Step 1. Build a SELF control string

Use the VALIDATE_SELF scalar function to confirm that the SELF control string is well-formed.

VALUES SYSIBMADM.VALIDATE_SELF('-913,   -551, -552, +551,+552');

Figure 1. Result string from VALIDATE_SELF

Step 2. Set up SELF at the system level

To enable SELF to work across all future user jobs, the SYSIBMADM.SELFCODES global variable needs to be recreated using a DEFAULT string set to the SQLCODE values to log. The string that was returned from step 1 is used, since we know it is a valid string.

CREATE OR REPLACE VARIABLE SYSIBMADM.SELFCODES VARCHAR(256) 
                  DEFAULT '551, 552, -551, -552, -913';

Step 3. Monitor results recorded by SELF

After letting SELF run for a user-determined period of time, check the SELF log.

Notice how the SELF log includes deep details on who, what, and when the failure occurred. There’s even detail regarding the values of the client special registers at the point of failure.

When a failure occurs multiple times, SELF shows not only the total occurrence count, but also detail about both the first and most recent failure occurrence.

To examine the errors that were set to be captured, run the following query:

SELECT *
  FROM QSYS2.SQL_ERROR_LOG
  WHERE LOGGED_SQLCODE IN (551, 552, -551, -552, -913)
  ORDER BY LOGGED_TIME DESC;

The following query can be used to examine the stack at the point of failure for every -551 authorization failure for a DELETE statement.

WITH SELF_ROWS AS (SELECT ROW_NUMBER() OVER() AS INSTANCE, LOG.* FROM QSYS2.SQL_ERROR_LOG LOG
                 WHERE LOGGED_SQLCODE = -551 AND
                       STATEMENT_OPERATION = 'DL') 

SELECT INSTANCE, STATEMENT_TEXT, A.*
  FROM SELF_ROWS,
       JSON_TABLE(INITIAL_STACK, 'lax $.initial_stack[*]'
              COLUMNS(
                 ORDINAL_POSITION INTEGER PATH 'lax $."ORD"',
                 PROGRAM_TYPE VARCHAR(10) CCSID 1208 PATH 'lax $."TYPE"',
                 PROGRAM_LIB  VARCHAR(10) CCSID 1208 PATH 'lax $."LIB"',
                 PROGRAM_NAME  VARCHAR(10) CCSID 1208 PATH 'lax $."PGM"',
                 MODULE_NAME  VARCHAR(10) CCSID 1208 PATH 'lax $."MODULE"',
                 PROCEDURE_NAME  VARCHAR(128) CCSID 1208 PATH 'lax $."PROC"',
                 STATEMENT_NUMBER  VARCHAR(10) CCSID 1208 PATH 'lax $."STMT"',
                 ACTIVATION_GROUP  VARCHAR(10) CCSID 1208 PATH 'lax $."ACTGRP"'
               )
             ) A 
       ORDER BY INSTANCE, A.ORDINAL_POSITION;