Error Resolution - CAUAJM_E_18402 ORA-00001: unique constraint

Products

• CA Workload Automation AE - Business Agents (AutoSys)
• CA Workload Automation AE - Scheduler (AutoSys)
• CA Workload Automation Agent

Issue/Introduction

In the event_demon log, we are getting the following Oracle error messages:

 

[11/08/2016 12:00:00] CAUAJM_E_18416 Event Server: <ATSYS> Failed Query: <BEGIN :RetVal := ujo_batch_move_event (:I_time, :B_count); END; <<1478606400>>> 

[11/08/2016 12:00:00] CAUAJM_E_18402 ORA-00001: unique constraint (AEDBADMIN.XPKUJO_PROC_EVENT) violated 

[11/08/2016 12:00:00] ORA-06512: at "AEDBADMIN.UJO_BATCH_MOVE_EVENT", line 33 

[11/08/2016 12:00:00] ORA-06512: at line 1 

[11/08/2016 12:00:00] CAUAJM_I_18403 Processing OCI function ODEFIN(34) 

[11/08/2016 12:00:00] CAUAJM_E_18400 An error has occurred while interfacing with ORACLE. 

[11/08/2016 12:00:00] CAUAJM_E_18401 Function <doExecute> invoked from <nextRow> failed <873>

Cause

There is an existing event ID (eoid) in the ujo_proc_event table. While processing events, the database is trying to assign an event an eoid that already exists. 

Environment

All Supported CA Workload Automation AE versions

Resolution

------------------------------------------------------

1. Identify duplicate events in the ujo_proc_event table

------------------------------------------------------

SELECT p.eoid FROM ujo_proc_event p, ujo_event e WHERE e.eoid=p.eoid;

 

------------------------------------------------------

2. Identify the job id from the duplicate events in the ujo_proc_event table

------------------------------------------------------

SELECT eoid, joid FROM ujo_proc_event WHERE eoid = '<actual_eoid>';

 

------------------------------------------------------

3. Find the job name associated with the job id from the ujo_proc_event table

------------------------------------------------------

SELECT job_name FROM ujo_job WHERE joid = '<actual_joid>';

 

------------------------------------------------------

4. Remove duplicate events from the ujo_proc_event table

------------------------------------------------------

 

DELETE FROM ujo_proc_event WHERE eoid='<actual_eoid>;

 

------------------------------------------------------

5. commit

 ------------------------------------------------------

6. Restart the Scheduler

 ------------------------------------------------------