AS/400 (IBM i)

IBM AS/400 (now IBM i) is a midrange computer system widely used in enterprise environments, with an integrated database (DB2 for i). DataMeans reads IBM i data from delimited text exports made with CPYTOIMPF.

What You Can Upload

• Delimited text exports (.csv, .txt) made on the IBM i with CPYTOIMPF, in EBCDIC or ASCII
• A ZIP archive of those exports

Save files (SAVF, made with SAVOBJ) and raw physical-file dumps are binary and can't be read, and DDS source isn't parsed. If you upload a save file, DataMeans says so and shows you how to export instead.

What You Get Out

DataMeans converts your data into multiple modern formats:

How to Export / Obtain Files

1. On IBM i, export each physical file to delimited text with CPYTOIMPF, for example CPYTOIMPF FROMFILE(MYLIB/MYFILE) TOSTMF('/tmp/myfile.csv') DTAFMT(*DLM) ADDCOLNAM(*SYS)
2. ADDCOLNAM(*SYS) writes a header row of column names. CPYTOIMPF's default writes none, and then the columns come through as COL_1, COL_2 and so on
3. Transfer the exported files to your computer (by FTP or IBM i Access)
4. Create a ZIP archive containing the files, or upload a single file directly
5. Upload the file or ZIP archive to DataMeans

Supported Features

• One table per exported file
• EBCDIC or ASCII text
• Fields delimited by commas, semicolons, tabs or pipes (whatever FLDDLM was set to), quoted or not
• A header row is used when the export has one; DataMeans checks rather than assumes, so no data row is ever taken for a header
• A preview of every file before you convert: its columns (typed from its values), the first rows, and a count of its records. The preview reads the start of each file, so the count of a large file is an estimate from its size

Known Limitations

• Save files and physical-file dumps can't be read: export to text first
• DDS source isn't parsed, so column types come from the exported values: whole numbers, decimals, ISO dates and CPYTOIMPF's timestamps (2024-01-15-13.45.00.000000); a code with leading zeros stays text
• Times in the *ISO form (13.45.00) stay text, because a *DMY date with a "." separator (15.01.24) looks the same; dates in *MDY, *DMY or *USA form stay text too, since the text does not say which number is the day
• Logical files (views) aren't converted; export the physical files they're built on

Troubleshooting

Note on the Excel formats. An .xlsx sheet holds at most 1,048,575 rows of data and 16,384 columns, and a legacy .xls sheet 65,535 rows and 256 columns. Rather than ship a file that looks complete but is silently cut short, DataMeans leaves a table out of any Excel format it does not fit and notes it in the job report. The .xlsx copy is also left out for tables of more than 8,000,000 cells (1,048,575 rows of 12 columns is 12.6 million), which take too long and too much memory to write, and the .xls copy for tables of more than 150,000 cells, which the legacy format takes far too long to write. The CSV, JSON and SQL outputs always contain every row.

Overview

AS/400 (now IBM i) is an integrated business computer system developed by IBM, featuring a built-in relational database called Db2 for i. The system uses a proprietary object-based architecture where all resources (files, programs, devices) are treated as objects. The database supports both traditional record-oriented access and modern SQL, with advanced features like journaling, commitment control, and referential integrity.

History and Background

• 1988: AS/400 announced in June as successor to System/36 and System/38; first systems shipped in August with OS/400.
• 1994: AS/400 Advanced Series models introduced; integrated database named DB2/400.
• 1995: Product line moved to 64-bit PowerPC AS (RISC) processors.
• 2000: Renamed to eServer iSeries as part of IBM's eServer branding initiative.
• 2004: eServer i5 systems introduced with POWER5 processors; OS/400 renamed i5/OS.
• 2006: Renamed to System i.
• 2008: System i and System p merged into IBM Power Systems; i5/OS renamed IBM i with release 6.1.
• 2019: IBM i 7.4 released, introducing Db2 Mirror for i.
• 2022: IBM i 7.5 released with security enhancements and a BOOLEAN SQL data type.

File Format Specifications

Database Objects:

• Physical files: Tables containing data records
• Logical files: Views and indexes over physical files
• Source physical files: Program source code storage
• Display files: User interface definitions
• Printer files: Report output specifications

File Structure:

• Record format level identifiers for compatibility
• Field-level descriptions with data types and attributes
• Access paths defined through logical files
• Member concept allows multiple data sets per file
• Integrated file system (IFS) for stream files

Storage Architecture:

• Single-level store architecture
• Db2 for i integrated database
• Object-based security model
• Journaling for audit trails and recovery
• Commitment control for transactions

Data Types and Structures

Database Features:

• Referential integrity constraints
• Check constraints and triggers
• User-defined functions and procedures
• Materialized query tables
• Row and column access control

Version Differences

Compatibility Notes:

• Record format levels ensure application compatibility
• SQL access available alongside native I/O
• Some features require minimum OS levels
• Migration tools available for hardware upgrades
• Backward compatibility maintained for business applications

Technical References

• IBM i Documentation
• Wikipedia: IBM AS/400
• Db2 for i SQL Reference
• IBM History: The AS/400
• IBM Redbooks