IBM i: The SQL Way · #3

Find and Analyze IBM i SQL Errors with SQL_ERROR_LOG

Use the SQL Error Logging Facility and QSYS2.SQL_ERROR_LOG to capture, group, and investigate recurring Db2 for i errors with the failing statement, program, job, and call-stack context.

An SQL error in a job log tells us what failed once. The SQL Error Logging Facility can show how often it failed, which program issued the statement, the job and thread involved, and the SQL text that was running at the point of failure.

QSYS2.SQL_ERROR_LOG is the queryable view for the SQL Error Logging Facility, commonly called SELF.

SELF is useful when an SQL problem is:

• intermittent
• happening in several jobs
• difficult to reproduce
• buried inside a service program
• recurring with the same statement
• affecting production but disappearing before someone investigates

The view can provide more than an SQLCODE. It can include the SQLSTATE, statement text, program, module, job, thread, client information, initial call stack, and the number of times the same failure has occurred.

Important: the log must be enabled

SQL_ERROR_LOG does not automatically contain every SQL error on the system.

SELF only captures SQLCODE values configured through:

SYSIBMADM.SELFCODES

By default, the value is null and SELF is off.

Check the setting for the current SQL session:

VALUES SYSIBMADM.SELFCODES;

Validate the SQLCODE list

Before enabling SELF, validate the control string:

VALUES SYSIBMADM.VALIDATE_SELF(
    '-913, -904, -204, -551'
);

The example monitors:

-913  Lock or object contention
-904  Resource unavailable
-204  Object not found
-551  Not authorized

Choose SQLCODEs that matter to your application. Avoid collecting everything without first considering the volume and purpose of the data.

Enable SELF for the current session

To monitor only the current SQL session:

SET SYSIBMADM.SELFCODES =
    '-913, -904, -204, -551';

This is useful while reproducing a problem from ACS Run SQL Scripts or testing one application flow.

To stop SELF for the current session:

SET SYSIBMADM.SELFCODES = NULL;

On supported PTF levels, broader values can also be used:

SET SYSIBMADM.SELFCODES = '*ERROR';

SET SYSIBMADM.SELFCODES = '*WARN';

SET SYSIBMADM.SELFCODES = '*ALL';

Use the broader options carefully. Targeted SQLCODEs usually produce a cleaner troubleshooting data set.

Enable SELF for future jobs

A system-wide default affects jobs that instantiate the global variable after the change.

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

This is a configuration change, not a harmless reporting query.

Before changing the default:

• confirm the intended SQLCODE list
• test it in a limited scope
• document the change
• determine how long it should remain enabled
• understand who is authorized to alter the variable
• plan how the collected data will be reviewed

Primary query: recent SQL errors

SELECT
    LOGGED_TIME,
    LOGGED_SQLCODE,
    LOGGED_SQLSTATE,
    NUMBER_OCCURRENCES,
    JOB_NAME,
    THREAD_ID,
    PROGRAM_LIBRARY,
    PROGRAM_NAME,
    MODULE_NAME,
    STATEMENT_OPERATION_DETAIL,
    STATEMENT_TEXT
FROM QSYS2.SQL_ERROR_LOG
ORDER BY LOGGED_TIME DESC
FETCH FIRST 50 ROWS ONLY;

This gives a recent point-in-time view of captured SQL failures.

A single row may represent more than one occurrence. NUMBER_OCCURRENCES shows how many times the same SQLCODE and statement context has been matched.

Find the most frequent SQL errors

SELECT
    LOGGED_SQLCODE,
    LOGGED_SQLSTATE,
    SUM(NUMBER_OCCURRENCES) AS TOTAL_OCCURRENCES,
    COUNT(*) AS DISTINCT_CONTEXTS,
    MAX(LOGGED_TIME) AS MOST_RECENT_OCCURRENCE
FROM QSYS2.SQL_ERROR_LOG
GROUP BY
    LOGGED_SQLCODE,
    LOGGED_SQLSTATE
ORDER BY
    TOTAL_OCCURRENCES DESC,
    MOST_RECENT_OCCURRENCE DESC;

This helps separate:

• one unusual failure
• a repeating application defect
• an authorization problem affecting many jobs
• an object-lock problem occurring throughout the day

Investigate one SQLCODE

SELECT
    LOGGED_TIME,
    NUMBER_OCCURRENCES,
    JOB_NAME,
    THREAD_ID,
    PROGRAM_LIBRARY,
    PROGRAM_NAME,
    MODULE_NAME,
    REASON_CODE,
    STATEMENT_TEXT,
    INITIAL_STACK
FROM QSYS2.SQL_ERROR_LOG
WHERE LOGGED_SQLCODE = -913
ORDER BY LOGGED_TIME DESC;

For a lock-related SQLCODE, the call stack and program context may identify the application path that encountered the contention.

SELF captures the failure context. It does not replace current lock investigation through services such as:

QSYS2.OBJECT_LOCK_INFO
QSYS2.RECORD_LOCK_INFO

