SEC/i — Securities / Investment Portfolio Accounting

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

SEC/i is a securities and investment-portfolio accounting application: it books buy and sell trades, opens and recursively relieves tax lots under FIFO/LIFO, tracks positions and multi-currency cash, applies corporate-action stock splits, rolls sub-accounts up a hierarchy, marks the portfolio to market, and posts every money movement to a self-balancing general ledger. Unlike the RPG-driven applications, SEC/i is pure SQL PL: there is no DDS display file and no 5250 screen. Every routine — procedure, function, and trigger — is invoked directly, through STRSQL, an EXEC SQL CALL from any host program, or a JDBC/CLI client. The application was built SQL-first specifically to exercise the hard corners of SQL PL: procedural and WITH RECURSIVE recursion, handler-in-handler RESIGNAL across CALL frames, nested cursors, SAVEPOINT/ROLLBACK TO SAVEPOINT, MERGE, high-precision DECIMAL, GENERATED columns, global temporary tables, and trigger cascades. This manual is grounded entirely in the committed source (sqlpl-app-sec/src/schema.sql, routines.sql, seed.mjs, and the test/sec_daily.mjs driver). Everything runs in library SECLIB.

A. Overview & Architecture ↑ top

A.1 What it does

SEC/i keeps the books for an investment portfolio across a hierarchy of sub-accounts:

• Trade booking — a buy opens a tax lot at a commission-loaded unit cost; a sell recursively relieves open lots FIFO or LIFO, computing realized gain/loss per lot consumed. Each booking posts cash and a balanced GL entry set, then re-syncs the position.
• Tax-lot relief (recursive) — SP_RELIEVE_LOT takes shares from one lot, writes a LOTREL audit row, and self-calls for the remainder until the sell is fully covered or the open lots are exhausted (an insufficient-shares rejection).
• Corporate actions — a stock split rewrites OPENQTY/COSTPX on every open lot of the affected security, across every holding account.
• Sub-account roll-up — total quantity held for a security summed across a master account and all its descendant sub-accounts, computed two independent ways (procedural self-CALL and a WITH RECURSIVE CTE) that must agree.
• Mark-to-market — a nightly run prices every open position at its latest price on/before the as-of date, applies the currency's FX rate, and snapshots cost, market value, and unrealized gain/loss into MTMSNAP.
• General ledger — every trade, cash effect, and cost-out posts a balanced entry set so SUM(GLENTRY.AMT)=0 is an invariant checked on every run.

A.2 A SQL-PL-only application (no 5250 screen)

SEC/i has no display file, no subfile, and no interactive program. All business logic lives in DB2 for i functions, stored procedures, and table triggers in routines.sql; all state lives in the SECLIB tables in schema.sql. There is nothing to "sign on to" — the application is its SQL-PL surface. It is driven three equivalent ways:

• STRSQL — an operator or analyst types a CALL SECLIB.SP_... or a SELECT ... FROM SECLIB.... at the interactive SQL session (section B.1).
• EXEC SQL CALL — any RPG/COBOL/host program can embed a call to a SEC/i procedure; the procedures are the reusable rule engine.
• A JDBC/CLI client — the test/sec_daily.mjs driver calls the procedures exactly as an external client would, over the same SQL engine.

The benefit is that the rules are one auditable place and the same procedure is reachable from batch, ad hoc, and embedded contexts identically. Integrity is enforced in the data layer by triggers (position cannot go negative; every lot adjustment is audited; a cascade counts the audits) rather than by any calling program.

A.3 Component & flow

  TRADE ENTRY            PROCESSING                    VALUATION / REPORTING
  -----------            ----------                    ---------------------
  INSERT INTO TRADE      SP_BOOK_TRADE (one trade)     SP_MTM_RUN   -> MTMSNAP
    (TSTAT='E')            buy  -> open LOT               (price x FX, per position)
        |                  sell -> SP_RELIEVE_LOT        SP_MTM_REPORT (2 result sets)
        v                          (RECURSIVE, FIFO/LIFO)
  SP_TRADE_BATCH          -> LOTREL / REALIZEDPL       SP_ROLLUP_ACCT (RECURSIVE)
    books all 'E' trades  -> CASHLEDG + GLENTRY        SP_ROLLUP_ALL_MASTERS -> ROLLUPCACHE
    GTT + SAVEPOINT       -> SP_UPSERT_POSITION (MERGE) FN_SUBTREE_QTY (WITH RECURSIVE)
    LEAVE/ITERATE            -> POSITION

  CORPORATE ACTION       SP_PROCESS_SPLIT (nested cursor: accounts x lots)
  INSERT INTO CORPACT       rewrites every open LOT of the security, re-MERGEs POSITION

  triggers on LOT/POSITION:  TR_LOT_AUDIT -> (SECAUDIT) -> TR_LOT_AUDIT_CASCADE -> CASCADEMARK
                             TR_POSITION_VETO (BEFORE UPDATE, QTYHELD < 0 veto)

A single event — booking one sell — flows: SP_BOOK_TRADE takes a SAVEPOINT, calls SP_RELIEVE_LOT which recurses lot-by-lot writing LOTREL rows and updating LOT.OPENQTY (firing TR_LOT_AUDIT, which cascades into TR_LOT_AUDIT_CASCADE); then it posts CASHLEDG and the balanced GLENTRY set, inserts REALIZEDPL, and MERGEs the position via SP_UPSERT_POSITION. If relief runs out of shares it SIGNALs 75020, the outer handler ROLLBACKs to the savepoint, and the trade is marked rejected — leaving the lots exactly as they were.

A.4 Object inventory

Object	Type	Role
ACCT	Table	Chart of sub-accounts (self-referencing hierarchy).
SECMAST	Table	Securities master (incl. DECIMAL(31,6) FACEVAL).
PRICEHIST	Table	Daily close prices (drives MTM).
FXHIST	Table	FX rate history (CCY→USD).
TRADE	Table	Trade blotter (entered/booked/rejected).
LOT	Table	Open/relieved tax lots (identity PK).
LOTREL	Table	Per-(sell,lot) relief audit & realized-P&L basis.
POSITION	Table	Denormalized qty/avg-cost per (account,security), MERGE-maintained.
CASHLEDG	Table	Append-only cash ledger (idempotency key SRCDOC+SRCSEQ).
CORPACT	Table	Corporate actions (stock splits).
REALIZEDPL	Table	Realized P&L (GENERATED NETGAIN column).
MTMSNAP	Table	Mark-to-market snapshot per (account,security,date).
GLACCT / GLENTRY	Table	GL balances + balanced entry ledger.
SECAUDIT	Table	Diagnostics / reject / lot-adjust audit trail.
ROLLUPCACHE	Table	Recursive roll-up totals per master account.
CASCADEMARK	Table	Trigger-cascade hit counter.
FN_* (2)	SQL functions	Settlement-date math; recursive subtree quantity.
SP_* (9)	SQL procedures	Book/relieve/upsert/batch/rollup/split/MTM/report.
TR_* (3)	Triggers	Lot audit, cascade counter, position veto.

The full catalogue is 2 functions + 9 procedures + 3 triggers over 15 base tables. Sections D and F expand each. All names are the application's own, grounded in the committed source.

B. Online / Access ↑ top

B.1 STRSQL CALL access (there is no screen)

Honest statement: SEC/i ships no 5250 display file and no interactive program. There is no menu, no subfile, no command-key handling — nothing to sign on to. The "online" surface of this application is the SQL-PL routines themselves, reached from an interactive SQL session (STRSQL), from an EXEC SQL CALL in a host program, or from a JDBC/CLI client. The operator equivalent of "open a screen and press Enter" is "type a CALL or SELECT and press Enter". Before invoking anything, the job's library list must include SECLIB — the tested job runs with LIBL = QSYS QGPL SECLIB QTEMP and CURLIB = SECLIB.

To do this	Type in STRSQL (or CALL)
Book one entered trade	CALL SECLIB.SP_BOOK_TRADE('T00001', ?, ?)
Book every entered trade in a run	CALL SECLIB.SP_TRADE_BATCH('BATCH1', 100, ?, ?, ?)
Apply a corporate-action split	CALL SECLIB.SP_PROCESS_SPLIT('CA00001', ?, ?)
Mark the portfolio to market for a date	CALL SECLIB.SP_MTM_RUN(20260802, ?, ?)
Report MTM (two result sets)	CALL SECLIB.SP_MTM_REPORT(20260802)
Roll a master account's holdings up	CALL SECLIB.SP_ROLLUP_ACCT('MASTER01','AAPL000001', ?)
Rebuild the whole roll-up cache	CALL SECLIB.SP_ROLLUP_ALL_MASTERS('RUN1', ?)
Ask a subtree quantity (scalar function)	VALUES SECLIB.FN_SUBTREE_QTY('MASTER01','AAPL000001')

The ? placeholders are the procedures' OUT parameters. In STRSQL, host-variable OUTs are shown after the call returns; from a client (as in sec_daily.mjs) they come back as the call's outs[]. A trade is staged by inserting a row into SECLIB.TRADE with TSTAT='E'; booking flips it to 'B' (booked) or 'R' (rejected). SEC/i has no notion of "the current user's session state" — each CALL is a complete unit of work.

B.2 Controls & audit

Honest statement: SEC/i does not implement a four-eyes maker–checker or separate-authorization workflow — there is no "one user enters, a second approves" gate. A trade inserted as 'E' is booked immediately by whoever calls SP_BOOK_TRADE. The control posture the application does have is entirely data-layer:

• Lot-adjustment audit (SECAUDIT via TR_LOT_AUDIT). Every change to a lot's OPENQTY — from recursive relief or from a split — writes a LOT_ADJUST row recording the before/after quantity. A second trigger (TR_LOT_AUDIT_CASCADE) fires off that write and increments a CASCADEMARK counter, so the audit trail is itself provably complete.
• Reject / diagnostic audit. A rejected sell (insufficient shares) writes a TRADE_REJECT SECAUDIT row from inside the exit handler, and the batch writes a BATCH_RUN summary row — a durable record survives even though the failing work was rolled back.
• Idempotency keys. CASHLEDG and GLENTRY both carry a unique index on (SRCDOC, SRCSEQ), so a trade's cash and GL legs can never be double-posted.
• State gating. SP_BOOK_TRADE returns RC=1 for an already-booked trade (no re-book, no second lot), RC=2 for a halted security, RC=3 for insufficient shares; SP_PROCESS_SPLIT is a no-op on an already-'P'rocessed corporate action.
• Integrity triggers & the GL invariant. TR_POSITION_VETO raises SQLSTATE 75021 rather than let a position go negative; the balanced double/triple GL postings make SUM(GLENTRY.AMT)=0 a checkable invariant.

In sum the control model is audit + idempotency + state gating + a balanced-ledger invariant, enforced in the data layer, not a segregation-of-duties approval workflow.

C. Batch & Cycle Procedures ↑ top

SEC/i has no time-triggered "nightly job" wrapper program; its cycle is a fixed sequence of procedure CALLs an operator or scheduler drives in order. There is no control-row idiom (no LNCTL analogue) — the as-of date and run tokens are passed as CALL parameters. A typical day is: stage trades → SP_TRADE_BATCH → process any corporate actions (SP_PROCESS_SPLIT) → rebuild roll-ups (SP_ROLLUP_ALL_MASTERS) → mark to market (SP_MTM_RUN) → report (SP_MTM_REPORT) → verify the GL invariant.

