AS/400 DB2 Logical File vs Table Index
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.
IBM DB2 documentation:
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.
Logical Files combine the features of both Views (column selection and table joining) and Indexes (row ordering). They usually function as an Index but do show up as a View in Navigator. As a side note a Physical File (not table) can also have an Index.
SQL Tables, Views and Indexes are implemented in DB2 for iSeries using Physical and Logical Files. The primary difference is when the database checks data integrity. It's checked on write for Tables and checked on read for Files. You can put trash data into a File but not into a Table.
There are actually many tiny differences between SQL created indexes/views and logical files created via DDS (that's the way of writing source files for your logical files (LF) and compiling them to LF-Objects).
So are they the same thing? That's a definete no there. But there are very similiar things and in most cases you can use either. It's possible that you will never experience any difference, but it's also possible, that one day you stand before an unexplainable situation, because of the differences. Here are some differences that I have learned so far (and I remember right now). (I'll talk about LFs -- that's logical files -- and PFs (Physical Files) here. A PF is more or less what you would call a table in SQL, but like with LFs and indices/views, I would not call them the same)
- LFs can have select/omit statements, which filter which rows of the PF. Be careful with those! Not only are they often confusing, but they can also have significant impact on your SQL-Queries. Such LFs are ignored by the modern query optimizer (SQE) and can even lead to the SQE not being used at all, only because there exist (depending on your SQL-configuration). You can normally get the same behaviour with an sorting index and a select.
- LFs can share data pathes (LF A having index col1, col2, col3 and LF B having index col1, col2, col4 should share the indexing afaik), sql-indices don't do that (but that advantage is supposed to be not as important as the next disadvantage)
- Indices can have a larger page size. From what I know, that can make a differnce on huge tables).
- Indices and LFs might act differently when you rename an PF and recrate it from its DDS source. Indices should stay on the renamed object, while LFs should refer to the new object with the old name
These differences are related to the fact, that IBMs DB2/400-system was created a long time ago, when noone was talking about SQL and developed ever since. But since SQL became important, IBM also introduced SQL-support for their well used DB. So indices/views need to support the stuff, SQL requires them to. LFs on the other hand must remain downward compatible with the AS/400s history. Those differ. And thus, they cannot be the same without dropping support for one. But they try to come pretty close.