Re: Sorting records in Physical and Logical files

The OS second guesses which logical is the most efficient performance wise to five you what you asked for.

This is a rethinking of the purpose of some DB features, that I think is a step backwards, but the world has moved on, so we need to learn how to live in the new world, not the old logic.

Thus, the program needs to explicitly duplicate what the logical says you want.
The logical says only certain records, and a certain sequence.
The program has to say the same thing.

Programs written under the old DB logic.
The DDS says do X. The program not replicate that.
Those old programs now malfunctioning.

Most of the programs I write these days ... whatever is the latest most efficient way (such as SQL cursor in an RPG loop), and sub-programs ... but I also have to maintain programs that were originally written 10 20 30 years ago.
They include a great spectrum of techniques.

The RPG cycle.
Matching Records
CHAIN
READ
READ other (like backwards)
internally described files

All have assumptions based on how the OS functioned in the day they were written.

I think the reason for this is that IBM was in the 24 th century with respect to DB development, while the rest of the computer industry in the 18th century. For SQL and other Query languages to work on all systems, including non-IBM systems, they have to work in accordance with the other realities.

SQL is pretty ignorant of a lot of DB/400 like members & formats.
As we move into a world more dependent on SQL, we have to give up a lot of the DB features that SQL is ignorant about.

That's always the way. Each new language can do some stuff GREAT that by comparison the legacy languages were poor at, but at the same time the legacy language still can do things GREAT that the designers of the new language did not realize had any value.

If you re-order the physical records from arrival sequence into agreement with some logical, that might adversely affect other programs that want a different sequence.

You can use a logical in a QUERY/400 but the same performance thing is going on ... the OS looks at your selection criteria in the QRYDFN & selects whichever logical makes most sense, irrespective of the one you specified, then reads in all the records anyway, not just the ones you selected to process. This is why, when we join more than one big file (like a million records each_ where we are only accessing a handful of records for the results, we have to run the thing on JOBQ.

What we have in a particular query/400 can be over-ridden when a CL to run it has the appropriate over-rides. I use the CLs heavily to put on some menu so easy for end users to run most commonly needed, but power users often go into WRKQRY.

I don't like having queries that play by different rules depending on how they get run.
It is murder for the end user of a report that THEY did not do the selection criteria or methodology, to figure out basis for stuff on this or that report.

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.