-- stage trades onto the blotter (TSTAT='E'), then run the day:
CALL SECLIB.SP_TRADE_BATCH('BATCH1', 100, ?, ?, ?);   -- book all entered trades
CALL SECLIB.SP_PROCESS_SPLIT('CA00001', ?, ?);        -- apply any split
CALL SECLIB.SP_ROLLUP_ALL_MASTERS('RUN1', ?);         -- rebuild ROLLUPCACHE
CALL SECLIB.SP_MTM_RUN(20260802, ?, ?);               -- snapshot MTMSNAP for the as-of date
CALL SECLIB.SP_MTM_REPORT(20260802);                  -- detail + summary result sets

C.1 Full procedure / trigger table

Routine	Purpose	Calls / fires	Inputs	Outputs / effects	Frequency
SP_BOOK_TRADE	Book one trade: buy opens a lot, sell relieves lots recursively; post cash + GL; MERGE position.	SP_RELIEVE_LOT, SP_UPSERT_POSITION; fires TR_LOT_AUDIT.	P_TRDNO.	OUT P_RC (0 booked / 1 already / 2 halted / 3 insufficient / 9 unexpected), P_MSG; LOT/LOTREL/REALIZEDPL/CASHLEDG/GLENTRY/POSITION rows.	Per trade / on demand.
SP_RELIEVE_LOT	Recursively relieve one lot at a time (FIFO/LIFO) until the sell is covered.	Self-CALL; fires TR_LOT_AUDIT.	P_SELLTRD, P_ACCTNO, P_SECID, P_NEEDQTY, P_SELLPX, P_METHOD, P_COMMPERSH.	LOTREL rows; LOT.OPENQTY/LSTAT updates; SIGNAL 75020 when exhausted.	Called by SP_BOOK_TRADE.
SP_UPSERT_POSITION	Recompute qty + weighted avg cost from open lots and MERGE into POSITION.	MERGE; may fire TR_POSITION_VETO.	P_ACCTNO, P_SECID.	POSITION row upserted.	Called by booking / split.
SP_TRADE_BATCH	Book every ENTERED trade in TRDNO order (GTT + SAVEPOINT + labeled LEAVE/ITERATE + result set).	SP_BOOK_TRADE per trade.	P_RUNTOKEN, P_MAXBOOK.	OUT P_BOOKED, P_REJECTED, P_SKIPPED; SECAUDIT BATCH_RUN row; 1 result set (per-trade outcome).	Daily.
SP_PROCESS_SPLIT	Apply a stock split to every open lot of the security across every holding account (nested cursor).	SP_UPSERT_POSITION; fires TR_LOT_AUDIT.	P_CAID.	OUT P_LOTSADJ, P_ACCTSADJ; LOT rewrites; CORPACT→'P'.	On corporate action.
SP_ROLLUP_ACCT	Sum a security's open qty across an account and its subtree (procedural recursion, OUT threaded).	Self-CALL per hierarchy level.	P_ACCTNO, P_SECID.	OUT P_QTY.	On demand.
SP_ROLLUP_ALL_MASTERS	Drive SP_ROLLUP_ACCT for every master × every held security; write ROLLUPCACHE.	SP_ROLLUP_ACCT; MERGE.	P_ASOFTOK.	OUT P_WRITTEN; ROLLUPCACHE rows.	Daily.
SP_MTM_RUN	Price every open position at latest px on/before as-of, apply FX, write MTMSNAP.	MERGE per position.	P_ASOF.	OUT P_MARKED, P_NOPRICE; MTMSNAP rows.	Daily (nightly).
SP_MTM_REPORT	Return two result sets for an as-of date: per-position detail and per-account summary.	—	P_ASOF.	2 dynamic result sets.	On demand / reporting.

Triggers TR_LOT_AUDIT, TR_LOT_AUDIT_CASCADE and TR_POSITION_VETO are not called directly; they fire automatically off the DML the procedures above perform (section F.3).

C.2 Daily cycle detail

SP_TRADE_BATCH

Books every trade with TSTAT='E' in TRDNO order. Its BATCH_LOOP FOR-loop LEAVEs once booked+rejected reaches P_MAXBOOK (a safety cap), and ITERATEs past any trade whose account is closed (ACCT.ASTAT='C'), counting it as skipped. Each booking is wrapped in its own SAVEPOINT SP_TRADE: a genuinely unexpected RC=9 rolls back only that trade's work without aborting the batch; handled business outcomes (halted / insufficient) are counted as rejected and left in place. A DECLARE GLOBAL TEMPORARY TABLE SESSION.BATCHSTAGE ... WITH REPLACE stages each trade's outcome, surfaced as the call's one dynamic result set via a WITH RETURN WITH HOLD cursor.

Expected OUT parms (seed batch: 1 buy books, 1 oversell rejects, 1 closed-acct skipped):
  P_BOOKED   = 1
  P_REJECTED = 1     the oversell (insufficient shares)
  P_SKIPPED  = 1     the closed-account trade, ITERATEd past
  result set : 3 rows in BATCHSTAGE (BOOKED / REJECTED-3 / SKIPPED-CLOSED-ACCT)

SP_MTM_RUN

For every POSITION with QTYHELD > 0, looks up the security's latest PRICEHIST.PXCLOSE on or before P_ASOF and the currency's latest FXHIST.RATE, then MERGEs a snapshot: MKTVALUE = QTYHELD × PX × FX, COSTBASIS = QTYHELD × AVGCOST, UNREALGN = MKTVALUE - COSTBASIS. A position with no price on/before the date is skipped and counted in P_NOPRICE (via a labeled ITERATE POS_CUR_LBL).

Expected (SUB0003/AAPL after split: 60 sh @96.05, px 195.75, USD fx 1.0):
  COSTBASIS = 60 x 96.05  = 5763.00
  MKTVALUE  = 60 x 195.75 = 11745.00
  UNREALGN  = 11745.00 - 5763.00 = 5982.00

