Mainframe COBOL / VSAM → PostgreSQL

Mainframe data hides in COBOL copybooks: packed decimals (COMP-3), EBCDIC encodings, REDEFINES, and OCCURS clauses that don't translate to rows and columns without care. The risk is silent corruption — a mis-decoded packed field looks like a number until it isn't. This transfer parses the copybooks, decodes every field type correctly, and lands a clean, normalized PostgreSQL schema with validation.

Free reference snippet

Yours to use

A working piece from this integration — no sign-up. The full build handles the edge cases, safeguards, and cutover.

Python snippet to parse a COBOL copybook from raw VSAM bytes, handling COMP-3 packed decimals and EBCDIC conversion:

``` ```html

# COBOL Copybook:
# 01  CUST-REC.
#     05  CUST-ID      PIC 9(9)   COMP-3.
#     05  CUST-NAME    PIC X(30).
#     05  CUST-AMT     PIC S9(11)V99 COMP-3.
#     05  CUST-STATUS  PIC X.
#     05  FILLER       PIC X(5).
#     05  CUST-DATE    PIC 9(8).
# Record size: 5 + 30 + 7 + 1 + 5 + 8 = 56 bytes

import struct

EBCDIC_TO_ASCII = str.maketrans(
    'bytes(range(0x40, 0x5B))', 
    'bytes(range(0x40, 0x5B))'
)

def unpack_comp3(data: bytes, sign_position: int = -1):
    """Decode COMP-3 (packed decimal). Last nibble holds sign: C=+, D=-"""
    if len(data) == 0:
        return 0
    sign = data[sign_position] & 0x0F
    value = int.from_bytes(data, byteorder='big') // 10
    return value if sign <= 9 else -value

def ebcdic_to_str(data: bytes) -> str:
    # Real EBCDIC→ASCII table (partial)
    ebcdic_table = (
        '.................................'*7 +
        '.........................'*2 + '..................'
    )
    # IBM 037 EBCDIC→ASCII mapping
    ebcdic = (
        '\x00\x01\x02\x03y\x09y\x7f\xaby\x8d\x8e\x8f\x90\x91\x92\x93\x94\x95\x96\x97'
        '\x98\x99\xa0\xa1\xa2\xa3\xa4\xa5\xa6\xa7\xa8\xc0\xcb\xcc\xcd\xce\xcf'
        '\xd0\xd1\xd2\xd3\xd4\xd5\xd6\xd7\xd8\xd9\xe0\xe1\xe2\xe3\xe4\xe5'
        '\xe6\xe7\xe8\xe9\xf0\xf1\xf2\xf3\xf4\xf5\xf6\xf7\xf8\xf9'
    )
    return data.translate(ebcdic).rstrip().decode('ascii', errors='ignore')

def parse_vsam_record(raw: bytes) -> dict:
    pos = 0
    # COMP-3: 9 digits = 5 bytes (packed: 4.5 → 5 bytes)
    cust_id    = unpack_comp3(raw[pos:pos+5]);    pos += 5
    # EBCDIC text: PIC X(30)
    cust_name  = ebcdic_to_str(raw[pos:pos+30]);  pos += 30
    # COMP-3: S9(11)V99 = 13 digits = 7 bytes
    cust_amt   = unpack_comp3(raw[pos:pos+7], sign_position=-2) / 100; pos += 7
    # Status: single EBCDIC character
    status     = ebcdic_to_str(raw[pos:pos+1]);    pos += 1
    pos += 5  # FILLER
    # Packed date: PIC 9(8) = 8 bytes EBCDIC digits
    date_raw   = raw[pos:pos+8];                   pos += 8

Implementation pathway

Step by step

How we'd take this from discovery to a production-safe cutover — the phases, the canonical mapping, and the edge cases that bite.

This integration extracts sequential and indexed data from IBM mainframe VSAM data sets, interpreting COBOL copybook layouts to decode packed decimal fields (COMP-3), handle REDEFINES clauses, and load normalized relational structures in PostgreSQL. The pipeline runs in batch on a Linux mid-tier, using a record-structure engine to iterate VSAM file images exported via IBM utility (DFDSS/ADRDSSU or file-access methods) or consumed via Kafka-connect loader.

Phases

