AS/400 DB2 Logical File vs Table Index

Question

I'm coming from a MSSQL background, and when I ask people at my company if they've created Indexes on certain columns they'll say yes but point me to these things call Logical Files.

In the iSeries Navigator these Logical Files show up under the 'Views' category. When I click the 'Indexes' category nothing is there, leading me to believe that there are actually no Indexes created on any columns, at least as I understand them. A Logical File appears to be a View sorted by certain columns.

So my question is, are Logical Files and Indexes (indexes in the MSSQL sense) the same thing?

Answer 1

Came across this discussion while looking for something else, so thought I'd contribute. A keyed logical file does provide the functionality of an index. However, indexes perform better than logical files, the query optimizer in DB2 for IBM i is more likely to use the SQE (SQL query engine) rather than the older and less efficient CQE ("classic" query engine) to optimize the query if it can use an index. By default, indexes have a larger page size than logical files, which helps with performance. In more recent releases of the IBM i operating system, the page size of a logical file can be specified, so that advantage of indexes isn't important as previously. The strategic direction of IBM is to concentrate database performance improvement efforts on the newer SQL DDL defined database objects (tables, indexes, etc.) and to ignore the older legacy DDS defined objects (physical and logical files.)