Steps

Core Query

This query aggregates messages by MESSAGE_ID, returns representative text and job, and counts occurrences:

Step 1:  Using the Run SQL Scripts tool, run the following SQL Statement:

SELECT 
    MESSAGE_ID,
    MIN(MESSAGE_TEXT) AS MESSAGE_TEXT,
    MIN(FROM_JOB) AS FROM_JOB,
    COUNT(*) AS MESSAGE_COUNT
FROM TABLE (
    QSYS2.MESSAGE_QUEUE_INFO(
        QUEUE_NAME    => 'QSYSOPR',
        QUEUE_LIBRARY => 'QSYS'
    )
)
GROUP BY MESSAGE_ID
ORDER BY MESSAGE_COUNT DESC,
         MESSAGE_ID;

Sample Results

What This Returns

• MESSAGE_ID: CPF/CPA/CPD message identifier (e.g., CPF0907)

• MESSAGE_TEXT: A representative text for that ID

• FROM_JOB: A representative sending job (job/user/number)

• MESSAGE_COUNT: Total occurrences of the ID in QSYSOPR for the queried scope

Interpreting the Results

• High MESSAGE_COUNT values indicate noisy or problematic conditions to prioritize.

• Repeated messages from the same FROM_JOB suggest job-level or program logic issues.

• Use drill-downs below for deeper attribution by job, program, time window, or severity.

Drill-Down Queries

1) Top Message IDs with All Sending Jobs

Breaks down counts by MESSAGE_ID and FROM_JOB to attribute volume per job.

SELECT 
    MESSAGE_ID,
    FROM_JOB,
    COUNT(*) AS MESSAGE_COUNT
FROM TABLE (
    QSYS2.MESSAGE_QUEUE_INFO(
        QUEUE_NAME    => 'QSYSOPR',
        QUEUE_LIBRARY => 'QSYS'
    )
)
GROUP BY MESSAGE_ID, FROM_JOB
ORDER BY MESSAGE_COUNT DESC, MESSAGE_ID, FROM_JOB;

Sample Results:

2) Include Severity and a Time Window (e.g., Last 7 Days)

Filters to a recent window and enriches with severity and first/last seen timestamps.

WITH M AS (
    SELECT *
    FROM TABLE(
        QSYS2.MESSAGE_QUEUE_INFO(
            QUEUE_NAME    => 'QSYSOPR',
            QUEUE_LIBRARY => 'QSYS'
        )
    )
    WHERE MESSAGE_TIMESTAMP >= CURRENT_TIMESTAMP - 7 DAYS
)
SELECT 
    MESSAGE_ID,
    MIN(MESSAGE_TEXT)   AS SAMPLE_TEXT,
    COUNT(*)            AS MESSAGE_COUNT,
    MIN(SEVERITY)       AS MIN_SEVERITY,
    MAX(SEVERITY)       AS MAX_SEVERITY,
    MIN(MESSAGE_TIMESTAMP) AS FIRST_SEEN,
    MAX(MESSAGE_TIMESTAMP) AS LAST_SEEN
FROM M
GROUP BY MESSAGE_ID
ORDER BY MESSAGE_COUNT DESC, MESSAGE_ID;

Sample Results:

3) Top Sending Jobs Overall

Ranks jobs by total messages sent to QSYSOPR.

SELECT 
    FROM_JOB,
    COUNT(*) AS MESSAGE_COUNT
FROM TABLE (
    QSYS2.MESSAGE_QUEUE_INFO(
        QUEUE_NAME    => 'QSYSOPR',
        QUEUE_LIBRARY => 'QSYS'
    )
)
GROUP BY FROM_JOB
ORDER BY MESSAGE_COUNT DESC, FROM_JOB;

Sample Results: