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?

Plasmolysis

I don't know the first thing about the AS/400 world, but try looking at Logical Files. They are described as being like an index - but not in the MSSQL sense of the word (seem more like "views" to me) – Purser

I don't think the current "best answer" is argumented enough, as the link support is broken and there was no abstract provided in 2011, and the sentence "I don't think ... is the same" is not as per Stack Overflow standards. So I recommend to accept one of the other more interesting answers. – Chkalov

Answer 1

According to this description, an AS/400 DB2 Logical File is called a view in most other relational databases. I'd have to say that I don't think a logical file is the same as an index.

Ophir

Incorrect. The description is inaccurate in several ways. Perhaps the most severe is that it neglects that Logical Files can include an index while having other features at the same time. The linked description page seems to have been written by someone who was not an AS/400 expert. See other answers instead. – Apfelstadt

On the object-based system is an object-type of *FILE. One variant of the *FILE is a Database file. And of the Database file, there are two types: Physical File (PF) and Logical File (LF). Of the SQL VIEW and the SQL INDEX, each is an extension of [inherits many attributes from] the LF, and the SQL TABLE is an extension of [inherits many attributes from] the PF. An LF may be Keyed Sequence or Arrival Sequence; an SQL INDEX is a Keyed LF. The Keyed file has an Access Path (ACCPTH) that is implemented as an index, specifically a DataSpace Index (QDDSI) over the data, a DataSpace (QDDS) – Unstudied

Answer 2

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.

Relay

Thanks, great to know. It seems like most of our Logical Files are indeed Keyed. – Plasmolysis

As a side note, keyed logical files show up as 'index used' on the explain plans the optimizer produces (and Navigator references). So one way to detect them is to run a query that should use the index, and check the explain plan (not always going to work, though). – Relay

When you Show Indexes on a table, the result includes Keyed logical files and indexes. The column Type tells you if the logical file is keyed. – Dituri

Answer 3

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.

Copyright

Answer 4

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.

Phobia

Answer 5

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.

Unrestrained

Answer 6

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.)

Polemist

Improvements to the SQE optimizer have virtually eliminated situations where Logical Files would cause DB2 revert to using the CQE optimizer. – Apfelstadt

Answer 7

This PDF from IBM which explains the indexing methods in DB2 was helpfull for me in order to understand differences between tables created using SQL or as physical files on AS400 systems.

IBM DB2 for i indexing methods and strategies

Ancy

Answer 8

Here is an interesting test result.

When querying a physical file, the query optimizer can utilize the index of a logical file even when the user profile is explicitly *EXCLUDE to the logical file.

However, attempting to query the logical file directly will result in an authorization error.

So, it appears that the index portion of the logical file is always available to the query optimizer and the view portion of the logical file is the securable portion.

This testing was a result of asking the question... If I give user profiles *USE access to physical files but no access to the logical files, will the query optimizer still be able to utilize the indexes of the logical files. The answer is Yes.

This is also true about SQL Indexes. The optimizer will use it even if the user is *EXCLUDE.

Mellar