DWETL/i — Data-Warehouse ETL Pipeline

SteelFrame X application operation manual · ← back to Operation Manuals · Sign On

DWETL/i is a data-warehouse ETL pipeline: it lands source rows in a staging (landing-zone) area, transforms them into a star schema of slowly-changing (SCD type-2) dimensions, and loads a sales fact table by natural-key upsert. It is a pure SQL PL application — there is no 5250 screen, no RPG driver, and no interactive transaction. Every stage is a DB2 for i stored procedure; change-data-capture and a running aggregate are maintained by statement-level transition-table triggers (OLD_TABLE/NEW_TABLE); surrogate keys come from CREATE SEQUENCE; the dimension load and the fact load are set-based MERGE statements. This manual is the reference for the operator who runs the ETL batch cycle and reconciles its figures, and for the developer maintaining the pipeline. It is grounded entirely in the committed source (sqlpl-app-dw/src/schema.sql, src/routines.sql, and the test/dw_daily.mjs driver/oracle). Everything lives in library / schema DW.

Honest platform note. DWETL/i is a deliberate platform-stress vehicle: several of its SQL-PL constructs surface confirmed engine limitations that the source itself documents and works around (labelled SQLPL-PLAT-DW-01 through -05 in the source comments and SQLPL-SIM-FINDINGS.md). Where a construct does not behave as an operator would expect on production DB2 for i, this manual says so plainly rather than papering over it — see the callouts in sections C, E and F. No behaviour is invented; every figure below is the hand-derived oracle in dw_daily.mjs.

A. Overview & Architecture ↑ top

A.1 What it does

DWETL/i turns a stream of source batches into a queryable star schema:

Stage (land). Source rows for customers, products and sales are loaded into three staging tables (STG_CUSTOMER, STG_PRODUCT, STG_SALES), which are reset between loads with TRUNCATE ... RESTART IDENTITY. Each staged row carries a source-batch tag and a load-local STG_ID identity for ordering/dedup.
Transform & load dimensions (SCD type-2). DIM_CUSTOMER and DIM_PRODUCT keep full version history: when a staged attribute changes, the current version is closed out (EFF_TO set, IS_CURRENT='N') and a fresh current version is inserted with a new surrogate key and an incremented VERSION_NO. Brand-new natural keys land as version 1.
Load the fact (natural-key upsert). FACT_SALES is loaded by a set-based MERGE keyed on the sale natural key SALE_NK: a corrected re-load of the same sale updates in place (no duplicate row); a new sale inserts with a fresh surrogate key. Dimension foreign keys are resolved to the current SCD2 version at load time.
Capture change (CDC) & a running aggregate. Statement-level transition-table triggers record one audit row per affected batch (AUDIT_DIM_CUSTOMER, AUDIT_FACT_SALES) and maintain a running row-count / total-amount aggregate (FACT_SALES_RUNAGG) purely from the OLD_TABLE/NEW_TABLE transition sets — never from a full-table scan.
Report. Views and a procedure expose window-function running totals (V_CUST_RUNNING_TOTAL), a revenue rank (V_PRODUCT_RANK), a GROUP BY ROLLUP region summary, and a WITH RECURSIVE SCD version chain (SP_CUST_VERSION_CHAIN).

A.2 Staging → transform → load: one SQL-PL layer, no orchestrating host program

Unlike the RPG-fronted applications in this estate, DWETL/i has no second orchestration layer: there is no SQLRPGLE driver, no 5250 program, and no DDS. The entire pipeline is DB2 for i SQL PL — procedures, triggers, sequences, distinct types and global variables in routines.sql over the schema in schema.sql. The “caller” is whatever drives the SQL: an operator at STRSQL, a scheduled CL/SBMJOB job issuing CALL statements, or (as tested) the dw_daily.mjs harness that loads the two SQL files and issues the same statements an operator would.

The pipeline is intentionally set-based: dimension close-outs are one bulk UPDATE, new versions one bulk INSERT ... SELECT, and the fact load one MERGE, so each statement-level transition-table trigger sees the whole affected batch in a single firing rather than row-by-row (RBAR). The benefit for operations: CDC and the running aggregate are maintained in-line with the load at bulk speed, and the version history is fully reconstructable.

A.3 Component & flow

  STAGE (land)              TRANSFORM + LOAD                 CDC / AUDIT (transition-table triggers)
  ------------              ----------------                 ---------------------------------------
  STG_CUSTOMER  ---\        SP_LOAD_DIM_CUSTOMER (SCD2)  ---> TR_DIMCUST_UPD (OLD/NEW TABLE close-out)
  STG_PRODUCT   ----+--CALL SP_LOAD_DIM_PRODUCT  (SCD2)       TR_DIMCUST_INS (NEW_TABLE new versions)
  STG_SALES     ---/        SP_LOAD_FACT (MERGE upsert)  ---> TR_FACT_INS / TR_FACT_UPD / TR_FACT_DEL
                                |                              (NEW_TABLE/OLD_TABLE -> AUDIT_FACT_SALES
       SP_RUN_ETL  ------------/                                                    + FACT_SALES_RUNAGG)
       chains the three loads, one ETL_RUNLOG row per step
                                |
                                v
  DIM_CUSTOMER (SCD2)  DIM_PRODUCT (SCD2)  --resolve current SK-->  FACT_SALES (star-schema fact)
       ^  surrogate keys                                                 |
       |  from SQ_CUST_SK / SQ_PROD_SK / SQ_FACT_SK (NEXT VALUE FOR      +--> reporting views:
       |  via SP_SEQ_RESERVE block-reserve workaround)                        V_CUST_RUNNING_TOTAL (window)
  run params: DW.RUN_ID / RUN_LOAD_DATE / RUN_BATCH_TAG (CREATE VARIABLE)     V_PRODUCT_RANK (RANK window)
                                                                              SP_CUST_VERSION_CHAIN (WITH RECURSIVE)

A single ETL run flows: the caller resets and re-loads staging → stamps the run parameters → CALL DW.SP_RUN_ETL(run_id, load_date, batch) → which chains SP_LOAD_DIM_CUSTOMER → SP_LOAD_DIM_PRODUCT → SP_LOAD_FACT, writing an ETL_RUNLOG row per step. The dimension loads fire the transition-table triggers; the fact MERGE is intended to fire the fact triggers (see the DW-05 callout in C and F).

A.4 Object inventory

