Not a Single Penny: How Reckonsys Migrates DB2 for i to PostgreSQL

When teams talk about modernizing an AS/400 system, the conversation gravitates to the exciting parts: the new API, the React frontend, retiring the green screens. But after running these engagements, we will say plainly — the data migration is where the project is actually won or lost.

The reason is simple. You can re-run a rewrite of a program if the logic is wrong. You cannot un-corrupt a customer's account balance after go-live. Financial ledgers, inventory positions, insurance policies, and order histories have been accumulating on DB2 for i for decades, and the business trusts them absolutely. A migration that introduces a fraction-of-a-cent rounding error across ten million rows does not just fail a test — it destroys confidence in the entire modernization programme.

So our first principle is uncompromising: every row, every field, every decimal place must reconcile between the old system and the new one, provably, before anyone cuts over. This is how we get there.

What DB2 for i Actually Stores — and What a Naïve Migration Misses

DB2 for i is a mature relational database — which is good news. You are not reverse-engineering a proprietary flat-file format. But it carries decades of conventions that a naive SELECT * INTO PostgreSQL will silently mangle. Four of them matter most in every migration we run.

The Six-Stage Migration Methodology

We treat data migration as its own disciplined programme with six stages, each with an explicit exit criterion. You do not proceed to the next stage until the current one reconciles. This structure is what makes the cutover boring by design — which is exactly what you want at 2 AM on a Saturday.

Stage 1 — Profile Before You Touch Anything

Before designing a target schema, we profile the real data. The DDS definition tells you what the field was designed to contain. Twenty-five years of production tells you what is actually in it. The gap between those two is where migrations fail.

We query system catalogs to inventory structure, then profile the actual contents for the surprises that break loads:

-- Every column, type, length, and scale in a schema

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,

LENGTH, NUMERIC_SCALE, IS_NULLABLE, COLUMN_TEXT

FROM QSYS2.SYSCOLUMNS

WHERE TABLE_SCHEMA = 'APPLIB'

ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- Profile a suspect 'date' column: what values really live there?

SELECT MIN(LSTORDDT) AS min_val, MAX(LSTORDDT) AS max_val,

SUM(CASE WHEN LSTORDDT = 0 THEN 1 ELSE 0 END) AS zero_dates,

COUNT(*) AS total

FROM APPLIB.CUSTMAST;

The output is a data profile report: real value ranges, null and sentinel counts, orphaned foreign keys, duplicate 'unique' keys, and encoding anomalies. This report drives every transformation rule that follows — and it routinely surfaces data issues the client did not know they had.

✓ Exit Criterion: Every column has a documented source type, real value range, and target mapping before Stage 2 begins.

Stage 2 — Translate the Schema Deliberately

DDS-to-DDL is not a mechanical one-liner. It is a set of decisions, each of which is recorded in a column-by-column mapping document. Here is the type-mapping policy we apply as a baseline:

A representative example: a CUSTMAST physical file with customer ID, name, credit limit, balance, status, and a packed-numeric date field maps to a PostgreSQL table with INTEGER, VARCHAR(30), NUMERIC(11,2) for both financial columns, CHAR(1) with a CHECK constraint enforcing the profiled domain, and a nullable DATE column where the packed zero becomes NULL.

We widen NUMERIC(9,2) to NUMERIC(11,2) deliberately — DB2's 9P2 allows seven integer digits plus two fractional, so the widened target preserves headroom and makes the decision explicit in the DDL comment. Every choice like this is recorded in the mapping spec, not left as an accident.

✓ Exit Criterion: A reviewed DDL script and a signed-off column-by-column mapping document before Stage 3 begins.

Stage 3 — Extract Exactly, Transform Correctly

We connect to DB2 for i and extract in a way that preserves precision end to end. The two most important transformation functions handle the two most dangerous conventions — packed-numeric dates and fixed-width character padding:

def to_date_yyyymmdd(n):

"""Convert packed-numeric YYYYMMDD to a real date.

Sentinels (0, 99999999) become NULL. Bad values are

logged and raised — never silently dropped."""

if n is None:

return None

i = int(n)

if i in (0, 99999999):

return None

s = f"{i:08d}"

return date(int(s[0:4]), int(s[4:6]), int(s[6:8]))

def clean_char(value):

"""Trim fixed-width padding; normalise empty to None."""

if value is None:

return None

trimmed = value.rstrip()

return trimmed or None

The recurring theme across every extraction function: numeric values pass through Python's Decimal type — never a float. Dates and encodings are converted explicitly. Anything unparseable is raised and logged, never quietly dropped. Silent data loss is worse than a failed load, because it is invisible until reconciliation — or worse, until a customer complains.

Rows are extracted in batches of 5,000 using a generator, so memory stays flat regardless of table size. A 200-million-row file loads with the same memory profile as a 200,000-row file.

✓ Exit Criterion: Every row extracts and transforms without unlogged exceptions. All rejects are captured in an exceptions table for review.

Stage 4 — Load Fast and Safely

For anything beyond a few thousand rows, row-by-row INSERT is orders of magnitude too slow. We stage the data and use PostgreSQL's COPY command — which loads data directly from a buffer rather than parsing individual SQL statements — and defer constraint validation until after the bulk load completes.

The staging pattern is non-negotiable in our process:

• Load into a temporary staging table first — an exact structural clone of the production table, without indexes or constraints active.
• Validate against staging before any data touches production — run all checks against the staging table.
• Promote in a single transaction — INSERT INTO production SELECT FROM staging, committed atomically.

This makes the load idempotent and re-runnable. You can drop staging, fix a transformation bug, and re-load without disturbing anything downstream. On a migration with twenty tables and six iterations of refinement, that property is not a nicety — it is the thing that keeps the project on schedule.

✓ Exit Criterion: All rows land in staging. The load is repeatable and produces identical results across runs.

Stage 5 — Reconcile, and Prove It

This is the stage that earns the trust. We never declare a table migrated because the load succeeded. We prove equivalence with three independent checks, applied in order, each catching a class of error the previous one cannot.

The checksum approach works by computing a deterministic hash of the business columns on both the DB2 source and the PostgreSQL target, then comparing key by key:

-- PostgreSQL: hash each row's business content

SELECT customer_id,

md5(concat_ws('|', name, credit_limit, balance,

status, to_char(last_order_date, 'YYYYMMDD')))

AS row_hash

FROM customer_master

ORDER BY customer_id;

-- Python: compare source and target hash dictionaries

def reconcile(src_hashes, tgt_hashes):

diffs = []

for key in src_hashes.keys() | tgt_hashes.keys():

s, t = src_hashes.get(key), tgt_hashes.get(key)

if s != t:

diffs.append({"key": key, "match": False})

return diffs   # must be empty before table is signed off

The reconciliation report — row counts equal, control totals equal to the cent, zero checksum mismatches — is a formal deliverable. A table is not done until that report is clean and the client's data owner has signed it off. Not the project manager. The data owner.

✓ Exit Criterion: Counts match, control totals match exactly, and the checksum diff set is empty. Client data owner sign-off obtained.

Stage 6 — Keep in Sync, Then Cut Over With a Rollback Path

A production system cannot freeze for the days a full migration takes. After the initial bulk load and reconciliation, we run change data capture (CDC) to keep PostgreSQL in step with the still-live AS/400 until the cutover window. DB2 for i's journals are the reliable source of changes — we read committed journal entries for the migrated files and apply the corresponding inserts, updates, and deletes to PostgreSQL on a short interval.

def apply_changes(since_seq, pg):

changes = read_journal_entries('APPLIB/QSQJRN', since_seq)

cur = pg.cursor()

for c in changes:

if c.operation in ('PT', 'UP'):   # put / update

cur.execute(UPSERT_SQL, transform_record(c.after_image))

elif c.operation == 'DL':          # delete

cur.execute('DELETE FROM customer_master

WHERE customer_id = %s', (int(c.key),))

pg.commit()

return changes[-1].sequence if changes else since_seq

Cutover then becomes a brief, well-rehearsed window: quiesce writes on the AS/400, apply the final journal delta, run reconciliation one last time, flip the application to PostgreSQL, and keep the AS/400 online in read-only mode as the rollback path.

Because every prior stage reconciled, the cutover is boring by design. Which is exactly what you want at 2 AM on a Saturday.

✓ Exit Criterion: Final delta applied, reconciliation clean, application pointed at PostgreSQL, AS/400 kept online as read-only rollback path until new platform has carried real production traffic.

How Claude Code Accelerates the Data Work

The six-stage methodology above is the same disciplined approach careful teams have used for years. What has changed is how fast we can execute it. Our data engineers use Claude Code to compress the mechanical parts of each stage:

• Schema translation: Claude Code reads the DDS source and generates the first-draft PostgreSQL DDL and the column mapping document — a task that previously took a senior engineer a full day per complex table.
• Profiling queries: Given the mapping specification, it writes the profiling queries for each column type — the sentinel checks, the value range queries, the orphan key counts.
• Transformation functions: It generates the Python transform functions from the mapping spec — date converters, character cleaners, decimal handlers — tailored to the column definitions identified in profiling.
• Reconciliation harness: It generates the control-total and checksum scripts for each table, with the correct column set and join key, ready to run against both systems.

Our engineers review and own every line. They apply the architectural judgment and edge-case handling that the profiling reveals — the sentinel values nobody documented, the referential integrity that exists in application code but not in the database, the column that looks like a date but is actually a period-end flag.

The pattern is AI-accelerated, human-governed. The tedious 80% is automated. Correctness stays a human responsibility.

What We Have Learned the Hard Way

Every principle below comes from a real project scar. Not a theoretical risk — a real event on a real client engagement.

Working with Reckonsys on AS/400 Data Migration

Data migration is a core part of the AS/400 modernization engagements we run at Reckonsys — not a phase we hand off or treat as a handoff from the application rewrite team. It is a parallel, disciplined programme with its own engineering practice, its own tooling, and its own sign-off process.

We start every migration engagement with a discovery-and-profiling assessment. That assessment produces three deliverables before a single row moves:

• The mapping specification — every table, every column, every transformation rule, every sentinel value, every referential integrity assumption documented and reviewed.
• The risk register — every data quality issue, encoding anomaly, and logical file dependency identified and assigned a remediation approach.
• The staged migration plan — a costed, sequenced plan with explicit exit criteria at each stage, so you know exactly what you are committing to before the programme begins.

From there we can own delivery end to end, extend your team with data engineers who know both DB2 for i and PostgreSQL deeply, or advise your team through the reconciliation and cutover process.

The AS/400's data is the most valuable thing your business owns. Moving it should feel careful, provable, and — by the time you cut over — completely undramatic.