IBM i: The SQL Way · #13

Find All IBM i File Overrides in the Current Job

Use SYSTOOLS.OVERRIDE_INFO_ALL to list every database, display, and printer file override active at the current call level of an IBM i job.

An IBM i program can open the correct file name and still access the wrong object, member, output queue, or device because an override is active in the job. SYSTOOLS.OVERRIDE_INFO_ALL exposes every current-call-level file override as SQL rows.

The view is:

SYSTOOLS.OVERRIDE_INFO_ALL

It returns information similar to:

DSPOVR FILE(*ALL)

for the current job.

Basic query

SELECT *
FROM SYSTOOLS.OVERRIDE_INFO_ALL;

The result contains one row for each active file override at the current call level.

Focused query

SELECT
    FILE_NAME,
    LEVEL,
    TYPE,
    OVERRIDE_LIBRARY,
    OVERRIDE_FILE,
    KEYWORD_SPECIFICATIONS
FROM SYSTOOLS.OVERRIDE_INFO_ALL
ORDER BY
    FILE_NAME,
    LEVEL;

The most useful columns are:

FILE_NAME
LEVEL
TYPE
OVERRIDE_LIBRARY
OVERRIDE_FILE
KEYWORD_SPECIFICATIONS

Example: database file override

Create an override:

OVRDBF FILE(CUSTOMER) +
       TOFILE(TESTLIB/CUSTOMER) +
       MBR(TESTDATA) +
       OVRSCOPE(*JOB)

Query it from SQL running in the same job:

SELECT
    FILE_NAME,
    LEVEL,
    TYPE,
    OVERRIDE_LIBRARY,
    OVERRIDE_FILE,
    KEYWORD_SPECIFICATIONS
FROM SYSTOOLS.OVERRIDE_INFO_ALL
WHERE FILE_NAME = 'CUSTOMER';

The override details can reveal:

• the overriding library and file
• the scope or call level
• the member
• open options
• other override keywords

Find overrides redirecting to a specific library

SELECT
    FILE_NAME,
    OVERRIDE_LIBRARY,
    OVERRIDE_FILE,
    LEVEL,
    TYPE,
    KEYWORD_SPECIFICATIONS
FROM SYSTOOLS.OVERRIDE_INFO_ALL
WHERE OVERRIDE_LIBRARY = 'TESTLIB'
ORDER BY FILE_NAME;

This is useful when a test library unexpectedly appears in a production or batch job.

Find all overrides for one file

SELECT
    FILE_NAME,
    LEVEL,
    TYPE,
    OVERRIDE_LIBRARY,
    OVERRIDE_FILE,
    KEYWORD_SPECIFICATIONS
FROM SYSTOOLS.OVERRIDE_INFO_ALL
WHERE FILE_NAME = 'ORDERS'
ORDER BY LEVEL;

An override at a different call level may explain why one program sees it while another program in the same job does not.

Search the returned keywords

KEYWORD_SPECIFICATIONS contains the keyword detail produced by the underlying override information.

Find overrides referencing one output queue:

SELECT
    FILE_NAME,
    LEVEL,
    TYPE,
    KEYWORD_SPECIFICATIONS
FROM SYSTOOLS.OVERRIDE_INFO_ALL
WHERE UPPER(KEYWORD_SPECIFICATIONS)
      LIKE '%OUTQ(MYLIB/MYOUTQ)%';

Find database-file member overrides:

SELECT
    FILE_NAME,
    OVERRIDE_LIBRARY,
    OVERRIDE_FILE,
    KEYWORD_SPECIFICATIONS
FROM SYSTOOLS.OVERRIDE_INFO_ALL
WHERE UPPER(KEYWORD_SPECIFICATIONS)
      LIKE '%MBR(%';

The keyword text can be truncated. When the returned list is incomplete, IBM ends the value with an ellipsis.

Do not treat the keyword string as a permanently stable machine-readable contract. Use it for support visibility and targeted diagnostics.

Why the current job matters

Overrides are job-scoped and call-level-sensitive.

A query from ACS Run SQL Scripts runs in the ACS database server job, not in an unrelated batch or interactive job.

Therefore:

Querying OVERRIDE_INFO_ALL in ACS
does not show overrides from another job.

