Re: Different Physical File Attributes.

["Ryan Hunt" <ryan.hunt@xxxxxxxxxxxxx>]

Charles, thanks for all the info.  While you've been learning SQL server
you've expanded my knowledge of the iSeries.

I still have a question about the "Arrival" sequence tables.  "Arrival"
sequence tables on the iSeries are synonymous (or most synonymous) with heap
tables in SQL Server (as you've stated).  In the SQL Server world, heaps are
notoriious for page fragmentation and forward-pointer issues.

Rather than writing a big explanation, check out the section titled:
"Problems with not having a clustered index " on the followint page:
http://www.quest-pipelines.com/newsletter-v4/1103_B.htm

Do iSeries arrival sequence tables suffer from the same or similar maladies?

Thanks for all the insite.

Ryan

"Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in
message
news:F520B5C51DB10041B239BC06383A7EDC01C4892D@xxxxxxxxxxxxxxxxxxxxxxxxxx
> > -----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
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>