Unable to delete extended calendar CAUAJM_E_18402 ORA-00001: unique constraint (AEDBADMIN.XPKUJO_CALENDAR) violated

book

Article ID: 221405

calendar_today

Updated On:

Products

CA Workload Automation AE - Scheduler (AutoSys)

Issue/Introduction

Unable to delete Extended calendar.  Calendar is not auto-generating new dates so was going to delete and re-add. 

Tried to regenerate and got this error:

Enter Calendar Name: ML_CALENDAR_MONTH_END
CAUAJM_I_50435 Regenerate dates Successful for Extended Calendar: ML_CALENDAR_MONTH_END
CAUAJM_E_18416 Event Server: <AUTOPA2P>  Failed Query: <INSERT INTO ujo_calendar (name,cal_id,day) VALUES ('ML_CALENDAR_MONTH_END',2,TO_DATE('08/31/2021 00:00','MM/DD/YYYY HH24:MI'))>
CAUAJM_E_18402 ORA-00001: unique constraint (AEDBADMIN.XPKUJO_CALENDAR) violated

CAUAJM_I_18403 Processing OCI function OEXEC, OEXN(3)
CAUAJM_E_18400 An error has occurred while interfacing with ORACLE.
CAUAJM_E_18401 Function <doExecute> invoked from <execute> failed <908>
CAUAJM_E_18416 Event Server: <AUTOPA2P>  Failed Query: <INSERT INTO ujo_calendar (name,cal_id,day) VALUES ('ML_CALENDAR_MONTH_END',2,TO_DATE('08/31/2021 00:00','MM/DD/YYYY HH24:MI'))>
CAUAJM_E_18402 ORA-00001: unique constraint (AEDBADMIN.XPKUJO_CALENDAR) violated

checked the DB and see this..

QL> select cal_id,name from ujo_calendar where name ='ML_CALENDAR_MONTH_END';

    CAL_ID NAME
---------- ----------------------------------------------------------------
         0 ML_CALENDAR_MONTH_END
         0 ML_CALENDAR_MONTH_END
         0 ML_CALENDAR_MONTH_END
         0 ML_CALENDAR_MONTH_END
         0 ML_CALENDAR_MONTH_END
         0 ML_CALENDAR_MONTH_END
         0 ML_CALENDAR_MONTH_END
         0 ML_CALENDAR_MONTH_END
         0 ML_CALENDAR_MONTH_END
         0 ML_CALENDAR_MONTH_END
         0 ML_CALENDAR_MONTH_END

    CAL_ID NAME
---------- ----------------------------------------------------------------
         0 ML_CALENDAR_MONTH_END

12 rows selected.

 

 

Deleted successfully:

>autocal_asc -D -e ML_CALENDAR_MONTH_END
CAUAJM_I_50427 Delete Successful for Extended Calendar: ML_CALENDAR_MONTH_END

 

Tried to re-add, and got this..

autocal_asc -I ML_CALENDAR_MONTH_END
CAUAJM_E_10601 Cannot insert calendar ML_CALENDAR_MONTH_END, calendar name already exists.
CAUAJM_I_50424 Insert Failed for Extended Calendar: ML_CALENDAR_MONTH_END

 

Tried with -F and got this:

autocal_asc -I ML_CALENDAR_MONTH_END -F
CAUAJM_E_10518 Failed to modify extended calendars, calendar does not exist.
CAUAJM_I_50424 Insert Failed for Extended Calendar: ML_CALENDAR_MONTH_END

 

Tried to delete again, and got this:

autocal_asc -D -e ML_CALENDAR_MONTH_END
CAUAJM_I_50428 Delete Failed for Extended Calendar: ML_CALENDAR_MONTH_END
CAUAJM_E_50309 Extended calendar ML_CALENDAR_MONTH_END does not exist.

And the above DB entry is still in place after deleting.  How can we get this removed and how will it affect jobs that are tied to this calendar??

Calendar Def:

Extended

extended_calendar: ML_CALENDAR_MONTH_END
description: "Last business day of the month.  Run previous business day if a holiday."
workday: mo,tu,we,th,fr
non_workday:
holiday: P
holcal: ML_HOLIDAY
cyccal:
adjust: 0
condition: EOMWORK

The standard calendar that was generated:

calendar: ML_CALENDAR_MONTH_END
description: "Last business day of the month.  Run previous business day if a holiday."
01/31/2021 00:00:00
02/28/2021 00:00:00
03/31/2021 00:00:00
04/30/2021 00:00:00
05/31/2021 00:00:00
06/30/2021 00:00:00
07/31/2021 00:00:00
08/31/2021 00:00:00
09/30/2021 00:00:00
10/31/2021 00:00:00
11/30/2021 00:00:00
12/31/2021 00:00:00

Environment

Release : 11.4

Component :

Resolution

The product should hot have allowed a standard and extended calendar to exist with the same names.  Our Sustaining Eng. team is working on code changes to better prevent that. If you know the extact process that was followed which allowed for two
calendars to be created with the same names please share that with us so our SE team can make sure to lock that process down as well, if it differs with the test cases they are already aware of.

To move forward, you will need to delete the calendar (both extended and standard) manually via the database and then add back what you need making sure each name is unique, regardless if they are different types.
And potentially resave job definitions if needed to get them back on schedule.

---
To delete the calendar(s) dates (for both) and definition (for the extended calendar) :
 delete from ujo_ext_calendar where name='ML_CALENDAR_MONTH_END';
 delete from ujo_calendar where name='ML_CALENDAR_MONTH_END';
---
Query the other extended calendars to see if any referenced the "ML_CALENDAR_MONTH_END" as its holiday calendar.  It the query does show any you will need to review that calendar and any jobs using that calendar as well.
 select name from UJO_EXT_CALENDAR where as_holcal='ML_CALENDAR_MONTH_END';
---
Query the ujo_jobst view to identify any jobs that reference the calendar "ML_CALENDAR_MONTH_END"
 select job_name from ujo_jobst where run_calendar='ML_CALENDAR_MONTH_END' or exclude_calendar='ML_CALENDAR_MONTH_END';
---