Why Bother? Performance and Integrity Wins from Moving Off AS/400 Files
Classic AS/400 Physical Files and Logical Files were designed in a different era. They still work, but they limit what the database engine can do for you.
When you define a table with SQL instead of DDS, several important things change under the covers.
- Validation timing With a DDS Physical File the engine checks data types and values when the record is read. With an SQL table the checks happen when the record is written. Most business programs perform far more reads than writes. Removing the validation work from every read produces a measurable performance gain on native I/O as well as SQL. Sites that have measured this after a clean conversion commonly see faster chain, setll/reade and sequential processing even before any program changes.
- Optimizer knowledge SQL tables collect and maintain better statistics. The SQL Query Engine (SQE) can make smarter decisions about which indexes to use, when to use parallel processing, and how to join tables. Old DDS Logical Files gave the optimizer less information. Modern indexes (including Encoded Vector Indexes) become practical and the engine uses them more effectively.
- Integrity enforcement DDS files mostly left integrity to the programs. SQL tables let you declare primary keys, unique constraints, foreign keys and check constraints. The database itself rejects bad data instead of relying on every RPG program to get the validation right. This reduces data quality problems that have been accumulating for years in many AS/400 systems.
- Future features New Db2 for i capabilities arrive almost exclusively for SQL-defined objects. Identity columns, row-change timestamps, temporal tables, better null handling, longer descriptive names and advanced indexing options all prefer or require SQL tables.
The modernization path is deliberately gentle. You can convert a Physical File to an SQL table while keeping the external record format identical. Existing programs keep working. Later you improve the internal definitions when you are ready. That combination of safety plus progressive improvement is why this approach works well on IBM i.
