The AAI Converter is failing: SchedulerUpdate failed in the PRE

Products

Automation Analytics & Intelligence

Issue/Introduction

The AAI Converter is failing on Autosys scheduler with the error message below.

024-03-15 08:30:58,804 INFO [AdminOperationQueuePolicy] task finished: DOWNLOAD_SCHEDULER
2024-03-15 08:30:58,805 INFO [JawsException] Exception returned to the client: com.termalabs.common.JawsException: com.termalabs.common.JawsException: com.termalabs.server.domain.api.SchedulerUpdate$UpdateFailedException: SchedulerUpdate failed in the PRE_UPDATE phase
2024-03-15 08:30:58,808 INFO [JawsException] Wrapped exception not returned to the client: com.termalabs.common.JawsException: com.termalabs.server.domain.api.SchedulerUpdate$UpdateFailedException: SchedulerUpdate failed in the PRE_UPDATE phase
at com.termalabs.server.cache.scheduler.SchedulerUpdateImpl.commit(SchedulerUpdateImpl.java:324)
at com.termalabs.server.cache.scheduler.SchedulerUpdateImpl.commit(SchedulerUpdateImpl.java:298)
at com.termalabs.server.app.scheduler.SchedulerHelper.convertScheduler(SchedulerHelper.java:259)
at com.termalabs.server.app.scheduler.SchedulerHelper.downloadScheduler(SchedulerHelper.java:211)
at com.termalabs.server.app.scheduler.SchedulerHelper.downloadNow(SchedulerHelper.java:159)
at com.termalabs.server.ejb3.SchedulerDataManagerEJB.download(SchedulerDataManagerEJB.java:586)
... 12 skipped
at com.termalabs.server.ejb3.RequestScopeEJB3Interceptor.invoke(RequestScopeEJB3Interceptor.java:94)
... 68 skipped
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:750)
Caused by: com.termalabs.server.domain.api.SchedulerUpdate$UpdateFailedException: SchedulerUpdate failed in the PRE_UPDATE phase
... 90 in common
Caused by: com.termalabs.common.datastore.DataAccessException: Flush failed
at com.termalabs.common.connection.hibernate.GenericSessionImpl.timedCommit(GenericSessionImpl.java:389)
at com.termalabs.common.connection.hibernate.GenericSessionImpl.commit(GenericSessionImpl.java:254)
at com.termalabs.server.cache.scheduler.SchedulerUpdateImpl.saveObjects(SchedulerUpdateImpl.java:561)
... 89 in common
Caused by: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:114)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109)
at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:244)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2372)
at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2325)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2625)
at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:115)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1028)
... 92 in common
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (JAWS_ADMIN.XAK1JAWSJOB) violated

Environment

Product: Automation Analytics & Intelligence v6.5.3 or lower.

Cause

Product defect

Resolution

Please note that if the issue occurred before AAI v6.5.3 HF1, then please perform the steps in the Workaround section and then upgrade to v6.5.3 HF1 to avoid duplicates to be inserted into AAI database.

Workaround

The remediation SQL scripts needs to be executed as explained below.

Step 1: To find if there's any duplicate Autosys jobs within the AAI database. Please replace <SCHEMA_OWNER> witht he actual schema owner.

--duplicate jobName, schedulerId, parentId
select jj.jobName, s.schedulerName, jj.jobSchedulerId, jj.parentJobId, count(*) as Count
from <SCHEMA_OWNER>.JawsJob jj, <SCHEMA_OWNER>.JobScheduler s
where s.jobSchedulerId = jj.jobSchedulerId
and (s.schedulerType = 'autosys_v2' or s.schedulerType = 'autosys')
group by jj.jobName, s.schedulerName, jj.jobSchedulerId, jj.parentJobId
having count(*) > 1
order by jj.jobName, jj.jobSchedulerId, jj.parentJobId;

For example:

jobName	schedulerName	jobSchedulerId	Count
TEST1	AutosySchedulerName	AY2OvbTERmU=	2
TEST2	AutosySchedulerName	Y2OvbTERmU=	2

Step 2: In this case, the jobs TEST1 and TEST2 have duplicates as shown in the Count column. The next step will be to to find the extID(Autosys) for each jobName above.

--All reference extId for duplicate jobs from above query
select s.schedulerName, jj.jobSchedulerId, jj.jobId, jj.jobName, jj.parentJobId, jj.extId, jj.deletedTime
from <SCHEMA_OWNER>.JawsJob jj, <SCHEMA_OWNER>.JobScheduler s
where jj.jobName IN
('TEST1','TEST2')
and jj.jobSchedulerId = s.jobSchedulerId
order by jj.jobName, jj.extId;

For example:

schedulerName	jobSchedulerId	jobID	JobName	parentJobId	extId	deletedTime
AutosySchedulerName	AY2OvbTERmU=	AY2PnWVyRXE=	TEST1		1832423
AutosySchedulerName	AY2OvbTERmU=	AY2PnVnzXeU=	TEST1		822479
AutosySchedulerName	AY2OvbTERmU=	AY2PnXeYGxs=	TEST2		1832424
AutosySchedulerName	AY2OvbTERmU=	AY2PnXaSeQU=	TEST2		822484

Based on the result, the jobs TEST1 and TEST2 have each duplicate and the difference is with their extID. The jobs with lower value of extId is an old one and they need to be renamed. Please proceed to Step 3.

Step 3: Build update JawsJob statements for the smaller number extId from Step 2 . One for each duplicated job name.

-Stop AAI

-Backup AAI database

-Execute the SQL query below. Please note that the jobname is updated with -DELETED string.

update JawsJob
set jobName = 'TEST1-DELETED'
where jobId = 'AY2PnVnzXeU=' and extId = '822479';

commit;

update JawsJob
set jobName = 'TEST2-DELETED'
where jobId = 'AY2PnXaSeQU=' and extId = '822484';

commit;

-Run the SQL in Step 1 to see if there is no more duplicates.

--duplicate jobName, schedulerId, parentId
select jj.jobName, s.schedulerName, jj.jobSchedulerId, jj.parentJobId, count(*) as Count
from <SCHEMA_OWNER>.JawsJob jj, <SCHEMA_OWNER>.JobScheduler s
where s.jobSchedulerId = jj.jobSchedulerId
and (s.schedulerType = 'autosys_v2' or s.schedulerType = 'autosys')
group by jj.jobName, s.schedulerName, jj.jobSchedulerId, jj.parentJobId
having count(*) > 1
order by jj.jobName, jj.jobSchedulerId, jj.parentJobId;

-If there's no duplicate then please start AAI and perform download(Converter) manually from the Web UI. If there's still some duplicates, please follow Step 2 and Step 3.