MSGMON — Message Queue Monitor

Monitors configured IBM i message queues for unanswered inquiry messages, emails a notification the first time each one is seen, and records replies (who replied, what they said, how long it took) once they come in.

Contents

Object	Type	Purpose
MSGMONR	SQLRPGLE program	Main monitor logic
MSGMONRC	CLLE program	Scheduled-job wrapper; catches unhandled errors from MSGMONR and alerts separately
YOURLIB.MSGMONCFG	Table	Which queues to monitor, and where to send email
YOURLIB.MSGMONHST	Table	One row per detected inquiry; tracks notification + reply history
INSTALL.SQL	Script	Creates MSGMONCFG and MSGMONHST

How it works

For each enabled row in MSGMONCFG:

1. Reads the target message queue once via QSYS2.MESSAGE_QUEUE_INFO and snapshots it into a job-scoped temp table (QTEMP.MSGSNAP).
2. Finds INQUIRY-type messages in the snapshot, newer than the configured cutoff, that have no associated REPLY message yet.
3. For each one not already logged in MSGMONHST:
   • Sends an HTML email via SNDSMTPEMM
   • Inserts a row into MSGMONHST with STATUS = 'A' (active/open)
4. For each one already logged, just bumps LAST_SEEN_TS — no repeat email.
5. Separately scans the same snapshot for REPLY-type messages. For any reply whose associated key matches an MSGMONHST row still STATUS = 'A', updates that row to STATUS = 'R' with the reply text, replying user, reply timestamp, and resolution time in minutes.

Run as a scheduled batch job, via MSGMONRC rather than calling MSGMONR directly (see below).

Setup

1. Create the tables

Run INSTALL.SQL once to create MSGMONCFG and MSGMONHST in YOURLIB.

2. Populate configuration

Insert one row per queue you want monitored:

INSERT INTO YOURLIB.MSGMONCFG
   (QUEUELIB, QUEUENAME, SYSTEM_NAME, EMAIL_TO, EMAIL_FROM, SUBJECT_PREFIX, ENABLED)
VALUES
   ('QSYS', 'QSYSOPR', 'PROD1', 'oncall@yourdomain.com', 'monitor@yourdomain.com',
    '[MSGMON]', 'Y');

Note on EMAIL_FROM: this column is currently informational only. SNDSMTPEMM does not allow specifying a From address (an IBM anti-spam design decision) — the actual sending address is always whatever's registered against the job's run-as user profile. See prerequisite #2 below.

3. Prerequisites

1. SMTP / Mail Server Framework must be configured and running on the system (STRTCPSVR SERVER(*SMTP) or equivalent, plus DNS/relay setup — see IBM's MSF/SMTP configuration docs if this isn't already in place).
2. The user profile the scheduled job runs under must be registered for SMTP via ADDUSRSMTP (or WRKDIRE/WRKSMTPUSR). Without this, SNDSMTPEMM calls will fail silently from the program's point of view — check WRKSMTPEMM to see failed send attempts and diagnostics.
3. Both MSGMONR's user and the message queues being monitored need *USE authority to the queue and *EXECUTE authority to its library (standard QSYS2.MESSAGE_QUEUE_INFO authorization requirements).

4. Compile

CRTSQLRPGI OBJ(YOURLIB/MSGMONR) SRCFILE(yourlib/QRPGLESRC) SRCMBR(MSGMONR)
CRTCLPGM   PGM(YOURLIB/MSGMONRC) SRCFILE(yourlib/QCLSRC) SRCMBR(MSGMONRC)

5. Schedule

Point your job scheduler entry at MSGMONRC, not MSGMONR directly — the wrapper is what catches an unhandled error in the monitor itself and sends a separate alert, so a broken monitor doesn't fail silently.

6. MSGMONRC error handling

MSGMONRC catches any unhandled error from MSGMONR (via MONMSG MSGID(CPF0000)), pulls the actual failing message off the job log, and:

