db2 index broken

Jan Rabaut

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?



[login to unmask email]

Re: db2 index broken
(in response to Jan Rabaut)
Hi,

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]

Re: db2 index broken
(in response to Mike_Levine@TEKHELP.NET)
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.

I hope it helps

Thanks
Sanjeev




Jan Rabaut <[login to unmask email]>@RYCI.COM> on 01/06/2000 08:43:26 PM

Please respond to [login to unmask email]

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
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?








[login to unmask email]

Re: db2 index broken
(in response to ssethi@LOT.TATASTEEL.COM)
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!

Kevin Mulcahy
CheckFree Corporation



Richard A Yevich

Re: db2 index broken
(in response to kmulcahy@CHECKFREE.COM)
Jan,

Indexes the determine the order into the DB2 engine.

ORDER BY determines the order returned to you.

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]
>