IBM i Operations

How to Diagnose Intermittent SQL Failures on IBM i After the Job Ends

A practical workflow for using the Db2 for i SQL Error Logging Facility to preserve and analyze SQL failures after the original IBM i job has ended.

SA

Saurabh Arora

IBM i Solution Architect | Technical Lead | Modernization & Integration Specialist

Published July 26, 20268 min read
IBM iDb2 for iSQLSQL_ERROR_LOGSELFProduction SupportTroubleshooting

An SQL failure occurs in production, the application reports a generic error, and the job ends before anyone begins investigating. By the time support arrives, the most useful point-of-failure evidence may already be gone. The Db2 for i SQL Error Logging Facility can preserve that evidence for selected SQL errors and warnings.

The facility is commonly called:

SELF

The preferred SQL interface for reviewing captured information is:

QSYS2.SQL_ERROR_LOG

SELF is not a replacement for application error handling, job logs, audit journaling, or SQL performance monitoring. It is an additional diagnostic layer for failures that are intermittent, workload-dependent, profile-specific, or difficult to reproduce.

The production-support problem

A common sequence looks like this:

1. A user submits a request.
2. An SQL statement fails.
3. The application catches or hides the failure.
4. The job ends or continues processing.
5. Support is contacted later.
6. The exact SQL context is no longer available.

The team may know only that SQLCODE -913 occurred sometime during the morning.

That does not answer:

SELF helps preserve evidence from the moment the configured SQL condition occurred.

What SELF records

SELF is configured with the SQLCODE values Db2 for i should monitor.

When a user-initiated SQL statement completes with a matching SQLCODE, Db2 for i can preserve point-of-failure information in its repository.

Review the information through:

SELECT *
FROM QSYS2.SQL_ERROR_LOG;

Depending on the failure and execution context, the view can include:

The statement text can be UNKNOWN when it is not available.

Repeated failures are grouped

SELF uses identifying details around the application, SQL condition, and statement to decide whether a new failure should create a row or update an existing one.

On IBM i 7.6 with Db2 Group PTF SF99960 Level 3 or later, and IBM i 7.5 with SF99950 Level 12 or later, the duplicate-row key includes:

The statement-operation element was added at those PTF levels. On earlier supported PTF levels, it is not part of the duplicate-row key.

When the identifying details match an existing entry, Db2 for i can update the existing row and increment its occurrence count. Otherwise, SELF inserts a new row.

This makes the facility useful for answering:

Which SQL failures are repeating most frequently?

SELECT
    LOGGED_SQLCODE,
    LOGGED_SQLSTATE,
    PROGRAM_LIBRARY,
    PROGRAM_NAME,
    MODULE_NAME,
    NUMBER_OCCURRENCES,
    LOGGED_TIME,
    STATEMENT_TEXT
FROM QSYS2.SQL_ERROR_LOG
WHERE NUMBER_OCCURRENCES > 1
ORDER BY
    NUMBER_OCCURRENCES DESC,
    LOGGED_TIME DESC;

A high count does not automatically mean the issue is the most severe. It means the failure should be classified:

Expected and handled
Known technical debt
Misconfiguration
Authority issue
Concurrency problem
Data-quality defect
Programming defect
Unexplained production failure

Start with a narrow objective

Do not begin by collecting every possible SQL error and warning simply because broad settings exist.

Start with a specific problem:

We need to understand recurring lock failures.
We need to identify authority errors after a security change.
We need to find cursor-state failures in one application.
We need to determine which statement is producing a recurring warning.

Then select only the SQLCODE values that support that investigation.

IBM’s example uses lock and authority conditions:

-913
-551
-552
+551
+552

Validate the control string first:

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

Validation proves that the control string is properly formed. It does not prove that every selected SQLCODE is appropriate for your environment.

Enable SELF for one job

For a limited test, set the session-scoped global variable inside the current job:

SET SYSIBMADM.SELFCODES =
    SYSIBMADM.VALIDATE_SELF(
        '-913, -551, -552, +551, +552'
    );

This affects the current job.

That makes job-level enablement useful for:

Confirm the active value:

VALUES SYSIBMADM.SELFCODES;

Enable SELF for future user jobs

System-wide enablement is performed by replacing the default of the global variable:

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

Treat this as a production configuration change.

Document:

Stop SELF processing

Use the special value:

*NONE

Example:

CREATE OR REPLACE VARIABLE SYSIBMADM.SELFCODES
    VARCHAR(256)
    DEFAULT '*NONE';

Prepare the rollback command before broad enablement.

Review recent failures

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

Use this to identify:

Focus on one application

SELECT
    LOGGED_TIME,
    LOGGED_SQLCODE,
    LOGGED_SQLSTATE,
    PROGRAM_LIBRARY,
    PROGRAM_NAME,
    MODULE_NAME,
    NUMBER_OCCURRENCES,
    STATEMENT_TEXT
FROM QSYS2.SQL_ERROR_LOG
WHERE PROGRAM_LIBRARY = 'APPLIB'
  AND PROGRAM_NAME = 'ORDERPGM'
ORDER BY LOGGED_TIME DESC;

This helps when one application team owns the investigation or one program changed recently.

Find the most frequent failures

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

This can expose failures that are being handled or ignored so consistently that nobody notices their frequency.