1. Logs full diagnostic detail to its own job log (SNDPGMMSG)
2. Emails an alert to a hardcoded address (deliberately not pulled from MSGMONCFG — if the config table or its library is itself the problem, the alert still needs to go out through an independent path)
3. If that alert email also fails to send, falls back to SNDBRKMSG to QSYSOPR, so a human sees something on the console even with email entirely out of the picture

Before compiling, update the hardcoded alert address in MSGMONRC (&ALERTTO) to wherever monitor-failure alerts should actually go.

Worth testing this path deliberately at least once — temporarily break something in MSGMONR (point queueLib at a nonexistent queue, for example), confirm the alert arrives, then revert. Otherwise this code path sits untested until the day it actually matters.

Testing

To generate a real test inquiry (an ordinary SNDMSG sends an *INFO message, which won't be picked up):

SNDMSG MSG('Test inquiry from MSGMON') TOUSR(*SYSOPR) MSGTYPE(*INQ)

Run MSGMONR, confirm an email arrives and a row appears in MSGMONHST with STATUS = 'A'. Reply to it via DSPMSG QSYSOPR (type a value into the underlined reply field), run MSGMONR again, and confirm the row flips to STATUS = 'R' with REPLY_TEXT/REPLY_USER/RESOLUTION_MINUTES populated.

Running the program twice without a reply in between should not produce a second email — that's MSGMONHST doing its job as a dedup log.

Known limitations

• SNDSMTPEMM's NOTE parameter is documented as accepting up to 400 characters, but is widely reported to actually truncate around ~200 on many systems/PTF levels, and HTML tags eat into that same budget. Verify test emails aren't getting cut off; if they are, trim the HTML markup down to <b>/<br> rather than the current <pre> block.
• EMAIL_FROM in MSGMONCFG is not currently honored — see prerequisite #2 above.
• REPLY_TEXT is VARCHAR(32) in MSGMONHST — a long typed reply will be silently truncated to fit (a Db2 truncation warning, not an error).
• The 14-day cutoff (cutoffTimestamp in MSGMONR) only limits which inquiries are newly flagged — QSYS2.MESSAGE_QUEUE_INFO still has to read the entire live queue on every run regardless; there's no parameter to narrow that at the source. The QTEMP.MSGSNAP snapshot keeps this to one live read per queue per run rather than repeated reads, but doesn't reduce the size of that one read.
• No retry logic on a failed SNDSMTPEMM call — if the send fails (sqlcod from the QCMDEXC call will be non-zero; check the job log), the MSGMONHST row is still inserted, so it won't be retried on the next run since it's already considered "notified."

Troubleshooting notes

• "No messages found" but you expected some — check SEVERITY_FILTER is 0 (not a higher value), confirm the config row's QUEUELIB/ QUEUENAME match exactly and ENABLED = 'Y', and confirm the test message was actually sent as MSGTYPE(*INQ).
• A message you expect to be "answered" still shows as open — many shops clear QSYSOPR using DSPMSG F16 ("remove all except unanswered inquiries"), which by design leaves old genuinely-unanswered inquiries sitting in the queue indefinitely. Check DSPMSG directly for that message; if it still shows the underlined reply field, it's genuinely still open from the system's point of view.
• Only the first configured queue ever gets processed — this was a real bug, fixed by giving each cursor's fetch loop its own dedicated status variable (cfgFetchStat/msgFetchStat/replyFetchStat) instead of relying on the shared SQLCA sqlcod, which any INSERT/UPDATE inside a loop body (e.g. one matching zero rows) can silently overwrite. Don't reintroduce dow sqlcod = 0 anywhere in this program.
• SNDPGMMSG fails with "not allowed in this setting" — this command can't run through QSYS2.QCMDEXC from embedded SQL (SQL treats it as an "external routine" context, which SNDPGMMSG explicitly rejects). Use the logMsg procedure in MSGMONR, which calls QMHSNDPM (the underlying API) directly instead.

TODO

• General source cleanup pass (mostly done as of the last revision)