Skip to content
PYD ENTERPRISES
Start free trial

Migration guide

IMS DB to relational: extracting tables, keys and access paths from DBDs and DL/I calls

IMS stores a hierarchy and programs navigate it with GU, GN and GNP calls. This guide shows how to derive a relational schema that keeps the keys, ordering and access rules the programs depend on.

Request a codebase scanExplore demo
IMS DBRelational schema
DIFFICULTY
Very high
OUTPUT
SQL DDL
SYNTAX TRAPS
6 covered
WORKFLOW
6 steps

By Pranav Kulkarni, Head of Language Engineering · Updated 15 Oct 2026

Technical syntax challenges

Where IMS DB migrations go wrong.

01

Segments become tables, parents become foreign keys

Each SEGM in the DBD becomes a table, with the full concatenated key of its parent path carried down. A CLAIM under POLICY needs polno as a foreign key; a PAYMENT under CLAIM needs both polno and clmno.

02

Sequence fields are unique only under one parent

FIELD NAME=(CLMNO,SEQ,U) makes claim numbers unique among twins under the same policy, not across the database. The relational primary key is therefore (polno, clmno). Declaring clmno alone as the key is a common and costly mistake.

03

Non-unique and missing sequence fields

SEQ,M allows duplicate values, and segments with no sequence field are ordered by the insert rule (FIRST, LAST or HERE). Programs that read twins with GNP depend on that order, so the target needs an explicit ordering column, not an assumption about ORDER BY.

04

Status codes drive the logic

Loops end on PCB status GE (segment not found) or GB (end of database), and II on insert means a duplicate. These codes are the control flow; each becomes an explicit query result condition in the specification.

05

PSBs define the access contract

A program's PCB lists which segments it is sensitive to (SENSEG) and with what processing options (PROCOPT=G, A). That is an authorization and data-access contract the new service layer should preserve.

06

Logical relationships and secondary indexes

LCHILD pointers and secondary indexes defined with XDFLD create access paths outside the physical hierarchy. They usually map to additional foreign keys and indexes, and programs using them through a separate PCB must be identified.

Extraction workflow

6 steps from source to signed-off spec.

The sequence a PYD ENTERPRISES Discovery Scan runs for this stack. Every output links back to source file and line range.

  1. 1

    Collect DBDs, PSBs and programs

    Export DBD and PSB source macros, the COBOL or PL/I programs that issue DL/I calls and the copybooks for segment I/O areas.

  2. 2

    Build the hierarchy and key paths

    PYD ENTERPRISES parses each DBD into a segment tree with sequence fields, uniqueness, field offsets and logical relationships, then computes the concatenated key for every segment.

  3. 3

    Map segment layouts to columns

    Segment I/O area copybooks are matched to DBD field definitions so every byte has a name, type and nullability candidate.

  4. 4

    Trace DL/I call patterns

    Every CBLTDLI or AIBTDLI call is resolved with its function code, PCB and SSAs, including command codes, to show which paths and orderings each program uses.

  5. 5

    Derive tables, keys and ordering columns

    Composite primary keys, foreign keys, ordering columns for non-unique or unsequenced twins and secondary indexes are proposed with the evidence for each.

  6. 6

    Export DDL, ER diagram and access specs

    DDL, a Mermaid ER diagram and per-program access specifications (which queries replace which call sequences) are exported for review.

Legacy in, specification out

The source, and what PYD ENTERPRISES extracts from it.

Left: the IMS DB construct as it sits in production. Right: the reviewed artifact your Relational schema engineers build against. PYD ENTERPRISES produces the spec and the tests; your team writes the service.

LEGACY SOURCE

CLMRSV01.cbl + CLAIMDBDCOBOL
1      * DBD NAME=CLAIMDBD,ACCESS=HIDAM
2      *   SEGM NAME=POLICY,PARENT=0,BYTES=120
3      *   FIELD NAME=(POLNO,SEQ,U),BYTES=10,START=1
4      *   SEGM NAME=CLAIM,PARENT=POLICY,BYTES=200
5      *   FIELD NAME=(CLMNO,SEQ,U),BYTES=8,START=1
6      *   SEGM NAME=PAYMENT,PARENT=CLAIM,BYTES=60
7      *   FIELD NAME=(PAYDT,SEQ,M),BYTES=8,START=1
8       01  SSA-POLICY.
9           05  FILLER     PIC X(19) VALUE 'POLICY  (POLNO   = '.
10           05  SSA-POLNO  PIC X(10).
11           05  FILLER     PIC X(01) VALUE ')'.
12       01  SSA-CLAIM-UNQ  PIC X(09) VALUE 'CLAIM    '.
13      *
14           CALL 'CBLTDLI' USING DLI-GU, CLAIM-PCB,
15                                POLICY-IO, SSA-POLICY.
16           PERFORM UNTIL PCB-STATUS NOT = SPACES
17              CALL 'CBLTDLI' USING DLI-GNP, CLAIM-PCB,
18                                   CLAIM-IO, SSA-CLAIM-UNQ
19              IF PCB-STATUS = SPACES
20                 ADD CLM-RESERVE TO WS-TOTAL-RESERVE
21              END-IF
22           END-PERFORM.

EXTRACTED ARTIFACT

claimdb.ddl.sqlSQL
1CREATE TABLE policy (
2  polno   CHAR(10) PRIMARY KEY
3);
4-- CLMNO is SEQ,U among twins only: key includes parent
5CREATE TABLE claim (
6  polno    CHAR(10) NOT NULL REFERENCES policy (polno),
7  clmno    CHAR(8)  NOT NULL,
8  reserve  NUMERIC(11,2) NOT NULL,
9  PRIMARY KEY (polno, clmno)
10);
11-- PAYDT is SEQ,M: duplicates allowed, twin order kept in pay_seq
12CREATE TABLE payment (
13  polno    CHAR(10) NOT NULL,
14  clmno    CHAR(8)  NOT NULL,
15  pay_seq  INTEGER  NOT NULL,
16  paydt    DATE     NOT NULL,
17  PRIMARY KEY (polno, clmno, pay_seq),
18  FOREIGN KEY (polno, clmno) REFERENCES claim (polno, clmno)
19);
20-- CLMRSV01: GU POLICY + GNP CLAIM loop until GE
21-- = SELECT SUM(reserve) FROM claim WHERE polno = :polno

Architectural best practices

Build the Relational schema side to the spec, not to the old shape.

  • Carry the full parent key path into every child table; add surrogate keys only alongside it, never instead of it.
  • Add explicit ordering columns wherever twin order is set by a non-unique sequence field or an insert rule.
  • Translate each DL/I call sequence into the set-based query it implies, and keep the original sequence in the spec for review.
  • Preserve PSB sensitivity as service-level access rules: a program that could only read CLAIM should not gain write access through the new API.
  • Validate the schema with fixtures built from real hierarchy shapes: policies with no claims, claims with duplicate payment dates.

In depth

Field notes from real scans.

Why IMS needs the programs, not just the DBD

A DBD tells you the physical hierarchy. It does not tell you that a program treats the first PAYMENT twin as the original payment and the rest as adjustments, or that another program inserts with the HERE rule to keep a specific order. Those meanings live in call sequences. PYD ENTERPRISES reads the DBD and every program that uses it, so the relational design reflects how the data is actually used.

The result is DDL, an ER diagram and access specifications linked to DBD lines and program lines. Data engineers run the unload and load, and application engineers rebuild access as SQL or services against the reviewed spec. PYD ENTERPRISES does not migrate the data or rewrite the programs.

Reading the example

CLMRSV01 totals reserves for one policy. The GU positions on the POLICY root by key, and the GNP loop reads every CLAIM twin under it until the status is no longer blank, which in practice is GE. The relational equivalent is a single aggregate query. The spec also records what the loop does not do: it ignores the PAYMENT segments beneath each claim, and it would stop early on any other non-blank status, such as an I/O error, without signaling failure. That last point is a review item, because the replacement should surface errors the legacy program silently treats as end of data.

FAQ

IMS DB to Relational schema questions

Does PYD ENTERPRISES support HDAM, HIDAM and HALDB databases?

Yes. Access method affects physical storage and performance, not the logical hierarchy, so all are modeled from their DBDs. HALDB partition definitions are recorded as metadata for the data engineering team.

Are logical databases and secondary indexes handled?

Logical relationships (LCHILD, logical parents) and secondary indexes (XDFLD) are parsed and turned into candidate foreign keys and indexes, and programs using them through a logical PCB are identified.

What about Fast Path DEDBs?

DEDB definitions are parsed like other DBDs. Fast Path specific behavior, such as sequential dependent segments, is flagged for explicit design decisions in the relational model.

Get the spec for your IMS DB estate.

Request a codebase scan and review extracted rules, contracts and fixtures for your own IMS DB estate, inside your VPC.

Request a codebase scanStart 14-day trial