AS/400 DB2 Logical File vs Table Index
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
While the previous answers aren't necessarily wrong, they don't give the complete picture.
See, there are two types of 'Logical Files' - keyed and unkeyed.
- Unkeyed logical files are indeed equivalent to a view, and will not act as an index.
- Keyed logical files are equivalent to an index (from what I remember, they're actually implemented in the same way in the underlying system). These will act as you expect for an index.
All logical files, keyed or not, actually show up in iSeries Navigator as views (I think only 'actual' - SQL - indices show up as indices).
I'm... not actually sure how to find out if a logical file is keyed from Navigator. And on the iSeries, my company has a (what I assume to be) custom command to show the various logical files (and their keys) for a given physical file (indices show up too). However, keyed column are pretty easy to spot on a logical file definition - have some of your AS/400 buddies show you the definitions, and what to look for.
From the perspective of the SQL interface, logical files are identical to views and indexes.
There's also this article "SQL indexes and native I/O – no contradiction (2016)" which talks about the differences between "DDS-keyed logical files" and "SQL indexes". Note: logical files are part of DDS, they are accessed via "native I/O". "DDS is an outdated technology" though.