RE: Different Physical File Attributes.

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

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
> Sent: Wednesday, April 12, 2006 8:54 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: Different Physical File Attributes.
> 
> Lastly, just an FYI, but as far as I can tell, all indexes on the
> iSeries are "clustered".
> 

I take back this last part.  I mis-read the explanation of clustered vs.
heap.

Further investigation leads me to say that iSeries indexes are closer to
SQL server's non-clustered indexes.  

But the difference is that in SQL server, a table is either a clustered
table, meaning it has a clustered index somewhere, or a heap table
meaning it does not have any clustered indexes.  So a table is either
heap or clustered while an index is either clustered or non-clustered.
By default, when you define a primary key for a SQL server table, SQL
server creates a clustered index for that key.  You can however create a
primary key that uses a non-clustered index.

iSeries tables are always "heap" and iSeries indexes are always
non-clustered.

For those unfamiliar with this aspect of SQL Server, a clustered table
is simply a table whose data is stored in the actual clustered index.
The end result is that the data is physically ordered by the clustered
index key.

SQL server treats non-clustered indexes differently depending upon if
the table they are over is a clustered table or a heap table.  If the
index is over a clustered table, the leaf node of a non-clustered index
contains the clustered key for the row.  If the index is over a heap
table, then the leaf node contains a pointer to the physical row. Note
that you can have only one clustered index per table.  Which makes sense
since the clustered index actually contains the data.  

On the iSeries, the data space and the index space are always separate.
The leaf nodes contain pointers to the data space.  Though on a keyed
table, both data space and index space are stored in the same OS level
object.  The physical order of the data in a iSeries object is not
affected by the existence of any indexes.  You can of course reorder the
physical data to match an index using RGZPFM with the KEYFILE parameter.
But the data is still separate from the index.  If you add records, they
will physically be placed at the end of the file, even if by key they
appear in the middle.

-Summary-
SQL Server
        clustered table - a table with a clustered index whose data is
physically ordered and stored in the clustered index.
        heap table - a table without a clustered index.
iSeries
        keyed table - a table with an integral index whose data is _NOT_
physically ordered according to the index
        arrival table - a table without an integral index



Well I don't know about you all, but I learned a little something about
SQL server.

HTH,

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