Re: Different Physical File Attributes.

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

Charles, thanks for the correction.

Since this "heap" like format is so common on the iSeries, I'm inclined to
think that iSeries tables are not as apt to suffer from the table
fragmentation and the forward-pointer random I/O issue as described in the
previously included link.

However, with that being said, JDE table design does STRONGLY favor the use
of CHAR (or rather, strongly dis-favors the use of all variable lengh data
types) which pads values with spaces as necessary.  This padding may waste
some drive space, but it does make it MUCH less likely that records will
change record length after an update.  This in turn will decrease page
splits (which in SQL would prevent a forward-pointer).  Maybe this is
another tactic used to be RDBMS agnostic - or maybe iSeries performance
suffers from page splits as well.

Would you be so gracious as to entertain yet another one of my questions?
Also, if forward-pointers do exist on the iSeries, does the RGZPFM of an
object remove deleted records and resolve page split pointers?

"Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in
message
news:F520B5C51DB10041B239BC06383A7EDC01C48967@xxxxxxxxxxxxxxxxxxxxxxxxxx
> > -----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
>
>
> -- 
> 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.
>
>