Object	Type	Role
STG_CUSTOMER / STG_PRODUCT / STG_SALES	Staging tables	Landing zone, reset via TRUNCATE RESTART IDENTITY.
STG_SALES_RESOLVED	Working table	Pre-joined, dimension-resolved fact source (JOIN-free MERGE input).
DIM_CUSTOMER / DIM_PRODUCT	Dimension tables	SCD type-2 versioned dimensions.
FACT_SALES	Fact table	Star-schema sales fact (grain: one staged sale-line).
AUDIT_DIM_CUSTOMER / AUDIT_FACT_SALES	CDC tables	Transition-table-fed change audit.
FACT_SALES_RUNAGG	Aggregate table	Running row-count / total-amount from transition sets.
ETL_RUNLOG	Log table	One row per ETL step per run.
SQ_CUST_SK / SQ_PROD_SK / SQ_FACT_SK / SQ_AUDIT_SK	Sequences	Surrogate-key generators (NEXT VALUE FOR).
MONEY_T / SKEY_T	Distinct types	Money-measure and surrogate-key distinct types.
RUN_ID / RUN_LOAD_DATE / RUN_BATCH_TAG	Global variables	Run-scoped ETL control values.
SP_STAGE_RESET	Procedure	Embedded TRUNCATE (see DW-01; not on the live path).
SP_SEQ_RESERVE	Procedure	Reserve a contiguous surrogate-key block (DW-03 workaround).
SP_LOAD_DIM_CUSTOMER / SP_LOAD_DIM_PRODUCT	Procedures	SCD2 dimension loads.
SP_LOAD_FACT	Procedure	MERGE upsert into FACT_SALES.
SP_PURGE_BATCH	Procedure	Bulk DELETE of a batch tag (exercises OLD_TABLE).
SP_RUN_ETL	Procedure	Orchestrates the three loads + ETL_RUNLOG.
SP_CUST_VERSION_CHAIN	Procedure	WITH RECURSIVE SCD version chain (result set).
TR_DIMCUST_INS / TR_DIMCUST_UPD	Triggers	Dimension CDC (NEW_TABLE / OLD&NEW TABLE).
TR_FACT_INS / TR_FACT_UPD / TR_FACT_DEL	Triggers	Fact CDC + running aggregate.
V_CUST_RUNNING_TOTAL / V_PRODUCT_RANK	Views	Window-function reporting.

The full catalogue is 8 procedures + 5 transition-table triggers + 2 reporting views, over 12 tables, 4 sequences, 2 distinct types and 3 global variables. Sections D and F expand each.

B. Online Access & Invocation ↑ top

B.1 STRSQL / driver invocation (there is no 5250 screen)

Honest statement: DWETL/i has no interactive 5250 screen and no DDS display file. It is a pure SQL-PL pipeline, so “online access” means issuing SQL directly — interactively at STRSQL, or from a batch/scheduler job that runs CALL statements. Before invoking anything the job's library list must include DW — the tested job runs with LIBL = QSYS QGPL DW QTEMP and CURLIB = DW (see dw_daily.mjs, where job.libl/job.curlib are set exactly so).

To do this	Issue (STRSQL or a CALL in a batch job)
Reset the staging area between loads	`TRUNCATE TABLE DW.STG_CUSTOMER RESTART IDENTITY` (& STG_PRODUCT / STG_SALES)
Land a source batch	`INSERT INTO DW.STG_CUSTOMER ...` / `STG_PRODUCT` / `STG_SALES`
Stamp the run parameters	`SET DW.RUN_ID = n; SET DW.RUN_LOAD_DATE = '...'; SET DW.RUN_BATCH_TAG = '...'`
Run the whole ETL cycle	`CALL DW.SP_RUN_ETL(:runid, :loaddate, :batch)`
Run a single stage	`CALL DW.SP_LOAD_DIM_CUSTOMER(...)` / `SP_LOAD_DIM_PRODUCT` / `SP_LOAD_FACT`
Purge a loaded batch from the fact	`CALL DW.SP_PURGE_BATCH(:batch)`
List a customer's SCD version chain	`CALL DW.SP_CUST_VERSION_CHAIN(:cust_nk)` (returns a result set)
Report / reconcile	`SELECT ... FROM DW.V_CUST_RUNNING_TOTAL` / `V_PRODUCT_RANK` / `FACT_SALES`

Two constructs must be issued at the top level, not from inside a procedure body — both are confirmed platform limitations the source documents and probes honestly (F.5):

Staging reset (SQLPL-PLAT-DW-01). The TRUNCATE ... RESTART IDENTITY statements exist inside SP_STAGE_RESET, but an embedded TRUNCATE in a SQL-PL compound body fails with a raw near "TRUNCATE": syntax error. The same statement works cleanly at the top level, so operators/schedulers issue the three TRUNCATEs directly. SP_STAGE_RESET is kept only to keep the failure reproducible.
Run-parameter stamping (SQLPL-PLAT-DW-02). Assigning a global CREATE VARIABLE with SET DW.RUN_ID = ... from inside a routine body fails with SQL0206 (“not a variable or parameter in this routine”), even though the variable exists. So the driver stamps DW.RUN_ID / RUN_LOAD_DATE / RUN_BATCH_TAG with top-level SET statements immediately before CALL DW.SP_RUN_ETL(...).

B.2 Controls & CDC audit workflow (transition-table change capture)

DWETL/i does not model a maker–checker approval workflow — it is an unattended pipeline. The control model it does have is data-layer change-data-capture and integrity, driven entirely by the statement-level transition-table triggers:

Dimension CDC (AUDIT_DIM_CUSTOMER). Every SCD2 close-out and every new-version insert on DIM_CUSTOMER is captured automatically: TR_DIMCUST_INS (AFTER INSERT, NEW_TABLE) writes one 'INS' detail row per new dimension row; TR_DIMCUST_UPD (AFTER UPDATE, OLD TABLE/NEW TABLE, gated WHEN EXISTS (SELECT 1 FROM NT)) writes one 'UPD' summary row whose N_ROWS is the whole close-out batch size. On the very first load nothing is closed out, so the WHEN gate correctly suppresses an empty-batch 'UPD' fire.
Fact CDC (AUDIT_FACT_SALES) & running aggregate (FACT_SALES_RUNAGG). TR_FACT_INS / TR_FACT_UPD / TR_FACT_DEL record one 'INS'/'UPD'/'DEL' audit row per statement and fold the affected transition set into FACT_SALES_RUNAGG — the running total is maintained purely from NEW_TABLE/OLD_TABLE, never a full-table SUM.
Empty-set firing. A statement that matches nothing (e.g. a DELETE hitting no rows) still fires the statement-level trigger once, recording an N_ROWS=0 audit row — the DB2 statement-trigger-always-fires rule (test EMP2).
Honest DDL rejects. Illegal transition-table trigger definitions are rejected, not silently accepted: a BEFORE trigger referencing a transition table (SQLSTATE 42898), an OLD_TABLE on an AFTER INSERT trigger (42887), and INSTEAD OF + transition table are all refused (tests HR1–HR3).