To inspect a problem job, the query must run inside that job or the job must expose its override state through an application-specific diagnostic interface.

This view is especially useful inside:

• an SQLRPGLE diagnostic program
• a stored procedure called by the affected application
• a support command that runs in the current job
• a job-start or job-end diagnostic capture
• an exception handler that records job state

Capture overrides before they disappear

Create a history table:

CREATE TABLE MYTOOLS.OVERRIDE_SNAPSHOT
(
    CAPTURE_TIMESTAMP      TIMESTAMP NOT NULL,
    QUALIFIED_JOB_NAME     VARCHAR(28) NOT NULL,
    FILE_NAME              VARCHAR(10),
    OVERRIDE_LEVEL         VARCHAR(7),
    OVERRIDE_TYPE          VARCHAR(3),
    OVERRIDE_LIBRARY       VARCHAR(10),
    OVERRIDE_FILE          VARCHAR(10),
    KEYWORD_SPECIFICATIONS VARCHAR(1000)
);

Capture the current job’s overrides:

INSERT INTO MYTOOLS.OVERRIDE_SNAPSHOT
SELECT
    CURRENT TIMESTAMP,
    QSYS2.JOB_NAME,
    FILE_NAME,
    LEVEL,
    TYPE,
    OVERRIDE_LIBRARY,
    OVERRIDE_FILE,
    KEYWORD_SPECIFICATIONS
FROM SYSTOOLS.OVERRIDE_INFO_ALL;

This can preserve evidence before:

• the call stack returns
• DLTOVR removes the override
• the job ends
• a later program changes the override

Detect unexpected overrides

SELECT
    FILE_NAME,
    OVERRIDE_LIBRARY,
    OVERRIDE_FILE,
    KEYWORD_SPECIFICATIONS
FROM SYSTOOLS.OVERRIDE_INFO_ALL
WHERE COALESCE(OVERRIDE_LIBRARY, '') NOT IN
      (
          '',
          'PRODLIB',
          'QSYS'
      );

The approved-library list must match the application’s design.

An override outside the list is not automatically malicious or wrong, but it deserves review.

Use it before a critical process

An application can validate that no unexpected override is present:

BEGIN
    IF EXISTS
       (
           SELECT 1
           FROM SYSTOOLS.OVERRIDE_INFO_ALL
           WHERE FILE_NAME = 'PAYMENT'
       )
    THEN
        SIGNAL SQLSTATE '75001'
           SET MESSAGE_TEXT =
               'Unexpected override exists for PAYMENT';
    END IF;
END;

This can protect a sensitive process from running under an unintended file redirection.

Use this technique carefully. Some legitimate job descriptions, commands, or frameworks intentionally establish overrides.

Native command versus SQL

Use:

DSPOVR FILE(*ALL)

when:

• working interactively in the affected job
• reviewing one file from a command line
• the native display is the fastest path

Use OVERRIDE_INFO_ALL when:

• override data must be stored
• an application must validate current state
• support diagnostics need selected columns
• the result must be included in a report
• current-job override state must be captured automatically

Implementation note

OVERRIDE_INFO_ALL is delivered in SYSTOOLS as an example of returning command outfile information through SQL.

Its authority requirements are determined by the IBM i interfaces used in the implementation.

You can inspect the generated SQL source in ACS and use it as a model for a customized user-owned service.

Do not modify the IBM-supplied SYSTOOLS object directly.

Release requirement

IBM lists SYSTOOLS.OVERRIDE_INFO_ALL among current IBM i SQL service enhancements for:

IBM i 7.6 — Db2 Group PTF SF99960 Level 3
IBM i 7.5 — Db2 Group PTF SF99950 Level 12

A practical troubleshooting workflow

1. Confirm which job is experiencing the problem.
2. Run the override query inside that job.
3. Review file, type, level, target library, and target file.
4. Inspect keyword specifications.
5. Compare with the job description and application design.
6. Capture a snapshot before changing anything.
7. Remove or correct only the unintended override.
8. Retest the affected file open or output operation.

Final takeaway

SYSTOOLS.OVERRIDE_INFO_ALL answers a deceptively important question:

What file overrides are actually in effect for this job right now?

That can explain why an application reads unexpected data, writes to the wrong member, sends output to an unexpected queue, or behaves differently between two otherwise similar jobs.