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

["James H. H. Lampert" <jamesl@xxxxxxxxxxxxxxxxx>]

Relative record number and arrival sequence are a holdover from 
non-database stream files, from ISAM, and from part of VSAM. It exists 
in DB2/400 mainly because, up until fairly recently in the evolution 
from the S/3 to the IBM i, IBM Midrange Systems didn't have stream 
files, at least not as they existed in PC-DOS, UNIX, Linux, RSTS, and so 
forth, and so database files had to be able to function as if they were 
stream files.

I would not, personally, go so far as to say that it's obsolete, but it 
is a fact that SQL has no native concept of RRN, and no native concept 
of arrival sequence, because far from assuming that the underlying 
database is an array-of-records, or a stream-of-records, with an 
inherent sequence to them, it assumes the most general case possible, 
i.e., that the underlying database is, at worst, a BAG of records, and 
that a well-formed database, with a unique primary key, is a properly 
formed SET of unique records.

Some databse systems, I'm told, make it difficult or impossible to 
create a database file at all without designating a unique primary key. 
Others (MySQL and DB2/400, for example) won't even warn you if you do 
so, and will happily allow you to create a bag of records with no 
uniqueness constraints at all. DB2/400 at least has RRNs in the 
underlying database, which can not only be readily used to sort the 
records, but is the default sequence for the native, non-SQL access 
method (i.e., RLA).

Now if I had designed SQL, and designed all of the database systems that 
support it, I would have (1) required that, at the very least, any 
database file created without an explicit primary key be automatically 
given an implicit one that would (whether a serial number or a 
timestamp) make sure individual records are uniquely addressable, as if 
they had actual RRNs, and (2) require that, whether the primary key is 
explicit, implicit, or an actual RRN, any SELECT that lacked an explicit 
ORDER BY clause have an implicit one, ordering results by primary key, 
unless (for whatever reason) the SELECT explicitly specified that no 
attempt be made to sequence the records.

And if wishes were horses, then beggars would ride. Isn't 20/20 
hindsight wonderful?

--
JHHL