Load-bearing caveat (SQLPL-PLAT-DW-05). The fact triggers are correctly defined, but on this engine a MERGE fires no triggers at all — so the MERGE-driven fact loads never populate AUDIT_FACT_SALES or FACT_SALES_RUNAGG, which stay empty even though FACT_SALES itself is correct. The plain-DELETE path (SP_PURGE_BATCH) does fire TR_FACT_DEL correctly, confirming the limitation is specific to MERGE, not to transition tables in general. This is documented in full in C, E.2 and F.5; the running-aggregate reconciliation is the app's headline RED invariant and is intended as a finding, not a defect to hide.

C. Batch / ETL Cycle ↑ top

DWETL/i's work is a batch cycle — there is no online path. One ETL run is: reset staging, land a source batch, stamp the run parameters, then CALL DW.SP_RUN_ETL(run_id, load_date, batch), which chains the two dimension loads and the fact load in a fixed order and writes one ETL_RUNLOG row per step. The pipeline is idempotent: re-running with the same staged batch and same load date changes nothing further (tests IDEM1–IDEM5, VOL11–VOL13).

-- one full cycle (top-level SQL, e.g. STRSQL or a scheduled CALL job)
TRUNCATE TABLE DW.STG_CUSTOMER RESTART IDENTITY;   -- DW-01: must be top-level
TRUNCATE TABLE DW.STG_PRODUCT  RESTART IDENTITY;
TRUNCATE TABLE DW.STG_SALES    RESTART IDENTITY;
INSERT INTO DW.STG_CUSTOMER (...) VALUES ...;       -- land the source batch
INSERT INTO DW.STG_PRODUCT  (...) VALUES ...;
INSERT INTO DW.STG_SALES    (...) VALUES ...;
SET DW.RUN_ID = 1;                                  -- DW-02: stamp run params top-level
SET DW.RUN_LOAD_DATE = '2026-01-01';
SET DW.RUN_BATCH_TAG = 'B1';
CALL DW.SP_RUN_ETL(1, '2026-01-01', 'B1');          -- dims then fact, ETL_RUNLOG per step

C.1 Full procedure & trigger table (the ETL stages)

Object	Purpose	Fires / calls	Inputs	Outputs
SP_RUN_ETL	Orchestrate the run.	Calls SP_LOAD_DIM_CUSTOMER → SP_LOAD_DIM_PRODUCT → SP_LOAD_FACT.	P_RUN_ID, P_LOAD_DATE, P_BATCH.	3 ETL_RUNLOG rows (DIM_CUSTOMER / DIM_PRODUCT / FACT_SALES) with staged row counts.
SP_LOAD_DIM_CUSTOMER	SCD2 load of DIM_CUSTOMER.	Bulk UPDATE close-out (fires TR_DIMCUST_UPD once), then INSERT...SELECT new versions (fires TR_DIMCUST_INS once); CALLs SP_SEQ_RESERVE.	P_LOAD_DATE, P_BATCH; reads STG_CUSTOMER.	Closed-out + fresh DIM_CUSTOMER rows; AUDIT_DIM_CUSTOMER rows.
SP_LOAD_DIM_PRODUCT	SCD2 load of DIM_PRODUCT.	Same pattern; CALLs SP_SEQ_RESERVE. (No dedicated product CDC trigger — deliberate.)	P_LOAD_DATE, P_BATCH; reads STG_PRODUCT.	Closed-out + fresh DIM_PRODUCT rows.
SP_LOAD_FACT	MERGE upsert into FACT_SALES on SALE_NK.	DELETE + INSERT...SELECT builds STG_SALES_RESOLVED; MERGE upserts. MERGE fires no triggers (DW-05). CALLs SP_SEQ_RESERVE.	P_BATCH; reads STG_SALES + current DIM rows.	Inserted/updated FACT_SALES rows. AUDIT_FACT_SALES / RUNAGG NOT updated (DW-05).
SP_SEQ_RESERVE	Reserve a contiguous surrogate-key block.	WHILE-loops NEXT VALUE FOR the named sequence P_N times (DW-03 workaround).	P_SEQNAME, P_N.	P_BASE = first key of the block [P_BASE .. P_BASE+P_N-1].
SP_PURGE_BATCH	Delete a batch's fact rows.	Bulk DELETE (fires TR_FACT_DEL correctly — plain DELETE, not MERGE).	P_BATCH.	Removed FACT_SALES rows; AUDIT_FACT_SALES 'DEL' row; RUNAGG decremented.
TR_DIMCUST_INS / _UPD	Dimension CDC.	Statement-level; NEW_TABLE (INS) / OLD&NEW TABLE (UPD).	DIM_CUSTOMER INSERT / UPDATE transition sets.	AUDIT_DIM_CUSTOMER rows.
TR_FACT_INS / _UPD / _DEL	Fact CDC + running aggregate.	Statement-level; NEW_TABLE / OLD_TABLE. Only _DEL is reachable at HEAD (DW-05).	FACT_SALES INSERT/UPDATE/DELETE transition sets.	AUDIT_FACT_SALES rows; FACT_SALES_RUNAGG folded from the transition set.

C.2 The ETL stages in order

Stage 1 — DIM_CUSTOMER (SCD type-2)

SP_LOAD_DIM_CUSTOMER(load_date, batch) runs two set-based steps. Close-out: one UPDATE sets EFF_TO = load_date - 1 DAY, IS_CURRENT='N' on every current row whose staged name/region/tier differs — firing TR_DIMCUST_UPD once for the whole batch via OLD TABLE/NEW TABLE. New versions: one INSERT ... SELECT adds a fresh current row for each brand-new natural key and each changed one, with EFF_FROM = load_date, EFF_TO = '9999-12-31', IS_CURRENT='Y', VERSION_NO = MAX(prior)+1 — firing TR_DIMCUST_INS once via NEW_TABLE. Surrogate keys come from a reserved block (SP_SEQ_RESERVE('SQ_CUST_SK', n)) assigned with a ROW_NUMBER() OVER (...) offset, not an inline NEXT VALUE FOR (DW-03).

Batch1 (load 2026-01-01): 3 new customers -> 3 current v1 rows,
CUST_SK from SQ_CUST_SK = 1000..1002; 3 'INS' audit rows, no 'UPD' row.
Batch2 (load 2026-01-02): C001 region EAST->WEST changes ->
  v1 closed (EFF_TO=2026-01-01, IS_CURRENT='N'), v2 inserted (EFF_FROM=2026-01-02, 'Y')
  C004 new -> v1; C002/C003 unchanged -> untouched. 1 'UPD' + 2 'INS' audit rows.

Stage 2 — DIM_PRODUCT (SCD type-2)

SP_LOAD_DIM_PRODUCT(load_date, batch) is the identical SCD2 pattern over STG_PRODUCT (name/category/unit-cost compared), keys from SQ_PROD_SK. There is no dedicated product CDC trigger — deliberately, so the estate exercises both a dimension that has a transition-table trigger (customer) and one that doesn't (product).

Stage 3 — FACT_SALES (MERGE upsert on SALE_NK)

SP_LOAD_FACT(batch) first rebuilds the flat working table STG_SALES_RESOLVED with a plain INSERT ... SELECT that JOINs STG_SALES to the current DIM_CUSTOMER/DIM_PRODUCT versions, computes EXT_AMT = QTY × UNIT_PRICE, and assigns reserved fact keys via ROW_NUMBER(). It then runs a single MERGE INTO DW.FACT_SALES USING DW.STG_SALES_RESOLVED ON TGT.SALE_NK = SRC.SALE_NK: WHEN MATCHED updates the row in place (a corrected re-load), WHEN NOT MATCHED inserts a new fact row with the reserved surrogate key. The JOIN is pre-resolved into a flat single-table source because a JOIN inside a MERGE ... USING (...) subquery corrupts the parse (SQLPL-PLAT-DW-04).

Batch1: 4 sales -> 4 fact rows, SUM(EXT_AMT)=120+60+60+30 = 270.00
Batch2: S0001 qty 10->12 corrected -> UPDATE in place (FACT_SK unchanged, EXT_AMT 120->144);
        S0005 new -> INSERT; SUM(EXT_AMT)=144+60+60+30+36 = 330.00 over 5 rows

SQLPL-PLAT-DW-05. The fact MERGE lands the correct rows, but fires no triggers — so AUDIT_FACT_SALES and FACT_SALES_RUNAGG receive nothing from any fact load. After batch2 the fact correctly totals 330.00 while RUNAGG is still empty (the headline RED invariant T7). A subsequent SP_PURGE_BATCH (plain DELETE) then correctly fires TR_FACT_DEL and decrements RUNAGG from its empty baseline — taking it negative (-1|-36, test PG5). This is DW-05 propagating downstream, not a second defect.

Purge — SP_PURGE_BATCH (the OLD_TABLE path)

SP_PURGE_BATCH(batch) is a bulk DELETE FROM DW.FACT_SALES WHERE LOAD_BATCH = batch. Because it is a plain DELETE (not a MERGE), it does fire TR_FACT_DEL correctly, recording a 'DEL' audit row from OLD_TABLE (N_ROWS, SUM_AMT) and decrementing the running aggregate.

C.3 Ordering & dependencies

Reset + stage before the run. Staging must be reset (top-level TRUNCATE) and re-loaded before SP_RUN_ETL — the procedure does not stage; the caller does.
Dimensions before the fact. SP_RUN_ETL loads both dimensions before SP_LOAD_FACT, because the fact resolves its CUST_SK/PROD_SK against the current dimension version. A changed customer must be re-versioned first so the fact re-points at the new surrogate key (test FM3).
Within a dimension load the close-out UPDATE runs before the new-version INSERT, so a changed NK is closed then re-inserted as the next version.
Stamp run parameters before CALL. The top-level SETs of DW.RUN_ID/RUN_LOAD_DATE/RUN_BATCH_TAG must precede SP_RUN_ETL (DW-02).

D. Data Files (data dictionary) ↑ top

All objects are in schema / library DW, grounded in schema.sql. Money is DECIMAL; dates are true SQL DATE; surrogate keys are INTEGER from the sequences.

Staging (landing zone)

STG_CUSTOMER — staged customers

Field	Type	Meaning
STG_ID	INTEGER identity	Load-local sequence (GENERATED ALWAYS, restarts on TRUNCATE).
CUST_NK	VARCHAR(10)	Customer natural key (business key).
CUST_NAME / CUST_REGION / CUST_TIER	VARCHAR	Attributes compared for SCD2 change detection.
SRC_BATCH	VARCHAR(10)	Source-batch tag.

STG_PRODUCT — staged products

Field	Type	Meaning
STG_ID	INTEGER identity	Load-local sequence.
PROD_NK	VARCHAR(10)	Product natural key.
PROD_NAME / PROD_CAT	VARCHAR	Name / category (SCD2-compared).
UNIT_COST	DECIMAL(11,2)	Unit cost (SCD2-compared).
SRC_BATCH	VARCHAR(10)	Source-batch tag.

STG_SALES — staged sales lines

Field	Type	Meaning
STG_ID	INTEGER identity	Load-local sequence.
SALE_NK	VARCHAR(14)	Sale natural key (the fact MERGE key).
CUST_NK / PROD_NK	VARCHAR(10)	Customer / product natural keys (resolved to current SKs at load).
SALE_DATE	DATE	Sale date.
QTY	INTEGER	Quantity.
UNIT_PRICE	DECIMAL(11,2)	Unit sale price (EXT_AMT = QTY×UNIT_PRICE).
SRC_BATCH	VARCHAR(10)	Source-batch tag.

STG_SALES_RESOLVED — JOIN-free MERGE source (working table)

Reset at the top of every SP_LOAD_FACT. Holds STG_SALES pre-joined to the current dimension rows: SALE_NK, CUST_SK, PROD_SK, SALE_DATE, QTY, UNIT_PRICE, EXT_AMT, plus RN_KEY (the reserved surrogate key assigned by ROW_NUMBER()). Exists so the fact MERGE reads from a flat single table (DW-04 workaround).

Dimensions (SCD type-2)

DIM_CUSTOMER — customer dimension (PK CUST_SK)

