RE: Sorting records in Physical and Logical files

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

That's correct until you "Reuse Deleted Records"...  In this case, OS400 locates a deleted row in the table and overwrites the old data.  This option breaks the arrival sequence rule, so only use RDR on files that you always access by key.

Access path maintenance depends on the file attribute of that name.  If MAINT(*IMMED), then new rows get added to the index immediately.  IICR, index maintenance 

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Booth Martin
Sent: Thursday, August 21, 2008 6:21 PM
To: Midrange Systems Technical Discussion
Subject: Re: Sorting records in Physical and Logical files


As I understand the process, records are always added in arrival 
sequence, and the key/address is loaded in arrival sequence also.  When 
the file is closed the keys themselves are sorted for performance 
benefits.  The records are left as-is until some action reorganizes them.



Pete Helgren wrote:
> Ok.  Thanks for that clarification.  So, am I correct in assuming that 
> if the keys were in the order in the DDS I had defined, when an *RPG* 
> program read them, it would be in the order that the keys were defined 
> in (as long as K was in the F spec)?  Is that also true for logicals?  
> That is, the ONLY time the keys are determining the "order" of the 
> records is when they are read through RPG? Then I would expect that if I 
> opened a physical file in Query OR opened any associated logical in 
> Query, the records would display in arrival sequence regardless of how 
> the DDS was defined in either the logical or physical file? If so, that 
> is new information for me.
>
> I thought DDS had broader implications than just in RPG programs. 
>
> Pete
>
>
> Jim Franz wrote:
>   
> > SQL is trying to be as efficient as possible.
> > If you don't ask for ORDER BY, then whatever order it returns is
> > assumed to be ok. Don't assume by rrn either.
> > In RPGLE it would be same if the "F" specs had no "K"
> > to indicate keyed access.
> > Jim Franz
> >
> > ----- Original Message ----- 
> > From: "Pete Helgren" <Pete@xxxxxxxxxx>
> > To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
> > Sent: Thursday, August 21, 2008 5:26 PM
> > Subject: Re: Sorting records in Physical and Logical files
> >
> >
> >   
> >     
> > > In the DDS? I though the point of a logical file was to build an index
> > > that was an alternate to the default sorting sequence of the Physical
> > > file (among other things). Yes, I could issue an ORDER BY using SQL or
> > > OPNQRYF by I though the file had a "natural" sorting order that was set
> > > by the DDS.
> > >
> > > So, there is no way to define a physical file using DDS to sort records
> > > in anything other than arrival sequence?
> > >
> > > Pete
> > >
> > > Alan Shore wrote:
> > >     
> > >       
> > > > What you are seeing is in fact correct. You have to ORDER BY for the 
> > > > order
> > > > you want
> > > >
> > > >
> > > >
> > > > Alan Shore
> > > > Programmer/Analyst, Direct Response
> > > > E:AShore@xxxxxxxxxxx
> > > > P:(631) 244-2000 ext. 5019
> > > > C:(631) 880-8640
> > > > "If you're going through Hell, keep going" - Winston Churchill
> > > >
> > > > midrange-l-bounces@xxxxxxxxxxxx wrote on 08/21/2008 04:43:26 PM:
> > > >
> > > >
> > > >       
> > > >         
> > > > > I thought I understood this but apparently I don't.
> > > > >
> > > > > I have a physical and logical file with the following specs:
> > > > >
> > > > >                             UNIQUE
> > > > > R FASA809                   TEXT('List Workfile')
> > > > > -------------------------------------------------------
> > > > > record layout here:
> > > > >   SLWID          6S 0       COLHDG('Absence ID')
> > > > >   SLW#           6S 0       COLHDG('Seq. Number')
> > > > >   SLWSN          9S 0       COLHDG('Sub SSN')
> > > > >
> > > > > K SLWID
> > > > > K SLW#
> > > > >
> > > > > The logical file looks like:
> > > > >
> > > > >                             UNIQUE
> > > > > R FASA809                   PFILE(PASA809)
> > > > >
> > > > > K SLWID
> > > > > K SLWSN
> > > > >
> > > > >
> > > > > The physical file has a randomly generated number in field SLW# and
> > > > > since the keys are SLWID and then SLW# in that order. I would expect the
> > > > > file to be ordered that way when I open it up in Query or a simple
> > > > > select with no ordering in SQL.  However, the file is always ordered in
> > > > > arrival sequence.  If I read through this file, I see the records in the
> > > > > order in which they were written in the file.  I *expected* them to be
> > > > > ordered by the key sequence.
> > > > >
> > > > > What did I miss or misunderstand?
> > > > >
> > > > > Thanks
> > > > >
> > > > > Pete
> > > > >
> > > > > --
> > > > > 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.
> > > > >
> > > > >
> > > > >         
> > > > >           
> > > -- 
> > > 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.
> > >
> > >     
> > >       
> >   
> >