What are the advantages of modernizing an old AS400 database to modern SQL?
As someone who's spent far too many hours staring at DDS source members from the 1980s, I'd say the biggest advantage of modernizing an IBM i database to SQL is not performance. It's making your data easier to understand, integrate, and extend.
An old AS/400 database typically consists of:
- DDS-defined physical files (PFs)
- Logical files (LFs)
- Limited relational constraints
- Application-enforced business rules
- Native record-level access in RPG
A modern SQL database on IBM i typically uses:
- SQL tables
- SQL indexes
- Primary and foreign keys
- Check constraints
- Views
- Triggers
- Stored procedures
- Embedded SQL in RPG
Better data integrity
With DDS, relationships are often "known by the programmer".
must exist in
CUSTOMERS.CUSTNO
But nothing in the database enforces this.
With SQL:
ADD CONSTRAINT FK_ORDERS_CUSTOMERS
FOREIGN KEY (CUSTNO)
REFERENCES CUSTOMERS (CUSTNO);
The database protects itself from bad data.
Easier reporting and analytics
Modern BI tools expect SQL.
Tools such as:
- Power BI
- Tableau
- Excel Power Query
- Cognos
- Snowflake integrations
- native applications written in Node/JS or other modern languages
These work much more naturally against SQL tables and views.
Instead of creating special logical files, you can create SQL views:
SELECT c.Name,
o.OrderNo,
o.OrderDate
FROM Customers c
JOIN Orders o
ON c.CustNo = o.CustNo;
Simpler application development
Modern RPG developers use embedded SQL extensively:
SELECT Name
INTO :CustomerName
FROM Customers
WHERE CustNo = :CustNo;
Compared with native I/O:
SQL is generally easier for new developers to learn and is more portable across platforms.
Better integration with web applications
If you're building WebSmart, PHP, .NET, Java, Python, Node.js, or REST APIs, SQL is the common language.
A modern SQL schema allows developers to connect using standard database drivers rather than learning decades of IBM i file design conventions.
Richer database features
DDS simply can't do many things SQL can.
Examples:
- Generated columns
- Common table expressions (CTEs)
- Window functions
- Constraints
- Triggers
- Stored procedures
- User-defined functions
- Temporal tables
These features allow more business logic to live in the database rather than scattered through hundreds of RPG programs.
Improved documentation
A DDS file might look like this:
CNAME 30A
STAT 1A
What does STAT mean?
Nobody knows. Barry 'The AS400 Programmer' retired in 2015.
With SQL, the intent is much clearer:
CustomerNumber DECIMAL(7,0),
CustomerName VARCHAR(30),
Status CHAR(1)
);
Future-proofing the application
Finding developers who understand:
- SQL
- REST APIs
- modern RPG with SQL
is much easier than finding developers who are experts in:
- DDS
- OPNQRYF
- multi-format logical files
- complex native I/O architectures
Modernization makes the system more attractive to future developers.
Performance is often equal or better
Many IBM i shops worry that SQL will be slower.
In reality:
- The Db2 for i optimizer is extremely mature.
- SQL indexes often outperform old logical file strategies.
- IBM continues to invest heavily in SQL rather than DDS.
A well-designed SQL database will typically match or exceed the performance of traditional designs.
What I recommend
Don't think of it as a database migration. Think of it as a database evolution.
A sensible modernization path is:
- Keep the data where it is on IBM i.
- Convert DDS physical files to SQL tables.
- Replace logical files with SQL indexes and views.
- Add primary keys and foreign keys.
- Move RPG to embedded SQL.
- Expose data through SQL views and REST APIs.
The beauty of IBM i is that you can do this incrementally. You don't need a risky "big bang" migration. I've seen plenty of shops modernize a 30-year-old system one file and one program at a time while keeping the business running every day.