AAI Converter fails for Autosys Scheduler due to duplicate GlobalVariable names in Autosys, one with leading space, one without.
Errors in jaws.log and hibernate.log will show constraint errors like below:
WARN [JDBCExceptionReporter] SQL Error: 1, SQLState: 23000
ERROR [JDBCExceptionReporter] ORA-00001: unique constraint (XAK1GLOBALVARIABLE) violated
WARN [JDBCExceptionReporter] SQL Error: 1, SQLState: 23000
ERROR [JDBCExceptionReporter] ORA-00001: unique constraint (XAK1GLOBALVARIABLE) violated
ERROR [AbstractFlushingEventListener] Could not synchronize database state with session: org.hibernate.exception.ConstraintViolationException: could not update: [com.termalabs.common.hibernate.jaws.GlobalVariable#UUID[XXXXXXXX=]]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2443)
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)
at com.termalabs.common.connection.hibernate.GenericSessionImpl.timedCommit(GenericSessionImpl.java:377)
at com.termalabs.common.connection.hibernate.GenericSessionImpl.commit(GenericSessionImpl.java:254)
at com.termalabs.server.cache.scheduler.SchedulerUpdateImpl.saveObjects(SchedulerUpdateImpl.java:561)
at com.termalabs.server.cache.scheduler.SchedulerUpdateImpl.commit(SchedulerUpdateImpl.java:314)
at com.termalabs.server.cache.scheduler.SchedulerUpdateImpl.commit(SchedulerUpdateImpl.java:298)
at com.termalabs.server.system.ConverterService.runConverterForScheduler(ConverterService.java:381)
at com.termalabs.server.system.ConverterService.convertScheduler(ConverterService.java:310)
at com.termalabs.server.system.ConverterService.run(ConverterService.java:227)
at com.termalabs.server.system.ConverterService$2.run(ConverterService.java:172)
at java.util.TimerThread.mainLoop(Timer.java:555)
at java.util.TimerThread.run(Timer.java:505)
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (XAK1GLOBALVARIABLE) violated
at oracle.jdbc.driver.OraclePreparedStatement.executeLargeBatch(OraclePreparedStatement.java:9711)
at oracle.jdbc.driver.T4CPreparedStatement.executeLargeBatch(T4CPreparedStatement.java:1447)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9487)
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:237)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
at org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:56)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2421)
... 20 more
Release : 6.0 and later
Component : AUTOMIC AUTOMATION INTELLIGENCE ENGINE
You need to look for leading spaces in the GlobalVariables in AAI and Autosys.
Below are a summary of what we did to identify and resolve the issue.
1. The query below would sort by globalVariableName, which would bring anything with a leading space to the top of the list, replace the SCHEDULER_NAME with your scheduler name.
select s.jobSchedulerId, s.schedulerName, gv.*
from GlobalVariable gv, JobScheduler s
where s.schedulerName = 'SCHEDULER_NAME'
and s.jobSchedulerId = gv.jobSchedulerId
order by gv.globalVariableName;
You can also use a query like below to find all GlobalVariableNames with a Space either leading, trailing or in the middle. Only Leading or Trailing should be an issue, if there is another globalVariable with the same name.
select s.jobSchedulerId, s.schedulerName, gv.*
from GlobalVariable gv, JobScheduler s
where s.schedulerName = 'SCHEDULER_NAME'
and gv.globalVariableName like '% %'
and s.jobSchedulerId = gv.jobSchedulerId
order by gv.globalVariableName;
2. Once you have identified the GlobalVariableName with a leading space, look for a duplicate of that name in the query output above.
3. Once you have identified the duplicate, search Autosy for this variable using the autorep command like below using that GlobalVariableName in the command below:
autorep -G *DupicateVariableName*
4. Use this query to identify if there were any jobConditions tied to the duplicate GlobalVariable in the AAI database:
select * from jobcondition where globalvariableid in(select globalvariableid from globalvariable where globalvariablename like '%DupicateVariableName%')
5. Then get the dummyJaws ID for the scheduler in question with:
select s.schedulerName, gv.globalVariableName, gv.globalVariableId, jj.jobId, jj.jobName, jc.*
from JobScheduler s, GlobalVariable gv, JawsJob jj, JobCondition jc
where gv.globalVariableName = 'dummyJaws'
and gv.jobSchedulerId = s.jobSchedulerId
and s.schedulerName = 'SCHEDULER_NAME'
and gv.globalVariableId = jc.globalVariableId
and jc.jobId = jj.jobId;
6. Stop the AAI service.
7. Then use the DummyJaws value for globalVariableId from the query output of step 3, in the query below
update JobCondition
set globalVariableId = 'DummyJawsId='
where jobConditionId IN
(select jc.jobConditionId
from JobCondition jc
where jc.globalVariableId IN
(select gv.globalVariableId
from GlobalVariable gv, JobScheduler s
where gv.globalvariablename like '%DupicateVariableName%'
and s.jobSchedulerId = gv.jobSchedulerId));
commit;
8. Then delete the entries for that GlobalVariable from the GlobalVariable table in AAI with:
delete
from GlobalVariableValueChange
where globalVariableId IN
(select gv.globalVariableId
from GlobalVariable gv, JobScheduler s
where gv.globalvariablename like '%DupicateVariableName%'
and s.jobSchedulerId = gv.jobSchedulerId);
commit;
9. Attempt a "Download Now" and verify if it completes.