Re: Why does the system see the logical file as a view

On 25-Feb-2015 18:02 -0600, Vicki Wilson wrote:

We are starting to talk about modernization. We have older logical
files that have new record names, specify the fields in addition to a
key for sorting.

We were experimenting with generating an SQL script. We did not find
the logical with indexes - we found it with the views. I'm curious.
Why does the system recognize it as a view? And how do the key
fields get handled since they can't be included in the view?
<<SNIP>>

For anything specific, what defines /found with/ is critical to enable worthwhile comment. IIRC both the SYSVIEWS and SYSINDEXES catalogs restrict their results to just the SQL-defined definitions, so a non-SQL Logical File (LF) would not appear for either.

However what may suffice generally to explain, is that every logical database *FILE object effectively provides a logical /view/ of the physical data, thus from the perspective of the relational model provides an unordered set of data, despite the existence of a keyed access path. The SQL INDEX is implemented as a database Logical File with a keyed Access Path; as just noted, then that LF can server as a view of the data. However the SQL INDEX is prohibited from use in SQL as a table-reference, and the DB2 for i SQL honors the prohibition [as required by the SQL standards], so an INDEX is not considered a view of the data regardless the non-SQL DB2 for i understands and treats those logical files the same as any other. Note: an exception, the Encoded Vector Index (EVI) logical file [an SQL INDEX] is _not_ supported as a /view/ of the data, even by the non-SQL DB2 for i.