order by issue

[db2group88]

i have the table create with append on option (append on the data to
the end of last page), so suppose the data order is by arrival
sequence. but when i query use select ... order by field1, if the
field1 is same, i couldn't get the right order of the other fields
which is inserted by arrival sequence, and this doesn't happen all the
time, so what problem is this? any solution?

[Rhino]

<db********@yahoo.com> wrote in message
news:11**************************@posting.google.c om...

i have the table create with append on option (append on the data to
the end of last page), so suppose the data order is by arrival
sequence. but when i query use select ... order by field1, if the
field1 is same, i couldn't get the right order of the other fields
which is inserted by arrival sequence, and this doesn't happen all the
time, so what problem is this? any solution?

This isn't a problem, this is the way DB2 works. In DB2, it has always been
the case - on every platform and DB2 version - that the only way to ensure a
desired sequence for your result set is to use ORDER BY in your query. (In a
few limited cases, GROUP BY or DISTINCT may accomplish the same affect.)

Furthermore, DB2 does not necessarily store data in arrival sequence. In
most cases, the sequence in which data is stored is governed by your choice
of clustering index. (If you have not chosen a clustering index, DB2 will
normally use your oldest existing index as the clustering index.) Even then,
a clustering index does not guarantee that a new row will be stored in
clustering sequence: if the target page for a new row is already full, the
new row will not be stored on the desired page and clustering will,
therefore, not be 100%. In other words, the new row will be stored on some
page other than the desired one and the cluster ratio for that table will no
longer be 100%.

Rhino

[Knut Stolze]

Rhino wrote:

<db********@yahoo.com> wrote in message
news:11**************************@posting.google.c om...

i have the table create with append on option (append on the data to
the end of last page), so suppose the data order is by arrival
sequence. but when i query use select ... order by field1, if the
field1 is same, i couldn't get the right order of the other fields
which is inserted by arrival sequence, and this doesn't happen all the
time, so what problem is this? any solution?
This isn't a problem, this is the way DB2 works. In DB2, it has always
been the case - on every platform and DB2 version - that the only way to
ensure a desired sequence for your result set is to use ORDER BY in your
query.

That's actually an SQL thing and not solely bound to DB2.
(In a few limited cases, GROUP BY or DISTINCT may accomplish the
same affect.)

Which you can't rely on, of course. Only ORDER BY defines how rows are
sorted and if there are any duplicates on the ordering columns, then you
can't predict and can't rely on any specific ordering of those rows.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena