09 · Case study
An IBM i (AS/400) databaseDDS · DB2 for i · PostgreSQL or DB2 LUW · JPA or JDBC
The system
The problem
The database on an IBM i is described in two places and neither of them is a schema in the sense a Java developer means. Physical files declare records and fields in DDS. Logical files declare key paths, select and omit rules, and joins — they are the indexes and the views, and they are where most of the access patterns live. Newer parts of the estate may have moved to SQL tables, so the same database usually has both.
The harder problem is that the rules are not in the database. Referential integrity between an order and its customer is very often enforced in the RPG that writes the rows, not by a constraint. Field names are six to ten characters, prefixed by file, and the meaning lives in a TEXT or COLHDG keyword beside the definition. A schema generated from the DDS alone is structurally correct and semantically empty — you get ORDHDR.OHCUNO as a five-digit zoned field, and nothing that says it points at a customer.
What ran
Every physical file with its record format, field names, types, lengths and decimal positions, and every TEXT and COLHDG keyword, because those carry the only English in the file. Then every logical file: its key fields in order, its select and omit rules, and for a join logical the files and fields it joins. Where the estate has SQL tables, the DB2 for i catalog is read alongside so both halves land in one model.
Packed and zoned decimals become BigDecimal with the declared precision and scale. A character field holding a date in CYYMMDD is a date and is reported as one — along with the fact that the source declares it as characters, so nobody later mistakes the conversion for something the database stated.
A keyed logical file is an index on those columns in that order. One with select or omit rules is a filtered index or a view, and the predicate is carried across verbatim. A join logical becomes a view with the join it declares. This is the step that preserves the performance characteristics people actually depend on, and it is derivable, so no model is involved.
Where the programs consistently read a customer before writing an order, that is evidence of a foreign key the schema never declared. Every such relationship is proposed with the code that implies it, and marked as inferred. None is applied silently. A rule the programs enforce ninety-eight per cent of the time is exactly the rule that will fail on the two per cent of rows already in the file, and finding those rows is part of the output.
A generated entity uses readable names derived from the TEXT and COLHDG keywords, with the original column name preserved in the mapping so the two can always be reconciled. Where there is no keyword to derive from, the original name is kept rather than a plausible one invented.
DDL for the target database, JPA entities or JDBC row mappers, and the migration scripts. Then the checks: row counts per file, the rows that violate each proposed constraint, and every field whose declared type and observed contents disagree.
What it did
A schema with the access paths intact
Tables from the physical files, indexes and views from the logical files. The queries the programs run have somewhere to run against, which is what stops a migrated system from being correct and unusably slow.
A reconciliation you can run before cutover
Per-file row counts, the violating rows for each proposed constraint, and the type mismatches. You find out that four thousand orders point at customers who no longer exist before the constraint is applied, not while the cutover window is open.
A mapping document that survives the project
Original file and field names against the new tables and columns, with the type decision and its evidence for each one. This is what the next person needs when they are reading a twenty-year-old program and a new database at the same time.
Where it ended
Which inferred constraints become real ones. The migration will show you the evidence and the violating rows; whether those rows are data to clean, an exception the business relies on, or a rule that was never actually a rule is a question about your business rather than your database.
What happens next
The reason this is a separate migration from the programs is that it has a different failure mode. A program migrated wrongly gives you a wrong answer you can find in a test. A database migrated wrongly gives you a schema that accepts every row today and rejects one in a thousand next quarter — and by then the old system is off. Doing the data first, with the violating rows listed before any constraint is enforced, is what makes the cutover a decision rather than a discovery.
CogniDev