Re: Navigator show *SYSOPR reply?

[Justin Taylor <jtaylor.0ab@xxxxxxxxx>]

That works.  This is the best workaround I've found so far.  Thank you



On Wed, Aug 6, 2025 at 3:37 PM Michael Mayer via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

> Here's the SQL I use as the QSYSOPR detailed message info solution.
> It's listed on my blog on the More Helpful Tips page.
> or  ....
>
> Here's a neat SQL that will tell you who answered a QSYSOPR message.
>
> Much easier than searching the message queue or QHST LOG.
>
> Change the date range as needed or run a single date:
>
> Suggested to run in ACS "Run SQL Scripts".
>
> Will also run in STRSQL.
>
> Note: In STRSQL, run either the single date or the range of dates.
>
>     Comment lines are NOT ignored in STRSQL.
>
> --- Who answered the message? ---
> --- Columns prefixed with the "A" come from the error result, and those
> "B" from the reply result. ---
>
> SELECT A.MESSAGE_TIMESTAMP AS "Date/Time of Error Msg",
>        A.MESSAGE_ID AS "Msg ID",
>        A.FROM_JOB AS "Job Generated Error Msg",
>        A.MESSAGE_TEXT AS "Msg Text",
>        B.MESSAGE_TIMESTAMP AS "Reply Date/Time",
>        SUBSTR(B.MESSAGE_TEXT, 1, 10) AS "Reply",
>        A.FROM_USER AS "Reply by"
>     FROM TABLE (QSYS2.MESSAGE_QUEUE_INFO(QUEUE_LIBRARY => 'QSYS',
>                 QUEUE_NAME => 'QSYSOPR',
>                 SEVERITY_FILTER => 99)) A,
>          LATERAL (SELECT MESSAGE_TIMESTAMP,
>                     MESSAGE_TEXT, FROM_USER
>                  FROM TABLE (QSYS2.MESSAGE_QUEUE_INFO(QUEUE_LIBRARY =>
> 'QSYS',
>                              QUEUE_NAME => 'QSYSOPR',
>                              SEVERITY_FILTER => 99))
>
> ---  Single Date
> ---     WHERE CHAR(A.MESSAGE_TIMESTAMP) LIKE '%2025-01-15%'
> ---         AND ASSOCIATED_MESSAGE_KEY = A.MESSAGE_KEY) B
>
> ---  Range of Dates
>                 WHERE CHAR(A.MESSAGE_TIMESTAMP) >= '2025-01-07' AND
>                               CHAR(A.MESSAGE_TIMESTAMP) <= '2025-01-16'
>                   AND ASSOCIATED_MESSAGE_KEY = A.MESSAGE_KEY) B
>
>      ORDER BY "Date/Time of Error Msg" ASC
>
>
> Respectfully,
> Michael Mayer
> IBM i on Power System Admin
> ERMCO-ECI
> 2225 Industrial Rd
> Dyersburg, Tennessee 38024
> Cell, Office and Text Messages: 518.641.8906
> Email: michael.mayer@xxxxxxxxxxxxx
> https://www.ermco-eci.com
> https://ermcopower.com/
> IBM i Award Winning Blog Site: https://ibmireference.blogspot.com
> Awarded IBM 2024/2025 Advocate, Contributor, Influencer and IBM i Ready
> Badges.