RE: DBA Question -- MIDRANGE-L

 >From a layman's point of view, you can think of it this way:
Joe Pluta says 
>> 1. An INDEX allows you to order records
>> 2. A VIEW allow you to select records
>> 3. A logical file allows both

Have to disagree with you here, Joe. 

Views and Logicals files are not ways to select records. Views and Logical 
files are logical views of the data base vs. physical views of the data base. 

Background:

In a file system, logical and physical views of the data are the same, that is 
you bring the whole file into every program. As far as the program knows, 
theirs is the physical view of the data. 

The whole purpose in having a database, which we hardly use on the AS/400, is 
be able to have a logical view of the database that is different from the 
physical view which is where views and logical files come in. 

If I have a table in a database that has the following fields:

CustomerNumber
CustomerName
CustomerAddress
CustomerZip
CustomerARBalance

and I create an SQL Statement

Select CustomerNumber,
          CustomerARBalance
>From CustomerTable

what I am creating is a logical view of the data. I can join to other tables, 
etc but what everything still comes down to is that I am presenting a logical 
view of the data that is different from the physical view. Anything that uses 
that SQL statement or a view created from it, thinks this is what the database 
looks like but in reality, the underlying physical tables are completely 
different. 

A view is just a complied logical view(An sql statement) of the database. It 
allows you to present a logical view of the database that you use as if that 
logical view was a physical view, i.e. it lets you treat as a table. The SQL 
database manages making it all work. 

A logical file was designed to be the same thing although IBM, also, use the 
logical file to create indexes. 

A logical file is designed to present a logical view of the data that is 
different than the physical. It can do this by three different methods. One, by 
only selecting certain records from a table, two, selecting fields from a table 
and three by the use of join logicals. 

The other thing to understand about a logical file is that a logical file 
doesn't really exist. All a logical file is a bunch of pointers to sub-objects. 
A format object, a index object so when we say that a logical file defines an 
index we have to be careful. If you define a logical file with an index and 
another index already exists with the same values and types, you end up just 
reusing the index. The logical file just points to other index. 

SQL Index's only allow you index a table. They do not allow any form of a 
sub-select. It just indexes the entire table.

The problem we have on the AS/400 is that we use the database so much as a file 
system that we think that logical files are just files. We define an index and 
bring in the entire record and the logical view ends up being the physical 
view. We don't know anything about using logical files as logical views of the 
database. We end up paying a hell of price for this. 

The other problem that we have is that because we see the logical view as the 
physical view, we end up defining our tables to look like the logical view of 
the data. Virtually every AS/400 database is defined as tables that look like 
what the programmer needed for a logical view of the data.

When we normalize a database, what we are doing is converting from a logical 
view of the data to a normalized physical view, something almost never done on 
the AS/400. The database just allows us to take that normalized view, and 
present it as we want to see it. 

And why do we want logical views of the database? So that we can change the 
unlaying tables without effecting anything that is using that tables or we can 
change our logical view to create new information, again without changing the 
underlying tables. Database independence. Plus a ton more I won't go into.