DEP/i — Schema-Migration & Deploy Platform

DEP/i is a DB2-for-i schema-migration / CI-CD deploy platform — the deploy tool itself, not the application it manages. It maintains a deploy history (MIGRATION_LOG), a "current deployed version" register (a global CREATE VARIABLE), a synthetic log-id generator (a SEQUENCE), a distinct TYPE, and an idempotent migration-runner procedure (SP_APPLY_MIGRATION) that every versioned migration is logged through. It has no 5250 screen: it is driven the way a real deploy CLI drives a database — an orchestrating job runs each version's DDL/DML batch inside a SAVEPOINT it takes itself, then calls the bookkeeping procedures to record the outcome and advance the version register. This manual is the reference for the operator who runs a deploy (and an idempotent re-deploy or a rollback) and for the developer maintaining the engine. It is grounded entirely in the committed source (sqlpl-app-dep/src/deploy_engine.sql, src/migrations.mjs, src/seed.mjs, and the test/dep_daily.mjs driver).

A. Overview & Architecture ↑ top

A.1 What it does

DEP/i deploys and evolves a database schema through versioned migrations, keeping an auditable record of every deploy and guaranteeing that a re-run is safe:

• Records every deploy — one MIGRATION_LOG row per (version, migration-name) attempt, with STATUS = OK (applied), SKIPPED (already applied — the idempotent no-op), or FAILED (a bad migration that was rolled back).
• Tracks the current version — a global CURRENT_VER register holds the highest applied version, advanced only after a version's DDL batch is confirmed clean.
• Runs migrations idempotently — SP_APPLY_MIGRATION checks whether a (version, name) already applied OK and, if so, logs a clean SKIPPED no-op instead of re-applying; a MERGE upsert makes the config write itself idempotent belt-and-braces.
• Rolls a bad migration back cleanly — a version's DDL batch runs inside a SAVEPOINT; if it fails (e.g. an ADD CONSTRAINT the existing data violates), ROLLBACK TO SAVEPOINT leaves the schema and data byte-for-byte unchanged and the log records FAILED, never a half-applied schema.
• Verifies the deployed state — after each migration the driver queries the DB2 catalog (QSYS2.SYSTABLES / SYSCOLUMNS / SYSSEQUENCES / SYSVARIABLES / SYSTYPES / SYSTABAUTH / SYSCOLAUTH) to confirm the deployed shape matches what it intended to apply.

The worked example that ships with it deploys a small order-management schema (ORDDB: CUSTOMER / PRODUCT / ORDERS / ORDER_LINE) through six versions V1–V6, plus a deliberately bad V5 to prove rollback.

A.2 A deploy engine, not an application — and why there is no screen

Two things distinguish DEP/i from a normal SteelFrame X application:

• The tool is the schema it owns. The engine lives in library/schema DEPSYS and contains only deploy bookkeeping: the log table, the sequence, the version variable, the distinct type, a config table, and the three runner procedures. The managed application schema (ORDDB) is data the tool operates on, shipped as versioned migration scripts in src/migrations.mjs — not part of the engine.
• No 5250 screen, by design. A deploy tool is driven by an orchestration job (a CI/CD pipeline step, a deploy CLI), not an interactive operator at a green screen. DEP/i's "operator surface" is the driver in test/dep_daily.mjs, which mirrors a real deploy CLI: for each version it takes its own SAVEPOINT, runs the version's DDL via executeSqlBatch, then calls SP_LOG_MIGRATION_OK / SP_LOG_MIGRATION_FAILED to record the outcome. This manual documents that honestly — where a loan or order app would have a screen, DEP/i has a driver loop.