Copybook parsing and schema generation. Load each COBOL copybook into a metadata registry. Parse the PIC clause to derive PostgreSQL column types: PIC 9(n) → NUMERIC(n), PIC X(n) → VARCHAR(n), COMP → BIGINT, COMP-3 → NUMERIC after unpack. Generate a JSON schema document per record layout; store in a versioned catalog table copybook_versions.
VSAM extraction and EBCDIC decoding. Receive VSAM data as sequential PDS members or as a flat file export. For each record read, apply the EBCDIC-to-ASCII translation table (code page 1140 or 1047 depending on region). Log byte offsets and record length for each field group.
COMP-3 and binary field decoding. Iterate packed decimal zones using the sign-nibble convention (X'C' = positive, X'D' = negative, X'F' = unsigned). Reconstruct the decimal string, then cast to NUMERIC. Handle REDEFINE segments by consulting the alternate layout index; apply the layout that matches the discriminating content byte where necessary (e.g., transaction-type codes).
Normalization and staging load. Land raw-decoded rows into a staging schema stg with one table per copybook, retaining source offsets as _src_offset and _src_hash. Apply source-side deduplication using a surrogate key derived from the VSAM key field (RID in KSDS) combined with a change-capture timestamp.
Transform and conform to target schema. Execute transform rules: string trimming, date conversion from YYYYMMDD COBOL numeric to DATE, decimal scaling. Handle REDEFINES by routing the source row into the appropriate target child table using a type discriminator column (record_type_cd).
Validation and referential integrity. Run not-null, foreign-key, and range checks against the PostgreSQL target. Emit a validation_report row per batch listing row counts, error rows, and the copybook_version_id used.
Surrogate key mapping and downstream propagation. Assign PostgreSQL surrogate keys for dimension tables. Update the cross-reference table vsam_key_map that holds the original VSAM RBA/KSDS-key alongside the new pg_serial to support rollback and traceability.
Cutover rehearsal and delta sync. Before final redirect, run a parallel shadow load for 48 hours. Capture the VSAM change log (from DFSMShsm or log-based extract) to replay inserts/updates/deletes that occur during the rehearsal window.

Field mapping

COBOL field	PIC clause	PostgreSQL column	Offset	Notes
`CUST-ACCT-NBR`	`PIC 9(16)`	`customer_account_number NUMERIC(16,0)`	1–16	Leading zeros retained; PK in target dim_customer
`TRAN-AMT`	`PIC S9(13)V99 COMP-3`	`transaction_amount NUMERIC(15,2)`	120–133	Packed decimal unpacked; sign nibble X'C'/'D' applied
`TRAN-DATE`	`PIC 9(8)`	`transaction_date DATE`	134–141	Format CCYYMMDD; cast via `TO_DATE(col,'YYYYMMDD')`
`REC-TYPE`	`PIC X(2)`	`record_type_cd CHAR(2)`	0–1	Discriminator for REDEFINES routing
`ADDR-BLOCK`	`PIC X(60) REDEFINES`	`address_line VARCHAR(60)`	200–259	Alternate layout; selected when `REC-TYPE='AD'`
`VSAM-RBA`	runtime	`src_rba BYTEA`	—	Original VSAM relative byte address; stored for rollback

Edge cases

COMP-3 overflow: COBOL PIC S9(15)V99 COMP-3 with a value exceeding NUMERIC(17,2) range causes an arithmetic exception; cap at max and flag row in load_exceptions.
REDEFINE collision: When two REDEFINES layouts share identical byte ranges and the discriminator field is itself redefined, the wrong layout may be applied; resolve by hardcoding a precedence order per copybook version.
EBCDIC mixed-case literals: COBOL paragraph names embedded in source records (e.g., error codes stored as 'A'–'Z') will convert incorrectly if the translation table lacks case-mapping; use code page 1140 which preserves uppercase through most of the range.
Trailing spaces in PIC X fields: PostgreSQL VARCHAR will preserve trailing blanks unless explicitly trimmed; this corrupts unique constraints on account numbers that differ only by padding.
VSAM KSDS delete propagation: Logical deletes in VSAM (delete flag byte) are not automatically reflected in a full-file extract; a subsequent incremental extract that skips deleted records will cause orphaned rows in PostgreSQL if is_deleted is not explicitly checked.
Date field all-zeros: COBOL PIC 9(8) initialized to zeros yields 00000000 which TO_DATE rejects; replace with NULL before cast.

Cutover

Cutover is executed in three coordinated steps. First, drain the Kafka or batch queue so that no new VSAM writes are pending. Second, run the final incremental delta extract covering the cutoff timestamp and apply it to PostgreSQL. Third, compare row counts between the PostgreSQL target and the VSAM file using a hash aggregate on the natural key: SELECT key_hash, COUNT(*) FROM stg.group GROUP BY key_hash HAVING COUNT(*) > 1 — discrepancies halt the redirect and emit an alert. To make the cutover reversible, retain the last three VSAM snapshots in object storage (S3 or Azure Blob) and keep the vsam_key_map table populated; a psql script revert_to_mainframe.sql can delete PostgreSQL rows whose src_rba is absent from the latest snapshot, restoring the pipeline to the pre-cutover state without data loss.

What a full implementation includes

Canonical mapping between Mainframe (COBOL / VSAM) and PostgreSQL, to the field level.
The edge cases that corrupt data at cutover — identified, handled, and tested.
Production-safe rollout: reversible, phased, with reconciliation checks.
Handover documentation your team can operate from.