SP_ROLLUP_ALL_MASTERS

Nested FOR-loop: outer over every master account (PARENTACC IS NULL), inner over every security with an open lot anywhere. For each pair it calls the recursive SP_ROLLUP_ACCT and, when the total is non-zero, MERGEs it into ROLLUPCACHE. On the seed set it writes exactly the three non-zero (master, security) combos: MASTER01/AAPL=30, MASTER01/MSFT=10, STANDALONE/AAPL=5 — proving one master's subtree never leaks into a sibling's.

C.3 Ordering & dependencies

• Book before you value. SP_MTM_RUN and the roll-ups read POSITION/LOT, which only SP_BOOK_TRADE (directly or via the batch) populates — book the day's trades first.
• Splits before valuation. SP_PROCESS_SPLIT rewrites lot quantities and cost and re-MERGEs positions; run it before the MTM and roll-up so the snapshot reflects post-split holdings.
• Roll-up after booking & splits. SP_ROLLUP_ALL_MASTERS reads open lots, so it must follow anything that opens, relieves, or splits a lot.
• Within a sell, order is fixed by the code: SAVEPOINT → recursive relief → cash/GL/realized-P&L postings → position MERGE → mark trade booked. The GL cost-out uses SUM(LOTREL.RELCOST) (the exact relieved basis), never qty × blended AVGCOST, so the debit=credit invariant holds even when a sell spans lots at different costs.

D. Data Files (data dictionary) ↑ top

All tables are in library SECLIB, grounded in schema.sql. Dates are stored as INT in YYYYMMDD form. Quantities are DECIMAL(19,4) (fractional shares allowed for funds); prices and cost bases are DECIMAL(19,6); cash and GL amounts are DECIMAL(19,2); SECMAST.FACEVAL is a deliberately wide DECIMAL(31,6).

ACCT — Chart of sub-accounts (PK ACCTNO)

