MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » February 2013

RE: how to retrieve records and make sure they arrive in RRN sequence



fixed

The point we're all trying to make is that there IS NO MIDDLE OR BEGINNING OR END OF FILE. In a modern database, records in a table are just IN a table. There is no implicit ordering. When you insert a row, it is added to the table, not to the END of the table, but somewhere in the table. You've got to stop thinking about database files as some ordered set. Records being sequentially ordered in some physical media went away with the System/3.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Wednesday, February 13, 2013 1:48 PM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: how to retrieve records and make sure they arrive in RRN sequence

OK I understand that you are fore-warning me.

And I understand that there should be a seq# or date/time stamp on each record.

But, given that this file has been around for 30 years, and the only key is the MEMO#, and the file hasn't been reorg'd;

Is UNION ALL any different than READE on the MEMO # as the only key?

I mean would SQL know to do anything differently than read by arrival seq?

I don't know much about the inner workings of SQL on DB2/400, but it seems unlikely that the SQL engine would start in the middle of a file and read backwards & forwards. Also unlikely that it would scramble the sequence from arrival.

Yes this could occur in RPG READE also (without a second key to guarantee proper ordering of the records). But it doesn't fail - rather it works well.

Would SQL retrieval be any more likely to scramble the sequence than RPG READE or Cobol or any other access method?

And I hear loud and clear that it is not a good idea. But will it really fail given the circumstances?



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, February 13, 2013 1:26 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: how to retrieve records and make sure they arrive in RRN sequence

On 13 Feb 2013 09:56, Stone, Joel wrote:
Wow - that worked!! Union ALL is the ticket. So simple and elegant too.

There is a huge difference between "that worked" and "that specific request to perform that SQL without an ORDER BY produced the desired effect that time, but at the whim of the optimizer for that invocation, so that same collation can not be expected to occur on every invocation of that same query." Dependence on a false assumption is not a good proposition for software programming.

<<SNIP>>

UNION has an added feature of sorting the data - I didn't know that.

The UNION is a synonym for UNION DISTINCT. UNION DISTINCT does not "sort" the data, even though its DISTINCT processing may produce a set that might appear to have some apparent order. Appearances can be deceiving. There is no obligation of the database to return DISTINCT results in any particular order, when no ORDER BY is specified.

UNION ALL leaves the data alone - like a CPYF. It simply concatenates
a file onto the end of another, which is what I am looking for.

The result of a UNION ALL is not defined as a concatenation of one file onto the end of another. The UNION ALL returns an unordered set of rows, as a union of data from the tables named as a party to the union.
The presumption of an arrival sequence concatenation is a fallacy, bound to be learned later, to have been based on a false assumption; though having been warned, that mistake can be avoided.

Also FWiW the CPYF provides arrival sequence for copying all data from a file.mbr only when using FROMRCD(1) versus the default of
FROMRCD(*START) which uses a keyed access path if one exists.

--
Regards, Chuck
--
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 inbound email has been scanned for all viruses by the MessageLabs SkyScan service.
________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--
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.








Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact