We have a vendor who manages an AS400 for our company. One of their services is providing files via a table that I can query using iSeries. However, the table has only one column, and each record is just a line from one of the files. So I need to select all of the records from the table while maintaining the order in which they were input, but I don't have a column I can use an ORDER BY on.
I understand in relational databases, there is no guaranteed sort order on a select statement without an ORDER BY clause. Does the AS400 / iSeries system operate the same way? From the reading I have done, it appears to be a different sort of DBMS, but I'm not finding anything that tells me whether or not a select will potentially alter/ not alter the order in which the records were inserted into the table. It "appears" that whenever I run a select, I always get what appears to be the right sort order, but I want to guarantee it if I can.
Thank you in advance for any advice and help.
Without an explicit ORDER BY clause the records could be returned in any order the SQL optimizer deems most efficient.
DB2/400 has the concept of "relative record number". New records are always appended to a table unless the "Reuse deleted records" setting on the table is *YES.
You can use the RRN function to order records by their physical ordering in the file:
SELECT * FROM TABLENAME ORDER BY RRN(TABLENAME)