469,582 Members | 2,300 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,582 developers. It's quick & easy.

Data of table not sorted by primary index in Control Center

Why is that even though I have added index with DESC sort order on
primary key, records in Control Center are displayed by the sequence
they were previously inserted, not by the value of primary key?
If I perform REORG on table primary index, records get sorted by
primary index properly. But when I enter new records afterwards, the
new ones appear again sorted in sequence they were inserted into table.
Why?

Dec 5 '05 #1
3 1946
Ian
Antanas wrote:
Why is that even though I have added index with DESC sort order on
primary key, records in Control Center are displayed by the sequence
they were previously inserted, not by the value of primary key?
If I perform REORG on table primary index, records get sorted by
primary index properly. But when I enter new records afterwards, the
new ones appear again sorted in sequence they were inserted into table.


SQL is set-based, and unless you specify an ORDER BY clause, there is
no guarantee that you'll get records back in any specific order.

The fact that you do get them in a noticeable order (i.e. order that
records were added or PK-order after the REORG) is "coincidence." (It
may be reproducible now, but other changes to your database and/or
config have a good chance of changing this.)

Good luck,
Dec 5 '05 #2

Ian wrote:
Antanas wrote:
Why is that even though I have added index with DESC sort order on
primary key, records in Control Center are displayed by the sequence
they were previously inserted, not by the value of primary key?
If I perform REORG on table primary index, records get sorted by
primary index properly. But when I enter new records afterwards, the
new ones appear again sorted in sequence they were inserted into table.


SQL is set-based, and unless you specify an ORDER BY clause, there is
no guarantee that you'll get records back in any specific order.

The fact that you do get them in a noticeable order (i.e. order that
records were added or PK-order after the REORG) is "coincidence." (It
may be reproducible now, but other changes to your database and/or
config have a good chance of changing this.)

Good luck,


The indexes are always in the exact order of the index key. Table rows
are only in order of an index after a reorg (if you specify an index to
be used in the reorg, or the table has a clustered index defined).

Dec 5 '05 #3
m0****@yahoo.com wrote:

Ian wrote:
Antanas wrote:
> Why is that even though I have added index with DESC sort order on
> primary key, records in Control Center are displayed by the sequence
> they were previously inserted, not by the value of primary key?
> If I perform REORG on table primary index, records get sorted by
> primary index properly. But when I enter new records afterwards, the
> new ones appear again sorted in sequence they were inserted into table.


SQL is set-based, and unless you specify an ORDER BY clause, there is
no guarantee that you'll get records back in any specific order.

The fact that you do get them in a noticeable order (i.e. order that
records were added or PK-order after the REORG) is "coincidence." (It
may be reproducible now, but other changes to your database and/or
config have a good chance of changing this.)


The indexes are always in the exact order of the index key. Table rows
are only in order of an index after a reorg (if you specify an index to
be used in the reorg, or the table has a clustered index defined).


And even that does not imply _any_ order for a simple SELECT ... FROM
<table>. The _only_ way to enforce a specific ordering is the ORDER BY
clause.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 6 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Nomen Nescio | last post: by
9 posts views Thread by Ed_No_Spam_Please_Weber | last post: by
31 posts views Thread by Greg Scharlemann | last post: by
2 posts views Thread by John Holmes | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.