Replicate EMPDEMO end to end

CREATE TABLE department (
  dept_id        NUMERIC(4)   PRIMARY KEY,          -- CALC key, DUPLICATES NOT ALLOWED
  dept_name      TEXT         NOT NULL,
  dept_head_id   NUMERIC(4),                        -- not a set: a soft reference in the data
  src_commit_lsn TEXT, src_op CHAR(1)
);

CREATE TABLE office (
  office_code    CHAR(3)      PRIMARY KEY,
  office_city    TEXT, office_state CHAR(2), office_phone TEXT
);

CREATE TABLE job (
  job_id         NUMERIC(4)   PRIMARY KEY,
  title          TEXT NOT NULL, min_salary NUMERIC(9,2), max_salary NUMERIC(9,2)
);

CREATE TABLE employee (
  emp_id         NUMERIC(4)   PRIMARY KEY,          -- CALC key
  first_name     TEXT, last_name TEXT NOT NULL,
  status         CHAR(2),                           -- spaces -> NULL, agreed with business
  start_date     DATE NOT NULL,
  termination_date DATE,                            -- 00000000 -> NULL
  dept_id        NUMERIC(4) REFERENCES department,  -- NULLABLE: DEPT-EMPLOYEE is OPTIONAL
  office_code    CHAR(3)    REFERENCES office       -- NULLABLE: OFFICE-EMPLOYEE is OPTIONAL
);

CREATE TABLE emposition (
  emp_id         NUMERIC(4) NOT NULL                -- MANDATORY member of EMP-EMPOSITION
                   REFERENCES employee ON DELETE CASCADE,
  start_date     DATE       NOT NULL,               -- sorted set key, DUPLICATES NOT ALLOWED
  finish_date    DATE,
  salary_amount  NUMERIC(9,2), salary_grade NUMERIC(2),
  job_id         NUMERIC(4) REFERENCES job,         -- NULLABLE: JOB-EMPOSITION is OPTIONAL
  is_current     BOOLEAN    NOT NULL DEFAULT FALSE, -- set order was carrying this meaning
  PRIMARY KEY (emp_id, start_date)
);

CREATE TABLE expertise (                            -- junction record
  emp_id         NUMERIC(4) NOT NULL REFERENCES employee ON DELETE CASCADE,
  skill_id       NUMERIC(4) NOT NULL REFERENCES skill,
  skill_level    CHAR(2), expertise_date DATE,
  PRIMARY KEY (emp_id, skill_id)
);

CREATE TABLE coverage (                             -- member of TWO sets: two FKs
  emp_id         NUMERIC(4) NOT NULL REFERENCES employee ON DELETE CASCADE,
  plan_code      CHAR(3)    REFERENCES insurance_plan,   -- OPTIONAL: nullable
  selection_date DATE NOT NULL, coverage_type CHAR(1),
  PRIMARY KEY (emp_id, selection_date, coverage_type)
);

The five decisions a reviewer is looking for:

1. employee.dept_id and office_code are nullable, because both sets are OPTIONAL.
2. emposition is keyed (emp_id, start_date), justified by DUPLICATES ARE NOT ALLOWED on the sorted set — not by a db-key and not by a UUID.
3. emposition.emp_id is NOT NULL … ON DELETE CASCADE, because membership is MANDATORY and the application uses ERASE PERMANENT.
4. is_current exists because ORDER IS SORTED … DESCENDING was encoding "current position first". Losing set order without materialising the semantic loses information.
5. coverage carries two foreign keys; dropping either loses a real relationship.

BIND RUN-UNIT
READY ALL USAGE RETRIEVAL
MOVE '0035' TO EMP-ID-0415
OBTAIN CALC EMPLOYEE
ACCEPT DB-KEY                       *> save it — you will need to come back
OBTAIN FIRST WITHIN EMP-EMPOSITION  *> sorted DESCENDING, so this is the current one
OBTAIN OWNER WITHIN JOB-EMPOSITION  *> the job title
OBTAIN DB-KEY IS 71048:1            *> back to the employee
OBTAIN OWNER WITHIN DEPT-EMPLOYEE   *> the department

The naive version omits the ACCEPT/OBTAIN DB-KEY pair and tries OBTAIN OWNER WITHIN DEPT-EMPLOYEE straight after the JOB. That fails, because currency of run unit is now a JOB and currency of DEPT-EMPLOYEE is stale. Run both versions in the interpreter and read the status codes.

For a full extract, do not drive this from OBTAIN CALC per employee. Sweep EMP-DEMO-REGION in db-key order (Module 06), which reads each page once, and chunk by page range for parallelism.

Given the brief at the top of this page, your runbook has to state at minimum:

• Maximum tolerable capture outage: well under 48 hours. Archives are retained 48 hours, so alert at, say, 8 hours of lag and escalate hard at 24 — you need time to fix a problem, not just to notice it.
• The nightly local-mode job is a blind spot until proven otherwise. Confirm whether it journals. If it does not, salary-band changes will never reach the target and no error will ever be raised. Either get JOURNAL=ON added, or schedule a targeted compare-and-sync after it runs every night. Do not let this be discovered later.
• One 20-minute Sunday quiesce is not enough for a bulk load of any size. Use the start-capture-first watermark pattern and take the extract live; keep the quiesce window in reserve for cutover, not for the load.
• MAXERUS headroom is 16. That is the entire budget for capture, validation queries and any ETL. Get an explicit allocation and get the ceiling raised before, not after, production batch starts failing.
• Notification list: reorg or unload/reload, cold start, area restore, schema change, DMCL change, journal reconfiguration. With lead time, in writing.
• Reconciliation schedule: counts continuously, checksums nightly, full compare weekly and after every event on the notification list.