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.
Ex.
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?

Anyone can help me?


Thanx in advance


Simon

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

it is not necessary that the rows are retrieved in the order in which they are stored ...

The only way to gurantee the order of the rows is by using an ORDER BY clause .. Of course, the ORDER BY is an expensive operation

HTH

Sathyaram

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.

cheers,
Rob.

I am not sure, but what I see here in this shop:

for DB2 MVS the index is usually a clustered index, so the data will be stored also according to that (and probably also returned that way). Primary keys are only defined for RI.

On distributed systems (DB2 on AIX), the index is a 'normal' index and defined as primary key, without clustering.

I can imagine, that this makes a difference.

Also: did you check the access path, whether the host uses some join method with an implicit sort and the other system does not ?

Juliane - Thanks for pointing out the difference in clustering index order between zos and luw.

For the op , the main point remains valid 'You cannot gurantee the row order if you have no ORDER BY Clause'. GROUP BY is only for grouping and does not gurantee ordered results

Cheers
Sathyaram

I've to thank to all of u for the answers,

till now the only way to solve the problem is to put an explicit "ORDER BY".

We tried also to rebuild a clustered index but with none result :-(


Simon

Sathyaram, Simon, of course that is very true. I just added my thoughts.

simon71, can you post the SQL 'create index..' statement?
There is posible you don't have anought PCTFREE parameter defined. Maybe you also need reorg of index...