Find errors by program

SELECT
    PROGRAM_LIBRARY,
    PROGRAM_NAME,
    MODULE_NAME,
    LOGGED_SQLCODE,
    SUM(NUMBER_OCCURRENCES) AS TOTAL_OCCURRENCES,
    MAX(LOGGED_TIME) AS LAST_SEEN
FROM QSYS2.SQL_ERROR_LOG
WHERE PROGRAM_LIBRARY = 'MYLIB'
GROUP BY
    PROGRAM_LIBRARY,
    PROGRAM_NAME,
    MODULE_NAME,
    LOGGED_SQLCODE
ORDER BY
    TOTAL_OCCURRENCES DESC,
    LAST_SEEN DESC;

This is useful during an application release when you want to determine whether a specific library or program is producing new failures.

Find errors from the last 24 hours

SELECT
    LOGGED_TIME,
    LOGGED_SQLCODE,
    LOGGED_SQLSTATE,
    NUMBER_OCCURRENCES,
    JOB_NAME,
    PROGRAM_LIBRARY,
    PROGRAM_NAME,
    STATEMENT_TEXT
FROM QSYS2.SQL_ERROR_LOG
WHERE LOGGED_TIME >= CURRENT TIMESTAMP - 24 HOURS
ORDER BY LOGGED_TIME DESC;

Understanding the main columns

LOGGED_SQLCODE and LOGGED_SQLSTATE

These identify the SQL condition that was captured.

Negative SQLCODE values generally represent errors. Positive values generally represent warning conditions.

NUMBER_OCCURRENCES

SELF can update an existing row when the same error and statement context occurs again.

This column helps reveal frequency without requiring one physical log row per failure.

STATEMENT_TEXT

The SQL statement that encountered the condition.

It can contain UNKNOWN when the statement text is unavailable.

PROGRAM_LIBRARY, PROGRAM_NAME, and MODULE_NAME

These connect the SQL failure to the IBM i application object that issued the statement.

For ILE applications, the module name can narrow the investigation further than the program name alone.

JOB_NAME and THREAD_ID

The qualified job name and thread involved in the most recent occurrence.

Thread information is important for server jobs and multithreaded applications where the job name alone may not identify the exact execution context.

INITIAL_STACK

The call stack from the first occurrence represented by the row.

This can help when the most recent occurrence happened through a different execution path or after the original evidence disappeared from the job log.

Authority

Reading QSYS2.SQL_ERROR_LOG requires either:

*ALLOBJ special authority

or authorization to:

QIBM_DB_SQLADM

Changing global-variable defaults requires additional authority.

Do not grant broad authority only to make the query work. Use function-usage authorization and controlled support profiles where appropriate.

Retention

SELF writes its underlying data to:

QSYS2.SQL_ERRORT

The global variable below controls how long dormant entries are retained:

SYSIBMADM.QIBM_SELF_BY_DAYS

Check the configured value:

VALUES SYSIBMADM.QIBM_SELF_BY_DAYS;

The shipped default is 365 days on supported levels.

A shorter retention value can be configured:

CREATE OR REPLACE VARIABLE SYSIBMADM.QIBM_SELF_BY_DAYS
    INTEGER
    DEFAULT 30;

Treat this as a managed configuration change. Retention should match the organization’s troubleshooting, audit, and storage requirements.

Common reasons the query returns no rows

SELF is not enabled

Check:

VALUES SYSIBMADM.SELFCODES;

A null value means SELF is not capturing SQLCODEs for the session.

The failing SQLCODE is not configured

SELF captures only the SQLCODEs listed in SELFCODES, or the broader groups configured on supported levels.

The error occurred before SELF was enabled

SELF is not retroactive.

The failure happened during precompile

Errors generated while precompiling embedded SQL are not recorded by SELF.

The user lacks authority

The caller needs the required authority to read the view.

Native tools versus SELF

Use a job log when:

• the affected job is still available
• the failure is isolated
• you need the complete sequence of job messages
• the error can be reproduced immediately

Use SELF when:

• the problem is intermittent
• many jobs may encounter it
• you need statement and program context
• you want occurrence counts
• you need historical evidence after the original job has ended

The approaches complement each other.

A practical workflow

1. Identify the SQLCODEs worth monitoring.
2. Validate the SELFCODES control string.
3. Enable SELF in a limited scope first.
4. Reproduce or observe the failure.
5. Query SQL_ERROR_LOG by time, SQLCODE, and program.
6. Review statement text, call stack, job, and thread.
7. Fix the application, authority, object, or resource issue.
8. Confirm the failure no longer occurs.
9. Disable temporary logging or refine the SQLCODE list.
10. Review retention and remove data according to policy.

Final takeaway

QSYS2.SQL_ERROR_LOG turns selected Db2 for i errors into structured troubleshooting data.

The most important point is that the view is only one part of the solution.

You must first configure SELF to capture the SQLCODEs that matter, and then use the captured program, job, statement, thread, stack, and occurrence information to understand why the failure keeps happening.