דיאגרמות קשרי ישויות מסכמות מסדי נתונים מדור קודם

Connect SchemaSpy to a modern PostgreSQL database and it produces a complete ERD in minutes: every table, every column, every relationship line drawn from the foreign key constraints that the database enforces. The diagram is accurate because the schema is complete, the database itself knows what entities exist, what their attributes are, and what relates them to each other. The tool reads the catalog; the diagram appears.

Connect the same category of tool to a mainframe DB2 database from 1987, a VSAM file system, or an IMS hierarchical database, and the result is different in every important way. For DB2, you get tables and columns but no relationship lines, because the foreign key constraints that would draw those lines were never defined. The relationships exist, but they live in COBOL PROCEDURE DIVISION logic, not in a DB2 REFERENCES clause. For VSAM, you get nothing, there is no catalog entry describing what fields exist in a VSAM file; that information lives in FD entries and copybooks distributed across thousands of COBOL source files. For IMS, the segment hierarchy that defines the data structure is in DBD (Database Description) source, not in any format that relational ERD tools can read. The ERD that modernization teams need, the one that shows what entities exist, what their attributes are, and how they relate, cannot be produced by any standard ERD tool from a legacy database without source code analysis.

Why Standard ERD Tools Fail for Legacy Databases

The standard ERD reverse engineering tool assumes a specific set of conditions: a running database server accessible via JDBC, a schema with declared tables and columns, and foreign key constraints that formally declare the relationships between tables. Given these conditions, ERD generation is a solved problem. SchemaSpy, Dataedo, dbForge, Aqua Data Studio, and dozens of similar tools produce accurate ERDs from any JDBC-accessible relational database in minutes.

Legacy database environments violate every one of these assumptions:

The schema is not in the database. VSAM files store records as byte streams. The record layout, which bytes represent which fields, what data type each field uses, what the field’s business meaning is, exists in COBOL FD entries and COPY members, not in any database catalog. There is no VSAM equivalent of INFORMATION_SCHEMA. A VSAM file that has been in production for forty years may have its record layout defined in a copybook that 200 programs include, with the actual field definitions visible only by parsing those source files.

The relationships are in the code, not the schema. Even when a DB2 schema is accessible via JDBC, the foreign key constraints that ERD tools use to draw relationship lines are frequently absent. DB2 on z/OS historically allowed, and many organizations practiced, enforcing referential integrity through COBOL program logic rather than through database-level constraints. A COBOL program that reads a CUSTOMER record by CUST-ID, validates that the CUST-ID exists before processing, and then reads the associated ACCOUNT records is implementing a customer-to-account relationship. That relationship is real and significant for the ERD. It is invisible to any tool that reads only the DB2 catalog.

The data structure is not relational. IMS (Information Management System) and IDMS organize data in hierarchical segment trees, not relational tables. The parent-child relationships between IMS segments are defined in DBD source files, not in foreign key constraints. An IMS PCB (Program Communication Block) defines which segment types a program can access and in what sequence. The ERD for an IMS database is a segment hierarchy diagram, not a crow’s foot relational diagram, and it must be derived from the DBD source rather than from a relational catalog.

The Three Legacy Database Types and Their ERD Challenges

DB2 on z/OS: Visible Schema, Invisible Relationships

DB2 on z/OS is, in principle, a relational database with a queryable catalog. SYSCOLUMNS, SYSTABLES, and SYSTABLEPART provide the entity and attribute definitions. The challenge is the relationship layer.

IBM DB2 on z/OS supports referential integrity constraints, but their adoption in production systems was historically inconsistent. Performance considerations during the batch-processing era led many organizations to disable constraint checking and enforce relationships in COBOL code instead. A DB2 schema with 500 tables may have fewer than 20 foreign key constraints declared, not because the tables are unrelated, but because the remaining relationships were deemed too expensive to enforce at the database level and are instead enforced by COBOL programs.

A DB2 ERD produced from the catalog alone shows the entities and their attributes correctly. It shows the relationships incorrectly, either drawing the 20 declared FK relationships and missing the 480 undeclared ones, or drawing no relationships at all.

The SQL to extract what the catalog does provide:

SQL

-- DB2 z/OS: extract declared tables and columns (entity/attribute layer)
SELECT
    T.NAME          AS entity_name,
    C.NAME          AS attribute_name,
    C.COLTYPE       AS data_type,
    C.LENGTH        AS field_length,
    C.SCALE         AS decimal_scale,
    C.NULLS         AS nullable,
    C.KEYSEQ        AS primary_key_sequence
FROM SYSIBM.SYSTABLES  T
JOIN SYSIBM.SYSCOLUMNS C
    ON C.TBNAME   = T.NAME
    AND C.TBCREATOR = T.CREATOR
WHERE T.TYPE = 'T'
ORDER BY T.NAME, C.COLNO;

