Skip to content
Documentation

Data Model Extraction

FieldValue
TypeSkill Resource
Source~/.copilot/skills/architecture/references/discovery/data-model-extraction.md
DescriptionNot specified

Source Content

Data Model Extraction

Turning legacy record layouts — DDS, copybooks, DB2, VSAM, flat files — into a data model a modern engineer can read: a logical ERD plus a field dictionary. This is phase 2 of the discovery playbook. Extract the data before the logic; the record layout tells you what the program is about, and the code tells you what it does to that data.

Contents

Jack got the export dump on a Monday and read PIC S9(7)V99 COMP-3 as a 12-character string. He mapped it straight into a VARCHAR, shipped, and the finance team found benefit amounts off by orders of magnitude — a $1,450.00 payment stored as raw packed bytes.

Jill, handed the same copybook, saw COMP-3 and stopped: five bytes of packed decimal, two implied decimals, a sign nibble at the end. She decoded it to a scaled integer, sampled ten real rows against the source, and her numbers matched to the cent. The difference was not skill with SQL — it was reading the storage clause before trusting the picture.

This file is the discipline that keeps you on Jill’s path. Every gotcha below is a place where the bytes on disk do not mean what the field name suggests.

Where the model lives, per platform

Legacy systems rarely wrote their schema as SQL DDL. The schema lives in the layout language of each platform — and you read it there. Decode syntax lives in the platform decoders; this section only says where to look.

IBM i — DDS physical and logical files

A PF (physical file) is the table: a record format, fields with type and length, and key fields. An LF (logical file) is a view, index, join, or row filter over PFs — treat each one as a query the business cared about enough to persist. Field-line syntax (A, P, S, B, L) and the PF/LF split are in ibm-i-as400.md. A join logical file is a declared relationship — harvest it directly for the ERD.

IBM i — DB2 for i DDL and the QSYS2 catalog

Modern IBM i code uses SQL CREATE TABLE / CREATE INDEX / CREATE VIEW in .sql members, which can carry long names and constraints. When those exist, they are authoritative — no reverse-engineering needed. The live catalog in QSYS2 (SYSTABLES, SYSCOLUMNS, SYSKEYS) confirms the model against the running box; see Validating against live data. Watch the DDS-file vs SQL-DDL coexistence noted in ibm-i-as400.md.

Mainframe — COBOL copybooks

A copybook is the single best source of the mainframe data model — the schema the system never wrote as DDL. One copybook is usually one file’s record; inventory the copybooks first and you have enumerated the entities. The COPY … REPLACING trap (field-name prefixing at copy time) means the effective layout can differ from the raw copybook — expand it before trusting field names. Level numbers, PIC, and USAGE are decoded in mainframe-cobol.md.

Mainframe — DB2 embedded SQL and DCLGEN

When a COBOL program contains EXEC SQL … END-EXEC, the data model for those tables is real SQL. Get the DDL or the catalog (SYSIBM.SYSTABLES, SYSCOLUMNS) and you have an authoritative schema. DCLGEN generates a copybook-like host-variable declaration from a table — a reliable column list straight from the source. Details in mainframe-cobol.md.

Mainframe — VSAM record keys

A KSDS is a keyed store — treat it as a single keyed table. Its record layout is the copybook; its primary key is in the COBOL SELECT … RECORD KEY IS … clause. An alternate index (AIX) is a secondary access path — note it as a query the business needed. ESDS (insertion order) and RRDS (by record number) map to append-log and array shapes; see mainframe-cobol.md.

Flat-file record layouts

Sequential datasets (PS), IBM i flat files, and fixed-width interchange feeds have no declared keys at all — the layout is only in the copybook or DDS that reads them, and the “key” is a convention. Read the layout from whatever program OPENs the file, and confirm field boundaries by offset arithmetic against sample bytes. A flat file shared between two systems is an integration point — flag it for the dependency map.

The decode-gotcha catalog

Each gotcha is a place where the raw bytes betray the field name. For each: how to detect it in the layout, and how to decode it. The platform decoders own the deeper syntax — this catalog is the extraction checklist.

Packed decimal (COMP-3)

