Overview: Modernizing an IBM i DB2 Database from Physical Files and Logicals to SQL Tables, Indexes and Views

We are taking the classic AS/400 style database (DDS Physical Files and Logical Files) and turning it into a modern IBM i SQL database (Tables, Indexes and Views).

This is not a rewrite of your applications. It is a careful under-the-covers upgrade. Done properly, the majority of your existing RPG, CL and embedded SQL programs continue to run without changes. You gain better performance on reads, stronger data integrity, proper constraints, longer column names, and a database that the modern SQL Query Engine actually likes.

The four lessons below walk through the practical path most shops follow:

Everything stays focused on real IBM i techniques you can use today.

Benefits of AS400 database modernization?

The biggest benefit is that you get a modern database without rewriting your applications. Most IBM i shops can convert DDS Physical Files and Logical Files into SQL Tables, Indexes and Views while keeping existing RPG, CL and SQLRPGLE programs running exactly as they do today.

Here are the key advantages:

Better Performance

The SQL Query Engine (SQE) is where IBM invests its database optimization efforts. SQL tables, indexes and constraints allow the optimizer to make better decisions than traditional DDS structures.

Benefits include:

• Faster SQL queries
• Better access path selection
• Improved reporting performance
• More efficient joins and aggregations
• Better support for modern analytics tools

Stronger Data Integrity

DDS offers only basic validation. SQL gives you true database rules.

Examples include:

• PRIMARY KEY constraints
• FOREIGN KEY relationships
• UNIQUE constraints
• CHECK constraints
• NOT NULL columns

Instead of relying on RPG programs to validate data, the database protects itself.

Longer and More Meaningful Names

DDS limits field names to 10 characters.

IBM-i SQL allows names like:

instead of the old AS400 style:

This makes development and maintenance significantly easier.

Modern Data Types

Many older systems use:

• Numeric dates (YYMMDD)
• Packed decimal timestamps
• Character fields holding dates and times

SQL provides proper types:

Benefits include:

• Automatic date calculations
• Easier comparisons
• Simpler reporting
• Less conversion code

Replace Logical Files with Better Objects

Logical Files served IBM i very well for decades, but SQL separates their responsibilities:

• Indexes provide performance.
• Views provide column and row presentation.

This creates cleaner database design and gives the optimizer more flexibility.

Better Integration with Modern Tools

Most modern software understands SQL databases, not DDS.

Examples:

• Power BI
• Tableau
• Python
• .NET
• Java
• Node.js
• REST APIs

SQL structures make IBM i look like a modern enterprise database platform.

Easier Database Evolution

Adding columns, constraints, indexes and new views is generally easier and more manageable with SQL DDL than DDS source members.

Database changes become:

instead of recompiling DDS and managing level checks.

Improved Documentation

SQL objects are largely self-documenting.

When a developer sees:

they immediately understand the business rule.

With DDS, much of that knowledge often exists only in RPG programs or developer notes.

Future-Proofing

IBM's strategic direction for Db2 for i is SQL. New database features almost always arrive in SQL first and sometimes only in SQL.

Converting to SQL positions your applications to take advantage of:

• New Db2 for i enhancements
• Modern development tools
• Open source technologies
• Data analytics
• API-driven applications

The Real Win

The real benefit is not replacing DDS with SQL for the sake of it. The real win is that your database becomes easier to understand, easier to integrate, easier to maintain, and better able to support future modernization projects, while your proven RPG business logic can continue doing what it has always done well.