The performance difference between a true SQL index on the as400 and a keyed logical index

Apparently there is a huge performance difference on the as400, that has existed since V4R2, between SQL indexes and DDS created logical files. The performance is so much better that it is worth converting all DDS logicals to SQL, even if they are only used by RPG programs

The evolution of the as400 / iSeries / i5 has resulted in a mix of old and new technologies. Most notably
are the methods used to create, populate and manipulate databases. Many iSeries/i5 application
developers have embraced modern tools for creating front end masterpieces to their existing data;
however the underlying foundation, the database itself, has been woefully neglected. Basically
the existing databases were created using a tool known as DDS (Data Description Specification).
All other Relational Database Management Systems (RDMS) use Structured Query Language, or
SQL, to define the database.
For many iSeries/i5 developers initial attempts in the use of SQL have resulted in unacceptable
performance causing system administrators and/or Chief Information Officers to discourage its
use. For others, the ease of use of SQL has made it the preferred tool for data creation and access.
When these two groups come together the result is like a bad beer commercial. Shouts of “It’s
great!” are hurled in response to yells of “Less fulfilling!”
As IBM continues to enhance DB2 UDB for iSeries (e.g. the new and improved SQL Query
Engine, more efficient data access methods, new database primitives, etc) the decision to use
SQL over traditional methods will no longer be if, but when. In fact, many iSeries/i5 shops are
developing all new applications using SQL. In addition, the use of SQL defined databases may
result in improved throughput as a result in changes to the underlying architecture (more on this
later).
So all new SQL development will benefit from new database technology but what about the
existing DDS defined databases that are still serving thousands of applications using Record
Level Access (RLA) methods via High Level Language (HLL) read and write operations? Is there
a way that these applications can take advantage of SQL database enhancements without a total
rewrite?
The answer is a resounding Yes! This is the basic premise of this article. In essence this article
provides a high level overview of the madness behind the methods known as the Stage 1 DDS to
DDL reengineering strategy detailed in the soon to be released Redbook "Modernizing iSeries
Application Data Access - a Roadmap Cornerstone".

Full PDF here -
as400 DDS and SQL Performance

Good luck with your as400 logical file performance