CODEWITHSUNDEEP
sortingibm-midrangeiseries-navigator
Mike
Mike

Reputation: 409

IBM AS400 iSeries - sort order of select statement

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.

Upvotes: 0

Views: 3206

Answers (2)

danny117
danny117

Reputation: 5651

Add a clause order by RRN(mytable) this will get the records in FIFO sequence. But it depends how vendor loaded table. Don't trust this it will someday fail. It will work with native as400 tables.

Consider adding an order by clause on your select statement to fit your needs.

This works forever. If you need the records in Zip code order then order by zipcode. If you need the records in name order then order by "name". There are no guarantees on the order of records unless you specify an order by clause.

Your question. Yes a select can alter the order by in DB2 (as400) with a select when there is no order by clause in the select. Db2 attempts to use an index to present the results faster.

For example

select * from customer when cust_name like 'Dan%'

And there is an index over customer by cust_name.

The results will probably come out ordered by cust_name. Depends on other factors but in theory a select with no order by clause can come out in any sequence but most likely it will be the sequence of an index over the table or the tables primary key.

Upvotes: -2

James Allman
James Allman

Reputation: 41198

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)

Upvotes: 3

Related Questions