-- RPTLOG — Retriever run log.
-- One row per execution, scheduled or interactive (SCHEDULE_ID NULL =
-- ad-hoc run from the screen). Doubles as the resource meter: CPU_MS and
-- TEMP_STG_MB are before/after deltas from QSYS2.ACTIVE_JOB_INFO for the
-- job that serviced the query; JOBNAME is that job's qualified name.
-- STATUS: 'R' running, 'D' rendered/delivery pending (outputs exist,
-- RPTMAIL rows written, sends not finished), 'S' success (all sends
-- done), 'E' error, 'K' skipped (exception report with nothing to say).
-- A row is written at start and completed at end, so an executor that
-- dies mid-query leaves a visible 'R'. DEF_HASH = SHA-256 of the
-- definition as-run; RUNVALS = the resolved $rel/$param values as JSON —
-- together they answer "exactly what query produced Tuesday's numbers".
-- OUTPUT_REF points at the archived output in the IFS; blanked on purge.
-- No FK to RPTDEF on purpose — the log outlives deleted reports.
-- Runs in each install's XXX_5WEB library.
SET SCHEMA XXX_5WEB;
CREATE TABLE RPTLOG (
LOG_ID INTEGER GENERATED ALWAYS AS IDENTITY
( START WITH 1 INCREMENT BY 1 ) NOT NULL ,
REPORT_ID INTEGER NOT NULL ,
SCHEDULE_ID FOR COLUMN SCHED_ID INTEGER DEFAULT NULL ,
USER_ID INTEGER NOT NULL ,
STARTED TIMESTAMP NOT NULL ,
ENDED TIMESTAMP DEFAULT NULL ,
STATUS CHAR(1) CCSID 37 NOT NULL DEFAULT 'R' ,
ERRORTEXT VARCHAR(1024) CCSID 37 NOT NULL DEFAULT '' ,
ROWCOUNT INTEGER NOT NULL DEFAULT 0 ,
ELAPSED_MS INTEGER NOT NULL DEFAULT 0 ,
CPU_MS INTEGER NOT NULL DEFAULT 0 ,
TEMP_STG_MB FOR COLUMN TEMPSTGMB INTEGER NOT NULL DEFAULT 0 ,
RECIPIENTS FOR COLUMN RECIPCNT INTEGER NOT NULL DEFAULT 0 ,
OUTPUT_REF FOR COLUMN OUTREF VARCHAR(255) CCSID 37 NOT NULL DEFAULT '' ,
DEF_HASH VARCHAR(64) CCSID 37 NOT NULL DEFAULT '' ,
RUNVALS VARCHAR(2000) CCSID 37 NOT NULL DEFAULT '' ,
JOBNAME VARCHAR(28) CCSID 37 NOT NULL DEFAULT '' ,
PRIMARY KEY ( LOG_ID ) )
RCDFMT RPTLOG ;
LABEL ON TABLE RPTLOG
IS 'Web - Retriever Run Log' ;
CREATE INDEX RPTLOG_REPORT_IX ON RPTLOG ( REPORT_ID , STARTED ) ;
