Legacy AS/400 (IBM i) ERP at distributors: extracting decades of history

What is inside an AS/400 ERP after decades of use?#

An AS/400 ERP at a long-running distributor usually holds the company's commercial memory: customer and ship-to masters, item files, pricing matrices, order headers and lines, invoice history, purchase orders, receipts, AR and AP, and note files attached to orders and accounts. Some distributors run packaged software such as JD Edwards World or Infor XA; many run custom RPG systems built and extended in-house.

The data sits in Db2 for i, the database built into IBM i, organized into libraries and physical files. Logical files act as indexes and views over them. Business rules, such as what a status code means or how a contract price is derived, often live in RPG programs rather than in the data itself.

That last point shapes the whole project. Pulling the tables out is the easy part; keeping their meaning is the hard part.

Extraction options compared#

Extraction routes range from direct SQL to partner archive services, and most projects combine two. The table compares the common choices for a distributor retiring or shrinking its IBM i footprint.

Whichever route you choose, extract full physical files rather than report output. A report reflects one question asked once; a table lets an analyst ask new questions years later.

Why old codes become unreadable#

Old codes become unreadable because AS/400 systems were built to save space and to be read by the programs that wrote them. Field names are short, many values are single characters, and the meaning sits in program logic or in one person's memory.

The traps below account for most broken extracts. Each has a simple fix if someone catches it before the archive is signed off, and an expensive one if it surfaces after the system is gone.

A field documentation checklist#

A field documentation checklist turns each extracted file into something a new analyst can read without the original programmer. Build it file by file, starting with the tables that carry orders, invoices, customers and pricing.

• 1Library, file and record format name, with a plain description of what one row represents.
• 2Each field's name, text description, type, length and decimal positions, taken from DSPFFD output or the SQL catalog.
• 3Character encoding for text fields and how it was converted.
• 4Date and time format for every date-like field, with worked examples.
• 5Every code value and its meaning, with the period when that meaning applied.
• 6Keys and relationships between files, from DSPDBR, logical files and program logic.
• 7Which programs write the file and which business rules they apply.
• 8Known gaps: purges, archived periods, branches added or merged, and fields no longer maintained.
• 9Control totals for validation, such as sales by year or order counts by branch from trusted reports.

Sequence the extraction so people trust it#

An extraction earns trust when it is inventoried, frozen, validated and archived in that order. Skipping validation is a common reason finance and sales later doubt old history and stop using it.

Start with an inventory of libraries and files, and sort business data from work files and system objects. Extract a stable point in time, ideally after a period close. Validate each table against control totals from reports users already trust, then store the data, the documentation and the validation results together under a named owner.

If the IBM i system keeps running during a transition, decide whether the archive is a single snapshot or a series of extracts, and label each with its cut-off so nobody mixes them.

Illustrative: a supply distributor before its programmer retires#

Illustrative: a fictional family-owned janitorial and packaging supply distributor has run a custom RPG order and billing system on IBM i since its founder's time. Its only in-house programmer plans to retire, and the company is moving to a cloud ERP.

The IT director scopes the archive around orders, invoices, customers, items, pricing and order notes. A contractor extracts full tables over JDBC to Parquet files while the programmer documents codes, date formats and the logic behind contract pricing. Control totals by year match the old sales reports.

The new ERP receives only open items and current masters. The archive and its data dictionary become the reference for pricing disputes and demand analysis, and the owners can later assess the history for other uses without keeping the old machine.

What decades of history can be worth#

Decades of distributor history matter beyond the migration: demand patterns across economic cycles, pricing behavior through cost swings, and order notes that show how inside sales handled substitutions, shortages and rush orders. For AI developers building ordering and pricing agents, long, linked and documented histories are hard to find.

The SourceX Enterprise Data Value Framework counts scale, domain expertise and data cleanliness in a record set's favor, and recency matters too, so older years add the most when they connect to recent ones. Preparation cost reduces net value, which is why a documented archive beats a raw dump. Rights still come first: customer contracts and confidentiality terms are reviewed in the Rights step of the SourceX five-step transaction before anything is in scope, and the company approves every step.