CODEWITHSUNDEEP
db2unique-constraintrowid
Jens Schauder
Jens Schauder

Reputation: 81862

How to get existing record on a unique key violation

when performing an insert I get the following error message

AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE INDEX IN INDEX SPACE xxxxx CONSTRAINS 
COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. 
RID OF EXISTING ROW IS X'0000000A20'.. SQLCODE=-803, SQLSTATE=23505, DRIVER=3.53.95

How can I do a select to get the row that already exists in the database?

Platform is z/OS; SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1 returns DSN09015 so I guess thats Version 9

Upvotes: 1

Views: 8880

Answers (3)

escartefigue
escartefigue

Reputation: 1

To find the row corresponding to the RID, you may use this request :

select * from my_table as XXX where RID(XXX) = nnnnn ;

with nnn = decimal conversion of RID hex value.

But be carefull that the RID value is altered by reorganization. So this request is only valid if no reorg as been run !

Upvotes: 0

Salva
Salva

Reputation: 11

You should consider the utility DSN1PRNT which helps you to read db2 zos data-pages in native format (even data are compressed). Read the SYSPRINT and find your Datas.

Keep in mind that the RID is an address in this file : X'PPPPPPPPII'

If the RID is: X'0000000A20' then the page number is X'0000000A' and the Id of the row ix x'20' (x'20' is 32 decimal ==> 32th row in the page) find the address of the 32th row with the ID-MAP table in SYSPRINT and enjoy your datas.

Upvotes: 1

woita
woita

Reputation: 41

X'0000000A20' is Hexadecimal format. You can translate it with Windows calc. 

A20 = 2592

So you can do SELECT * FROM table_name where RID(table_name) = 2592 and you will get what you need

Upvotes: 4

Related Questions