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

[CRPence <CRPbottle@xxxxxxxxx>]

On 13 Feb 2013 07:53, Stone, Joel wrote:
> <<SNIP>> Iseries users normally do a SELECT and the rows appear in
> arrival sequence.

  There is an expression about what "assuming" does.  Many of those who 
had assumed that "arrival sequence" could be expected, were in for a 
shock when they learned they should never have been dependent on that 
false assumption.  They had been FD&H for perhaps years while all 
/seemed/ to work as expected, when suddenly, sometimes, their data was 
no longer in arrival sequence.  They are shocked, so they call IBM, I 
had to tell them... Tough luck, the rules are clear, that without an 
explicit order being requested, the database can return the data in *any 
order* so there is no defect [as long as the _unordered set_ is 
complete\accurate].

> But for some reason when UNION is introduced, the arrival sequence
> is lost.

  Shocker!  The arrival sequence was never there as a guarantee before 
SQL UNION, nor after.

  Using UNION ALL versus UNION DISTINCT to try to influence the effect, 
then perhaps seeing an apparent arrival sequence, that would still be a 
dependence on a false assumption.  Again, the database is under 
absolutely *no obligation to return data in arrival sequence* when no 
ORDER BY is specified.  The optimizer will choose what is felt to be the 
fastest method of retrieval, and that may or may not reflect arrival 
order.  More often than not, arrival order is quickest for a read-only 
of all data in a file.member.  However if the database can perform 
parallel retrieval or determines something about the file or environment 
[amount of memory, CPU, etc.] for which an alternate access path is 
desirable for quicker access, then the database will easily return the 
data in an order that may not reflect arrival sequence.