Field	Type	Meaning
ACCTNO	VARCHAR(10)	Account number (PK).
PARENTACC	VARCHAR(10)	Parent account; NULL = top-level (master) account.
ACCTNM	VARCHAR(40)	Account name.
BASECCY	CHAR(3)	Base reporting currency (default USD).
ASTAT	CHAR(1)	A active, C closed (batch skips a closed account's trades).

Seeded hierarchy: MASTER01 → {SUB0001 → SUB0003, SUB0002}; STANDALONE is an independent master (EUR base) used to prove subtree isolation.

SECMAST — Securities master (PK SECID)

Field	Type	Meaning
SECID	VARCHAR(12)	CUSIP-like identifier (PK).
SECNM	VARCHAR(40)	Security name.
SECTYPE	CHAR(1)	E equity, B bond, F fund.
CCY	CHAR(3)	Trading/pricing currency.
LOTSIZE	INT	Round-lot size (informational).
FACEVAL	DECIMAL(31,6)	Bond face value — the wide-DECIMAL probe field.
SSTAT	CHAR(1)	A active, H halted (a halted security rejects booking, RC=2).

PRICEHIST — Daily price history (PK SECID, PXDATE)

Field	Type	Meaning
SECID / PXDATE	VARCHAR(12) / INT	Security + price date YYYYMMDD (PK).
PXCLOSE	DECIMAL(19,6)	Close price. MTM uses the latest on/before the as-of date.

FXHIST — FX rate history (PK CCY, FXDATE)

Field	Type	Meaning
CCY / FXDATE	CHAR(3) / INT	Currency + rate date YYYYMMDD (PK).
RATE	DECIMAL(15,8)	Units of USD per 1 unit CCY (USD = 1.0).

TRADE — Trade blotter (PK TRDNO)

Field	Type	Meaning
TRDNO	VARCHAR(12)	Trade number (PK).
ACCTNO / SECID	VARCHAR(10) / VARCHAR(12)	Account and security traded.
TRDSIDE	CHAR(1)	B buy, S sell.
TRDQTY	DECIMAL(19,4)	Quantity (fractional allowed).
TRDPX	DECIMAL(19,6)	Trade price per share.
TRDCCY	CHAR(3)	Trade currency (default USD).
TRDDATE / SETTDATE	INT	Trade / settlement date (YYYYMMDD).
COMMISH	DECIMAL(13,2)	Commission (loaded into buy cost / netted from sell proceeds).
RELMETH	CHAR(1)	F FIFO, L LIFO (sell relief method).
TSTAT	CHAR(1)	E entered, B booked, R rejected.

LOT — Tax lots (identity LOTID; unique OPENTRD)

Field	Type	Meaning
LOTID	INT identity	Generated-always lot id.
ACCTNO / SECID	VARCHAR	Owning account and security.
OPENTRD	VARCHAR(12)	The buy TRDNO that opened this lot (unique index LOT_TRD).
OPENDATE	INT	Lot open date (FIFO/LIFO ordering key).
ORIGQTY / OPENQTY	DECIMAL(19,4)	Original / remaining unrelieved quantity.
COSTPX	DECIMAL(19,6)	Unit cost basis (commission-loaded; split-adjusted).
LSTAT	CHAR(1)	O open, X fully relieved.

LOTREL — Lot-relief detail (identity RELID)

Field	Type	Meaning
RELID	INT identity	Relief id.
SELLTRD / LOTID	VARCHAR / INT	The sell trade and the lot it consumed.
RELQTY	DECIMAL(19,4)	Quantity taken from that lot.
RELCOST	DECIMAL(19,6)	Cost basis relieved (RELQTY × lot COSTPX).
RELPROC	DECIMAL(19,6)	Proceeds allocated (RELQTY × sell px net of pro-rated commission).
REALGAIN	DECIMAL(19,6)	RELPROC − RELCOST.

POSITION — Current holdings (PK ACCTNO, SECID)

Field	Type	Meaning
ACCTNO / SECID	VARCHAR	Account + security (PK).
QTYHELD	DECIMAL(19,4)	Sum of open-lot quantities (MERGE-maintained).
AVGCOST	DECIMAL(19,6)	Cost-weighted average of open lots.

CASHLEDG — Cash ledger (identity CSEQ; unique SRCDOC, SRCSEQ)

Field	Type	Meaning
CSEQ	INT identity	Ledger sequence.
ACCTNO / CCY	VARCHAR / CHAR(3)	Account and currency of the posting.
AMT	DECIMAL(19,2)	Signed amount (cash in +, cash out −).
CDESC	VARCHAR(60)	Description.
SRCDOC / SRCSEQ	VARCHAR(12) / INT	Idempotency key (unique index CASHLEDG_TOKEN).
CDATE	INT	Posting date.

CORPACT — Corporate actions (PK CAID)

Field	Type	Meaning
CAID	VARCHAR(12)	Corporate-action id (PK).
SECID	VARCHAR(12)	Affected security.
CATYPE	CHAR(1)	S split.
RATIONEW / RATIOOLD	DECIMAL(9,4)	Split ratio (2-for-1 ⇒ NEW=2, OLD=1). Factor = NEW/OLD.
CADATE	INT	Effective date.
CASTAT	CHAR(1)	E entered, P processed (re-run guard).

REALIZEDPL — Realized P&L (identity RPID; GENERATED NETGAIN)

Field	Type	Meaning
RPID	INT identity	Row id.
ACCTNO / SECID / SELLTRD	VARCHAR	Account, security, sell trade.
RELQTY	DECIMAL(19,4)	Quantity relieved.
PROCEEDS / COSTBASIS	DECIMAL(19,6)	Allocated proceeds / relieved cost.
FEES	DECIMAL(13,2)	Fees applied to this relief (0 in the shipped path).
NETGAIN	DECIMAL(19,6)	GENERATED ALWAYS AS (PROCEEDS − COSTBASIS − FEES) — never written directly.

MTMSNAP — Mark-to-market snapshot (PK ACCTNO, SECID, MTMDATE)

Field	Type	Meaning
ACCTNO / SECID / MTMDATE	VARCHAR / INT	Position + as-of date (PK).
QTYHELD	DECIMAL(19,4)	Quantity marked.
COSTBASIS	DECIMAL(19,6)	QTYHELD × AVGCOST.
MKTVALUE	DECIMAL(19,6)	QTYHELD × price × FX.
UNREALGN	DECIMAL(19,6)	MKTVALUE − COSTBASIS.

GLACCT / GLENTRY — General ledger

Field	Type	Meaning
GLACCT.GLCODE	VARCHAR(10)	GL account code (PK) — CASH, SECURITIES, REALGAIN, COMMISH.
GLACCT.GLDESC / BAL	VARCHAR / DECIMAL(19,2)	Description / running balance.
GLENTRY.GSEQ	INT identity	Entry sequence.
GLENTRY.GLCODE / AMT	VARCHAR / DECIMAL(19,2)	Posted account and signed amount.
GLENTRY.SRCDOC / SRCSEQ	VARCHAR / INT	Idempotency key (unique index GLENTRY_TOKEN).
GLENTRY.GLTEXT	VARCHAR(60)	Narrative.

Invariant: every posted document nets to zero, and SUM(GLENTRY.AMT)=0 across the whole run; the GLACCT balances mirror it.

SECAUDIT — Audit / diagnostics (identity AUDSEQ)

Field	Type	Meaning
AUDSEQ	INT identity	Audit sequence.
EVTTYPE	VARCHAR(20)	LOT_ADJUST, TRADE_REJECT, CLEANUP_FAIL, BATCH_RUN.
REFDOC	VARCHAR(12)	Referenced trade / run token.
EVTTEXT	VARCHAR(100)	Human-readable detail.

ROLLUPCACHE / CASCADEMARK

Field	Type	Meaning
ROLLUPCACHE (MASTERACC, SECID)	PK	Master + security; TOTQTY subtree total, ASOFTOK run token.
CASCADEMARK (MARKID, HITS)	PK MARKID	Single-row counter TR_LOT_AUDIT_CASCADE bumps once per LOT_ADJUST audit.

Relationships

• ACCT.PARENTACC → ACCT.ACCTNO (self-referencing hierarchy; NULL = master).
• TRADE / LOT / POSITION / CASHLEDG reference ACCT.ACCTNO and SECMAST.SECID (by value; not all declared FKs).
• LOT.OPENTRD → TRADE.TRDNO (unique: one lot per buy).
• LOTREL.LOTID → LOT.LOTID and LOTREL.SELLTRD → TRADE.TRDNO (per-relief detail).
• REALIZEDPL is derived from LOTREL for each sell; MTMSNAP is derived from POSITION + PRICEHIST + FXHIST.
• CASHLEDG / GLENTRY keyed for idempotency by (SRCDOC, SRCSEQ) = (TRDNO, leg number).

E. Operations Runbook ↑ top

E.1 The daily cycle

SEC/i's day is a fixed sequence of CALLs in STRSQL (or from a scheduler that opens an SQL session). There is no control row to advance and no parameterless job — the as-of date and a run token are passed on the CALL.

1. Stage trades. Insert the day's trades into SECLIB.TRADE with TSTAT='E'.
2. Book the batch. CALL SECLIB.SP_TRADE_BATCH('BATCH-yyyymmdd', 1000, ?, ?, ?). Confirm P_BOOKED + P_REJECTED + P_SKIPPED equals the entered-trade count and inspect the returned per-trade result set.
3. Process corporate actions. For each entered CORPACT: CALL SECLIB.SP_PROCESS_SPLIT('CAxxxxx', ?, ?); confirm P_LOTSADJ/P_ACCTSADJ and that CASTAT flipped to 'P'.
4. Rebuild the roll-up cache. CALL SECLIB.SP_ROLLUP_ALL_MASTERS('RUN-yyyymmdd', ?).
5. Mark to market. CALL SECLIB.SP_MTM_RUN(<YYYYMMDD>, ?, ?); note P_MARKED and P_NOPRICE (positions lacking a price on/before the date).
6. Report. CALL SECLIB.SP_MTM_REPORT(<YYYYMMDD>) for the detail + summary result sets.
7. Verify the GL invariant (below).

Pre-checks: the job's library list includes SECLIB; the day's PRICEHIST/FXHIST rows are loaded (else positions count as P_NOPRICE).

E.2 Reconciling figures

These are the same figures the volume driver checks against an independent hand-derived oracle (test/sec_daily.mjs). Worked against the seed set:

-- GL invariant (must be exactly zero):
SELECT COALESCE(SUM(AMT),0) FROM SECLIB.GLENTRY;                       -- 0
SELECT SRCDOC, SUM(AMT) FROM SECLIB.GLENTRY GROUP BY SRCDOC
  HAVING ABS(SUM(AMT)) > 0.01;                                        -- no rows

-- realized P&L for a FIFO sell across two lots (T00003, sell 120 @200 comm 12):
SELECT SELLTRD, SUM(REALGAIN) FROM SECLIB.LOTREL WHERE SELLTRD='T00003'
  GROUP BY SELLTRD;                                                    -- 1136.00 (980.00 + 156.00)

• GL nets to zero — SUM(GLENTRY.AMT)=0 across the run and per SRCDOC; the GLACCT running balances mirror it. This is the master reconciliation control.
• Realized gain — per sell, SUM(LOTREL.REALGAIN) equals SUM(RELPROC − RELCOST). The seed sell T00003 relieves L1 (100 sh, gain 980.00) then L2 (20 sh, gain 156.00) FIFO ⇒ 1136.00. The GL cost-out leg uses SUM(LOTREL.RELCOST), never the blended average, so debits still equal credits when a sell spans costs.
• Positions — POSITION.QTYHELD = SUM(open LOT.OPENQTY); AVGCOST = cost-weighted average of open lots (e.g. SUB0003/AAPL = 30 sh @ 192.10 after T00003).
• Subtree roll-up — FN_SUBTREE_QTY (WITH RECURSIVE) and SP_ROLLUP_ACCT (procedural recursion) must return the same number, e.g. MASTER01/AAPL = 30, with STANDALONE's 5 sh excluded.
• MTM — UNREALGN = MKTVALUE − COSTBASIS per snapshot row; a 2-for-1 split doubles qty and halves cost, leaving cost basis unchanged (60 sh @ 96.05 = the same 5763.00 as 30 sh @ 192.10 was pre-split... adjusted for the extra lots).
• Split — after a 2-for-1, every open lot has OPENQTY × 2 and COSTPX / 2; P_LOTSADJ/P_ACCTSADJ equal the affected lot/account counts.

E.3 Failure & re-run rules

Each procedure reports success by SQLCODE 0 and its OUT parameters; a business rejection surfaces as an RC/return code or an application SQLSTATE (section F.5), not a crash.

Situation	Behaviour	Action
Re-book an already-booked trade	SP_BOOK_TRADE returns RC=1, opens no second lot.	Safe no-op — naturally idempotent per trade (TSTAT terminal at B).
Sell exceeds open shares	Recursion SIGNALs 75020; outer EXIT handler ROLLBACK TO SAVEPOINT SP_RELIEF, marks trade R, writes a TRADE_REJECT audit. RC=3.	No partial LOTREL/lot change survives. Correct the quantity and re-stage as a new trade.
Security halted	Booking returns RC=2, marks trade R.	Un-halt (SSTAT='A') and re-stage.
Re-run SP_TRADE_BATCH	Only TSTAT='E' trades are considered; booked/rejected are terminal.	Books/rejects nothing new; a closed-account trade is legitimately re-skipped each run until the account reopens.
Re-run SP_PROCESS_SPLIT on a processed action	CASTAT='P' guard returns 0/0.	Safe no-op — a split is never applied twice.
Re-run SP_MTM_RUN for the same date	MERGE upserts the same MTMSNAP rows.	Idempotent per (account, security, date).
Re-run SP_ROLLUP_ALL_MASTERS	MERGE overwrites ROLLUPCACHE totals.	Idempotent — recompute freely.
Position would go negative	TR_POSITION_VETO raises SQLSTATE 75021.	Last-line defense; indicates upstream lot data is inconsistent — investigate LOT/LOTREL.

F. Developer Reference ↑ top

The complete SQL-PL surface, from routines.sql. All objects are in library SECLIB. Signatures are given exactly as declared.

F.1 Functions (2)

FN_SETTLE_DATE (P_TRDDATE INT, P_DAYS INT) RETURNS INT

Adds P_DAYS to a YYYYMMDD trade date and returns the settlement date as YYYYMMDD. It splits the integer into Y/M/D, builds an ISO string, adds P_DAYS DAYS via DATE(), and re-packs to an integer. Month/year boundaries are handled by the DATE arithmetic (e.g. 20260830 + 3 → 20260902). The date math is done through a DATE() round-trip rather than a labeled-duration operator on the raw integer — a deliberate calendar-safe pattern noted in the source.

FN_SUBTREE_QTY (P_MASTERACC VARCHAR(10), P_SECID VARCHAR(12)) RETURNS DECIMAL(19,4)

Total open-lot quantity for one security across an account and all descendant sub-accounts, via WITH RECURSIVE. The recursive CTE (SUBACC) seeds with the master account and walks children through PARENTACC; the outer query sums LOT.OPENQTY for LSTAT='O' over the subtree. Assigned as a scalar-subquery expression (SET V_TOTAL = ( WITH RECURSIVE ... SELECT ... )) rather than SELECT ... INTO — the source notes this is required because a compound body's SELECT-INTO dispatcher only recognizes text that starts with the literal keyword SELECT, so a statement led by WITH must be wrapped as a scalar subquery.

F.2 Procedures (9)

SP_BOOK_TRADE (IN P_TRDNO VARCHAR(12); OUT P_RC INT, P_MSG VARCHAR(100))

Books one trade. Buy: opens a LOT at COSTPX = TRDPX + COMMISH/TRDQTY, posts cash out and a SECURITIES/CASH GL pair. Sell: takes SAVEPOINT SP_RELIEF, calls the recursive SP_RELIEVE_LOT, then posts cash in and a three-leg GL set (CASH / SECURITIES cost-out / REALGAIN), inserts one REALIZEDPL row per relieved lot, and the cost-out uses SUM(LOTREL.RELCOST) (exact relieved basis). Both sides then MERGE the position and mark the trade 'B'. Nested handler-in-handler: the outer EXIT handler for SQLSTATE 75020 ROLLBACKs to the savepoint and marks the trade rejected; an inner CONTINUE handler inside that handler's own body guards the cleanup UPDATE and records CLEANUP_FAIL if it fails. RC: 0 booked, 1 already booked, 2 security halted, 3 insufficient shares, 9 unexpected.

SP_RELIEVE_LOT (IN P_SELLTRD, P_ACCTNO, P_SECID, P_NEEDQTY DECIMAL(19,4), P_SELLPX DECIMAL(19,6), P_METHOD CHAR(1), P_COMMPERSH DECIMAL(19,6))

Recursive. Picks the oldest (P_METHOD='F', ORDER BY OPENDATE ASC) or newest ('L', DESC) open lot, takes MIN(P_NEEDQTY, OPENQTY), writes a LOTREL row (RELCOST=take×COSTPX, RELPROC=take×(sellpx−commpersh), REALGAIN=RELPROC−RELCOST), updates the lot (zero+'X' if fully consumed, else decrement), and self-CALLs for the remainder. Base cases: P_NEEDQTY≤0 (return) or no open lot left (SIGNAL SQLSTATE '75020').

SP_UPSERT_POSITION (IN P_ACCTNO VARCHAR(10), P_SECID VARCHAR(12))

Recomputes QTYHELD = SUM(OPENQTY) and AVGCOST = SUM(OPENQTY×COSTPX)/SUM(OPENQTY) over open lots, then MERGEs into POSITION (UPDATE on match, INSERT otherwise).

SP_TRADE_BATCH (IN P_RUNTOKEN VARCHAR(20), P_MAXBOOK INT; OUT P_BOOKED, P_REJECTED, P_SKIPPED INT) DYNAMIC RESULT SETS 1

Books every TSTAT='E' trade in TRDNO order. Labeled BATCH_LOOP FOR-cursor: LEAVE at the P_MAXBOOK cap, ITERATE past a closed-account trade (counted skipped). Each booking wrapped in SAVEPOINT SP_TRADE; an unexpected RC=9 rolls back only that trade. Stages outcomes in a DECLARE GLOBAL TEMPORARY TABLE SESSION.BATCHSTAGE ... WITH REPLACE, surfaced by a WITH RETURN WITH HOLD cursor as the one result set; writes a BATCH_RUN audit row.

SP_PROCESS_SPLIT (IN P_CAID VARCHAR(12); OUT P_LOTSADJ, P_ACCTSADJ INT)

Applies a split (FACTOR = RATIONEW/RATIOOLD) to every open lot of the security across every holding account. Nested cursor-per-row: outer FOR over distinct holding accounts, inner FOR (opened fresh per account) over that account's open lots, each set to OPENQTY×FACTOR, ORIGQTY×FACTOR, COSTPX/FACTOR; re-MERGEs the position per account. Guarded by CASTAT='P' (no-op if already processed); flips CORPACT to 'P' at the end.

SP_ROLLUP_ACCT (IN P_ACCTNO VARCHAR(10), P_SECID VARCHAR(12); OUT P_QTY DECIMAL(19,4))

Recursive (procedural self-CALL, distinct from FN_SUBTREE_QTY's CTE). Sets P_QTY to the account's own open-lot sum, opens a cursor over child accounts (PARENTACC=P_ACCTNO), and for each child self-CALLs and adds the returned quantity. Base case: an account with no children contributes only its own lots. A CONTINUE HANDLER FOR NOT FOUND ends the child fetch loop.

SP_ROLLUP_ALL_MASTERS (IN P_ASOFTOK VARCHAR(20); OUT P_WRITTEN INT)

Nested FOR loop: outer over masters (PARENTACC IS NULL), inner over distinct securities with an open lot; calls SP_ROLLUP_ACCT and MERGEs each non-zero total into ROLLUPCACHE, counting rows written.

SP_MTM_RUN (IN P_ASOF INT; OUT P_MARKED, P_NOPRICE INT)

Labeled POS_CUR_LBL FOR-cursor over positions with QTYHELD>0. Per row: a scalar-subquery lookup of the latest PXCLOSE on/before P_ASOF (no price ⇒ ITERATE POS_CUR_LBL, bump P_NOPRICE), the currency's latest FX rate, then a MERGE into MTMSNAP with market value, cost basis, and unrealized gain.

SP_MTM_REPORT (IN P_ASOF INT) DYNAMIC RESULT SETS 2

Opens two WITH RETURN cursors: (1) per-position detail from MTMSNAP for the date, (2) an account-level aggregate (SUM of cost/market/unrealized, GROUP BY account).

F.3 Triggers (3)

TR_LOT_AUDIT — AFTER UPDATE OF OPENQTY ON LOT, FOR EACH ROW

Writes a SECAUDIT 'LOT_ADJUST' row recording the old→new open quantity. Fires from both the recursive relief UPDATE and the split's inner-cursor UPDATE (BEGIN ATOMIC body).

TR_LOT_AUDIT_CASCADE — AFTER INSERT ON SECAUDIT, WHEN (N.EVTTYPE='LOT_ADJUST'), FOR EACH ROW

A trigger fired by another trigger's own write: on each LOT_ADJUST audit row it inserts or increments the single CASCADEMARK counter row. The WHEN filter on EVTTYPE keeps it from re-firing itself (proving a controlled trigger-fires-trigger cascade).

TR_POSITION_VETO — BEFORE UPDATE ON POSITION, WHEN (N.QTYHELD < 0), FOR EACH ROW

Raises SIGNAL SQLSTATE '75021' to refuse any update that would drive a position negative — the last line of defense behind the MERGE-based upsert.

F.4 SQL-PL patterns

The routines deliberately exercise these SQL-PL constructs; developers maintaining SEC/i will see each:

• Two kinds of recursion. Procedural self-CALL (SP_RELIEVE_LOT, SP_ROLLUP_ACCT) and WITH RECURSIVE CTE (FN_SUBTREE_QTY, conceptually the same walk) — the driver asserts both give identical roll-up answers.
• Handler-in-handler with RESIGNAL across CALL frames. A 75020 raised several recursion frames deep in SP_RELIEVE_LOT is caught by SP_BOOK_TRADE's outer EXIT handler, whose body contains its own inner CONTINUE handler.
• SAVEPOINT / ROLLBACK TO SAVEPOINT. Per-sell (SP_RELIEF) and per-batch-trade (SP_TRADE) savepoints scope a rollback to just the failed unit; a non-ATOMIC EXIT handler does not auto-undo, so the rollback is explicit.
• Nested cursors. SP_PROCESS_SPLIT (accounts × lots) and SP_ROLLUP_ALL_MASTERS (masters × securities) open an inner cursor per outer row.
• Labeled LEAVE / ITERATE. BATCH_LOOP and POS_CUR_LBL control flow across nested IF blocks.
• MERGE with DECIMAL. Position and roll-up and MTM upserts all use MERGE ... WHEN MATCHED / WHEN NOT MATCHED.
• Dynamic result sets. DYNAMIC RESULT SETS 1/2 via WITH RETURN (and WITH HOLD) cursors.
• GENERATED column & wide DECIMAL. REALIZEDPL.NETGAIN GENERATED ALWAYS AS (...) and SECMAST.FACEVAL DECIMAL(31,6).
• Global temporary table. DECLARE GLOBAL TEMPORARY TABLE SESSION.BATCHSTAGE ... WITH REPLACE in the batch.
• Trigger cascade. LOT update → TR_LOT_AUDIT insert → TR_LOT_AUDIT_CASCADE counter.

F.5 Application SQLSTATE table

SQLSTATE	Raised by	Meaning
75020	SP_RELIEVE_LOT	Insufficient open-lot shares to relieve the sell (caught by SP_BOOK_TRADE’s outer handler → RC=3).
75021	TR_POSITION_VETO	A position update would drive QTYHELD negative (vetoed).

Business outcomes that are not SQLSTATE-signalled are reported through SP_BOOK_TRADE's P_RC return code instead: 1 already booked, 2 security halted, 3 insufficient shares (the caught 75020), 9 unexpected. The GL-nets-to-zero invariant is not a SQLSTATE but a post-run reconciliation check (section E.2).

G. Glossary ↑ top

Tax lot (LOT)

One purchase parcel of a security at a known unit cost. Sells relieve lots; a lot is O (open) until fully consumed (X).

FIFO / LIFO relief

The order lots are consumed on a sell: First-In-First-Out (oldest lot first) or Last-In-First-Out (newest first), selected per trade by RELMETH and walked recursively by SP_RELIEVE_LOT.

Realized vs unrealized P&L

Realized gain/loss is locked in when a lot is relieved (proceeds − cost basis, in LOTREL/REALIZEDPL); unrealized is the paper gain on still-held positions from mark-to-market (MTMSNAP.UNREALGN).

Mark-to-market (MTM)

Revaluing open positions at current market price (× FX), snapshotting cost, market value, and unrealized gain per position for an as-of date (SP_MTM_RUN).

Corporate action / split

An issuer event that adjusts holdings. A stock split rewrites every open lot's quantity and cost by the split ratio (SP_PROCESS_SPLIT).

Sub-account roll-up

Summing a holding across a master account and all its descendant sub-accounts, computed by procedural recursion (SP_ROLLUP_ACCT) and by a WITH RECURSIVE CTE (FN_SUBTREE_QTY).

Position (POSITION)

The denormalized current quantity and weighted-average cost per (account, security), kept in sync from open lots by a MERGE (SP_UPSERT_POSITION).

General ledger invariant

Every trade posts a balanced set of GL entries so SUM(GLENTRY.AMT)=0 across the run and per document — the master reconciliation control.

Idempotency token

A unique (SRCDOC, SRCSEQ) key on CASHLEDG/GLENTRY preventing a trade's legs from being double-posted.

SAVEPOINT / ROLLBACK TO SAVEPOINT

A nested transaction marker; rolling back to it undoes work since the marker without aborting the whole unit — scopes a failed sell or batch trade to its own effects.

Dynamic result set

Rows a procedure returns to its caller through a WITH RETURN cursor (DYNAMIC RESULT SETS n), distinct from OUT parameters.

GENERATED column

A column whose value the engine always derives from an expression (here REALIZEDPL.NETGAIN), never written by the application.

STRSQL

The IBM i interactive SQL session — SEC/i's operator surface, since it has no 5250 screen.

SQLSTATE / SQLCODE / return code (RC)

SQL status signals. SEC/i signals application SQLSTATEs 75020/75021 and additionally reports handled business outcomes through SP_BOOK_TRADE's P_RC.