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: Wednesday, April 12, 2006 2:42 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Re: Different Physical File Attributes.
> 
> 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).  

You misunderstood me.

Arrival sequence tables on the iSeries are _not_ synonymous with heap
tables in SQL server.

In fact, both "arrival" and "keyed" iSeries tables work like SQL server
heap tables.

On the iSeries, it doesn't matter if you're dealing with a "keyed
tabled" or an "arrival" table, the physical layout of the data is
generally in arrival sequence.  I say generally, because if all you do
are ADDs and UPDATEs then the data is in arrival sequence.  If you do
DELETEs and ADDs and the table is set to reuse deleted records, by
default tables created using SQL instead of DDS are so configured; then
there is no real order to the physical data.

> 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

Actually, that's the page where I found out the info about SQL server.

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

No.  As far forward pointers go, the iSeries doesn't use them.  Rows are
never normally moved.  The example given for SQL server, expanding a
VARCHAR column, doesn't apply as the iSeries allocates a fix amount of
space (>=0) for VARCHAR (and BLOB,CLOB) fields in the standard row data
space.  Anything more than the allocated space is stored outside the
standard row data space.  

As far as fragmentation, I'm not sure that it's a big deal for heap
tables in SQL server, "Fragmentation matters little in heap tables
because the data rows do not have to be kept in order."  from
http://www.dba-sql-server.com/sql_server_tips/t_hpsdba_41_indexdefrag_db
cc_dbreindex.htm

As far as the iSeries goes, the above quote certainly applies since
iSeries tables don't have an order. 

Fragmentation in general on the iSeries isn't usually a concern.  Single
level store is part of the reason.  Plus the fact that the iSeries, like
its predecessors were designed from the ground up to be scalable
multi-user systems.  Part of that design was a RAID 0 disk subsystem.
The OS is highly optimized for dealing with pieces of objects scattered
all over the place.  Like memory, the more disks you add to a iSeries
the better it performs.

> 
> Thanks for all the insite.
> 
> Ryan
> 

HTH,

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