p_file_20: "unique constraint (ABC50.Z353_ID) violated"
- Article Type: General
- Product: Aleph
- Product Version: 20, 21, 22, 23
Description:
We've discovered a problem while testing PLIF loads into Version 18.
I ran p_file_20 in non-update mode and it revealed no errors. However, when I run p_file_20 with update = Y, I get the following error:
Oracle error: io_z353_write
ORA-00001: unique constraint (ABC50.Z353_ID) violated
Next, we looked for possible duplicates in Z353, since that seems to be what the error is indicating. We found that each patron (z353_id) seems to have 6 entries in the z353:
test18-NFTEST18-NFU50 >>fcsql "select z353_rec_key, z353_id from z353 where z353_id = 'NF17123'"
Z353_REC_KEY: BC 20300013867585
Z353_ID: NF17123
Z353_REC_KEY: ID NF17123
Z353_ID: NF17123
Z353_REC_KEY: NAME hooks prentice c NF17123
Z353_ID: NF17123
Z353_REC_KEY: NFU50 BC 20300013867585
Z353_ID: NF17123
Z353_REC_KEY: NFU50 ID NF17123
Z353_ID: NF17123
Z353_REC_KEY: NFU50 NAME hooks prentice c NF17123
Z353_ID: NF17123
6 rows selected.
We expected that the "library" field in the z353_rec_key would be blank, as is indicated by the z353 description for that field, since we have user sharing = Y and our usr_library is NFU50. However, from this it looks like there are two sets of records created in the index; one set for the Global Patron List without the "library" field data; and a second with the "library" field set to NFU50 for the Local Patron List - even though there is no distinction between Global and Local patrons for us.
After discovering this, I recalled that some patrons updated via the client "disappeared" from being able to retrieve them via name or id in the client patron list. Re-running p_cir_25 fixed that - which also seems to indicate a problem with the z353.
Resolution:
The message "Successed to REWRITE table z30x with modification restrictions" in the $data_scratch/p_file_20_report occurs for the same PLIF records that get the z353 unique constraint error.
The following grep shows that there are only 47 of the "Successed to REWRITE table z30x with modification restrictions" out of a total of 39620 updates:
> grep -c modification p_file_20_report
47
> grep -c Successed p_file_20_report
36920
The "Successed to REWRITE table z30x with modification restrictions" is message is related to the z303_plif_modification field. We see that patron NF00103039 is one of only 60 patrons who have a non-null z303_plif_modification field.
The following SQL will give you the z303 record keys whose z303_plif_modification value is not null.
SQL> select z303_rec_key, z303_plif_modification from z303 where z303_plif_modification is not null;
Here are the first 11 (of 60) results:
Z303_REC_KEY Z303_PLIF_MODIFICATION
------------ --------------------------------------------------
NF00016696 B D
NF00057236 A E
NF00060008 A
NF00077951 A
NF00087288 A
NF00089675 E
NF00090315 E
NF00092012 B
NF00103039 1
NF00103498 E
NF00104214 1
<etc.>
The z303_plif_modification field is described in the z303.pdf document as follows: "This field is used to determine which fields and/or records should be protected when updating borrower records using the PLIF - Patron Loader (p-file-20) service. The field can contain a list of codes delimited by a space."
Codes are:
A = Do not modify the borrower’s address records (Z304).
B = Do not modify the borrower’s status.
D = Do not modify the borrower’s expiry date.
E = Do not modify the borrower’s e-mail address (Z304-EMAIL-ADDRESS) in the address record (Z304).
1 = Do not modify all borrower’s records (Z303, Z304, Z305 and Z308).
I believe that all you need to consider is whether the z303_plif_modification is set correctly and, if it is, you can ignore any z353_unique_constraint messages for such patrons.
- Article last edited: 10/8/2013