The SQL Error Logging Facility (SELF) is a new tool that can help you find and understand SQL errors and warnings.

SQL has become the standard method for communication with your database. You can use SQL to create and modify your database tables, insert or update data in your table, or access data in your database. More-complex SQL features allow you to create triggers, procedures, and functions to perform more-complicated operations. Additionally, IBM i SQL services provide you with new ways of interacting with everything IBM i that isn’t the database. However, if an error occurs while using SQL to perform these tasks, it can cause significant problems in your applications. It thus becomes very important to proactively find and fix these errors before they become problems.

When you are using SQL and a problem occurs, either an SQL error message or SQL warning message is returned to your application. These errors and warnings contain an SQLSTATE, a corresponding SQLCODE, and text that describes the problem that occurred. For example, if you are attempting to insert into a table, and the table is locked by another job, a message is returned with an SQLCODE of SQL0913, SQLSTATE 57033, and a message stating, “Row or object MY_TABLE in MY_LIBRARY type *FILE in use.”

These messages can be investigated and the problem fixed; however, it can be easy to overlook errors, especially when the problem you are trying to understand is intermittent. Sometimes just finding the problem is the first hurdle to clear before you can begin to understand why the SQL error or warning occurred. We cannot fix a problem until we find it and understand it.

The SQL Error Logging Facility (SELF) is a new tool that can help you find and understand SQL errors and warnings. SELF can capture additional detail about specific SQL errors and warnings when they occur. Information that is collected when an SQL error or warning occurs includes the SQLCODE, SQLSTATE, failing statement text, information about the program that took the error, when it occurred, the job it occurred in, the current user, the stack at the time of the failure, and much, much more.

To use SELF, you first must identify which error messages and warning message you want to gather information for. Once you’ve identified the messages, determine those messages’ corresponding SQLCODEs. An error SQLCODE starts with a negative sign (-). A warning SQLCODE starts with a plus sign (+). The listing of SQL messages (https://www.ibm.com/docs/en/i/7.5?topic=codes-listing-sql-messages) can be used to identify the SQLCODE that matches a specific message. For the intermittent lock failure described earlier, search the listing of SQL messages for SQL0913. This will return a table that defines the corresponding SQLCODE as -913. SELF allows you to identify one or multiple SQLCODEs to gather information for.

Once you have identified the SQLCODEs you want to gather information about, simply configure SELF to collect information when it encounters one of those SQLCODEs. In order to do this configuration, set the SYSIBMADM.SELFCODES global variable to the SQLCODES you want to gather information about. The SELFCODES global variable can be set for a single job or for the entire system.

Let’s look at how we would set it for a single job. To enable SELF in a single job, set the SELFCODES global variable in that job. For example, if we want to monitor for the SQL0913 in our job, we would set SELFCODES in the job by running the following SQL SET statement.

SET SYSIBMADM.SELFCODES = SYSIBMADM.VALIDATE_SELF('-913');

The VALIDATE_SELF scalar function is used to ensure that the string being used to set SELFCODES is valid.

Once the value is set, you can run the application that contains the problematic SQL statements. SELF will collect information at the point where the failure occurred and log that information. After the information is collected, you can analyze it more fully by querying the QSYS2.SQL_ERROR_LOG view. This information can help you to pinpoint where and when the problem occurred. To query the SQL_ERROR_LOG, run the following query:

SELECT LOGGED_SQLCODE,
       STATEMENT_TEXT,
       LOGGED_TIME,
       JOB_NAME
FROM QSYS2.SQL_ERROR_LOG
ORDER BY LOGGED_TIME DESC;

This query returns the following:

Many more columns can queried, including LOGGED_SQLSTATE, NUMBER_OCCURRENCES, STATEMENT_OPERATION, STATEMENT_OPERATION_DETAIL, REASON_CODE, PROGRAM_LIBRARY, PROGRAM_NAME, PROGRAM_TYPE, MODULE_NAME, ADOPTED_USER_NAME, USER_NAME, SYSTEM_USER_NAME, CLIENT_ACCTNG, CLIENT_APPLNAM, CLIENT_PROGRAMID, CLIENT_USERID, CLIENT_WRKSTNNAME, RDB_NAME, INITIAL_LOGGED_TIME, INITIAL_JOB_NAME, INITIAL_ADOPTED_USER_NAME, and INITIAL_STACK.

You can also set the SELFCODES for all user jobs. This is useful when there are errors or warnings that you always want to gather information about—for example, authorization failures. This is done by setting the default value for SELFCODES. The first time you run an SQL statement in a job, the SEFLCODES global variable in the job is set to SELFCODES default value. If you change the default value for the global variable, then all jobs that start after the change is made will use the new value. Use the CREATE OR REPLACE VARIABLE statement to change the default value for SELFCODES. If I wanted all jobs to gather information when the job encounters either an SQL0551 “Not authorized to object &1 in &2 type *&3” or an SQL0552 “Not authorized to &1”, I would run the following SQL statement:

CREATE OR REPLACE VARIABLE SYSIBMADM.SELFCODES VARCHAR(256)

DEFAULT '551, 552, -551, -552';

SELF is very easy to use and, importantly, safe to run in production. This is because SELF reacts only when it encounters an SQLCODE that you requested to monitor for. It does not have any performance impacts on SQL statements that run successfully or on statements that fail with an SQLCODE that does not match with the ones being monitored for.

For more information about SELF, visit https://www.ibm.com/docs/en/i/7.5?topic=tools-sql-error-logging-facility-self. SELF is available in IBM i 7.5 TR1 and IBM i 7.4 TR7.

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions