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

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