db2 index broken

Jan Rabaut

db2 index broken

January 6, 2000 09:13 AM

We have a strange problem on new created indexspaces since 1 Jan
2000.

Situation: We created a type 2 index containing 2 columns as key
in
ascending order.
When we insert rows in random order in that table, we receive the
rows back
in random order
although we did a select via the index.

Only the reorg solves the problem, but new inserts gave again a
random
result of selection via the index
(which where again solved after doing a reorg).

When we tried to do the same on indexes created before 1 of Jan: no
problem
occurs, but when we drop
and re-create these index, we receive the rows in random order

[login to unmask email]

This is probably not a Y2K issue. If you want the rows back in
a
certain order use must code an ORDER BY clause. The ORDER BY
may
or may not cause a sort depending on the access method DB2
uses.

Regards,

Michael Levine
Permier Data Services, Inc.

>We have a strange problem on new created indexspaces since 1
Jan 2000.
>
>Situation: We created a type 2 index containing 2 columns as
key in
>ascending order.
>When we insert rows in random order in that table, we receive
the rows back
>in random order
>although we did a select via the index.
>
>Only the reorg solves the problem, but new inserts gave again a
random
>result of selection via the index
>(which where again solved after doing a reorg).
>
>When we tried to do the same on indexes created before 1 of
Jan: no problem
>occurs, but when we drop
>and re-create these index, we receive the rows in random
order
>
>Any help or remarks?
>
>
>

>
>

[login to unmask email]

Hi Jan,
U are selecting the rows via index but the possibility is that u
are
not selecting only the columns which are involved in index , i mean
the
scan might not be index only. if the scan is index only then i
think the
rows will be returned in the index order or the ASC order in ur
case.
In this case u must use ORDER BY clause if u need the rows to
be
returned in the order of index columns.If reorg solved ur problem i
think
LIST PREFETCH might also be done in ur case however it depends on
the
CLUSTER RATIO .
It is not the Y2K problem , it might be the case that ur insertion
order of
data has changed in year 2000 and it was uniform(same as index
order)
before year 2000 and table might be highly organised with good
cluster
ratio.

We have a strange problem on new created indexspaces since 1 Jan
2000.

Situation: We created a type 2 index containing 2 columns as key
in
ascending order.
When we insert rows in random order in that table, we receive the
rows back
in random order
although we did a select via the index.

Only the reorg solves the problem, but new inserts gave again a
random
result of selection via the index
(which where again solved after doing a reorg).

When we tried to do the same on indexes created before 1 of Jan: no
problem
occurs, but when we drop
and re-create these index, we receive the rows in random order

Any help or remarks?

[login to unmask email]

The only way to guarantee the order of an SQL result set is to use
the
ORDER BY clause. I suspect that without the ORDER BY the order of
your
result depends on the contents of the bufferpool. Try this -- when
you get
a random result stop/start the tablespace and run your query
again!

Richard A Yevich

If they match, wonderful! If they don't, DB2 sorts them to make
sure you
them correctly.

Regards,
Richard
+===+===+===+===+====+
[login to unmask email]

> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of
> Jan Rabaut
> Sent: Thursday, January 06, 2000 10:13 AM
> To: [login to unmask email]
> Subject: db2 index broken
>
>
> We have a strange problem on new created indexspaces since 1
Jan 2000.
>
> Situation: We created a type 2 index containing 2 columns as
key in
> ascending order.
> When we insert rows in random order in that table, we receive
the
> rows back
> in random order
> although we did a select via the index.
>
> Only the reorg solves the problem, but new inserts gave again
a random
> result of selection via the index
> (which where again solved after doing a reorg).
>
> When we tried to do the same on indexes created before 1 of
Jan:
> no problem
> occurs, but when we drop
> and re-create these index, we receive the rows in random
order
>
> Any help or remarks?
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The
owners of the list can be reached
> at [login to unmask email]
>