RE: Different Physical File Attributes.

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Ryan Hunt
> Sent: Tuesday, April 11, 2006 4:47 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Re: Different Physical File Attributes.
> 
> Ok, I found some information on "Keyed" and "Arrival" 
> sequences.  Coming
> from the MS SQL world, it sounds alot like Arrival is synonymous with
> "heaps" and keyed is synonymous with "clustered". Please note 
> I use the
> synonyms lightly...
> 
> This sound about right?
> 

No.

Heap vs. clustered both relate to an index for a table.

On the iSeries, a table object can have a integral index.  This is a
"keyed" table.  Assuming no other indexes exist, then you have two
available methods to access the data, called "access paths", into the
table.  You can read by "key" or you can read by relative record number
("arrival") sequence.

If the table doesn't have a integral index, then you have only available
only the "arrival" sequence access method.


Consider a table with two columns, named fld1 and fld2.  A statement
such as
Select * from mytable where fld1 = 'something'

If mytable is an "arrival" table, then every row in the table would be
examined to determine if it should be returned.

Whereas if mytable is "keyed" on fld1, then the index can be used to
determined what rows to return.


Note that as far as I can tell, SQL server works the same way.  When you
create a table with a primary key, you're also creating a integral index
over the primary key field that can be used by the query engine to
determine when a row can be returned.  If you create a table without a
primary key, then SQL server would have to read every record to
determine which to return.


The only real difference between the iSeries and SQL server is that a
table the iSeries can have an integral index defined without having a
primary key.  Using DDS you'd do the following:
                R MYTABLE
                  FLD1          10A
                  FLD2          15A
                K FLD1
The integral index is over FLD1, but you couldn't consider it a primary
key index as duplicates are allowed.


Historically, it was considered "safer" to make all your physical tables
"arrival" or non-keyed and depend on separate external indexes for keyed
access.  Nowadays, SQL standards require/recommend that every table have
a primary key.


Lastly, just an FYI, but as far as I can tell, all indexes on the
iSeries are "clustered".


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121