DB2 for i Migration

Part 4 moved the logic off the platform. But logic without its data is half a system, and on the AS/400 the data layer carries some of the most consequential — and most easily lost — business rules in the whole estate. This part is about migrating DB2 for i, the database built into IBM i, off the platform without quietly losing the integrity it enforces. The principle that runs through it: a database migration is not a data-copy job, it is a rule-preservation job that happens to move some rows along the way.

What makes DB2 for i different

The name causes confusion, so start there. DB2 for i is not the same product as DB2 for z/OS (the mainframe database) or DB2 LUW (the standalone Linux/Unix/Windows product). It is the relational database built into the IBM i operating system — part of the platform, not something installed on top of it. That integration is the AS/400’s signature strength, and it shapes everything about migrating off it.

Three characteristics matter most for migration:

• The schema is often defined in DDS, not SQL. Older AS/400 applications describe their files and fields in Data Description Specifications — a legacy, positional way of defining physical and logical files that predates the SQL DDL most databases use today. A modern target speaks SQL; the DDS has to be understood and re-expressed, and the translation is not always one-to-one.
• Access is often record-level, not set-based. RPG frequently reads and writes DB2 for i through native record-level I/O — reading one record at a time by key, chaining through files — rather than through SQL queries against sets of rows. The application’s data-access patterns are built around this, and the migration to a SQL-querying application changes how the data is reached, not just where it lives.
• Integrity is enforced in two places. Some rules live in the database — keys, referential constraints, logical-file relationships. Others live in the RPG that has always been the gatekeeper for the data. The full set of rules protecting the data is split across both, and a migration has to account for the whole set, not just the part the database declares.

This is the AS/400 analog of the schema-recovery problem the mainframe series describes for VSAM and DB2 to PostgreSQL: the real schema is partly implicit, and recovering it is the work.

Why a naive copy is dangerous

The tempting view is that data migration is the easy part — surely you just extract the rows and load them into the new database? Two problems make that view dangerous on the AS/400.

Dropped enforced rules. If integrity was enforced partly by the RPG and partly by logical files or constraints that don’t translate cleanly, a row-by-row copy moves the data but loses the rules. The new system then accepts inputs the old one would have rejected — a malformed code, an out-of-range value, a relationship that should never exist — and integrity erodes slowly and invisibly. By the time anyone notices, the corrupt data is months deep and entangled with everything downstream. This is the worst kind of migration failure: it passes every smoke test and fails silently in production.

Lost meaning in the schema. DDS-defined files accumulate meaning over decades — field names that encode conventions, packed-decimal formats with specific precision the financial logic depends on, logical files that represent business relationships. Reproduce the schema mechanically without understanding it and you can lose the precision (rounding and truncation bugs), misrepresent the relationships, or carry forward dead structure that confuses the new system. The schema is documentation written in a language you have to learn to read.

The migration that works

A sound DB2 for i migration is a deliberate, recovered, and proven process — not an export/import.

1. Recover the true schema and constraints. Read the DDS and SQL definitions, the logical files, and — critically — the RPG that enforces rules the database doesn’t, and assemble the complete picture of what protects the data. Capture it as living specs: the entities, the relationships, the constraints, the formats and precisions, the validation rules wherever they currently live. This is the same business-rule recovery that underpins the RPG migration, applied to the data layer.

2. Design the target deliberately. Choose the target store — PostgreSQL, SQL Server, or a cloud-managed relational database — to fit the target application stack from Part 4. Then decide, rule by rule, where each constraint should live in the new architecture: in the database as a constraint, in the application’s domain layer, or both. This is an architectural decision, not a mechanical one — moving a rule from RPG into a database constraint can be an improvement, but only if it is done knowingly. Preserve the precision and formats the financial logic depends on exactly.

3. Migrate and reconcile. Move the data, then prove it. Reconciliation compares the migrated data against the source — record counts, key values, financial totals, the awkward edge cases — until they match exactly. On ledger-grade or compliance-sensitive data the bar is total: not “close”, but reconciled to the cent and the record. Discrepancies are findings — either a migration bug or a source rule you hadn’t captured — and both are valuable.

4. Run in parallel and prove behavior. Because the application’s data access is changing from record-level I/O to SQL, the migrated data layer is exercised under parity validation alongside the migrated logic — the rebuilt slice reads and writes the new store while its behavior is compared against the AS/400’s, so a subtle difference in how a query returns or orders data is caught before cutover, not after.

The reconciliation burden is real

One honest warning for planning, the same one the mainframe cost article raises: on data with an absolute integrity bar, the reconciliation effort can rival or exceed the build. It is the line item teams most consistently underestimate, because copying rows looks trivial and proving them correct does not. Budgeting for reconciliation up front — rather than discovering it after a “finished” migration won’t balance — is one of the differences between a predictable program and a runaway one. The size of the burden depends on the data: ledger-grade, history-rich, weakly-constrained data is expensive to reconcile; smaller, well-understood, tightly-constrained data is cheaper. This is one of the things a discovery phase sizes before you commit.

What doesn’t need to move

Not all of the data has to move at once, and some may not need to move at all. If a workload stays on IBM i, its DB2 for i data stays with it — the integrated database is a strength, not a liability, for code that is staying put. The migration applies to the data belonging to workloads moving off the platform, and even there it is sliced: each capability’s data moves with its logic, proven together, while everything else keeps running on the AS/400. Migrating the whole database in one cutover, divorced from the logic that uses it, is the data-layer version of the big-bang trap.

Where this leads

With logic and data accounted for, one layer of the AS/400 remains — the one users actually see and touch every day. The 5250 green screen is the face of the system, and modernizing it is both the most visible win and the most commonly mis-sold piece of an AS/400 program. Part 6, 5250 Green-Screen to Web UI Modernization, covers modernizing the interface — what refacing genuinely delivers, where it stops, and how to give users a modern experience without mistaking a new coat of paint for modernizing the system underneath it.