Field	Type	Meaning
CUST_SK	INTEGER	Surrogate key (PK, from SQ_CUST_SK).
CUST_NK	VARCHAR(10)	Natural key (repeats across versions).
CUST_NAME / CUST_REGION / CUST_TIER	VARCHAR	Versioned attributes.
EFF_FROM / EFF_TO	DATE	Version validity window (current = '9999-12-31').
IS_CURRENT	CHAR(1)	'Y' current, 'N' closed out.
VERSION_NO	INTEGER	1..N version within a natural key.

DIM_PRODUCT — product dimension (PK PROD_SK)

Field	Type	Meaning
PROD_SK	INTEGER	Surrogate key (PK, from SQ_PROD_SK).
PROD_NK	VARCHAR(10)	Natural key.
PROD_NAME / PROD_CAT	VARCHAR	Versioned attributes.
UNIT_COST	DECIMAL(11,2)	Versioned unit cost.
EFF_FROM / EFF_TO / IS_CURRENT / VERSION_NO	DATE / CHAR / INT	Same SCD2 versioning as DIM_CUSTOMER.

FACT_SALES — sales fact (PK FACT_SK; grain: one staged sale-line)

Field	Type	Meaning
FACT_SK	INTEGER	Surrogate key (PK, from SQ_FACT_SK).
SALE_NK	VARCHAR(14)	Sale natural key (the MERGE upsert key).
CUST_SK / PROD_SK	INTEGER	Dimension FKs, resolved to the current SCD2 version.
SALE_DATE	DATE	Sale date.
QTY / UNIT_PRICE	INTEGER / DECIMAL(11,2)	Quantity / price.
EXT_AMT	DECIMAL(13,2)	Extended amount = QTY×UNIT_PRICE.
LOAD_BATCH	VARCHAR(10)	Batch tag stamped by the last load/upsert (purge key).

CDC / audit & running aggregate

AUDIT_DIM_CUSTOMER — dimension CDC (PK AUDIT_SK)

Field	Type	Meaning
AUDIT_SK	INTEGER	Audit key (PK, from SQ_AUDIT_SK via SP_SEQ_RESERVE for INS, inline for UPD).
EVT	CHAR(4)	'INS' (new version) or 'UPD' (close-out).
CUST_SK / CUST_NK	INTEGER / VARCHAR	Affected row (per-row on INS; NULL on the UPD summary).
N_ROWS	INTEGER	1 per INS detail row; whole batch size on the UPD summary row.
EVT_TS	TIMESTAMP	When the trigger fired.

AUDIT_FACT_SALES — fact CDC (PK AUDIT_SK)

Field	Type	Meaning
AUDIT_SK	INTEGER	Audit key (PK, inline NEXT VALUE FOR SQ_AUDIT_SK — one row per statement).
EVT	CHAR(4)	'INS' / 'UPD' / 'DEL'.
N_ROWS	INTEGER	Affected-set size (0 on an empty-set fire).
SUM_AMT	DECIMAL(15,2)	SUM(EXT_AMT) of the transition set (delta on UPD).
EVT_TS	TIMESTAMP	When the trigger fired.

At HEAD only 'DEL' rows appear (from SP_PURGE_BATCH); the MERGE paths write no 'INS'/'UPD' rows (DW-05).

FACT_SALES_RUNAGG — running aggregate (PK AGG_KEY, single row 'A')

Field	Type	Meaning
AGG_KEY	CHAR(1)	Always 'A' (single-row aggregate).
ROW_COUNT	INTEGER	Running fact row count from transition sets.
TOTAL_AMT	DECIMAL(17,2)	Running SUM(EXT_AMT) from transition sets.
LAST_BATCH	VARCHAR(10)	Last batch to touch it.

Intended to reconcile to SELECT COUNT(*), SUM(EXT_AMT) FROM FACT_SALES; at HEAD it does not, because the MERGE loads never feed it (DW-05) — see E.2.

ETL_RUNLOG — per-step run log

