loan history - unique constraint error
- Article Type: General
- Product: Aleph
- Product Version: 16.02
Description:
We see this error in the pc_server log:
ORA-00001: unique constraint (xxx50.Z36H_ID2) violated
Oracle error: io_z36h_write
Why does this happen?
Resolution:
When an item is returned, the z36 loan record is converted into a z36h loan history record. The z36h_number comes from the z36_number (sequential loan number) of the z36.
This error occurs when the system tries to write a record with the same z36h_number as that in an existing z36h record.
The z36_number is assigned using the xxx50 util g/2 last-loan-number counter.
This means that at some point the last-loan-number has been been lower than it should have been.
It is only with version 16.02 that the system has required that the z36/z36h_number be unique.
Contact Ex Libris Support if you have this problem.
This is the procedure that we do in this cases:
The last-loan-number needs to be higher than any existing z36_number or z36h_number. You can do this SQL to get these values:
SQL> select max(z36_number) from z36;
SQL> select max(z36h_number) from z36h;
In this case we found that max(z36_number) was 4160830 and max(z36h_number), 4160829, while last-loan-number was 3625907.
Thus, the first thing we needed to do was to increase the last-loan-number (using util g/2) (to 4160900).
Then we used this SQL to see how many Z36's there were which had z36_rec_key's which, if left unchanged, would, when returned, generate the z36h unique constraint:
select count(*) from z36, z36h where z36_number = z36h_number and z36_rec_key ^= z36h_rec_key;
In this case, we found 806 with z36/z36h_numbers ranging from 200,000 - 260,000. We used SQL to alter these.
See also Knowledge Base records 5806 and 5871.
Additional Information
z36, z36h, unique constraint error
- Article last edited: 10/8/2013