RE: DBA Question

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Jack, 

Basically, view is a non-keyed LF and index is keyed LF.
I tend to use DDS created files for native access and DDL for files that'll
primarily be accessed using SQL, but there is no rule per say.  Regardless
of the creation method you can use native I/O or DML on either.

DDL created objects should give you better performance in terms of
synchronous I/O due to some enhancements IBM made for DDL created objects.
For example, indexes have 64KB page size while LFs can have at most 32 and
usually have 4 or 8KB page size.  This means more data is brought into main
memory in a single swoop.
For tables (physical files) data validation is done on the write so less
data validation is done on reads.  This means that for SQL accessed tables,
read performance will be much better, and in real life there are a lot more
reads than there are writes.

One of our newsletters had a brief comparison of the two:

http://tinyurl.com/95mu2

And there is a terrific article by Dan Cruikshank on iSeriesNetwork website:

http://tinyurl.com/8vd6a

Hope that helps cool down your heated discussions :)

Elvis

-----Original Message-----
Subject: DBA Question

To the List,

 

Got into a discussion this afternoon about what the real differences are
between logical files, views and indexes. The discussion got a little heated
at times so I would ask that some of you well versed members to please help
out less fortunate uninformed members about the real physical and logical
attributes of these object types.

 

Jack Derham

Direct Systems, Inc.