map-vsam-db2-to-jpa

name

map-vsam-db2-to-jpa

description

Maps the persistent-data surface of one Enterprise COBOL program — its VSAM file definitions (`SELECT ... ASSIGN`, `FD`, key structure from the FILE-CONTROL paragraph and the associated record copybook) and its embedded DB2 access (`EXEC SQL DECLARE TABLE`, DCLGEN copybooks, `EXEC SQL SELECT/INSERT/UPDATE/DELETE/FETCH ... INTO :host-var`) onto idiomatic Spring Data JPA artefacts for Java 21 + Spring Boot 3.x: one `@Entity` class per VSAM file or DB2 table, one `JpaRepository<Entity, Key>` per entity, a composite `@IdClass` or `@EmbeddedId` when the key has more than one field, a `jpa-mapping.json` sidecar that records every host-variable → column → record-component binding (so `transpile-cobol-to-java21` can rewrite `EXEC SQL` blocks into repository or `EntityManager` calls without re-parsing the COBOL), and an optional Flyway-style baseline DDL derived from the DECLARE TABLE statements. USE this skill when `analyze-cobol-program` has surfaced `data.files[]` of type VSAM (KSDS / ESDS / RRDS) and/or `data.dbAccess[]` with non-empty `tables[]` / `statements[]`, and you already have the corresponding record/DCLGEN copybook converted via `convert-copybook-to-java-record`. DO NOT use it for: (a) pure copybook → DTO conversion — that is `convert-copybook-to-java-record`; (b) generating *immutable* domain records (JPA needs a no-arg constructor and mutable fields); (c) flat-file QSAM / sequential data sets without a key (model those as read-only `Resource` streams in the transpile step instead); (d) writing actual procedural code that calls the repositories — that is `transpile-cobol-to-java21`; (e) deciding *which* slice of data to migrate first — that is `plan-strangler-fig-migration`. Inputs: `program-profile.json`, zero or more `copybook-mapping.json` files, zero or more DCLGEN copybook paths, optional `target-repo-style-profile.json`. Outputs: one `.java` file per `@Entity`, per `@IdClass`/`@Embeddable`, and per `JpaRepository`; one `jpa-mapping.json`; optional `db/migration/V<n>__<table>.sql` per DECLARE TABLE.

Purpose

Produce the persistence-layer skeleton that lets a transpiled Java 21 service read and write the same records the original COBOL program read and wrote — without changing the on-disk / on-DB2 layout, the primary keys, the column names, or the access patterns.

This skill is intentionally type + repository only. It emits no procedural code, no SQL builders, no service classes. The downstream transpile-cobol-to-java21 skill is responsible for replacing READ, WRITE, REWRITE, START, EXEC SQL SELECT, and cursor loops with JPA / EntityManager calls; this skill simply guarantees that the targets of those calls exist with the correct names, keys, and types.

When to use / When not to use

Use when:

• analyze-cobol-program#/data/files[] contains at least one entry with accessMethod: "VSAM-KSDS"|"VSAM-ESDS"|"VSAM-RRDS", or
• analyze-cobol-program#/data/dbAccess[] contains DB2 tables / statements, and
• the record copybook (for VSAM) or DCLGEN copybook (for DB2) has already been processed by convert-copybook-to-java-record so a copybook-mapping.json is available.

Do not use when:

• The program only does QSAM sequential I/O (no RECORD KEY, no ALTERNATE RECORD KEY, no RELATIVE KEY) — there is no JPA abstraction that fits; let the transpile skill emit a streaming reader/writer over the byte layout in copybook-mapping.json.
• The program uses IMS DL/I, ADABAS, Datacom, or another non-relational store — out of scope for v1.0.
• The DB2 access is only EXEC SQL CONNECT / COMMIT / ROLLBACK with no DML — there is nothing to map; record this fact in chaining.notes[] and skip.
• You are building immutable domain DTOs — those come from convert-copybook-to-java-record; JPA entities are a separate, mutable layer that the entity ↔ record mapper (generated by transpile-cobol-to-java21 or hand-written with MapStruct ) bridges.

Inputs

Required:

1. program_profile_path — absolute path to a program-profile.json emitted by analyze-cobol-program. The skill reads:
   • data.files[] — VSAM SELECT / FD entries with recordKey, alternateRecordKey[], accessMode (SEQUENTIAL|RANDOM|DYNAMIC), organization, recordName, copybook (member name → resolvable via data.copybooks[]).
   • data.dbAccess[].tables[] — schema, table, declared columns, primary key (when DCLGEN included PRIMARY KEY).
   • data.dbAccess[].statements[] — each EXEC SQL with its host variables and inferred read/write pattern.

Optional:

2. copybook_mappings[] — array of paths to copybook-mapping.json files. The skill resolves each VSAM record by matching data.files[i].recordName → records[].fromCobolName. When a mapping is missing for a referenced record, that file is skipped with W-COPYBOOK-MISSING:<recordName>.
3. dclgen_paths[] — array of DCLGEN copybook paths. Used when a data.dbAccess[].tables[] entry has no inline column list (older programs EXEC SQL DECLARE TABLE via INCLUDE). The skill parses the DECLARE TABLE block plus the matching host-variable group (typically named DCL<TABLE>).
4. style_profile_path — target-repo-style-profile.json from scan-target-repo-patterns. The skill reads javaVersion, packageLayout, persistenceStack (must include jpa — otherwise hard-fail E-PERSISTENCE-MISMATCH), namingConventions, and configStrategy (for application.yml snippet hints in the chaining.notes[]).
5. target_package — explicit Java package override (wins over the style profile).
6. emit_ddl — boolean (default false). When true, emit one db/migration/V<n>__<table>.sql per DECLARE TABLE in declaration order, starting at V1.
7. vsam_strategy — "jpa-table" (default) or "jpa-table+codec". jpa-table models VSAM as a relational table whose primary key mirrors RECORD KEY and whose remaining columns mirror the record copybook one-to-one. jpa-table+codec additionally records, in jpa-mapping.json, the byte offsets so a downstream reader can also load legacy fixed-length files into the same entity during cut-over.

Workflow

Execute the steps in order. After each numbered step, write a one-line checkpoint to the agent log so the run is resumable.

1. Validate input. Confirm program_profile_path exists, parses as JSON, and has schemaVersion ≥ 1.0. Confirm at least one of data.files[] (VSAM) or data.dbAccess[] (DB2) is non-empty. If both are empty, hard-fail E-NO-PERSISTENT-DATA.

2. Resolve copybooks. Build an in-memory index copybookByRecordName[fromCobolName] = mapping. For each VSAM file, locate the matching record mapping. For each DB2 table without inline columns, locate the matching DCLGEN block. Record misses as W-COPYBOOK-MISSING:<name> and exclude that file/table from emission (do NOT halt).

3. Pick the target package. Precedence: target_package > style_profile.packageLayout.persistence > derived from program name (com.acme.<programname-lower>.persistence). Record the chosen value in jpa-mapping.json#/javaPackage.

4. Plan each VSAM entity. For each surviving VSAM file:

   • Entity class name = UpperCamelCase of recordName, suffixed with Entity if the copybook already emitted a record with the bare name (look it up in the matching copybook-mapping.json#/records[].javaName).
   • Table name = the VSAM dataset DD-name lowercased with _ between words (e.g. BILL.CUST.KSDS → bill_cust_ksds) — or the explicit tableName from style_profile.namingConventions.
   • Primary key columns = the fields covered by RECORD KEY (one or more contiguous components of the record). Use @Id if single-column, @EmbeddedId (preferred) or @IdClass otherwise; pick @EmbeddedId unless style_profile says otherwise.
   • Alternate keys = @Index(columnList = "...") on the entity, name = "<entity>_alt<i>_idx" (i is 1-based in declaration order). When accessMode includes DYNAMIC, the index is unique = false; when the original ALTERNATE RECORD KEY had WITH DUPLICATES clause omitted, the index is unique = true.
   • All other record components become @Column fields, mirroring the Java type chosen by convert-copybook-to-java-record. The entity is mutable (private fields + getters/setters + protected no-arg constructor + equals/hashCode on @Id only).

5. Plan each DB2 entity. For each surviving table:

   • Entity class name = UpperCamelCase of the table name with the schema dropped (BILLDB.CUSTOMER → Customer).

   • @Table(schema = "BILLDB", name = "CUSTOMER") — preserve original case (DB2 catalog names are case-sensitive on z/OS).

   • Primary key from tables[].primaryKey[] (a column list) using the same @Id / @EmbeddedId rules as step 4.

   • Each declared column becomes a field. Map DB2 SQL types to Java per the table below:

     DB2 declaration	Java type	JPA hints
     CHAR(n)	String	length = n
     VARCHAR(n)	String	length = n
     SMALLINT	short	—
     INTEGER / INT	int	—
     BIGINT	long	—
     DECIMAL(p,s) / NUMERIC(p,s)	BigDecimal	precision = p, scale = s
     REAL / FLOAT(≤24)	float	—
     DOUBLE / FLOAT(>24)	double	—
     DATE	java.time.LocalDate	@Column(columnDefinition="DATE")
     TIME	java.time.LocalTime	—
     TIMESTAMP	java.time.LocalDateTime	—
     TIMESTAMP WITH TIME ZONE	java.time.OffsetDateTime	—
     ROWID	byte[]	@Column(length = 40)
     BLOB(n) / CLOB(n)	byte[] / String	@Lob
     XML	String	@Lob

     Columns declared NOT NULL set @Column(nullable = false); all others default to nullable = true. A DB2 WITH DEFAULT <lit> is captured in jpa-mapping.json#/entities[].columns[] .defaultValue only (do not translate to a @PrePersist side-effect — defaults belong in the DB).

6. Plan repositories. For each entity, emit interface <Entity>Repository extends JpaRepository<<Entity>, <KeyType>>. Add one derived query per alternate key (findBy<AltKeyCamel>(...)) and one Optional<<Entity>> findById(...) (inherited; mentioned only to call out the type). For every EXEC SQL statement in data.dbAccess[].statements[] whose target table belongs to an emitted entity, plan a method:

   • SELECT ... WHERE <pkCols> → inherit findById.
   • SELECT ... WHERE <other> → derived findBy<Cols>(...) using Spring Data naming conventions ; when the predicate is non-trivial (joins, LIKE, subqueries), emit @Query("...") instead and quote the original SQL verbatim with :hostVar rewritten to :javaName.
   • INSERT INTO <t> VALUES (...) → inherit save.
   • UPDATE ... WHERE <pk> → inherit save (after findById).
   • UPDATE ... SET <cols> WHERE <other> → emit a @Modifying @Query method with the same predicate.
   • DELETE ... WHERE <pk> → inherit deleteById.
   • DECLARE <cur> CURSOR FOR ... → emit a method returning Stream<<Entity>> annotated @Query(...); the transpile step will wrap it in a try-with-resources.

7. Resolve Java names and collisions. Apply the same naming pipeline as convert-copybook-to-java-record. Reserved-word collisions in column-mapped fields get _ suffix and emit W-NAME-RESERVED:<original>. Two columns that normalise to the same Java name get _2, _3, … and emit W-NAME-COLLISION:<javaName>.

8. Emit Java files. For each plan, write:

   • <javaPackage-as-dirs>/<Entity>.java — @Entity, @Table, fields, getters/setters, protected no-arg ctor, public all-args ctor, equals/hashCode on id only, toString of id + class name only (never the full row, to avoid PII in logs).
   • <javaPackage-as-dirs>/<Entity>Id.java — when key is composite, an @Embeddable record-like class (regular class, not record, because JPA requires no-arg ctor) implementing Serializable.
   • <javaPackage-as-dirs>/<Entity>Repository.java — JpaRepository-derived interface with planned methods.

9. Emit jpa-mapping.json. See the schema below. Deterministic: arrays sorted by (entityClass, columnName); map keys sorted; no timestamps; no absolute paths (only repo-relative).

10. Emit DDL (optional). When emit_ddl == true, render one db/migration/V<n>__<table>.sql per DB2 entity in declaration order: CREATE TABLE, PRIMARY KEY, CREATE INDEX for each alternate key. No IF NOT EXISTS (Flyway tracks state itself). VSAM-derived entities do not get DDL unless vsam_strategy == "jpa-table" and the user explicitly opts in by passing emit_ddl: true and the file's recordKey is fully covered by record components. Otherwise emit W-DDL-SKIPPED:<entity>:<reason>.

jpa-mapping.json schema (informative)

{
  "schemaVersion": "1.0",
  "source": {
    "programProfile": "BILL010.program-profile.json",
    "copybookMappings": ["BILLREC.copybook-mapping.json"],
    "dclgenPaths": ["DCLCUST.cpy"],
    "styleProfileSeen": true,
    "vsamStrategy": "jpa-table"
  },
  "javaPackage": "com.acme.billing.persistence",
  "entities": [
    {
      "entityClass": "BillCustomer",
      "javaFile": "com/acme/billing/persistence/BillCustomer.java",
      "kind": "VSAM-KSDS",
      "table": { "schema": null, "name": "bill_cust_ksds" },
      "primaryKey": {
        "kind": "embedded",
        "idClass": "BillCustomerId",
        "columns": ["cust_id"]
      },
      "alternateKeys": [
        { "name": "bill_customer_alt1_idx", "unique": true,
          "columns": ["cust_ssn"] }
      ],
      "columns": [
        {
          "javaName": "custId",
          "columnName": "cust_id",
          "javaType": "long",
          "sqlType": "BIGINT",
          "nullable": false,
          "fromCobolName": "BILL-CUST-ID",
          "fromCopybook": "BILLREC",
          "byteOffset": 0,
          "byteLength": 8,
          "defaultValue": null
        }
      ],
      "repository": {
        "javaFile": "com/acme/billing/persistence/BillCustomerRepository.java",
        "interfaceName": "BillCustomerRepository",
        "keyType": "BillCustomerId",
        "methods": [
          { "name": "findByCustSsn",
            "kind": "derived",
            "returnType": "Optional<BillCustomer>",
            "params": [ { "name": "custSsn", "type": "String" } ],
            "fromStatement": null },
          { "name": "findActiveSince",
            "kind": "query",
            "returnType": "Stream<BillCustomer>",
            "params": [ { "name": "since", "type": "LocalDate" } ],
            "query": "select c from BillCustomer c where c.status = 'A' and c.openedOn >= :since",
            "fromStatement": "stmt-014" }
        ]
      }
    }
  ],
  "warnings": [
    "W-COPYBOOK-MISSING:LEGACY-REC",
    "W-NAME-RESERVED:CLASS"
  ],
  "chaining": {
    "styleProfileSeen": true,
    "programProfileSeen": true,
    "copybookMappingsSeen": 1,
    "notes": [
      "Add spring.jpa.hibernate.ddl-auto=validate in application.yml",
      "BillCustomerRepository.findActiveSince was derived from stmt-014"
    ]
  }
}

Validation

Run all checks before declaring success. V1–V5 are hard-fail; V6–V12 append a code to warnings[] (sorted as strings).

• V1 (hard). jpa-mapping.json is valid JSON and conforms to the schema (all required keys present, arrays present even when empty).
• V2 (hard). Every emitted .java file parses with a Java 21 parser (javac --release 21 -proc:none -d /tmp/out) and resolves every JPA import (jakarta.persistence.* ) plus org.springframework.data.jpa.repository.JpaRepository . No raw `javax.persistence.*` imports — this skill targets Spring Boot 3.x / Jakarta EE 10+ only.
• V3 (hard). Every entity has exactly one @Id or exactly one @EmbeddedId (never both, never zero). Composite-key classes implement Serializable and override equals/hashCode.
• V4 (hard). Every entities[].columns[].javaType agrees with the matching copybook-mapping.json record component (when a copybook mapping is present) or the DB2-type table in step 5. Type drift between copybook and entity is hard-fail E-TYPE-DRIFT:<entity>.<column>.
• V5 (hard). entities[] is non-empty when at least one VSAM file or DB2 table survived step 2. Otherwise emit E-NO-ENTITIES.
• V6. A VSAM file whose RECORD KEY is not fully covered by components in the matching copybook-mapping.json emits W-VSAM-KEY-UNRESOLVED:<file> and is skipped from entity emission (the JSON still records the attempt for traceability).
• V7. A DB2 table referenced by data.dbAccess[].statements[] but absent from data.dbAccess[].tables[] and from any DCLGEN emits W-DB2-TABLE-UNDECLARED:<table>; the statement is recorded in entities[].repository.methods[].fromStatement only if the table is later resolvable, otherwise it is dropped.
• V8. Reserved-word / collision warnings as W-NAME-RESERVED:<original> and W-NAME-COLLISION:<javaName>, matching the codes used by convert-copybook-to-java-record.
• V9. A cursor whose SELECT references columns from more than one entity emits W-CURSOR-MULTI-ENTITY:<cursor> and is emitted as a method returning Stream<Object[]> with the raw @Query; the transpile skill is expected to project it.
• V10. An EXEC SQL UPDATE ... SET <col> = :host where <col> is part of the primary key emits W-PK-UPDATE:<table> and is rendered as a @Modifying query (never as save).
• V11. Mixing vsam_strategy: jpa-table+codec with a copybook that contains a REDEFINES family is allowed but emits W-VSAM-REDEFINES-CODEC:<file> — the codec hint in the mapping records the variants[] group and leaves projection to the consumer.
• V12. When style_profile.persistenceStack does not contain jpa, the run hard-fails E-PERSISTENCE-MISMATCH. Use this skill only against a JPA-target repo.

Common pitfalls

• Treating a VSAM ESDS as a queryable table. ESDS is append-only and accessed by relative byte address; modelling it as JpaRepository is misleading. For ESDS, prefer the jpa-table+codec strategy and document that the only safe derived query is by RBA-equivalent surrogate id.
• Composite keys via @IdClass. @IdClass requires the entity to also duplicate the id fields. Prefer @EmbeddedId unless the target style profile explicitly says otherwise; mark primaryKey.kind: "idclass" only when forced.
• String for fixed-length COBOL keys. A PIC X(10) VSAM key must be padded to 10 spaces on lookup or it will miss. Record byteLength in the mapping and document the padTrailingSpaces() requirement in entity Javadoc; do not silently String.format("%-10s", ...) inside the entity.
• DB2 TIMESTAMP with microseconds. Java's LocalDateTime preserves nanos; DB2 truncates to 6 fractional digits by default. Record precision = 6 in jpa-mapping.json and let the transpile skill add a precision normaliser.
• DECIMAL(p,0) is not always long. DB2 packs DECIMAL(18,0) as packed-decimal — using long will overflow at 19 digits. Always map DECIMAL(p,s) to BigDecimal regardless of scale.
• Cursor WITH HOLD semantics. A DECLARE ... CURSOR WITH HOLD survives COMMIT. JPA does not have a direct equivalent — record cursorHold: true in the method metadata so the transpile skill can choose between @Transactional(propagation = REQUIRES_NEW) and explicit EntityManager.flush() calls.
• Lowercased table names on z/OS DB2. DB2 on z/OS stores unquoted names in uppercase. Always emit @Table(name = "<UPPER>", schema = "<UPPER>") exactly as declared; do not lowercase for "Java aesthetics".
• Implicit FOR UPDATE OF on cursor. A SELECT FOR UPDATE cursor must be modelled with LockModeType.PESSIMISTIC_WRITE. Record lock: "PESSIMISTIC_WRITE" in the method metadata; do not silently downgrade to a plain SELECT.
• VSAM ALT KEY with WITH DUPLICATES. Treat as unique = false index. Forgetting this turns later INSERTs into surprise constraint violations under cut-over load.

Outputs

Written to the agent's current working directory:

1. One .java file per entity under <javaPackage-as-dirs>/.
2. One .java file per @EmbeddedId / @IdClass (composite keys only).
3. One .java file per <Entity>Repository.
4. jpa-mapping.json — canonical mapping (schema above). Consumed by transpile-cobol-to-java21 and generate-modernization-tests.
5. Optional: db/migration/V<n>__<table>.sql per DB2 table when emit_ddl == true.

All emitted files are idempotent: re-running the skill on unchanged inputs MUST produce byte-identical output (sorted arrays, stable formatting, LF endings, single trailing newline, no timestamps, no absolute paths).

Chaining

Upstream (required or strongly recommended):

• analyze-cobol-program — supplies program-profile.json with data.files[] and data.dbAccess[]. Without it this skill has nothing to map.
• convert-copybook-to-java-record — supplies one or more copybook-mapping.json files so entity column types match the DTO record types exactly (no drift).
• scan-target-repo-patterns — supplies target-repo-style-profile.json so package, naming, and persistence stack match the host repository.

Downstream (typical):

• transpile-cobol-to-java21 — reads jpa-mapping.json to rewrite each EXEC SQL and each VSAM READ/WRITE/REWRITE/START into the corresponding repository or EntityManager call, using fromStatement to locate every site.
• plan-strangler-fig-migration — reads entities[] to choose which tables/files form the first migration slice (entities with no inbound @ManyToOne foreign keys are usually the safest starting point).
• generate-modernization-tests — reads entities[].columns[] and the copybook-mapping.json byteOffset / byteLength to build Testcontainers + JPA characterisation tests that load legacy fixed-length records, persist them via the new entities, and reread them via the repositories, asserting byte-level fidelity.