How to Query the MSF Journal to Find Specific Entries

Resolving The Problem

https://www.ibm.com/support/pages/node/641533

Option 1: In this example, the LG is the 'type' and the 88 is the 'sub type' for the MSF journal entry.
 

SELECT *
FROM QGPL/EMAIL2
WHERE JOENTT = 'LG'
  AND JOESD LIKE '%88 DLV%'

Option 2: Here is another query to display all %88 and %82 successful sub type entries for the SMTP Client for a specific date and time range.
 

SELECT *
FROM QGPL/EMAIL2
WHERE JOENTT = 'LG'
  AND JODATE > '022426'
  AND JOTIME > '150100'
  AND (JOESD LIKE '%88 D%' OR JOESD LIKE '%82 R%')
ORDER BY JOSEQN

SELECT JOSEQN,
       JOENTT,
       CAST(JOESD AS VARCHAR(2000) CCSID 37) AS ENTRY_DATA
FROM QGPL.EMAIL2
WHERE JOENTT = 'LG' AND (JOESD LIKE '%88 DLV%' OR JOESD LIKE '%82 R%')
ORDER BY JOSEQN;

 

Option 4):  In Access Client Solutions (ACS)  you can run SQL statement:

This last query is provided As-Is (no guarantee). Test environment on R740 and above environments with QZMF journaling enabled. This query reconstructs complete email messages from IBM i mail journal entries and produces one clean row per email showing who sent it, who it was sent to, its size, and its final delivery outcome.

How it does this:

* Decodes the binary journal column (JOESD) so mail text can be read. 
* Extracts key mail attributes (message ID, sender, recipient, size, status) from individual journal rows. 
* Correlates all related fragments for the same email using the message ID plus a safe journal‑sequence window.
* Aggregates those fragments into one authoritative row per message, choosing the correct final status (FAILED DELIVERED SENT PENDING).

/* ============================================================================
   IBM i MAIL JOURNAL RECONSTRUCTION QUERY
   --------------------------------------
   OBJECTIVE:
     Produce ONE ROW PER MESSAGE_ID (one logical email) from asynchronous,
     multi-row IBM i mail journal entries (QSYSARB / QMSF / QTMS*).

   IMPORTANT CONTEXT:
     - IBM i mail processing is asynchronous by design
     - Sender, recipient, size, and delivery status are logged in
       SEPARATE journal entries, often far apart
     - Correlation requires BOTH a message identifier and a tolerance window

   THIS QUERY IS HEAVILY ANNOTATED ON PURPOSE.
   DO NOT REMOVE COMMENTS — THEY DOCUMENT OPERATIONAL ASSUMPTIONS.
   ============================================================================ */

WITH

/* ============================================================================
   CTE: all_entries
   ----------------
   BASE NORMALIZATION LAYER

   PURPOSE:
     - Read raw journal rows
     - Decode JOESD (binary CCSID 65535) into readable EBCDIC text
     - Extract possible mail attributes from EACH row independently

   NOTE ON CCSID:
     - JOESD is stored as CCSID 65535 (binary) to prevent auto-conversion
     - CAST to CCSID 37 is INTENTIONAL and REQUIRED to read embedded text
     - This conversion is read-time only and does not modify stored data
   ============================================================================ */
all_entries AS (

    SELECT
        /* JOSEQN = Journal Entry Sequence Number
           --------------------------------------
           - Strictly increasing per journal
           - Represents ORDER of events, NOT time
           - Used later for proximity-based correlation
        */
        JOSEQN,

        /* Date/time of the journal entry */
        JODATE,
        JOTIME,

        /* Journal entry type (LG = log entry) */
        JOENTT,

        /* Decode binary JOESD so EBCDIC mail text is readable */
        CAST(JOESD AS VARCHAR(4000) CCSID 37) AS ENTRY_DATA,

        /* ====================================================================
           MESSAGE_ID
           ----------
           Uniquely identifies ONE logical email message.

           Mail subsystems log this in different formats, so we support:
             - QZMFSLOGEID=<id>
             - ID <id>

           Rows without a message ID are not primary correlation anchors.
           ==================================================================== */
        CASE
            WHEN JOESD LIKE '%QZMFSLOGEID%' THEN
                TRIM(SUBSTR(
                    CAST(JOESD AS VARCHAR(4000) CCSID 37),
                    LOCATE('QZMFSLOGEID',
                           CAST(JOESD AS VARCHAR(4000) CCSID 37)) + 12,
                    30
                ))
            WHEN JOESD LIKE '%ID %' THEN
                TRIM(SUBSTR(
                    CAST(JOESD AS VARCHAR(4000) CCSID 37),
                    LOCATE('ID ',
                           CAST(JOESD AS VARCHAR(4000) CCSID 37)) + 3,
                    30
                ))
            ELSE NULL
        END AS MSG_ID,

        /* ====================================================================
           SENDER
           ------
           Sender may be logged in more than one phase:
             - E1 O = envelope originator
             - C1 O = mail services originator

           Both represent the same logical "From" address.
           ==================================================================== */
        CASE
            WHEN JOESD LIKE '%E1 O %' THEN
                TRIM(SUBSTR(
                    CAST(JOESD AS VARCHAR(4000) CCSID 37),
                    LOCATE('E1 O ',
                           CAST(JOESD AS VARCHAR(4000) CCSID 37)) + 5,
                    200
                ))
            WHEN JOESD LIKE '%C1 O%' THEN
                TRIM(SUBSTR(
                    CAST(JOESD AS VARCHAR(4000) CCSID 37),
                    LOCATE('C1 O',
                           CAST(JOESD AS VARCHAR(4000) CCSID 37)) + 4,
                    200
                ))
            ELSE NULL
        END AS SENDER,

        /* ====================================================================
           RECIPIENT
           ---------
           Recipient can appear multiple times in different stages:
             - E2 R             (envelope)
             - C2 R             (mail services)
             - P2 R SMTP MsgFwd (FINAL SMTP routing — preferred)

           This CASE normalizes all variants into one column.
           ==================================================================== */
        CASE
            WHEN JOESD LIKE '%P2 R SMTP MsgFwd%' THEN
                TRIM(SUBSTR(
                    CAST(JOESD AS VARCHAR(4000) CCSID 37),
                    LOCATE('MsgFwd',
                           CAST(JOESD AS VARCHAR(4000) CCSID 37)) + 6,
                    200
                ))
            WHEN JOESD LIKE '%E2 R %' THEN
                TRIM(SUBSTR(
                    CAST(JOESD AS VARCHAR(4000) CCSID 37),
                    LOCATE('E2 R ',
                           CAST(JOESD AS VARCHAR(4000) CCSID 37)) + 5,
                    200
                ))
            WHEN JOESD LIKE '%C2 R%' THEN
                REPLACE(
                    REPLACE(
                        TRIM(SUBSTR(
                            CAST(JOESD AS VARCHAR(4000) CCSID 37),
                            LOCATE('C2 R',
                                   CAST(JOESD AS VARCHAR(4000) CCSID 37)) + 4,
                            200
                        )),
                        '&lt;', ''
                    ),
                    '&gt;', ''
                )
            ELSE NULL
        END AS RECIPIENT,

        /* Message size if logged */
        CASE
            WHEN JOESD LIKE '%ET MSG SIZE%' THEN
                TRIM(SUBSTR(
                    CAST(JOESD AS VARCHAR(4000) CCSID 37),
                    LOCATE('ET MSG SIZE',
                           CAST(JOESD AS VARCHAR(4000) CCSID 37)) + 12,
                    20
                ))
            ELSE NULL
        END AS MSG_SIZE,

        /* ====================================================================
           DELIVERY STATUS (per fragment)
           ------------------------------
           Each journal row indicates ONE step, NOT final outcome.

           Meanings:
             EH     -> message submitted
             88 DLV -> delivered
             83     -> undeliverable / failure
           ==================================================================== */
        CASE
            WHEN JOESD LIKE '%EH SNDMAIL TO MSF%' THEN 'SENT'
            WHEN JOESD LIKE '%88 DLV%' THEN 'DELIVERED'
            WHEN JOESD LIKE '%83%' THEN 'FAILED'
            ELSE NULL
        END AS STATUS,

        /* Delivery / failure diagnostic detail */
        CASE
            WHEN JOESD LIKE '%88 DLV%' THEN
                TRIM(SUBSTR(
                    CAST(JOESD AS VARCHAR(4000) CCSID 37),
                    LOCATE('88 DLV',
                           CAST(JOESD AS VARCHAR(4000) CCSID 37)) + 6,
                    100
                ))
            WHEN JOESD LIKE '%83%' THEN
                TRIM(SUBSTR(
                    CAST(JOESD AS VARCHAR(4000) CCSID 37),
                    LOCATE('83',
                           CAST(JOESD AS VARCHAR(4000) CCSID 37)) + 2,
                    200
                ))
            ELSE NULL
        END AS DETAILS

    FROM QGPL.EMAIL2
    WHERE JOENTT = 'LG'
      AND JOESD IS NOT NULL
),

/* ============================================================================
   Role-based CTEs
   ---------------
   These isolate specific fragment types.
   No logic — filtering only.
   ============================================================================ */
msg_ids    AS (SELECT JOSEQN, JODATE, JOTIME, MSG_ID     FROM all_entries WHERE MSG_ID     IS NOT NULL),
senders    AS (SELECT JOSEQN, JODATE, JOTIME, SENDER    FROM all_entries WHERE SENDER    IS NOT NULL),
recipients AS (SELECT JOSEQN, JODATE, JOTIME, RECIPIENT FROM all_entries WHERE RECIPIENT IS NOT NULL),
sizes      AS (SELECT JOSEQN, JODATE, JOTIME, MSG_SIZE  FROM all_entries WHERE MSG_SIZE  IS NOT NULL),
deliveries AS (SELECT JOSEQN, JODATE, JOTIME, STATUS, DETAILS FROM all_entries WHERE STATUS IS NOT NULL),

