• Article Type: General
• Product: Aleph
• Product Version: 2

Description:
In our last ETL p_arc_01 process (full ETL) we noticed some errors in the log. In the log file : abc50 $data_scratch/p_arc_01_transform.log:

PROCESS_CODE TASK_CODE ERROR_MESSAGE

8296 684 ORA-00001: unique constraint (.) violated
8296 684 ORA-00001: unique constraint (.) violated
8296 684 ORA-00001: unique constraint (.) violated
8296 1001 ORA-00001: unique constraint (.) violated
8296 1001 ORA-00001: unique constraint (.) violated
8296 1001 ORA-00001: unique constraint (.) violated
8296 1001 ORA-00001: unique constraint (.) violated
8296 1001 ORA-00001: unique constraint (.) violated
8296 1001 ORA-00001: unique constraint (.) violated

Resolution:
The "unique constraint violated" errors are coming from the process of copying the extracted values from Aleph into the "STG_" tables. The extract process gets information from Aleph libraries as well as Aleph tables and loads it into ARC tables prefixed with "MRR_" (for mirror). These tables allow for duplicate keys to be inserted, as they are never used for reports. However, when the records in the MRR tables are loaded into the STG tables, there can be no duplicates. So, for each duplicate value in the MRR tables, there will be an error message that says that a unique constraint has been violated. In other words, the records must all have unique keys, but the process has tried to insert a duplicate key and that is not allowed.

The unique constraint errors from the 1001 step are all duplicate records that appear in your tab_base file. It appears that these bases are included more than once (BC, LOC, OHIOLINK, SIMON, and OXFORD (3 times)). If you remove these duplicate entries, I believe those errors will be eliminated. The errors from the 684 step are related to Budget Groups.

I have not yet been able to figure this out completely, but it appears that perhaps you have Budget Groups such as MON, SER and OTH that have names in both English and French. I'm not sure that there is anything you would want to do about that or if eliminating those would even make a difference. In any case, these particular errors are completely unimportant and can be ignored. The fact that a duplicate record exists means that there is at least one record in ARC for each defined Budget Group and each defined Base, so you won't miss any data no matter what.

• Article last edited: 10/8/2013