Examples include:

Connect failures to deployments

SELF becomes more useful when it is correlated with your own deployment history.

A practical design can associate:

That allows support to ask:

Did this SQLCODE begin after a specific release?
Did the occurrence rate increase after deployment?
Is the problem isolated to one module version?

The exact join depends on the organization’s release model, but the operational principle is the same: connect failure evidence to change history.

Combine historical and current lock information

SELF can show that a statement repeatedly encountered:

SQLCODE -913

Current lock state can be investigated through services such as:

QSYS2.OBJECT_LOCK_INFO

Related Era of i guides:

SELF explains what failed historically.

Lock services explain what is locked now.

They answer different questions.

Protect sensitive statement text

STATEMENT_TEXT can contain information that should not be broadly exposed.

Examples include:

Authority to QSYS2.SQL_ERROR_LOG depends on the IBM i release and Db2 Group PTF level.

On IBM i 7.6 with SF99960 Level 3 or later, and IBM i 7.5 with SF99950 Level 12 or later, a user can see SELF rows where their user profile matches one of these columns:

USER_NAME
ADOPTED_USER_NAME
INITIAL_ADOPTED_USER_NAME

To see all rows for all users, the caller must still have either:

*ALLOBJ

or authorization to:

QIBM_DB_SQLADM

IBM i 7.4 does not support the enhancement that lets users view only the SELF rows associated with their own profile, so the broader authority requirement still applies there.

Do not grant excessive authority merely to simplify support access.

A restricted reporting view can omit sensitive columns:

CREATE OR REPLACE VIEW SUPPORT.SQL_ERROR_SUMMARY AS
SELECT
    LOGGED_TIME,
    LOGGED_SQLCODE,
    LOGGED_SQLSTATE,
    PROGRAM_LIBRARY,
    PROGRAM_NAME,
    MODULE_NAME,
    NUMBER_OCCURRENCES,
    JOB_NAME
FROM QSYS2.SQL_ERROR_LOG;

Review the authority model for both the view and the underlying data.

Plan retention

SELF stores repository data in:

QSYS2.SQL_ERRORT

QSYS2.SQL_ERROR_LOG is the preferred view for reading the information.

Automatic SELF retention is available on:

IBM i 7.6 - SF99960 Level 2 or later
IBM i 7.5 - SF99950 Level 11 or later

At those levels, Db2 for i can automatically prune dormant rows in QSYS2.SQL_ERRORT using:

SYSIBMADM.QIBM_SELF_BY_DAYS

The shipped default is 365 days. IBM’s current Db2 for i enhancement matrix lists this automatic-pruning enhancement as not supported on IBM i 7.4.

For systems without that enhancement, or where a different cleanup policy is required, a manual cleanup pattern is:

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

Before adding cleanup, review:

Retention is both a storage decision and an evidence-management decision.

Production-performance considerations

IBM documents SELF as safe for production use because it does not affect statements that complete successfully or errors and warnings whose SQLCODE values do not match the configured list.

Focused configuration is still important.

Operational impact depends on:

A narrow SQLCODE list is easier to understand, review, retain, and act upon.

SELF is not complete observability

SELF does not automatically provide:

A mature design can connect SELF to:

The SQL repository becomes one input into a broader observability model.

A practical rollout plan

Phase 1 - Development validation

Select one SQLCODE.
Enable SELF in one job.
Reproduce the failure.
Review SQL_ERROR_LOG.
Confirm sensitive-data handling.
Disable the session setting.

Phase 2 - Controlled production investigation

Select a small approved SQLCODE list.
Document the investigation objective.
Define a start and end date.
Enable for future jobs.
Review results daily.
Classify repeated failures.
Disable or narrow the list.

Phase 3 - Operational adoption

Define permanent SQLCODE categories.
Assign application owners.
Set retention.
Create protected reporting views.
Integrate with deployment history.
Add alert thresholds.
Review trends.
Update development standards.

Questions to answer before enabling SELF

Which problem are we trying to solve?
Which SQLCODEs support that investigation?
Could statement text contain sensitive data?
Who can view the results?
How long will the information be retained?
Who reviews repeated failures?
How will findings become code or configuration changes?
How will the facility be disabled?

Without ownership and follow-up, diagnostic collection can become another repository that grows without improving the application.

When SELF is especially valuable

SELF is a strong fit when:

When another tool may be better

Use job logs when the exact job still exists and surrounding messages are essential.

Use SQL Performance Monitors when statement duration, resource use, and access-plan behavior are the main concern.

Use application logging when business context, transaction identifiers, and recovery decisions must be preserved.

Use audit journaling when the investigation is primarily security related.

These tools complement one another.

Detailed SQL reference

For the complete SQL-oriented implementation guide, see:

Find and Analyze IBM i SQL Errors with SQL_ERROR_LOG

Final takeaway

The most difficult production failures are often the ones that happen:

occasionally
under load
for one user
in one job
with one unexpected value
after support has stopped watching

SELF gives IBM i teams a way to preserve selected SQL point-of-failure evidence after the original moment has passed.

The value is not in collecting every possible error.

The value is in collecting the right evidence, protecting it, assigning ownership, and converting repeated failures into permanent improvements.

Comments

Share your thoughts, questions, or real-world IBM i experiences related to this article.