-- DB2 z/OS: extract declared referential constraints (incomplete relationship layer)
SELECT
    R.RELNAME       AS relationship_name,
    R.TBNAME        AS child_table,
    R.REFTBNAME     AS parent_table,
    R.DELETERULE    AS delete_rule,
    R.UPDATERULE    AS update_rule
FROM SYSIBM.SYSRELS R
ORDER BY R.TBNAME;

The second query may return zero or very few rows for a legacy DB2 schema, not because the relationships do not exist, but because they were not declared. The ERD lines for undeclared relationships must come from COBOL source analysis.

VSAM Files: Schema Exists Only in Source Code

A VSAM KSDS (Key-Sequenced Data Set) stores records physically ordered by a prime key. The prime key is a byte offset and length within the record. What those bytes represent in business terms, what the remaining bytes contain, and how this record relates to records in other VSAM files are defined nowhere in VSAM metadata. They exist only in the COBOL FD entries and COPY members that programs use to read and write the file.

Reconstructing a VSAM entity definition from source:

קובול

      *> FD entry: the entity definition for CUSTOMER-FILE
      *> This IS the ERD entity "Customer" with its attributes
       FD  CUSTOMER-FILE
           LABEL RECORDS ARE STANDARD
           RECORD CONTAINS 350 CHARACTERS.
       01  CUSTOMER-RECORD.
           COPY CUSTMSTR.           *> Attribute definitions in copybook

      *> CUSTMSTR copybook expands to:
       01  CUSTOMER-RECORD.
           05  CUST-ID          PIC 9(10).       *> Primary key
           05  CUST-NAME        PIC X(40).       *> Customer name
           05  CUST-DOB         PIC 9(8).        *> Date of birth
           05  CUST-STATUS-CD   PIC X(2).        *> Status code
               88 CUST-ACTIVE   VALUE 'AC'.
               88 CUST-INACTIVE VALUE 'IN'.
           05  CUST-OPEN-DATE   PIC 9(8).        *> Account open date
           05  CUST-REGION-CD   PIC X(4).        *> Region (FK to REGION-FILE)
           05  FILLER           PIC X(286).

From this FD entry, the ERD entity “Customer” has six attributes with their types, lengths, and a primary key (CUST-ID). The 88-level values define the valid values for CUST-STATUS-CD, these map to ERD constraints or enumerated type definitions in the target schema. The field CUST-REGION-CD appears to be a foreign key to REGION-FILE, but this is only confirmable by analyzing the COBOL PROCEDURE DIVISION to verify that a program reads REGION-FILE using the value of CUST-REGION-CD as the key.

The relationship line in the ERD between Customer and Region is not in the VSAM catalog, it is in the COBOL program logic.

IMS Hierarchical Databases: Segment Trees, Not Relational Tables

IMS organizes data as hierarchical segment trees. A root segment type (the parent entity) has one or more child segment types. Each child can have its own children. The resulting tree structure is the IMS equivalent of an ERD, but it is not a relational ERD, it is a containment hierarchy where child records are physically stored within or adjacent to their parent records.

The IMS data structure is defined in a DBD (Database Description) source file:

DBD    NAME=CUSTDB,ACCESS=HDAM
DATASET DD1=CUSTDD,DEVICE=3390
SEGM   NAME=CUSTROOT,BYTES=200,FREQ=1000
FIELD  NAME=(CUSTID,SEQ),BYTES=10,START=1,TYPE=C
FIELD  NAME=CUSTNAME,BYTES=40,START=11,TYPE=C
FIELD  NAME=CUSTSTAT,BYTES=2,START=51,TYPE=C
SEGM   NAME=ACCTSEGS,PARENT=CUSTROOT,BYTES=100,FREQ=5
FIELD  NAME=(ACCTNO,SEQ),BYTES=12,START=1,TYPE=C
FIELD  NAME=ACCTBAL,BYTES=13,START=13,TYPE=P
SEGM   NAME=TXNSEGS,PARENT=ACCTSEGS,BYTES=80,FREQ=20
FIELD  NAME=(TXNDATE,SEQ),BYTES=8,START=1,TYPE=C
FIELD  NAME=TXNAMT,BYTES=13,START=9,TYPE=P

This DBD defines three entity types in a parent-child-grandchild hierarchy: CUSTROOT (Customer) contains ACCTSEGS (Account), which contains TXNSEGS (Transaction). In ERD terms:

• Customer (1) —- has (many) —- Account
• Account (1) —- has (many) —- Transaction

The ERD lines are one-to-many from parent to child. The cardinality is encoded in the FREQ parameter (average occurrences) and in the physical access patterns visible in PCB definitions. The entity attributes are the FIELD definitions under each SEGM.

Converting an IMS hierarchical structure to a relational ERD for migration target design requires making explicit the implicit parent-child foreign key that IMS enforces physically: ACCTSEGS always belongs to exactly one CUSTROOT. In the target relational schema, this becomes a FK column in the Account table referencing the Customer primary key.

The COBOL Code as Relationship Specification

The relationship layer of the ERD for any legacy database type, DB2, VSAM, or IMS, is found primarily in COBOL PROCEDURE DIVISION data access patterns. Three patterns are the most common relationship indicators:

Sequential read with key qualification. A program reads File A to get a key value, then uses that key to read File B:

קובול

       PROCESS-CUSTOMER.
           READ CUSTOMER-FILE
               KEY IS WS-CUST-KEY
               INVALID KEY PERFORM CUST-NOT-FOUND
           END-READ

           MOVE CUST-ID TO WS-ACCT-SEARCH-KEY
           READ ACCOUNT-FILE
               KEY IS WS-ACCT-SEARCH-KEY
               INVALID KEY PERFORM ACCT-NOT-FOUND
           END-READ.

This pattern establishes a relationship: Customer relates to Account through CUST-ID. The ERD line is: CUSTOMER (1) —- has (0 or more) —- ACCOUNT.

The cardinality (one customer to one account, or one customer to many accounts) is determined by whether the second READ is followed by a PERFORM UNTIL EOF pattern for sequential scanning:

קובול

       READ-ALL-ACCOUNTS.
           MOVE CUST-ID TO WS-ACCT-KEY
           START ACCOUNT-FILE KEY >= WS-ACCT-KEY
           PERFORM UNTIL WS-ACCT-EOF = 'Y'
               READ ACCOUNT-FILE NEXT RECORD
                   AT END MOVE 'Y' TO WS-ACCT-EOF
               END-READ
               IF NOT WS-ACCT-EOF
                   PERFORM PROCESS-ACCOUNT
               END-IF
           END-PERFORM.

The PERFORM UNTIL EOF pattern after a START establishes that one CUSTOMER has many ACCOUNTS, the cardinality is one-to-many.

Lookup/validation pattern. A program reads a reference file to validate a code value in the main record:

קובול

       VALIDATE-REGION.
           MOVE CUST-REGION-CD TO WS-REGION-KEY
           READ REGION-FILE
               KEY IS WS-REGION-KEY
               INVALID KEY MOVE 'INVALID' TO WS-VALIDATION-STATUS
           END-READ.

This establishes a foreign key relationship: CUSTOMER.CUST-REGION-CD references REGION.REGION-CD. The ERD line is: REGION (1) —- classifies (0 or more) —- CUSTOMER.

Embedded SQL join pattern. For DB2-accessing programs, embedded SQL SELECT statements with JOIN clauses are the most explicit relationship specification:

קובול

       EXEC SQL
           SELECT C.CUST-NAME, A.ACCT-BALANCE
           INTO  :WS-CUST-NAME, :WS-ACCT-BAL
           FROM  CUSTOMER C
           JOIN  ACCOUNT  A ON A.CUST-ID = C.CUST-ID
           WHERE C.CUST-ID = :WS-KEY-CUST-ID
       END-EXEC.

The JOIN ON clause is the explicit FK relationship: ACCOUNT.CUST-ID references CUSTOMER.CUST-ID. This relationship may not exist in DB2’s SYSRELS table, but it is definitively present in the COBOL source.

Handling REDEFINES: Multiple Entity Types in One Record

VSAM records that use REDEFINES create a specific ERD modelling challenge: the same physical record contains different attribute sets depending on a discriminator field. This maps to the ERD concept of subtype entities, a parent entity type (the common record) and multiple subtype entities (the REDEFINES variants).

The ERD representation:

TRANSACTION (supertype)
    |-- TXN-TYPE = 'PM' --> PAYMENT-TRANSACTION (subtype)
    |-- TXN-TYPE = 'RF' --> REFUND-TRANSACTION (subtype)
    |-- TXN-TYPE = 'AJ' --> ADJUSTMENT-TRANSACTION (subtype)

In the target relational schema, this can be implemented as:

• Single-table inheritance: one table with all columns, variant-specific columns nullable
• Class-table inheritance: one table for the supertype, one per subtype with a FK to the supertype
• Concrete-table inheritance: separate table per subtype, no supertype table

The ERD must represent the REDEFINES hierarchy accurately so that the target schema design decision can be made with full knowledge of the variant structure.

Building the Complete ERD: The Combination Approach

A complete ERD for a legacy database environment requires combining three sources:

Source 1: Database catalog. For DB2, extract tables, columns, and declared FK constraints from SYSCOLUMNS and SYSRELS. For IMS, extract segment types and field definitions from DBD source. For VSAM, there is no catalog, proceed directly to Source 2.

Source 2: COBOL FD entries and copybooks. For every data file in scope, extract the record layout from the FD entry and the COPY member it references. This produces the entity attribute definitions for VSAM files and supplements the DB2 catalog with column-level business metadata (field descriptions from copybook comments, 88-level valid values as constraint definitions, COMP-3 precision as data type precision).

Source 3: COBOL PROCEDURE DIVISION access patterns. For every program that accesses data in scope, extract the data access patterns: which files are read with which keys, which join patterns establish relationships between files, which validation patterns establish foreign key relationships to reference files. This produces the relationship lines that neither the database catalog nor the FD entries contain.

The three sources combined produce an ERD that accurately represents:

• All entities (tables, VSAM files, IMS segments) with their attributes
• All explicitly declared relationships (from DB2 catalog FK constraints)
• All implicitly enforced relationships (from COBOL code patterns)
• All subtype structures (from REDEFINES hierarchies)
• All reference data relationships (from validation lookup patterns)

This combined ERD is the data model specification that migration target schema design requires.

Using the Legacy ERD for Target Schema Design

The legacy ERD serves three specific purposes in target schema design:

Primary key mapping. The prime key of a VSAM KSDS, the key field of a DB2 table, and the sequence field of an IMS segment all map to primary keys in the target relational schema. The COBOL field definition (PIC 9(10), PIC X(12)) determines the target column type.

Foreign key definition. Every relationship line in the legacy ERD that was enforced in COBOL code becomes an explicit FK constraint in the target relational schema. The COBOL enforcement is replaced by database-level enforcement, which is the correct behavior for a modern relational system.

Subtype normalization. REDEFINES hierarchies in VSAM records become one of the three inheritance mapping patterns (single-table, class-table, or concrete-table) depending on the business requirements for query performance, null tolerance, and access pattern.

Precision-preserving type mapping. COMP-3 packed decimal fields with specific precision (PIC S9(9)V99 COMP-3) map to DECIMAL(11, 2) in the target schema, not to FLOAT, which loses precision, and not to VARCHAR, which loses the numeric constraint. The ERD’s attribute definitions must carry this precision metadata to prevent data quality loss during migration.

איך SMART TS XL Reconstructs Legacy ERDs

SMART TS XL"S ניתוח קוד סטטי parses every COBOL FD entry, COPY member, and PROCEDURE DIVISION data access pattern across the full portfolio. From this analysis, it produces the entity and attribute definitions for every data file in scope, including VSAM files that have no database catalog entry, and the relationship map derived from data access patterns in the COBOL source.

השמיים מיפוי תלות יישומים traces the data flow between programs and data stores: which programs access which files, with which keys, in which access patterns. This operational dependency map is the source of the relationship lines that the legacy ERD’s code-derived relationship layer requires, the customer-to-account relationship that the DB2 catalog does not declare but the COBOL JOIN ON clause does.

השמיים ניתוח השפעות capability makes the ERD actionable for migration planning: when a target schema decision changes an entity’s primary key structure, the impact analysis identifies every COBOL program that accesses that entity and would require corresponding changes. The ERD shows the data model; the impact analysis shows the program scope of any change to it.

השמיים מודרניזציה מורשת analysis produces the VSAM structural inventory, every FD entry with its field definitions, REDEFINES hierarchies, COMP-3 precision specifications, and 88-level valid values, that forms the entity attribute layer of the complete legacy ERD. For organizations that have not formally documented their VSAM schemas, this structural inventory is the first complete data model they have ever had.

השמיים חיפוש ארגוני capability makes the ERD findings queryable: find every program that accesses a specific entity, every field that appears to be a foreign key to a specific reference file, every REDEFINES hierarchy that creates entity subtypes. This search capability supports the iterative ERD construction process, building, verifying, and refining the legacy data model before migration target schema design is finalized.

The ERD the Database Never Kept

Every modern database keeps its own schema. PostgreSQL, MySQL, Oracle, and SQL Server maintain catalog tables that describe every entity, attribute, and relationship the database contains. Reverse engineering an ERD from these systems is fast, accurate, and fully automated.

Legacy databases were built on different premises. VSAM assumes the schema is known to the programs that use it, not stored in the file itself. DB2 on z/OS allowed referential integrity to live in COBOL code for performance reasons that made sense in 1987 and are now architectural debt that every modernization program must resolve. IMS stores its schema in DBD source files that relational ERD tools cannot read.

The ERD that these systems never formally maintained exists nonetheless, distributed across FD entries, copybooks, PROCEDURE DIVISION data access patterns, embedded SQL join clauses, and IMS DBD definitions. Reconstructing it requires reading all of these sources systematically and combining them into the unified data model that the legacy database environment implicitly contained and never explicitly recorded.

That reconstruction is the prerequisite for every correct migration target schema design. The target schema that is designed without it is designed for a legacy data model that nobody has fully seen.