/* ============================================================================
   CTE: correlated
   ----------------
   FRAGMENT REASSEMBLY LAYER

   ===================== CORRELATION SPREAD ======================

   The following condition:
       ABS(x.JOSEQN - m.JOSEQN) <= 200

   Defines the MAXIMUM journal distance for two entries to be
   considered part of the same email.

   THIS IS THE PRIMARY TUNABLE PARAMETER.

   RECOMMENDED VALUES:
     <= 50    : Low-volume systems, minimal retries
     <= 100   : Normal SMTP, light system load
     <= 200   : Typical production (CURRENT SETTING)
     <= 300   : Heavy SMTP retry / DSN activity
     <= 500   : Extreme latency (consider MSG_ID-only correlation)

   CONSEQUENCES:
     Too SMALL -> missing sender/recipient/status
     Too LARGE -> increased join cost, possible mis-correlation

   IMPORTANT:
     - Keep THIS VALUE IDENTICAL in ALL joins below
     - Always combine with JODATE to prevent cross-day matches
   =============================================================== */
correlated AS (
    SELECT DISTINCT
        m.MSG_ID        AS MESSAGE_ID,
        m.JODATE        AS EMAIL_DATE,
        m.JOTIME        AS EMAIL_TIME,
        s.SENDER        AS FROM_ADDRESS,
        r.RECIPIENT     AS TO_ADDRESS,
        sz.MSG_SIZE     AS SIZE_BYTES,
        COALESCE(d.STATUS, 'PENDING') AS DELIVERY_STATUS,
        d.DETAILS       AS DELIVERY_DETAILS

    FROM msg_ids m
    LEFT JOIN senders s
        ON s.JODATE = m.JODATE
       AND ABS(s.JOSEQN - m.JOSEQN) <=20
    LEFT JOIN recipients r
        ON r.JODATE = m.JODATE
       AND ABS(r.JOSEQN - m.JOSEQN) <= 20
    LEFT JOIN sizes sz
        ON sz.JODATE = m.JODATE
       AND ABS(sz.JOSEQN - m.JOSEQN) <= 20
    LEFT JOIN deliveries d
        ON d.JODATE = m.JODATE
       AND ABS(d.JOSEQN - m.JOSEQN) <= 20
)

/* ============================================================================
   FINAL AGGREGATION
   -----------------
   ONE ROW PER MESSAGE_ID

   AGGREGATION LOGIC:
     - MIN(EMAIL_TIME): earliest observed event (message start)
     - MAX(FROM/TO/SIZE): collapse NULL fragments
     - STATUS precedence resolves final outcome
   ============================================================================ */
SELECT
    MESSAGE_ID,
    EMAIL_DATE,

    /* Earliest appearance of the message in journal */
    MIN(EMAIL_TIME) AS EMAIL_TIME,

    /* Collapse NULLs; sender/recipient appear once per message */
    MAX(FROM_ADDRESS) AS FROM_ADDRESS,
    MAX(TO_ADDRESS)   AS TO_ADDRESS,
    MAX(SIZE_BYTES)   AS SIZE_BYTES,

    /* STATUS PRECEDENCE:
         FAILED     overrides everything
         DELIVERED  overrides SENT
         SENT       overrides PENDING
    */
    COALESCE(
        MAX(CASE WHEN DELIVERY_STATUS = 'FAILED'    THEN 'FAILED' END),
        MAX(CASE WHEN DELIVERY_STATUS = 'DELIVERED' THEN 'DELIVERED' END),
        MAX(CASE WHEN DELIVERY_STATUS = 'SENT'      THEN 'SENT' END),
        'PENDING'
    ) AS DELIVERY_STATUS,

    /* Diagnostic information */
    MAX(DELIVERY_DETAILS) AS DELIVERY_DETAILS,

    /* Number of journal fragments contributing to this message */
    COUNT(*) AS FRAGMENT_COUNT

FROM correlated
GROUP BY MESSAGE_ID, EMAIL_DATE
ORDER BY EMAIL_DATE DESC, EMAIL_TIME DESC;

Results:


In this example, you can see that most of the recipient and sender information is correct, however we have some areas where we did not retrieve the details about the size and delivery details. This most likely is due to the spread values for ABS(x.JOSEQN - m.JOSEQN) = xx. The default spread is 20 rows however for more busy and congested servers, it may need to expand the range.

     <= 50    : Low-volume systems, minimal retries
     <= 100   : Normal SMTP, light system load
     <= 200   : Typical production (CURRENT SETTING)
     <= 300   : Heavy SMTP retry / DSN activity
     <= 500   : Extreme latency (consider MSG_ID-only correlation)