Getting your data off an AS/400® green-screen system

Here's the thing about the AS/400® (later called iSeries, now IBM i): underneath the green screens sits a real relational database — Db2 — built into the operating system. Every "file" your application uses is a queryable table. The menus your staff use every day may never show it, but the data is reachable, and the tools to reach it are free.

Any conversion needs three things: access to the raw data, a way to export to CSV or Excel, and the table layouts. The AS/400 has real answers for all three — and a couple of famous traps.

1 · Where the raw data lives

Tables are "physical files" stored in libraries; the application's screens are just programs reading them. Two structural things to know:

• A file can contain multiple members — sub-partitions, often one per year or per company. Naive exports grab only the first member. If your history seems short, this is usually why.
• Layouts are stored with the file ("externally described"), which is exactly why the export tools below work without programming.

2 · Four real ways to export

IBM ACS Data Transfer (the point-and-click way)

IBM i Access Client Solutions (ACS) is IBM's free client. Its Data Transfer tool downloads any table straight to .csv or .xlsx from a Windows PC — pick the file, pick the format, go. Its Run SQL Scripts tool runs any SELECT and saves the results the same way. If your box has a network connection and you have a user profile with read authority, this is the shortest path.

CPYTOIMPF (the native command)

From a command line, CPYTOIMPF copies a file to a delimited CSV, handling the EBCDIC-to-ASCII translation and unpacking packed numbers correctly along the way. This is the command an operator or your support person would use to dump entire files server-side.

ODBC (the familiar-tools way)

The ACS package includes an ODBC driver, so Excel, Access, and Power Query can connect to the box and pull tables directly — useful when the person doing the extract lives in Excel.

Query/400 (the green-screen way)

Nearly every one of these machines has Query/400 (WRKQRY). An operator who knows the menus can select, join, and filter data into a new file with zero programming — then export that file with any method above. This matters because on locked-down systems, users only ever saw menus; Query/400 is the escape hatch that was there all along.

3 · Getting the table layouts

One command: DSPFFD (Display File Field Description) lists every field in a file — name, type, length, and decimal positions — and can write it all to a file you export like any other. Run it across the application's library and you've extracted the entire schema. DSPFD adds file-level info like key fields and the member list.

4 · The traps that ruin naive extracts

These are why "just FTP the files to a PC" produces garbage, and why conversions from these boxes go wrong in the hands of people who haven't done one:

• EBCDIC: character data is stored in a different encoding than PCs use. Raw copies are unreadable; proper tools translate automatically.
• Packed decimals: numbers stored two digits per byte. A text-mode FTP destroys them — IBM documents this outright. CPYTOIMPF, ACS, and ODBC unpack them correctly.
• Money ×100: decimal position lives in the field definition, not the data — $12.99 is stored as 1299. Without the layout from DSPFFD, every price looks a hundred times too big.
• Six- and seven-digit dates: legacy RPG applications store dates as YYMMDD or CYYMMDD numbers (the C is a century digit), plus the occasional Julian format — they need decoding, not just reformatting.

None of these are exotic to people who work with this platform — they're Tuesday. But every one of them has ruined a conversion done by someone who'd never seen them.

A note on Business Basic systems

If your green screen is a Business Basic system (BBx, PRO/5, ProvideX) rather than an AS/400, the story is similar but the tooling differs: data lives in keyed files whose layouts live in the application or an optional data dictionary. ODBC drivers exist (BASIS and PxPlus both ship them) if a data dictionary exists; without one, the reliable route is a small export program in the Basic itself — an afternoon's work per file for anyone who knows the language — or capturing the app's own reports to a file. Dates are often stored as Julian day numbers. Same three requirements, same verdict: the data can move.

Then verify it

Whatever comes off the box, prove the conversion decoded it right: record counts, then tie out open order value, A/R, A/P, and open PO balances against the old system's reports. The full verification routine is here — the money tie-outs are precisely what catch a mis-decoded packed field.

AS/400® is a registered trademark of its owner. All product names are used for identification only and imply no affiliation or endorsement.