A.3 Component & flow

  ENGINE (DEPSYS)                 DRIVER (deploy CLI)            MANAGED SCHEMA (ORDDB)
  --------------                  ------------------            ----------------------
  MIGRATION_LOG (history)         for each version V:           V1 baseline (CREATE)
  DEPLOY_SEQ    (log-id seq)        SAVEPOINT SP_V              V2 ADD COLUMN / ALIAS
  CURRENT_VER   (VARIABLE)          run V's DDL batch  ------>  V3 TYPE / RENAME / grants
  VERNO_T       (TYPE)             confirm clean?               V4 audit / FK / staging
  DEPLOY_CFG    (config kv)          yes -> SP_LOG_MIGRATION_OK V5 BAD -> ROLLBACK -> FAILED
                                     no  -> ROLLBACK TO SP_V    V5 FIXED (clean then ADD)
  SP_LOG_MIGRATION_OK  --------->        SP_LOG_MIGRATION_FAILED V6 teardown (DROP/REVOKE)
  SP_LOG_MIGRATION_FAILED         confirm OK? bumpVer CURRENT_VER
  SP_APPLY_MIGRATION (config)     verify catalog (QSYS2.SYS*)

A single deploy step flows: driver takes SAVEPOINT SP_V → runs version V's DDL/DML batch against ORDDB → if clean, calls SP_LOG_MIGRATION_OK(V, name, detail) which inserts an OK row (log-id from DEPLOY_SEQ) → driver reads the returned status and, when OK, issues a top-level SET DEPSYS.CURRENT_VER = V → driver verifies the new shape in the DB2 catalog. If the batch fails, the driver instead does ROLLBACK TO SAVEPOINT SP_V and calls SP_LOG_MIGRATION_FAILED.

A.4 Object inventory

The engine catalogue is 1 schema + 3 tables + 1 sequence + 1 variable + 1 type + 3 procedures, driving 6 versioned migrations over the managed ORDDB schema. Sections D and F expand each.

B. Driving a Deploy (Access) ↑ top

B.1 How it is driven — there is no screen

DEP/i is a deploy engine, so it is driven by an orchestration job, not by an operator at a 5250 screen. Honestly stated: there is no DSPF, no subfile, no command-entry program — the operator surface is a deploy driver (the committed reference driver is test/dep_daily.mjs, which mirrors a real deploy CLI). Every action below is a SQL batch or a procedure CALL issued by that driver against schema DEPSYS (the engine) and ORDDB (the managed schema).

The three engine procedures take the migration's identity and detail as IN parameters and return the outcome status as an OUT parameter (SP_LOG_MIGRATION_OK and SP_APPLY_MIGRATION return P_STATUS = OK / SKIPPED; SP_LOG_MIGRATION_FAILED is fire-and-forget). The driver reads the status and decides whether to advance CURRENT_VER.

B.2 Controls & audit — the deploy log, idempotency, rollback

DEP/i's control posture is a durable deploy log + idempotency + savepoint rollback, all enforced in the data layer. It does not model a four-eyes maker–checker approval workflow: a deploy is applied by the driver as it runs each version. The controls it does have:

• Deploy log (MIGRATION_LOG). Every apply attempt writes one immutable, timestamped row with a synthetic LOGID from DEPLOY_SEQ and a STATUS of OK / SKIPPED / FAILED plus a free-text DETAIL. This is the after-the-fact accountability trail: the full version history 0…6 is reconstructable, and the single V5 FAILED row is preserved as evidence of the rolled-back attempt.
• Idempotency (SKIPPED). SP_LOG_MIGRATION_OK and SP_APPLY_MIGRATION first SELECT COUNT(*) for an existing OK row with the same (VERNO, MIGNAME); if one exists they return SKIPPED and log a SKIPPED row rather than re-applying. The version register only ever advances (bumpVer ignores a lower/equal version), so a re-run cannot regress it.
• Rollback (SAVEPOINT / ROLLBACK TO SAVEPOINT). The driver takes a SAVEPOINT before each risky version and rolls back to it on failure, so a bad DDL batch leaves the schema and data byte-for-byte unchanged. Combined with logging FAILED (never OK) for that version, the log can never claim a half-applied schema succeeded.
• Config integrity (MERGE upsert). SP_APPLY_MIGRATION writes config through a MERGE (update-if-matched / insert-if-not), so even a re-run that somehow bypassed the log check cannot duplicate or corrupt a DEPLOY_CFG row — and a re-apply of the same key never overwrites the already-set value (it SKIPs first).
• Catalog verification. After each migration the driver reads back the DB2 catalog to confirm the deployed shape (new tables/columns/sequences/types/grants present, dropped objects gone) — a real deploy tool's confirm-what-you-intended step (section F.3).

