IBM AS400 iSeries - sort order of select statement

Question

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.

Solution

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)

Comments

[Gordon Linoff]

If you are using SQL, what is the problem with including the ORDER BY, if only to be sure that you don't have unexpected errors in the future?

[user2338816]

Are all queries via SQL SELECT? If it's only a single-column table, is there a reason to "query" other than for a person simply to read the content?

[CJ7]

Thanks for your answer. Regarding steps 4-9, can computer names be used instead of IP addresses?

[Perdana Putra]

Yeah .. Welcome, I think it can't .

[Mike]

There's only 1 column, and the data in that column doesn't have any logical way to be sorted. So I have nothing to use ORDER BY with. It comes into our vendor as a text file, and our vendor inserts it into the table in the same order as the lines in the file, but on their own they don't have a number or anything to sort off of.

[jmarkmurphy]

Down vote because of the "Consider adding an order by clause" stuff since the OP had previously explained why that is not a possibility in this case, both in the question and the comments.

[danny117]

Some people blindly follow. Some don't.

[Mike]

This solved my issue. Thank you very much for your help!