Re: Sorting records in Physical and Logical files -- MIDRANGE-L

Thanks Vern,

Yeah, I know that SQL is a different beast. When I access a file through SQL I always specify an "order by" if the sorting is important. What was throwing me was I didn't realize that DDS only defined file access for *RPG* programs (as long as you used a "K" in the F spec). I just assumed (always dangerous) that the physical file was ordered by the key order in the definitions and that logicals were "views" in a real sense. It just never occurred to me that DDS wouldn't apply to files that were accessed through other means than RPG.

If the key order specified in the DDS for logical *does* affect the way records are presented outside of an RPG program (I'll have to try this) , then that seems really inconsistent to me. e.g. if key order doesn't apply to physicals but does apply to logicals when the files are accessed outside of RPG then that seems inconsistent.

Anyway, most of what I write these days is SQL in RPGLE not the traditional record access of chains and reads. But this is good stuff to remember the next time I get confused....

Pete

vhamberg@xxxxxxxxxxx wrote:

Pete

Query/400 is not the same as SQL - yes, it did use, I think, the same engine under the covers as the classic query engine. So it probably does honor the order of a logical file - but I've not tested that in forever - never use Query/400 queries anymore. I always us QM queries, which ARE SQL.

OTOH, you do have an option on a QRYDFN to specify a sort order - maybe an comparison test is in order.

I also believe that if you specify a logical on an F-spec and leave off the K, it'll be in the arrival sequence of the PF. Is that right?

Now SQL knows nothing about this thing called a logical file - it knows views and indexes. The iSeries implements those object types USING a logical file. That does not mean there is a strong equivalence there as to how they work in native and how in SQL mode.

Vern

-------------- Original message -------------- From: Pete Helgren <Pete@xxxxxxxxxx>

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" To: "Midrange Systems Technical Discussion" 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.

--
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.