Hi everybody and first of all sorry my bad english
The customer i work for has just migrated from DB2 MVS to DB2/AIX64 8.2.2 (same version) and we noticed that while select on MVS without explicit "order by" clause were sorted according the primary key, now in AIX the select are sorted in random way. Not only, every time we refresh the select the sort order is always different.
We noticed also that the problem is not limited where there's "order by" absence but also where the sorting is not explicited for all the fields of the table.
MyTable has 4 fields :A, B,C and D
If i run following statement: select * from Mytable order by A,B
Every time i refresh the select we have random sorting on the other 2 fields not included into the order by.
At the moment the only way i have to avoid this is to explicit the "order by" clause for all the fields.
There's a way (db side) to force to change this unpredicatable way of sorting avoiding rewriting every sql statements?
Select without an order by or group by clause will not sort your results. This includes the mainframe. The rows are sometimes retrieved in the order that they are stored. On the mainframe, you most likely encounterd the rows in order of the primary key because they were physically stored that way. Probably because the PK index was the clustering index. You will have to add the order by or group by clause to sort your results.
Hi urquel ,
i was thinking the same thing but why every time i refresh the select the sort order is different?
As u sad the rows should be retrieved ALWAYS in the order that they are stored but now here is not like this and in mainframe we didn't have this problem
I did not say that the rows are ALWAYS retrieved in the order that they are stored. I was saying that they probably came back that way because of how they are stored, and that can not always be guaranteed. In other words, DB2 got the rows for you and they happened to be in order(the rows are not sorted), but not because they will alway be in that order. It's just a phnenomenon that occurs with a clusteing index. The only way to guarantee the order of your results in with an order by/group by clause which tells DB2 to sort.
Well, that should be easy, should it not?
"while select on MVS without explicit "order by" clause were sorted according the primary key"
I assume you have a primary key on your DB2 table. Use that order! This will prevent DB2 from using a temporary table for sorting.
If that is not what you want, then your primary key differs from the primary key as it was on MVS. Should it be? I hope not.