RPTSCHED - Retriever Schedules

Jul 30, 2026

-- RPTSCHED — Retriever schedules.
-- One row per recurring run of a report. A report may have many schedules
-- (daily detail vs weekly summary), each with its own recipients in
-- RPTRECIP. USER_ID is the SCHEDULE's owner (on a shared report the
-- scheduler may differ from the author — the scheduler gets failure
-- emails and edit rights). RUNDAYS is a 7-position Y/N mask,
-- Monday..Sunday ('YYYYYNN' = weekdays). PARAMVALS pins the definition's
-- named params for this schedule as a small JSON object ('{"LOCN":"001"}').
-- FORMATS overrides the definition's output formats for this schedule
-- (blank = definition default). SENDMODE: 'A' = always send, 'N' = only
-- when result is non-empty, 'T' = only when THRESHOLD is met (exception
-- reports). LASTRUN/LASTSTATUS/LASTERROR are written by the executor
-- ('S' success, 'E' error, blank = never run); LASTRUN is the idempotency
-- anchor — never double-send, catch up after downtime. FAILCOUNT counts
-- CONSECUTIVE failures (reset on success); the executor auto-disables the
-- schedule at 5 and says so in the final failure email.
-- Runs in each install's XXX_5WEB library.

SET SCHEMA XXX_5WEB;

CREATE TABLE RPTSCHED (
	SCHEDULE_ID FOR COLUMN SCHED_ID INTEGER GENERATED ALWAYS AS IDENTITY
		( START WITH 1 INCREMENT BY 1 ) NOT NULL ,
	REPORT_ID INTEGER NOT NULL ,
	USER_ID INTEGER NOT NULL ,
	RUNTIME TIME NOT NULL ,
	RUNDAYS CHAR(7) CCSID 37 NOT NULL DEFAULT 'YYYYYYY' ,
	ENABLED CHAR(1) CCSID 37 NOT NULL DEFAULT 'Y' ,
	PARAMVALS VARCHAR(2000) CCSID 37 NOT NULL DEFAULT '' ,
	FORMATS VARCHAR(50) CCSID 37 NOT NULL DEFAULT '' ,
	SENDMODE CHAR(1) CCSID 37 NOT NULL DEFAULT 'A' ,
	THRESHOLD VARCHAR(200) CCSID 37 NOT NULL DEFAULT '' ,
	BURSTCOL VARCHAR(50) CCSID 37 NOT NULL DEFAULT '' ,
	FAILCOUNT INTEGER NOT NULL DEFAULT 0 ,
	LASTRUN TIMESTAMP DEFAULT NULL ,
	LASTSTATUS FOR COLUMN LASTSTS CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
	LASTERROR VARCHAR(1024) CCSID 37 NOT NULL DEFAULT '' ,
	BIRTH TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
	LASTUPDATE FOR COLUMN LASTU00001 TIMESTAMP GENERATED ALWAYS
		FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL ,
	PRIMARY KEY ( SCHEDULE_ID ) ,
	CONSTRAINT RPTSCHED_REPORT_FK FOREIGN KEY ( REPORT_ID )
		REFERENCES RPTDEF ( REPORT_ID ) ON DELETE CASCADE )

	RCDFMT RPTSCHED ;

LABEL ON TABLE RPTSCHED
	IS 'Web - Retriever Schedules' ;

CREATE INDEX RPTSCHED_REPORT_IX ON RPTSCHED ( REPORT_ID ) ;

---