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

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.

1. Unkeyed logical files are indeed equivalent to a view, and will not act as an index.
2. 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.

Answer 2

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.

Answer 3

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.

Answer 4

Came across this discussion while looking for something else, so thought I would just add a contribution, too. PFs and LFs are usually referred to as "native files" due to the fact that they were born with this system ancestor (S/38, not sure if even before), whereas tables/views were introduced later with SQL. Though nowadays SQE considers both PFs/LFs and tables/views in optimization process, another huge difference among the two is that while both can be used in any SQL statements, even if embedded in compiled programmes, only PFs/LFs can be used native in compiled programmes. Considering compiled programmes, changes in PFs/LFs record formats imply rebinding/recompiling process, while there is no need of it in case of tables/views changes, unless they are referred to from outside SQL statement.