Field	Type	Meaning
RUN_ID	INTEGER	Run identifier (from SP_RUN_ETL's P_RUN_ID).
STEP_NAME	VARCHAR(20)	'DIM_CUSTOMER' / 'DIM_PRODUCT' / 'FACT_SALES'.
N_ROWS	INTEGER	Staged row count for that step.
RUN_TS	TIMESTAMP	When the step logged.

Sequences, distinct types, global variables

Object	Definition	Role
SQ_CUST_SK	START 1000 INCREMENT 1	DIM_CUSTOMER surrogate keys.
SQ_PROD_SK	START 2000 INCREMENT 1	DIM_PRODUCT surrogate keys.
SQ_FACT_SK	START 5000 INCREMENT 1	FACT_SALES surrogate keys.
SQ_AUDIT_SK	START 1 INCREMENT 1	Audit-row keys.
MONEY_T	DECIMAL(13,2) WITH COMPARISONS	Distinct money-measure type.
SKEY_T	INTEGER WITH COMPARISONS	Distinct surrogate-key type.
RUN_ID	INTEGER DEFAULT 0	Run-scoped ETL run id (top-level SET).
RUN_LOAD_DATE	DATE DEFAULT '2020-01-01'	Run-scoped load date.
RUN_BATCH_TAG	CHAR(10) DEFAULT 'INIT'	Run-scoped batch tag.

Relationships

FACT_SALES.CUST_SK → DIM_CUSTOMER.CUST_SK (resolved to IS_CURRENT='Y' at load).
FACT_SALES.PROD_SK → DIM_PRODUCT.PROD_SK (same).
DIM_CUSTOMER / DIM_PRODUCT are keyed by surrogate SK; a natural key (CUST_NK/PROD_NK) spans versions 1..N.
STG_*.*_NK are natural keys resolved into the dimensions; STG_SALES_RESOLVED carries the resolved SKs.
AUDIT_* and FACT_SALES_RUNAGG are fed only by triggers (no declared FK); ETL_RUNLOG is a plain log.

E. Operations Runbook ↑ top

E.1 Run the ETL batch

Reset staging at the top level (DW-01): TRUNCATE TABLE DW.STG_CUSTOMER RESTART IDENTITY, and likewise STG_PRODUCT and STG_SALES. Confirm STG_ID restarts at 1 (tests SI1/SI2b).
Land the source batch: INSERT the customer, product and sales rows for this run, all tagged with the same SRC_BATCH.
Stamp the run parameters at the top level (DW-02): SET DW.RUN_ID = n; SET DW.RUN_LOAD_DATE = '<date>'; SET DW.RUN_BATCH_TAG = '<batch>'.
Run the cycle: CALL DW.SP_RUN_ETL(n, '<date>', '<batch>').
Post-check (below), then reconcile (E.2).

Pre-checks: confirm the job's library list includes DW; confirm staging holds exactly this batch; confirm the three SETs took (VALUES DW.RUN_ID, VALUES DW.RUN_BATCH_TAG).

Post-checks after a run:

SP_RUN_ETL returned code = 0; ETL_RUNLOG has three rows for this RUN_ID (DIM_CUSTOMER / DIM_PRODUCT / FACT_SALES).
Dimension counts moved as expected: new NKs added a version-1 current row; changed NKs closed the old version (IS_CURRENT='N', EFF_TO = load_date-1) and added a new one; unchanged NKs are untouched (no spurious re-versioning — SCD3/IDEM2).
Surrogate keys came from the sequences (customer 1000+, product 2000+, fact 5000+); a re-versioned row got a fresh key, never a reused one (SCD5).
FACT_SALES row count and SUM(EXT_AMT) reconcile to the staged batch; a corrected re-load updated in place (same FACT_SK), it did not duplicate (FM1/FM2).
AUDIT_DIM_CUSTOMER has the expected 'INS'/'UPD' rows. Expect AUDIT_FACT_SALES and FACT_SALES_RUNAGG to be empty of MERGE-driven events at HEAD (DW-05) — this is the known limitation, not a run failure.

E.2 Reconciling figures

The same figures the volume simulation checks against a hand-derived JS oracle:

Fact total — SELECT COUNT(*), SUM(EXT_AMT) FROM DW.FACT_SALES. Batch1 = 4 rows / 270.00; after batch2 = 5 rows / 330.00 (S0001 corrected 120→144, S0005 new +36).
Per-line EXT_AMT — each equals QTY × UNIT_PRICE (computed in STG_SALES_RESOLVED).
Dimension resolution — a fact row's CUST_SK/PROD_SK equal the current dimension version's SK (re-pointed on every MERGE run, so SCD2 changes flow through).
SCD version chain — CALL DW.SP_CUST_VERSION_CHAIN('C001') returns the versions oldest-to-newest (v1 then v2), a WITH RECURSIVE walk of VERSION_NO.
Reporting — V_PRODUCT_RANK ranks by revenue (P001 = 240.00 rank 1, P002 = 90.00 after batch2); a GROUP BY ROLLUP region query yields a grand-total (NULL-region) row equal to the full 330.00.

The running-aggregate reconciliation (DW-05). On production DB2 for i, FACT_SALES_RUNAGG would equal SELECT COUNT(*), SUM(EXT_AMT) FROM FACT_SALES because the fact triggers fold each MERGE transition set into it. On this engine a MERGE fires no triggers, so RUNAGG never receives the INSERT/UPDATE events and stays empty; after a plain-DELETE purge it even goes negative (-1|-36). Operationally: treat FACT_SALES itself as the source of truth and reconcile against a live SUM(EXT_AMT); do not rely on FACT_SALES_RUNAGG until DW-05 is fixed at the engine. The 'DEL' audit path (via SP_PURGE_BATCH) is reliable.

E.3 Failure & re-run rules

Situation	Behaviour	Action
Re-run same batch, same load date	Dims: unchanged attributes → no close-out, no new version. Fact MERGE: matched → updated in place with the same values.	Idempotent. Row counts and SUM(EXT_AMT) stay put (IDEM1–5, VOL11–13). Safe to re-run.
Corrected re-load of a sale (same SALE_NK, new qty)	Fact MERGE WHEN MATCHED updates EXT_AMT in place; FACT_SK unchanged.	Intended path. No duplicate row (FM1/FM2).
Purge a batch	`SP_PURGE_BATCH` DELETEs by LOAD_BATCH; fires TR_FACT_DEL (audit + RUNAGG decrement).	Re-load by re-running `SP_LOAD_FACT` off current staging (PG7/PG8).
Embedded TRUNCATE via SP_STAGE_RESET	Fails: raw `near "TRUNCATE": syntax error` (DW-01).	Issue the three TRUNCATEs at the top level instead. Do not put staging reset on the CALL path.
SET a global variable inside a procedure body	Fails: `SQL0206` not a variable/parameter (DW-02).	Stamp DW.RUN_ID/RUN_LOAD_DATE/RUN_BATCH_TAG with top-level SETs before CALL.
FACT_SALES_RUNAGG / AUDIT_FACT_SALES empty after a load	Expected at HEAD: MERGE fires no triggers (DW-05).	Reconcile against a live `SUM(EXT_AMT) FROM FACT_SALES`. Not a run failure.
Empty-set statement (DELETE matches nothing)	Statement trigger still fires once, N_ROWS=0 audit row (EMP2).	Normal — the DB2 statement-trigger-always-fires rule.

Because every dimension change is captured in AUDIT_DIM_CUSTOMER and the full SCD2 version history is retained in the dimensions, any run's dimension effect is fully reconstructable. The fact table is authoritative for reconciliation; the CDC/aggregate side carries the documented DW-05 gap on MERGE-driven fact changes.

F. Developer Reference ↑ top

The complete SQL-PL surface, from schema.sql and routines.sql. All objects are in schema DW.

F.1 Tables, sequences, distinct types, global variables

See section D for the full column-level dictionary of all 12 tables. Summary of the non-table objects:

CREATE SEQUENCE SQ_CUST_SK / SQ_PROD_SK / SQ_FACT_SK / SQ_AUDIT_SK: Surrogate-key generators (START 1000 / 2000 / 5000 / 1, INCREMENT 1). Drawn via NEXT VALUE FOR — but never inline inside a bulk INSERT/MERGE (see DW-03 and SP_SEQ_RESERVE).
CREATE TYPE MONEY_T AS DECIMAL(13,2) WITH COMPARISONS: Distinct money-measure type for fact money columns.
CREATE TYPE SKEY_T AS INTEGER WITH COMPARISONS: Distinct surrogate-key type.
CREATE VARIABLE RUN_ID INTEGER DEFAULT 0 / RUN_LOAD_DATE DATE DEFAULT '2020-01-01' / RUN_BATCH_TAG CHAR(10) DEFAULT 'INIT': Run-scoped ETL control values, stamped by the driver via top-level SET before SP_RUN_ETL (assigning them inside a routine body is DW-02).

F.2 Procedures (8)

SP_RUN_ETL (IN P_RUN_ID INTEGER, P_LOAD_DATE DATE, P_BATCH VARCHAR(10)): Orchestrator: CALL SP_LOAD_DIM_CUSTOMER → SP_LOAD_DIM_PRODUCT → SP_LOAD_FACT, inserting one ETL_RUNLOG row per step with the staged row count. Idempotent on an unchanged batch/date.
SP_LOAD_DIM_CUSTOMER (IN P_LOAD_DATE DATE, P_BATCH VARCHAR(10)): SCD2 load. Step 1: set-based UPDATE closes out current rows whose staged attributes changed (EFF_TO = P_LOAD_DATE - 1 DAY, IS_CURRENT='N') — fires TR_DIMCUST_UPD once. Step 2: SP_SEQ_RESERVE('SQ_CUST_SK', n) then INSERT ... SELECT fresh current versions with V_BASE + ROW_NUMBER() OVER (ORDER BY CUST_NK) - 1 keys and VERSION_NO = COALESCE(MAX(prior),0)+1 — fires TR_DIMCUST_INS once.
SP_LOAD_DIM_PRODUCT (IN P_LOAD_DATE DATE, P_BATCH VARCHAR(10)): Identical SCD2 pattern over STG_PRODUCT, keys from SQ_PROD_SK. No product CDC trigger by design.
SP_LOAD_FACT (IN P_BATCH VARCHAR(10)): DELETE FROM STG_SALES_RESOLVED; SP_SEQ_RESERVE('SQ_FACT_SK', n) for the count of not-yet-present sale NKs; INSERT ... SELECT builds the resolved source (JOIN to current dims, EXT_AMT = CAST(QTY*UNIT_PRICE AS DECIMAL(13,2)), RN_KEY via ROW_NUMBER() ordered so new NKs get the reserved keys); then MERGE INTO FACT_SALES USING STG_SALES_RESOLVED ON SALE_NK — WHEN MATCHED UPDATE all measures + LOAD_BATCH, WHEN NOT MATCHED INSERT with RN_KEY. Fires no triggers (DW-05).
SP_SEQ_RESERVE (IN P_SEQNAME VARCHAR(20), P_N INT; OUT P_BASE INT): Reserve a contiguous block of P_N keys from the named sequence; returns the first key in P_BASE. Sets P_BASE = 0 first (so a P_N=0 call does not clobber the caller's default with NULL — an app bug fixed here), then, if P_N > 0, takes one NEXT VALUE FOR into P_BASE and WHILE-loops P_N-1 more (each a separate top-level PL statement, so not subject to the same-statement caching collapse of DW-03). Caller assigns the block via ROW_NUMBER() offsets.
SP_PURGE_BATCH (IN P_BATCH VARCHAR(10)): Bulk DELETE FROM FACT_SALES WHERE LOAD_BATCH = P_BATCH — fires TR_FACT_DEL correctly (plain DELETE, not MERGE), exercising the OLD_TABLE path.
SP_CUST_VERSION_CHAIN (IN P_CUST_NK VARCHAR(10)) DYNAMIC RESULT SETS 1: WITH RECURSIVE CTE walking VERSION_NO 1..N for a natural key (anchor VERSION_NO=1, recursive join on VERSION_NO+1), returned oldest-to-newest via a WITH RETURN cursor.
SP_STAGE_RESET (): Contains the three TRUNCATE ... RESTART IDENTITY statements. Not on the live path: embedded TRUNCATE fails (DW-01); kept only to keep that failure reproducible. Operators issue the TRUNCATEs top-level.

F.3 Triggers (5) & the views

TR_DIMCUST_INS — AFTER INSERT ON DIM_CUSTOMER, REFERENCING NEW_TABLE AS NT, FOR EACH STATEMENT: One audit detail row per new dimension row: COUNT(*) INTO V_N FROM NT, CALL SP_SEQ_RESERVE('SQ_AUDIT_SK_DC', V_N, V_BASE), then INSERT ... SELECT V_BASE + ROW_NUMBER() OVER (ORDER BY NT.CUST_SK) - 1, 'INS', .... Uses the reserved-block key trick because an inline NEXT VALUE FOR over NT would hit the same DW-03 collapse (an earlier bare version caused a UNIQUE failure on multi-row batches).
TR_DIMCUST_UPD — AFTER UPDATE ON DIM_CUSTOMER, REFERENCING OLD TABLE AS OT NEW TABLE AS NT, FOR EACH STATEMENT, WHEN (EXISTS (SELECT 1 FROM NT)): One summary 'UPD' audit row per close-out statement, N_ROWS = COUNT(*) FROM NT (whole batch size), key from an inline NEXT VALUE FOR SQ_AUDIT_SK (single row, so DW-03-safe). The WHEN EXISTS gate suppresses an empty-batch fire. (Note the deliberate two-word OLD TABLE/NEW TABLE spelling here vs the underscore spelling in TR_DIMCUST_INS — both spellings are exercised.)
TR_FACT_INS — AFTER INSERT ON FACT_SALES, REFERENCING NEW_TABLE AS NT, FOR EACH STATEMENT: Inserts an 'INS' audit row (COUNT(*), SUM(EXT_AMT)), then MERGEs the batch's count/sum into FACT_SALES_RUNAGG. Unreachable at HEAD because its firing statement is a MERGE (DW-05).
TR_FACT_UPD — AFTER UPDATE ON FACT_SALES, REFERENCING OLD_TABLE AS OT NEW_TABLE AS NT, FOR EACH STATEMENT: Audit row with the delta SUM(NT.EXT_AMT) - SUM(OT.EXT_AMT); folds that delta into FACT_SALES_RUNAGG.TOTAL_AMT. Also unreachable at HEAD (MERGE UPDATE branch, DW-05).
TR_FACT_DEL — AFTER DELETE ON FACT_SALES, REFERENCING OLD_TABLE AS OT, FOR EACH STATEMENT: Audit 'DEL' row (COUNT(*), SUM(EXT_AMT) from OT); decrements FACT_SALES_RUNAGG. Reachable and correct — SP_PURGE_BATCH uses a plain DELETE. From an empty (DW-05) baseline the decrement goes negative (E.2).
VIEW V_CUST_RUNNING_TOTAL: Per-customer running total via a window: SUM(F.EXT_AMT) OVER (PARTITION BY DC.CUST_NK ORDER BY F.SALE_DATE, F.FACT_SK ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), joined to DIM_CUSTOMER on the surrogate key.
VIEW V_PRODUCT_RANK: Revenue rank: RANK() OVER (ORDER BY SUM(F.EXT_AMT) DESC) over a GROUP BY PROD_NK, PROD_NAME.

F.4 ETL SQL-PL patterns

The load-bearing SQL-PL idioms, and the concrete workarounds each carries:

SCD type-2 (close-out + re-version). A set-based UPDATE (with an EXISTS attribute-change predicate) closes changed current rows; a set-based INSERT ... SELECT (with a NOT EXISTS guard) adds fresh current versions. Two set statements → two single transition-table firings.
MERGE upsert (natural-key fact load). MERGE INTO FACT_SALES USING STG_SALES_RESOLVED ON SALE_NK with WHEN MATCHED THEN UPDATE / WHEN NOT MATCHED THEN INSERT. The source is pre-flattened to avoid a JOIN inside USING (...) (DW-04). MERGE fires no triggers on this engine (DW-05).
Transition tables (OLD_TABLE/NEW_TABLE, statement-level). CDC and the running aggregate read the whole affected set once per statement — not RBAR. Both the underscore and two-word spellings are used; a WHEN EXISTS gate suppresses empty-batch fires; an empty affected set still fires once (N_ROWS=0).
Sequence keys via reserved block. SP_SEQ_RESERVE advances the real sequence N times in a WHILE loop (each a separate statement) and the caller assigns V_BASE + ROW_NUMBER() OVER (...) - 1 — because an inline NEXT VALUE FOR over a multi-row SELECT/MERGE-insert collapses to one shared value (DW-03).
Window / CTE / recursive reporting. SUM() OVER (PARTITION BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running totals; RANK() OVER (...); GROUP BY ROLLUP grand totals; WITH RECURSIVE version-chain walk.
CREATE VARIABLE run parameters. Stamped top-level with SET, read with VALUES DW.RUN_ID (assigning inside a body is DW-02).

F.5 Platform SQLSTATE / bug table

DWETL/i raises no application SQLSTATEs of its own (unlike the RPG-fronted apps); the notable statuses are the confirmed platform limitations the source documents and probes honestly. Each has an isolated repro logged in SQLPL-SIM-FINDINGS.md and a live probe in dw_daily.mjs.

Code	SQLSTATE / SQLCODE	Where	Meaning & workaround
DW-01	raw syntax error	SP_STAGE_RESET (embedded TRUNCATE)	Embedded `TRUNCATE` in a SQL-PL body fails (`near "TRUNCATE": syntax error`); works top-level. Issue TRUNCATEs top-level.
DW-02	SQL0206 (-206)	SET of a global CREATE VARIABLE in a body	“not a variable or parameter in this routine”; works top-level. Stamp RUN_* top-level before CALL.
DW-03	silent-wrong / UNIQUE violation	inline NEXT VALUE FOR in a bulk SELECT/MERGE	`DB2_NEXTVAL` is deterministic, so the planner collapses all rows to one value. Use `SP_SEQ_RESERVE` + ROW_NUMBER offset.
DW-04	parse corruption	JOIN inside a MERGE USING (...) subquery	Pre-resolve into `STG_SALES_RESOLVED` (flat single-table MERGE source).
DW-05	no error, triggers silently skipped	MERGE-driven changes on FACT_SALES	MERGE fires no triggers (`runMerge` bypasses `runDmlTrg`); AUDIT_FACT_SALES / RUNAGG stay empty. Plain DELETE (TR_FACT_DEL) is unaffected. Reconcile off FACT_SALES directly.
42898	honest reject	BEFORE trigger + transition table	Correctly refused at CREATE (HR1).
42887	honest reject	OLD_TABLE on AFTER INSERT trigger	Correctly refused at CREATE (HR2).
—	honest reject	INSTEAD OF + transition table	Correctly refused at CREATE (HR3).

G. Glossary ↑ top

Change data capture (CDC): Recording what changed in a table as it changes. Here: the transition-table triggers write one audit row per affected statement into AUDIT_DIM_CUSTOMER / AUDIT_FACT_SALES.
Dimension / fact (star schema): A dimensional model: descriptive dimension tables (customer, product) surrounding a numeric fact table (sales) that references them by surrogate key.
ETL: Extract–Transform–Load: land source data (stage), reshape it (transform), and load it into the warehouse. DWETL/i's SP_RUN_ETL is the load half; staging is loaded by the caller.
Extended amount (EXT_AMT): The line value QTY × UNIT_PRICE, computed once into the fact.
Idempotent: Safe to run again with the same result. Re-running the ETL with the same staged batch and load date changes nothing (unchanged dims, MERGE-in-place fact).
MERGE (upsert): A single statement that inserts non-matching rows and updates matching ones. Here keyed on SALE_NK, so a corrected sale updates in place.
Natural key (NK) / surrogate key (SK): The business key (CUST_NK, PROD_NK, SALE_NK) vs the warehouse-generated integer key (CUST_SK, PROD_SK, FACT_SK) from a sequence. One NK spans many SCD2 versions, each with its own SK.
Running aggregate: A maintained-in-place total (FACT_SALES_RUNAGG) updated from each transition set rather than recomputed by a full scan. Currently not fed by the MERGE loads (DW-05).
SCD type-2 (slowly changing dimension): Versioning strategy that keeps history: a changed attribute closes the current version (IS_CURRENT='N', EFF_TO set) and inserts a new current version with a fresh SK and incremented VERSION_NO.
Sequence (NEXT VALUE FOR): A DB2 sequence object generating monotonic keys. Here consumed via SP_SEQ_RESERVE to avoid the same-statement collapse (DW-03).
Staging (landing zone): Raw source rows loaded before transformation, reset between loads with TRUNCATE ... RESTART IDENTITY.
STRSQL: Start SQL Interactive Session — the IBM i interactive SQL prompt. DWETL/i's only interactive surface (there is no 5250 screen).
Transition table (OLD_TABLE / NEW_TABLE): The set of rows a statement affected, exposed to a FOR EACH STATEMENT trigger — NEW_TABLE for inserts/updates, OLD_TABLE for deletes/updates — so the whole batch is processed in one firing.
WITH RECURSIVE / window function / ROLLUP: Recursive common table expression (the SCD version-chain walk); windowed aggregate over an ordered partition (running total, rank); grouping-sets extension producing sub- and grand-total rows.

DWETL/i — SteelFrame X application operation manual. All table, procedure, trigger, sequence, variable and column names are the application's own, grounded in the committed source (sqlpl-app-dw/src/schema.sql, src/routines.sql, test/dw_daily.mjs); the DW-01..DW-05 platform findings are documented as-is. ← back to Operation Manuals