VIR01.Z200_ID unique constraint errors

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

Article Type: General
Product: Aleph
Product Version: 20

Description:
We are seeing numerous(128 in the last 10 days) errors of the following in our pc_server logs:

ORA-00001: unique constraint (VIR01.Z200_ID) violated

I looked at our version 19 logs and the error was not present there. I do not find the error in other logs and it is only present in version 20.

In searching the KB I do not see any reference to this error. There is a LAST_RESULT_SET sequence that has been growing, although it is not very large.

We run clear_vir01 in the hot mode Monday through Friday. On Saturday we do a clear_vir01 as part of our cold backup that rebuilds the VIR01 library.

What are the consequences of these errors?

We are seeing occasions where the Aleph pc client crashes about the time of the errors, but we do not know if they are related, nor can we replicate the crashes with regularity.

Resolution:
z200 is a temporary table in the vir01 library related to ILL processing. The z200_id Oracle index is built on the z200_rec_key. The unique constraint error for z200_id indicates that the ./com_io/io_z200 program has tried to write a z200 record with the same z200_rec_key as an existing z200.

The value of the z200_rec_key is acquired from the Oracle "last_result_set" sequence in the ADM library.

The ./com_io/io_z200 program does this:

WHEN "WRITE"
WHEN "WRITEX"
PERFORM GET-SET-NUMBER
...
CALL "io_z200_write" USING
...
...
GET-SET-NUMBER SECTION.
GET-SET-NUMBER-0.

MOVE "last_result_set" TO L-SEQ-NAME.
CALL "io_sequence_next" USING
SET-LIBRARY
L-SEQ-NAME
L-SEQ-NUMBER
ERROR-CODE
END-CALL
MOVE L-SEQ-NUMBER TO Z200-SEQUENCE.

(Note: Z200-SEQUENCE = Z200-REC-KEY.)

In spite of these messages the count of z200's *is* increasing.

This issue has been sent to 2nd-line Support for analysis.

There does *not* seem to be any connection between the client crashes and the Z200_ID unique constraint errors.

This issue is still open and was escalated to Development for further investigation <2011-02-20 01:00:05>.

Article last edited: 10/8/2013