Two decimal digits per byte, with a sign nibble in the low half of the last byte. A PIC S9(7)V99 COMP-3 is 5 bytes, not 12 characters.

  • Detect: COBOL USAGE COMP-3 or PACKED-DECIMAL; DDS field type P. Byte length is ceil((digits + 1) / 2).
  • Decode: read two digits per byte; the final nibble is the sign (C/F positive, D negative). Then apply the implied decimal below. This is the single most common extraction error — Jack’s error.

Zoned decimal

Numbers stored one digit per byte as text, with the sign folded into the zone nibble of the last byte.

  • Detect: DDS field type S; COBOL numeric DISPLAY with a leading S in the PIC.
  • Decode: each byte is one digit; the last byte carries the sign in its high nibble (see signed overpunch). Length in bytes equals the digit count.

Signed overpunch

The sign of a zoned-decimal number is encoded into the last digit’s byte, so the final character prints as a letter or symbol.

  • Detect: a numeric field whose last position shows {, }, A–R, or a stray letter in a data sample.
  • Decode: { = +0, A–I = +1–+9; } = -0, J–R = -1–-9. So 12{ is +120 and 12} is -120. Glossary entry: signed overpunch.

EBCDIC vs ASCII

Mainframe and IBM i text is EBCDIC, not ASCII — the byte values differ for every letter and digit.

  • Detect: raw bytes where A is 0xC1 (not 0x41) and 0 is 0xF0 (not 0x30); tools showing garbage for known text.
  • Decode: transcode with a named code page (US is CP037; some shops use CP1140 for the euro sign). Pick the code page deliberately — the wrong one corrupts [, ], @, and currency symbols silently.

Implied decimal (V)

The decimal point is defined but never stored. S9(7)V99 holds 145000 on disk for the value 1450.00.

  • Detect: a V in a COBOL PIC; the decimal-position digit in a DDS packed/zoned field (the 2 in 9 2P).
  • Decode: scale by 10^(-decimals) after reading the raw integer. Keep the scale in the field dictionary — a missing scale is a silent hundred-fold error.

Binary and endianness (COMP)

COMP / COMP-4 / BINARY fields are true binary integers (2, 4, or 8 bytes), big-endian on these platforms.

  • Detect: COBOL USAGE COMP/BINARY; DDS field type B.
  • Decode: read as a big-endian signed integer of the declared byte width. Reading it little-endian (the x86 default) swaps the bytes and produces nonsense. Note the width — PIC S9(4) COMP is 2 bytes, S9(9) COMP is 4.

REDEFINES (one field, several meanings)

REDEFINES overlays a second layout on the same bytes, so 20 bytes can be a date in one branch and a code in another.

  • Detect: the REDEFINES keyword in COBOL; on IBM i, the same bytes read through different field definitions or a REFFLD-driven overlay.
  • Decode: find the condition that selects which view — that selector is a business rule, not just a layout note. Record both interpretations in the dictionary and cite the branch that picks each. See mainframe-cobol.md.

OCCURS and OCCURS DEPENDING ON

OCCURS makes an array; OCCURS DEPENDING ON makes the record variable-length, so offsets shift at runtime.

  • Detect: the OCCURS clause; a trailing DEPENDING ON counter-field.
  • Decode: a fixed OCCURS becomes a child table or a repeating group (12 monthly amounts → 12 rows keyed by month). DEPENDING ON means no fixed offset past that point — flag it; any flat extract must read the counter first. Full trap in mainframe-cobol.md.

Date storage forms

