Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    64

    Unanswered: RID and corresponding Row

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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".
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2004
    Posts
    64

    reply

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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There is no logic in the English language.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Apr 2004
    Posts
    64
    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!

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Apr 2004
    Posts
    64
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •