Hi,

I am using IBM DB2 Ver 7.1 on OS390

While re-building a unik index after the corresponding tablespace was recovered, the build failed due to duplicate key. It printed the RID of the duplicate row. Can someone tell me how to map this RID to a row in the table?

the error is,
DSNURBXC - ERROR LOADING INDEX, DUPLICATE KEY,
INDEX = DBDBDBDB.IXIXIX1,
TABLE = DBDBDBDB.TBTBT,
RID OF INDEXED ROW = X'0000051503',
RID OF NON-INDEXED ROW = X'00001FA30B'

The composite unik index is on two columns as follows.

AAAA integer (4)
BBBB timestamp (10)

How to locate the corresponding rows of these RIDs in the table?

Amar

You can find the offending keys, and then later select the entire rows with the following query:

select aaaa, bbbb, count(*)
from table-name
group by aaaa, bbbb
having count(*) > 1

BTW unik is spelled "unique".

Thanks Marcus!
I did that. In fact my query was something like

select a.count from
(select aaaa, bbbb, count(*) as count
from table-name
group by aaaa, bbbb ) a
where a.count > 1

I know that 'having' clause is much better...but my query gave result as 0. Since I was running short of time, I unloaded the table and did a load replace and let it discard the two dup records.

I will analyze more of them on monday and get back...But isn't there a way to use those RIDs?
I mean why are they printed in that error message if we can't ever use them?

About this 'unik', I think I came across it some where and seemed more logical, in the same line as 'color' instead of 'colour'.

There is no logic in the English language.

I found why for me that query was giving result as zero.

When the rebuild index failed with duplicate records, I tried that query. The query failed with index not available.

So I made that index into RW status forcefully. Now query succeeded but gave zero. I think this is because the index was not completely built, and hence could not identify the other record which was duplicate and not part of index.

But I am still wondering if there is a way to use ROWID to get the rows.

And yes, there is nothing logical about english language and for that matter any language. Instead I should have written, 'unik' is more user friendly, especially for new users and any curious old user as well!

I would drop the index, and then run the query.

Since "unique" is a DB2 reserved word, I would use it as it is correctly spelled when referring to indexes and you ask for help on a DB2 forum. It took me a long time to figure out what you were talking about.

Yes, I think dropping the index is a good idea. Thanks!

And I am sorry that unik caused confusion. I shd. have realised that 'unique' is a db2 key word apart from an English word.

Amar