As I like to tell IBM i clients: the goal isn’t to get rid of the AS/400 database. It’s to discover that you’ve actually been running one of the world’s best SQL databases all along.
The Advantages of Modernizing an Old AS/400 Database to Modern SQL
If you are still rocking physical files defined in DDS on your Power Systems box, you are not alone. Many of us have inherited these trusty old workhorses that have been running since the last century. But here is the thing: keeping your database in classic DDS forever is like driving a 1995 Ford Fiesta that still runs perfectly. Sure, it works, but would you not rather upgrade to something with better fuel economy, airbags, and actual sat-nav?
Today we are going to look at why modernizing those old AS/400 databases to proper SQL tables, views, and all the shiny bits that come with them is one of the smartest moves you can make. We will also look at some concrete code examples so you can see exactly how the old world compares to the new one. Grab a coffee (or a proper cup of tea if you are civilized) and let’s dive in.
1. Better Performance and Smarter Querying
DDS-defined physical and logical files have served us well, but they come with limitations. Logical files are great for fixed access paths, yet they can be rigid. When you move to SQL tables you unlock the query optimizer on IBM i, which is seriously clever these days.
You get proper indexing options, including encoded vector indexes and sparse indexes that DDS logicals simply cannot match. Your SELECT statements can use modern WHERE clauses, JOINs, and window functions without having to create a new logical file every time someone wants a slightly different view of the data.
I once spent half a day creating three logical files just to support one report that the users changed their mind about the next week. With SQL I could have written the query in five minutes and gone for another cuppa. The optimizer loves SQL and will often find better paths than your carefully hand-crafted logicals.
Side-by-Side: Old DDS vs Modern SQL
Let us look at a simple customer master example. First the classic way.
Old School – Physical File (PF) in DDS
A R CUSTMASR
A CUSTNO 10A
A NAME 40A
A ADDRESS1 40A
A CITY 30A
A POSTCODE 10A
A BALANCE 11P 2
A STATUS 1A
A K CUSTNOOld School – Logical File (LF) in DDS (for example, by name and only active customers)
A R CUSTMASR PFILE(CUSTMAST)
A CUSTNO
A NAME
A CITY
A K NAME
A S STATUS COMP(EQ 'A')Now the modern SQL equivalents. These live in source members, or IBM-i IFS, or locally in your PC, network share or github repository (all created using IBM BOB or VSCODE for IBM i)
Modern SQL – Table
CREATE OR REPLACE TABLE CUSTMAST (
CUSTNO CHAR(10) NOT NULL,
NAME VARCHAR(40) NOT NULL,
ADDRESS1 VARCHAR(40),
CITY VARCHAR(30),
POSTCODE VARCHAR(10),
BALANCE DECIMAL(11,2) DEFAULT 0,
STATUS CHAR(1) NOT NULL DEFAULT 'A',
PRIMARY KEY (CUSTNO)
) RCDFMT CUSTMASR;Modern SQL – Index
CREATE INDEX CUSTMAST_NAME_IDX
ON CUSTMAST (NAME)
WHERE STATUS = 'A';Modern SQL – View (equivalent to the LF above, but far more flexible)
CREATE OR REPLACE VIEW ACTIVE_CUSTOMERS AS
SELECT
CUSTNO,
NAME,
CITY,
BALANCE
FROM CUSTMAST
WHERE STATUS = 'A'
ORDER BY NAME;See how much cleaner and more readable the SQL version is?
No more remembering DDS column specs and format names. The SQL version also supports proper variable length characters, defaults, and constraints right in the table definition.
2. Easier Integration with Modern Tools and Languages
Want to expose your data to the web, mobile apps, or that fancy new Node.js service your team is building? Good luck doing it cleanly with traditional record-level access. SQL opens the door to standard interfaces like ODBC, JDBC, and HTTP REST services.
On IBM i you can now call SQL stored procedures from pretty much anywhere. Your RPGLE programs can use embedded SQL (which is first-class these days) or you can write pure SQL routines. This makes it much simpler to modernize gradually. Keep the old RPG programs running while new services talk nicely to the SQL layer.
Tools like VS Code with the IBM i extensions, or even RDi, understand SQL far better than they understand old DDS. Syntax highlighting, auto-completion, and debugging all work properly. Your younger developers (who think SEU is something you do to a carpet) will thank you.
3. Improved Maintainability and Developer Productivity
Let us be honest: reading someone else’s DDS can sometimes feel like deciphering ancient runes. SQL is a well-known standard. New team members can jump in faster. You also get proper data types (DECIMAL with proper precision, DATE/TIMESTAMP handling that actually makes sense, CLOB/BLOB support without the old tricks).
Constraints, foreign keys, and check constraints at the database level mean fewer bugs sneaking into your RPG code. The database protects itself instead of relying on every program to behave nicely.
And triggers! Modern SQL triggers on IBM i are powerful and easy to write. Need to audit changes or keep a shadow table up to date? One clean trigger instead of scattering logic across multiple programs.
4. Future-Proofing and Modernization Path
IBM continues to invest heavily in the SQL side of IBM i. New features arrive in SQL first. Moving to SQL positions you to take advantage of things like:
- JSON_TABLE and JSON support for modern APIs
- Better stored procedure capabilities
- Easier migration to Db2 for LUW or cloud environments if you ever need it
- Tools like BOB (which many of you know I love) that work beautifully with SQL-based projects
It also makes it much easier to bring in technologies like Node.js, PHP, or Python running directly on IBM i or connecting remotely. Your old database stops being a bottleneck.
5. Data Quality and Security Benefits
SQL gives you proper referential integrity without the old join logical file dance. You can enforce business rules closer to the data. Combined with row and column level security (available on modern IBM i), you get much finer control over who sees what.
From a compliance and audit point of view this is gold. Auditors love seeing proper constraints and audit triggers rather than “we have programs that should do this.”
How to Get Started (Without Breaking Everything)
You do not have to do a big bang rewrite. A common approach is to create SQL tables that mirror your existing physical files, then use views or aliases to keep old programs happy while you gradually convert programs to use SQL.
Tools like the IBM i Access Client Solutions (ACS), with its DDS to SQL conversion tools, make this migration smoother than you might expect. Many shops use a hybrid approach in their database, for years with zero downtime.
If you are already using BOB for builds, adding SQL objects to your project is straightforward and gives you proper dependency management.
As my coffee cup is nearly empty…
Modernizing your old AS/400 database to SQL is not about chasing shiny objects. It is about making your life easier, your systems faster, and your team more productive. The old DDS files will still run (IBM i is famously backwards compatible), but the future belongs to SQL.
If you are running a Power System in 2026 and still living entirely in the DDS world, now is a great time to start the journey. Your future self (and the person who inherits your code) will buy you a pint.
Until next time, keep those IBM i systems humming.
