RPTMAIL - Retriever Delivery Ledger

Jul 30, 2026

-- RPTMAIL — Retriever delivery ledger.
-- One row per recipient per run, written by the worker when outputs are
-- rendered (STATUS 'P' pending), consumed by the mailer ('S' sent / 'E'
-- error). This is the ledger the data-queue doorbell points at: the
-- mailer re-scans STATUS='P' at startup, so a crash costs latency —
-- never a duplicate send, never a lost one. OUTPUT_REF is per-row
-- because bursting gives each recipient their own file. Separates the
-- two failure kinds RPTLOG can't: the RUN failed (RPTLOG 'E') vs ONE
-- SEND failed (bounce/SMTP — retryable per recipient via ATTEMPTS,
-- without re-running the report). No FKs on purpose — ledger rows
-- outlive schedule/recipient edits.
-- Runs in each install's XXX_5WEB library.

SET SCHEMA XXX_5WEB;

CREATE TABLE RPTMAIL (
	MAIL_ID INTEGER GENERATED ALWAYS AS IDENTITY
		( START WITH 1 INCREMENT BY 1 ) NOT NULL ,
	LOG_ID INTEGER NOT NULL ,
	SCHEDULE_ID FOR COLUMN SCHED_ID INTEGER NOT NULL ,
	EMAIL VARCHAR(254) CCSID 37 NOT NULL ,
	BURSTVAL VARCHAR(100) CCSID 37 NOT NULL DEFAULT '' ,
	OUTPUT_REF FOR COLUMN OUTREF VARCHAR(255) CCSID 37 NOT NULL DEFAULT '' ,
	STATUS CHAR(1) CCSID 37 NOT NULL DEFAULT 'P' ,
	ATTEMPTS INTEGER NOT NULL DEFAULT 0 ,
	LASTTRY TIMESTAMP DEFAULT NULL ,
	ERRORTEXT VARCHAR(1024) CCSID 37 NOT NULL DEFAULT '' ,
	BIRTH TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
	PRIMARY KEY ( MAIL_ID ) )

	RCDFMT RPTMAIL ;

LABEL ON TABLE RPTMAIL
	IS 'Web - Retriever Delivery Ledger' ;

CREATE INDEX RPTMAIL_STATUS_IX ON RPTMAIL ( STATUS , LOG_ID ) ;

---