Dates are the richest source of silent bugs because the legacy world stored them a dozen ways, rarely as a real date type.

  • Detect: a numeric field named *DTE/*DATE/DT; a 6- or 7-digit packed field near an event.
  • Decode by form:
    • Packed CYYMMDD — the IBM i “century-year” form: a leading 0 means 19xx, 1 means 20xx. 1240704 is 2024-07-04.
    • Julian (YYDDD or CYYDDD) — year plus day-of-year (24186 = 2024 day 186).
    • Separate y/m/d fields — three numeric fields to reassemble.
    • 2-digit years — a windowing rule decides the century (often <50 → 20xx). Find the pivot in the code; do not assume it.

Reference fields (DDS REFFLD)

A DDS field can inherit its full definition — type, length, decimals, text — from another field’s dictionary entry via REFFLD.

  • Detect: the REF/REFFLD keyword on a DDS field; a field with no inline type.
  • Decode: resolve the reference to the source field’s definition before you record the type. The referenced field may live in another file, so the true type is not in the file you are reading. Glossary: reference field.

Coded and flag fields (single-char status codes)

A one-character field often carries a whole state machine: 'A' approved, 'P' pending, 'D' denied.

  • Detect: a PIC X or 1-char DDS field tested against literals; COBOL 88-level condition names bound to it.
  • Decode: harvest every value and its meaning. The 88-levels are gold — they name the states in plain English (88 CLAIM-APPROVED VALUE 'A'). On IBM i, the meanings hide in IF/SELECT comparisons and message files. Put the full value list in the field dictionary’s “Coded values” column.

FILLER

FILLER is unnamed space in a record — but it is not always dead.

  • Detect: the FILLER keyword (COBOL) or an unnamed gap in a DDS/flat layout.
  • Decode: confirm it is truly reserved. FILLER is a favorite hiding place for a field someone repurposed without renaming, or bytes later reclaimed by a REDEFINES. Note its offset and length so a re-add of meaning is caught.

Inferring relationships without foreign keys

Legacy files almost never declare foreign keys — the database engine of 1990 did not enforce them, so the relationships live in naming, access code, and convention. You infer them, and you mark how confident you are.

Signals that a relationship exists

  • Shared key field names. CUSTNO in both CUSTMAST and ORDHDR is a near-certain link. Watch for renamed twins — CUST-NO here, CUSTOMER-NUMBER there — same value, different label.
  • Naming conventions. A shop-wide prefix (CL for claims, EM for employers) groups a file family; a *MAST / *TRANS / *HIST suffix pattern signals master-detail-archive triples.
  • DDS join logical files. A join LF declares the relationship in the layout — the join fields are the foreign key, stated outright. Harvest these first; they are the only relationships the legacy system wrote down.
  • RPG access patterns. A CHAIN to file B using a field read from file A is a lookup — a many-to-one from A to B. A SETLL + READE loop on B keyed by an A value is a one-to-many (read all children of one parent). These key patterns are defined in ibm-i-as400.md.
  • Copybook reuse across files. The same copybook COPY’d into several programs means those programs share a record and likely a responsibility — a structural relationship even without a key. See mainframe-cobol.md.
  • EVALUATE / lookup on code fields. An EVALUATE or table lookup that maps a code to a description implies a lookup/reference entity — the code field points at a (often implicit) code table.

Inferring cardinality, and marking confidence

Cardinality comes from the access pattern, not the layout. A single-record CHAIN reads one parent per child (child → parent is many-to-one). A READE loop reads many children per parent (parent → child is one-to-many). A unique key on the child’s foreign field would make it one-to-one — confirm against the data.

Never present an inferred relationship as fact. Tag each edge with a confidence level and the evidence:

ConfidenceBasisExample
DeclaredA join LF, a DB2 constraint, or DDLCLAIMLF joins CLAIM to CLAIMANT on CLMID
StrongShared key name plus matching access codeCHAIN CUSTNO from ORDHDR into CUSTMAST
InferredNaming/convention only, no access code seen yetEM-prefixed files assumed one employer family
GuessA hunch worth checking against dataFILLER bytes may hold a region code

What to notice: the confidence column is the load-bearing one — it converts each relationship into a testable claim (“confirm CHAIN CUSTNO resolves 1:1 against CUSTMAST”), so a reviewer knows exactly what still needs proving against live data.

Building the ERD and field dictionary

The output of phase 2 is two artifacts: a logical ERD (entities, keys, relationships) and a field dictionary per entity (the decoded columns). Together they replace the schema the legacy system never wrote.

The logical ERD

Draw entities as boxes, primary keys and foreign keys marked, and relationships as lines carrying the inferred cardinality. Use the erDiagram pattern in mermaid-legacy-patterns.md — follow the technical-writing skill’s diagram rules before emitting the block, and put a “what to notice” line beneath it. Keep it logical, not physical: model what the data means (Claim, Claimant, Employer), collapsing the master/trans/hist file split into the entity it represents. Note the confidence of each inferred edge on or beside the line.

The field dictionary

One table per entity. This is where the decode-gotcha work becomes durable — every packed field, implied decimal, and coded value is written down once so no downstream engineer repeats Jack’s mistake.

FieldType / decodedMeaningCoded valuesNotes
CLMIDPacked 9,0 → int64Claim identifier (PK)-Join key to CLAIMANT (strong)
CLMAMTS9(7)V99 COMP-3 → decimal(9,2)Weekly benefit amount-Implied 2 decimals; sign nibble last byte
CLMSTSX(1) → enumClaim statusA approved, P pending, D deniedFrom 88-levels; see rule catalog
CLMDTEPacked CYYMMDD → dateClaim filed date-Century digit: 0=19xx, 1=20xx
FILLER1X(4) reservedUnused (verify)-Confirm not repurposed

What to notice: the “Type / decoded” column always shows both the raw legacy type and the modern target type with an arrow between them — that arrow is the extraction work, and its absence means a field was transcribed rather than decoded.

Verify before you trust the model

The data model is where a reverse-engineering pass is most often subtly wrong — a foreign key stated backwards, a byte-width off by two, a constraint quoted from a commented-out line. These errors pass every readability check and then break a literal reimplementation. Treat the first extraction as a draft, and re-open the source before the ERD ships.

For every key, type, and foreign key in the ERD, go back to the source line and confirm three things:

  • Direction. Re-read the access code that establishes the FK. A CHAIN from ORDHDR into CUSTMAST makes ORDHDR the child, not the parent — confirm the arrow points child → parent, not the reverse.
  • Width. Re-read the PIC/USAGE or DDS type and recompute the byte length yourself. A PIC S9(7)V99 COMP-3 is 5 bytes; a 2-byte length prefix is not 4. Do not carry a width forward from the first read.
  • Live vs. commented-out. State whether a constraint or key is active in the source or sitting in a comment / dead ---guarded DDL. A commented ON DELETE CASCADE is not a live foreign key — say so explicitly rather than promoting it to the ERD.

This is a source re-read, distinct from validating against live data below: this confirms you read the source correctly; that confirms the source matches the running box. Do both when a live connection exists; do at least this one always.

Validating against live data

A reverse-engineered model is a hypothesis until you check it. If a live DB2 or database connection or a data dictionary exists, confirm the model two ways — against the catalog and against sample rows.

Confirm structure against the catalog

Compare your entities and columns to the system catalog: QSYS2.SYSTABLES / SYSCOLUMNS / SYSKEYS on IBM i, SYSIBM.SYSTABLES / SYSCOLUMNS on DB2 for z/OS. The catalog gives real column names, types, lengths, and declared keys — reconcile every field, and treat any mismatch as a decode error to run down, not a catalog error. DDS-defined files and copybook-only files may have no catalog row; those stay reverse-engineered and marked as such.

Confirm values against sample rows

Pull a small sample and test each gotcha against reality:

  • Packed and zoned fields decode to plausible business numbers, not astronomical or negative-where-impossible values.
  • Implied-decimal scaling produces money that matches a known report total.
  • Coded fields contain only the values your dictionary lists — an unlisted code is an undocumented state.
  • Date fields fall in a sane range once decoded; a 1900 or 2099 row usually means the wrong date form.
flowchart TD
  RE[Reverse-engineered model]:::a --> Q{Live connection<br/>or dictionary?}:::b
  Q -->|no| MARK[Ship as inferred,<br/>mark confidence]:::c
  Q -->|yes| CAT[Reconcile vs catalog<br/>SYSTABLES / SYSCOLUMNS]:::d
  CAT --> SMP[Sample rows,<br/>test each gotcha]:::d
  SMP --> OK{Matches?}:::b
  OK -->|yes| CONF[Mark confirmed]:::e
  OK -->|no| FIX[Fix decode,<br/>re-sample]:::c
  FIX --> SMP
  classDef a fill:#e8eef7,stroke:#5b7aa8,color:#1a2a3a
  classDef b fill:#f3ecda,stroke:#b08a3a,color:#3a2e14
  classDef c fill:#f2e3e0,stroke:#a8685b,color:#3a1e1a
  classDef d fill:#e5efe6,stroke:#5a8a5f,color:#1e2e1f
  classDef e fill:#e8e2ef,stroke:#7a5ba8,color:#241a3a

What to notice: the model is never “done” — it is either confirmed against live data or shipped as inferred with its confidence marked, and the loop back from a value mismatch to fix-and-re-sample is where most silent decode errors get caught before a rebuild inherits them.