Data of table not sorted by primary index in Control Center

[Antanas]

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?

[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,

[m0002a]

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).

[Knut Stolze]

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