AS / 400 DB2 Logical File vs Table Index

Question

I come from the MSSQL background, and when I ask people in my company if they created indexes in certain columns, they will say yes, but they indicate that these things cause logical files.

In iSeries Navigator, these logical files appear in the Views category. When I click on the Indexes category, nothing happens, making me believe that there are actually no indexes on any columns, at least as far as I understand them. A logical file looks like a view sorted by specific columns.

So my question is: are logical files and indexes (indexes in the sense of MSSQL) the same?

Answer 1

As described, the DB2 AS / 400 logical file is called a view in most other relational databases. I have to say that I do not think the logical file is the same as the index.

Answer 2

Although the previous answers are not necessarily wrong, they do not give a complete picture.

You see, there are two types of "logical files" - with a key and without a key.

1. Unchanged logical files are truly equivalent to presentation and will not act as an index.
2. Logical files with keys are equivalent to an index (as far as I remember, they are actually implemented in the same way in the base system). They will act as you expect for the index.

All logical files, with or without a key, are actually displayed in iSeries Navigator as representations (I think that only "actual" SQL indexes are displayed as indexes).

I ... really don't know how to find out if a logical file was typed in Navigator. And in iSeries, my company has (as I believe, a user) command to display various logical files (and their keys) for a given physical file (indexes are also displayed). However, the key column is fairly easy to find in the logical definition of the file — let some of your interlocutors in AS / 400 show you the definitions and what to look for.

IBM DB2 Documentation :

From an SQL interface perspective, logical files are identical to representations and indexes.

There is also an article, “SQL Indexes and Custom I / O - No Controversy (2016),” which talks about the differences between “logical files with a DDS key” and “SQL indexes”. Note: logical files are part of DDS, accessed through their own input / output. "DDS - Deprecated Technology."

Answer 3

Logical files combine the functions of both views (select columns and join tables) and indexes (row ordering). They usually function as an index, but appear as "View in Navigator." As a side note, a physical file (rather than a table) may also have an index.

SQL tables, views, and indexes are implemented in DB2 for iSeries using physical and logical files. The main difference is that the database checks the integrity of the data. He checked the write for the tables and checked for read for the files. You can put garbage data in a file, but not in a table.

Answer 4

In fact, there are many tiny differences between the created SQL indexes / views and the logical files created using DDS (the way you write the source files for your logical files (LF) and compile them into LF objects).

So they are the same ? It is definitely not . But there are very similar things, and in most cases you can use them. You may never experience any difference, but it is also possible that one day you will face an inexplicable situation due to differences. Here are some of the differences that I have learned so far (and I remember right now). (Here I will talk about LFs - these logical files and PF (physical files). PF is more or less what you would call a table in SQL, but as with LF and indexes / views, I would not call them the same )

• LFs can have select / omit commands that filter PF strings. Be careful with these! Not only are they often confused, but they can also have a significant impact on your SQL queries. Such LFs are ignored by the modern query optimizer (SQE) and can even lead to the fact that SQE is not used at all, only because they exist (depending on your SQL configuration). You can usually get the same behavior with the sort index and selection.
• LFs can exchange data (LF A with index col1, col2, col3 and LF B with index col1, col2, col4 should share indexing afaik), sql indexes do not do this (but this advantage is supposed not as important as the next disadvantage)
• Indexes may have a larger page size. From what I know, this can make a difference on huge tables).
• Indexes and LFs can act differently when you rename PF and return it from your DDS source. Indexes must remain on the renamed object, while LF must refer to the new object with the old name

These differences are due to the fact that the IBM DB2 / 400 system was created long ago when no one talked about SQL and has not been developed since. But as SQL became important, IBM also introduced SQL support for its well-used database. Thus, indexes / views must support the material SQL requires. LF, on the other hand, should remain compatible with the history of AS / 400. They are different. And thus, they cannot be the same without giving up support. But they are trying to get closer.

Answer 5

I came to this discussion, looking for something else, so I thought that I would just add my contribution. PF and LF are usually called “native files” due to the fact that they were born with this ancestor of the system (S / 38, not sure even if earlier), whereas tables / views were introduced later with SQL. Although SQE currently considers both PFs / LFs and tables / views in the optimization process, another huge difference between them is that although both can be used in any SQL statements, even if they are embedded in compiled programs, only PFs / LFs can be used in compiled programs. Given compiled programs, changes to the PF / LFs recording formats imply a rewrite / recompilation process, while changes to tables and views are not necessary unless they reference external SQL statements.

Answer 6

I came to this discussion, looking for something else, so I thought that I would contribute. A logical key with a key provides index functionality. However, indexes work better than logical files. The query optimizer in DB2 for IBM is more likely to use SQE (the SQL query engine), rather than the older and less efficient CQE (the "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 in performance. In later versions of the IBM operating system, I can specify the page size of the logical file, so the advantage of indexes does not matter as before. IBM's strategic direction is to focus on improving database performance on new DDL SQL database objects (tables, indexes, etc.), and to ignore old deprecated DDS objects defined (physical and logical files.)

Answer 7

This IBM PDF document explaining indexing methods in DB2 was useful for me to understand the differences between tables created using SQL or as physical files on AS400 systems.

IBM DB2 for i, indexing methods and strategies