-- RPTWORK — Retriever export staging.
-- One row per report row, per export run. A saved report is a join (often
-- aggregated), so it has no RRN or key of its own and the shared Export
-- modal's chunk paging has nothing to hold onto. Staging the rows here
-- gives it an ordinary keyed file to page over: the export runs one
-- INSERT INTO RPTWORK ... SELECT (server-side — the rows never cross the
-- ODBC connection), then /exports/chunk reads it with a hard filter on
-- RUN_ID and a keyset on ROWSEQ, unchanged from every other export.
--
-- RUN_ID is the RPTLOG.LOG_ID of the export run, so staged rows tie back
-- to the run log that produced them; USER_ID scopes them to their owner.
-- C1..C120 hold the report's output columns as text in the report's own
-- order — labels live in the report definition, not here. Rows are
-- deleted when their export finishes, and any left by an abandoned
-- download are purged by age on the next export (hence the BIRTH index).
--
-- SIZING (measured against the schema, 07/27/2026): 120 x VARCHAR(256) =
-- 30,982 bytes of DB2's 32,766-byte row limit (each VARCHAR costs its
-- length + 2). Of 8,074 fields across all 257 tables, only 8 are longer
-- than 256 — AI_DESC (1024, K_AISCN), DB_FLD11 (700, K_DBGTBL), three
-- 500-char memos (K_SALSFLW / K_LOGSLFW / K_INTSLFW) and three 300s
-- (K_INTMFUB, K_MFUBENT x2) — all in debug, AI-scan, sales-flow and
-- mass-update audit tables. Those truncate here; everything else,
-- including the 255-char supplier/product help memos, fits.
-- Do NOT try VARCHAR(1024) ALLOCATE(64) to avoid that: SQL0101 reason
-- code 3 — the ALLOCATE lengths only count toward the row limit when the
-- table contains a LOB or XML column; without one DB2 uses the declared
-- length and 120 x 1024 fails outright. (Tried, 07/27/2026.)
--
-- Deliberately a permanent, app-owned table: Retriever only INSERTs and
-- DELETEs here, exactly like RPTDEF/SCREENOPT. Nothing is created or
-- dropped at run time, so the web user needs NO CREATE/DROP authority.
-- Reports wider than 120 columns fall back to the streaming CSV download.
-- Runs in each install's XXX_5WEB library.
SET SCHEMA XXX_5WEB;
CREATE TABLE RPTWORK (
RUN_ID INTEGER NOT NULL ,
ROWSEQ INTEGER NOT NULL ,
USER_ID INTEGER NOT NULL ,
BIRTH TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
C1 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C2 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C3 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C4 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C5 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C6 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C7 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C8 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C9 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C10 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C11 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C12 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C13 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C14 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C15 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C16 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C17 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C18 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C19 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C20 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C21 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C22 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C23 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C24 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C25 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C26 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C27 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C28 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C29 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C30 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C31 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C32 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C33 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C34 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C35 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C36 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C37 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C38 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C39 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C40 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C41 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C42 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C43 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C44 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C45 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C46 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C47 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C48 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C49 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C50 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C51 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C52 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C53 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C54 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C55 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C56 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C57 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C58 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C59 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C60 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C61 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C62 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C63 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C64 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C65 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C66 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C67 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C68 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C69 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C70 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C71 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C72 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C73 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C74 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C75 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C76 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C77 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C78 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C79 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C80 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C81 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C82 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C83 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C84 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C85 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C86 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C87 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C88 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C89 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C90 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C91 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C92 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C93 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C94 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C95 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C96 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C97 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C98 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C99 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C100 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C101 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C102 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C103 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C104 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C105 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C106 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C107 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C108 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C109 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C110 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C111 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C112 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C113 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C114 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C115 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C116 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C117 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C118 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C119 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
C120 VARCHAR(256) CCSID 37 NOT NULL DEFAULT '' ,
PRIMARY KEY ( RUN_ID , ROWSEQ ) )
RCDFMT RPTWORK ;
LABEL ON TABLE RPTWORK
IS 'Web - Retriever Export Staging' ;
CREATE INDEX RPTWORK_BIRTH_IX ON RPTWORK ( BIRTH ) ;
