Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    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
    • Was this article helpful?