C. The Deploy Cycle & Migrations ↑ top

A DEP/i deploy is a sequence of versioned migrations applied in order. The engine is bootstrapped once (running deploy_engine.sql), then each version's migration is applied, logged, verified, and — on a re-trigger — safely re-run. The driver wraps every version the way a real deploy tool would: a pre-flight catalog probe to detect an already-applied step, a SAVEPOINT around risky DDL, and an outcome log.

-- bootstrap the engine (first deploy only)
run deploy_engine.sql            -- CREATE SCHEMA DEPSYS + tables/seq/var/type/procs

-- apply a structural version (driver pseudo-code)
SAVEPOINT SP_V4;
run V4 DDL batch;                -- ADD/DROP COLUMN, ADD CONSTRAINT, CREATE TRIGGER, RENAME ...
CALL DEPSYS.SP_LOG_MIGRATION_OK(4, 'V4_AUDIT_FK_AND_STAGING', 'applied', ?);
SET DEPSYS.CURRENT_VER = 4;      -- caller-side, only when status = OK

-- a bad version rolls back and logs FAILED
SAVEPOINT SP_V5;
run V5 DDL batch;                -- ADD CONSTRAINT the data violates -> fails
ROLLBACK TO SAVEPOINT SP_V5;
CALL DEPSYS.SP_LOG_MIGRATION_FAILED(5, 'V5_BAD_QTY_CHECK', 'CHECK re-validation failed');

C.1 Engine procedures & the version set

C.2 Per-version detail (V1–V6)

V1 — baseline schema & seed

Creates the managed schema ORDDB with CUSTOMER, PRODUCT, ORDERS (identity PK, FK to CUSTOMER, DEFAULT OSTAT='O') and ORDER_LINE, a hint SEQUENCE, the V_OPEN_ORDERS view, the FN_ORDER_TOTAL function and SP_ORDER_TOTAL procedure, and table/EXECUTE/schema-level grants. Seed data lands two orders; order 1 has three lines (one a deliberately zero-quantity line, for V5). ORDER_LINE's FKs are deliberately deferred to V4 — see the ordering note in C.3.

Expected after V1:
  CURRENT_VER = 1
  FN_ORDER_TOTAL(1) = 225.00   10x12.50 + 4x25.00 + 0x7.50
  V_OPEN_ORDERS     = 2 rows

V2 — add shipping columns, a CHECK, an alias

ADD COLUMN SHIPZIP (nullable) and ADD COLUMN FREIGHT DECIMAL(7,2) NOT NULL DEFAULT 0 (existing rows backfill to 0), a CHECK (FREIGHT ≥ 0) constraint (CK_FREIGHT), a view redeploy projecting the new columns, and CREATE OR REPLACE ALIAS OORD FOR ORDERS. The driver's pre-flight probes SYSCOLUMNS for FREIGHT; if present it logs SKIPPED instead of re-adding. Data survives the rebuild (order 1 total still 225.00).

V3 — distinct type, re-type/rename, unique, column grants

CREATE TYPE MONEY_T AS DECIMAL(11,2); ALTER COLUMN UNITPRC SET DATA TYPE DECIMAL(11,2) and SET DEFAULT 0.01; RENAME COLUMN SHIPZIP TO SHIPPOSTAL; ADD CONSTRAINT UQ_PDESC UNIQUE (PDESC); and a column-level GRANT UPDATE (UNITPRC), REFERENCES (PRODNO). Prices are unchanged by the widening; a fresh insert picks up the new default (0.01); the old column name is gone after the rename; the UNIQUE is enforced. Pre-flight probes SYSTYPES for MONEY_T.

V4 — audit trail, deferred FKs, stronger CHECK, staging, TRUNCATE

The heaviest version: create ORD_AUDIT (identity PK); CREATE OR REPLACE TRIGGER TR_ORD_STAT (AFTER UPDATE OF OSTAT, WHEN status changes — a same-status update is suppressed); DROP CONSTRAINT CK_FREIGHT then ADD CONSTRAINT CK_FREIGHT2 CHECK (FREIGHT ≥ 0 AND FREIGHT < 10000); add the deferred ORDER_LINE FKs to ORDERS/PRODUCT (now safe, see C.3); redeploy the view to stop referencing SHIPPOSTAL, then DROP COLUMN SHIPPOSTAL; and create + RENAME TABLE a staging table. The driver then exercises TRUNCATE TABLE and TRUNCATE TABLE … RESTART IDENTITY (identity resets to 1).

V5 — the deliberately bad migration & its real fix

V5_BAD attempts ADD CONSTRAINT CK_QTY_POS CHECK (QTY > 0), which the pre-existing zero-quantity seed row (ORDNO 1, LINENO 3) violates on re-validation. The driver took a SAVEPOINT SP_V5 first; the ADD fails; it does ROLLBACK TO SAVEPOINT SP_V5 and calls SP_LOG_MIGRATION_FAILED. Afterward the schema (same column count) and data (same row count, the bad row exactly as it was) are byte-for-byte unchanged, no CK_QTY_POS is left behind, CURRENT_VER is still 4, and the session still transacts normally. V5_FIXED is the real roll-forward: DELETE FROM ORDER_LINE WHERE QTY ≤ 0, then the same ADD CONSTRAINT succeeds; CURRENT_VER→5.

V5-BAD proof of clean rollback:
  schema column count  = unchanged
  ORDER_LINE row count = unchanged, bad row still QTY=0
  MIGRATION_LOG        = one FAILED row (V5_BAD_QTY_CHECK)
  CURRENT_VER          = 4  (did NOT advance)

V6 — teardown

Retires the superseded staging objects: DROP TABLE IF EXISTS ORD_STAGE RESTRICT, DROP ALIAS IF EXISTS OORD, and REVOKE UPDATE (UNITPRC) ON PRODUCT FROM PUBLIC. The base ORDERS table (aliased earlier) is untouched by the alias drop; the column-level UPDATE grant is gone while the REFERENCES grant (not revoked) remains. DROP … IF EXISTS makes the whole version a clean no-op on re-run.

C.3 Ordering & dependencies

• Bootstrap before anything. The engine (DEPSYS) must exist before any version is logged — the runner procedures write MIGRATION_LOG and mint from DEPLOY_SEQ.
• Versions apply in monotonic order V1→V6. Each version assumes the prior shape (V2 adds to V1's tables, V3 re-types V1's columns, V4 drops V3's renamed column, V6 tears down V4's staging). The log records a clean progression 0…6 with no gaps.
• Deferred FKs added last (V4), not at V1. A confirmed platform bug (SQLPL-PLAT-DEP-02): once a live FK references a table, any rebuild-based ALTER TABLE on that referenced table fails with a raw "FOREIGN KEY constraint failed", even when unrelated to the FK. V2/V3 do several such rebuild ALTERs on ORDERS/PRODUCT, so ORDER_LINE's FKs are deferred to V4, after those tables' shape has stabilized — a realistic migration pattern that also routes around the gap.
• Redeploy dependent views before dropping a column. V4 redeploys V_OPEN_ORDERS to stop projecting SHIPPOSTAL before the DROP COLUMN, or the drop would break the dependent view.
• Within V5: fail-and-rollback before roll-forward. V5_BAD (rollback proof) runs before V5_FIXED (clean data, then add the constraint) — the fix presupposes the offending row is still present.

D. Data Files (the platform's own tables) ↑ top

These are the deploy platform's own objects, all in schema DEPSYS, grounded in src/deploy_engine.sql. They are the tool's bookkeeping — distinct from the managed application schema ORDDB (section C), which is data the tool operates on.

MIGRATION_LOG — Deploy history (PK LOGID)

DEPLOY_CFG — Deploy config key/value (PK CFGKEY)

Written only through SP_APPLY_MIGRATION's idempotent MERGE upsert. A re-applied key returns SKIPPED and does not overwrite the existing value.

DEPLOY_SEQ — Sequence

CREATE SEQUENCE DEPSYS.DEPLOY_SEQ START WITH 1 INCREMENT BY 1 — the generator every runner draws LOGID from via NEXT VALUE FOR, so all MIGRATION_LOG rows share one monotonic id sequence.

CURRENT_VER — Global variable

CREATE VARIABLE DEPSYS.CURRENT_VER INTEGER DEFAULT 0 — the tool's "highest applied version" register. Read as VALUES DEPSYS.CURRENT_VER and set as SET DEPSYS.CURRENT_VER = <n> from top-level SQL only; it is unreachable from inside a routine body (SQLPL-PLAT-DEP-03), so the driver owns advancing it (section B.1).

VERNO_T — Distinct type

CREATE TYPE DEPSYS.VERNO_T AS INTEGER — a distinct type used by the deploy tool's own version-number bookkeeping (and available to the managed schema). Demonstrates the CREATE TYPE ... AS <source> DevOps surface; the managed schema also ships its own distinct type ORDDB.MONEY_T in V3.

The managed schema (ORDDB) — for reference

The tool operates on a small order-management schema, evolved across V1–V6: CUSTOMER (CUSTNO PK, CNAME, CSTAT, +SHIPZIP→SHIPPOSTAL→dropped), PRODUCT (PRODNO PK, PDESC, UNITPRC widened to DECIMAL(11,2)), ORDERS (ORDNO identity PK, CUSTNO FK, ODATE, OSTAT DEFAULT 'O', +FREIGHT), ORDER_LINE (ORDNO+LINENO PK, PRODNO, QTY; deferred FKs added in V4), plus ORD_AUDIT, staging ORD_STAGE, view V_OPEN_ORDERS, alias OORD, function FN_ORDER_TOTAL, procedure SP_ORDER_TOTAL, sequence ORDNO_HINT_SEQ, type MONEY_T, and trigger TR_ORD_STAT. These are the subject of the deploy, not part of the engine.

Relationships

• MIGRATION_LOG.LOGID ← DEPLOY_SEQ (every runner mints the next value).
• DEPLOY_CFG written only by SP_APPLY_MIGRATION (MERGE); no FK to the log.
• CURRENT_VER is a standalone register advanced by the driver, not by any table.
• The engine (DEPSYS) and the managed schema (ORDDB) are fully separate schemas; the engine references ORDDB only via the catalog (QSYS2.SYS*) during verification.

E. Operations Runbook ↑ top

E.1 Run a deploy

1. Bootstrap the engine (first deploy on a fresh target only): run src/deploy_engine.sql as a batch. Confirm VALUES DEPSYS.CURRENT_VER = 0.
2. For each version in order (V1→V6):
   1. Run the driver's pre-flight probe for that version (does the target column / type / table already exist?). If it does, log a SKIPPED via SP_LOG_MIGRATION_OK and move on — the step is already applied.
   2. Take a SAVEPOINT (for any risky/structural version).
   3. Run the version's DDL/DML batch against ORDDB.
   4. If clean, CALL DEPSYS.SP_LOG_MIGRATION_OK(verno, migname, detail, ?); read the OUT status; when it is OK, issue SET DEPSYS.CURRENT_VER = verno.
   5. Run the version's catalog verification checks (section F.3).
3. For a pure config-seed step, call SP_APPLY_MIGRATION(verno, name, cfgkey, cfgval, ?) directly — it does the idempotency check, the MERGE, and the log in one call.

Pre-checks: the deploy job's schema path can resolve both DEPSYS and ORDDB; the engine exists (CURRENT_VER answers). Post-checks (whole deploy):

SELECT MIN(VERNO), MAX(VERNO), COUNT(DISTINCT VERNO)
  FROM DEPSYS.MIGRATION_LOG WHERE STATUS='OK';     -- expect 0|6|7 (clean 0..6, no gaps)
VALUES DEPSYS.CURRENT_VER;                          -- expect the final version (6)

E.2 Idempotent re-deploy

The entire pipeline is safe to re-run end-to-end (e.g. a CI re-trigger). On a second pass:

• Replaceable objects redeploy cleanly. Every CREATE OR REPLACE object (view, sequence, function, procedure, trigger, alias) can be re-issued as a clean no-op-shaped redeploy. Note a CREATE OR REPLACE SEQUENCE re-seeds its START WITH value.
• Structural steps SKIP. Each version's pre-flight probe (or the log check inside the runner) detects the already-applied state and returns SKIPPED — no re-add, no data drift. A full re-run of V2…V6 grows MIGRATION_LOG by exactly five SKIPPED rows and no OK rows.
• Raw DDL is NOT re-run. Re-running the bare deploy_engine.sql or a version's CREATE SCHEMA/CREATE TABLE is expected to fail on a second pass — that is the DDL-vs-OR REPLACE distinction. A real re-deploy re-runs only a version's replaceable objects plus its guarded apply-function, which is exactly what the driver does.
• Config re-apply is a safe SKIP. Re-calling SP_APPLY_MIGRATION for an already-applied key returns SKIPPED and does not overwrite the value (the MERGE never runs because the log check short-circuits first).

Verify no drift after a re-run: order totals still 225.00 / 250.00, row counts stable, and CURRENT_VER unchanged at 6.

E.3 Rollback & verification

A structural migration that fails must leave the target exactly as it was. The pattern — proven by the deliberately bad V5 — is SAVEPOINT before, ROLLBACK TO SAVEPOINT on failure, and log FAILED (never OK):

Verification after every migration (a real deploy tool's confirm step): query the DB2 catalog to confirm the intended shape landed — new tables in SYSTABLES, new columns in SYSCOLUMNS, sequences at their START in SYSSEQUENCES, types in SYSTYPES, and grants in SYSTABAUTH/SYSCOLAUTH. See F.3 for the exact queries and the two honest catalog gaps (native-table FKs and native UNIQUE/CHECK are enforced but not cataloged).

F. Developer Reference ↑ top

The complete deploy-engine surface, from src/deploy_engine.sql, plus the DevOps SQL exercised by the versioned migrations (src/migrations.mjs) and verified by the driver (test/dep_daily.mjs). Engine objects are in schema DEPSYS.

F.1 Engine procedures (3)

SP_LOG_MIGRATION_OK (IN P_VERNO INTEGER, IN P_MIGNAME VARCHAR(40), IN P_DETAIL VARCHAR(200); OUT P_STATUS CHAR(8))

SELECT COUNT(*) for an existing OK row with the same (VERNO, MIGNAME). If found → P_STATUS='SKIPPED' and insert a SKIPPED row; else P_STATUS='OK' and insert an OK row, LOGID from NEXT VALUE FOR DEPSYS.DEPLOY_SEQ. Deliberately does not touch CURRENT_VER (SQLPL-PLAT-DEP-03 — the caller advances it top-level).

SP_LOG_MIGRATION_FAILED (IN P_VERNO INTEGER, IN P_MIGNAME VARCHAR(40), IN P_DETAIL VARCHAR(200))

Fire-and-forget: insert one MIGRATION_LOG row with STATUS='FAILED'. Called by the driver after its own ROLLBACK TO SAVEPOINT, so the log records a failure that left no schema change.

SP_APPLY_MIGRATION (IN P_VERNO INTEGER, IN P_MIGNAME VARCHAR(40), IN P_CFGKEY CHAR(20), IN P_CFGVAL VARCHAR(60); OUT P_STATUS CHAR(8))

The genuinely self-contained idempotent runner for pure single-table config-seed migrations. Idempotency check on MIGRATION_LOG; if already OK → SKIPPED (log only). Otherwise a MERGE INTO DEPSYS.DEPLOY_CFG upsert (update-if-matched / insert-if-not) of the key/value, then P_STATUS='OK' and an OK log row. Structural versions' bookkeeping is instead split across the two LOG procedures because SQL PL cannot portably EXECUTE IMMEDIATE arbitrary DDL (see A.2).

F.2 The DevOps SQL surface exercised

The versioned migrations deliberately exercise the full schema-evolution surface a real deploy tool must emit. Each is real DDL/DML from src/migrations.mjs, verified in the catalog by the driver.

DDL object creation

ALTER TABLE evolution

Authorization, truncation & teardown

F.3 Catalog verification queries

After each migration the driver confirms the deployed shape against the DB2 catalog. The real queries:

-- base tables present (V1)
SELECT COUNT(*) FROM QSYS2.SYSTABLES
  WHERE TABLE_SCHEMA='ORDDB' AND TABLE_TYPE='T'
    AND TABLE_NAME IN ('CUSTOMER','PRODUCT','ORDERS','ORDER_LINE');   -- 4
-- a column default (V1)
SELECT COLUMN_DEFAULT FROM QSYS2.SYSCOLUMNS
  WHERE TABLE_SCHEMA='ORDDB' AND TABLE_NAME='ORDERS' AND COLUMN_NAME='OSTAT';
-- sequence at its START (V1)
SELECT START, NEXT_AVAILABLE_VALUE FROM QSYS2.SYSSEQUENCES
  WHERE SEQUENCE_SCHEMA='ORDDB' AND SEQUENCE_NAME='ORDNO_HINT_SEQ';   -- 1000|1000
-- grants (V1): table privileges to PUBLIC
SELECT PRIVILEGE_TYPE FROM QSYS2.SYSTABAUTH
  WHERE TABLE_SCHEMA='ORDDB' AND TABLE_NAME='ORDERS' AND GRANTEE='*PUBLIC';   -- INSERT;SELECT
-- alias (V2), distinct type (V3), column grants (V3)
SELECT TABLE_TYPE FROM QSYS2.SYSTABLES WHERE ... TABLE_NAME='OORD';            -- A
SELECT SOURCE_TYPE FROM QSYS2.SYSTYPES WHERE USER_DEFINED_TYPE_NAME='MONEY_T'; -- DECIMAL...
SELECT COUNT(*) FROM QSYS2.SYSCOLAUTH
  WHERE TABLE_NAME='PRODUCT' AND COLUMN_NAME='UNITPRC' AND PRIVILEGE_TYPE='UPDATE';  -- 1

The engine's own registers are read with VALUES DEPSYS.CURRENT_VER and SELECT ... FROM DEPSYS.MIGRATION_LOG (grouped by STATUS to confirm the OK/SKIPPED/ FAILED counts per version).

F.4 Idempotency & rollback patterns

• Log-based idempotency. The runner's SELECT COUNT(*) ... WHERE VERNO=? AND MIGNAME=? AND STATUS='OK' is the canonical "already applied?" check; a positive count yields SKIPPED.
• Catalog-based pre-flight guards. Before a structural version the driver probes the catalog (column in SYSCOLUMNS? type in SYSTYPES? table in SYSTABLES?) so a re-run does not attempt to re-add an existing object — a real deploy tool's pre-flight.
• OR-REPLACE redeploy. Views, sequences, functions, procedures, triggers and aliases redeploy via CREATE OR REPLACE as clean no-op-shaped redeploys (a sequence re-seeds its START).
• MERGE upsert. SP_APPLY_MIGRATION's config write is a MERGE, idempotent even if the log check were bypassed.
• SAVEPOINT rollback. The driver takes its own SAVEPOINT around a risky version and ROLLBACK TO SAVEPOINT on failure, then logs FAILED — the schema/data are byte-for-byte unchanged and the session keeps working.
• Caller-owned version register. CURRENT_VER is advanced top-level by the driver only on OK, and only forward (never regressed on a re-run).

F.5 SQLSTATE & platform notes

DEP/i's engine procedures do not raise custom application SQLSTATEs (unlike a business app such as LOANSVC/i): a failed migration surfaces the engine's native SQL error from the failing DDL (e.g. a CHECK re-validation failure, a duplicate-object error, a foreign-key violation), which the driver captures from the batch result and routes to SP_LOG_MIGRATION_FAILED. The outcome is carried in the P_STATUS OUT parameter (OK/SKIPPED) and in MIGRATION_LOG.STATUS. The confirmed platform findings the source works around or asserts:

G. Glossary ↑ top

Alias

A second name for a table (CREATE ALIAS OORD FOR ORDERS). Reads and writes route through to the base table; DEP/i uses one to demonstrate the alias DevOps surface (and a confirmed alias insert gap, SQLPL-PLAT-DEP-04).

Catalog (QSYS2.SYS*)

DB2 for i's system catalog views (SYSTABLES, SYSCOLUMNS, SYSSEQUENCES, SYSVARIABLES, SYSTYPES, SYSTABAUTH, SYSCOLAUTH) DEP/i queries to verify a migration deployed the intended shape.

CI/CD deploy tool

Continuous-integration / continuous-delivery tooling that applies schema changes automatically. DEP/i is such a tool for a DB2-for-i schema: it runs versioned migrations, logs them, and is safe to re-trigger.

CURRENT_VER (version register)

A global CREATE VARIABLE holding the highest applied version. Advanced top-level by the driver (not from inside a routine, per SQLPL-PLAT-DEP-03), only forward, only on OK.

Distinct type (CREATE TYPE)

A user-defined type based on a built-in source type (MONEY_T AS DECIMAL(11,2), VERNO_T AS INTEGER), giving a domain a distinct name.

Idempotent

Safe to run again with the same result. DEP/i achieves this per step (log check → SKIPPED, catalog pre-flight guards, CREATE OR REPLACE, MERGE, DROP IF EXISTS) and end-to-end (a full pipeline re-run only adds SKIPPED rows).

MERGE

An upsert: update the row if it matches, insert if not. Makes SP_APPLY_MIGRATION's config write idempotent belt-and-braces.

Migration (versioned)

A numbered schema-change script (V1…V6) applied in order. Each assumes the previous shape and is logged by name and version.

MIGRATION_LOG

The deploy history table: one row per (version, name) attempt with STATUS OK / SKIPPED / FAILED, a timestamp, and a detail — the auditable record and the idempotency source of truth.

Rollback (SAVEPOINT / ROLLBACK TO SAVEPOINT)

A savepoint marks a point in a transaction; rolling back to it undoes everything since, leaving schema and data byte-for-byte unchanged. DEP/i wraps risky migrations this way so a bad step logs FAILED without half-applying.

Sequence (CREATE SEQUENCE)

A monotonic number generator (DEPLOY_SEQ, ORDNO_HINT_SEQ) drawn from via NEXT VALUE FOR.

SKIPPED

The idempotent no-op outcome: a migration already applied OK is not re-applied; a SKIPPED row is logged instead.

SQL PL

SQL Procedural Language — DB2's procedural dialect. DEP/i's runner procedures are SQL PL; the migration DDL is issued as external batches (SQL PL cannot portably EXECUTE IMMEDIATE arbitrary DDL).

TRUNCATE ... RESTART IDENTITY

Empties a table and resets its identity counter to the start (next generated value = 1) — a common CI pattern for resetting a staging area between deploys.