AAI Converter fails for Autosys Scheduler due to duplicate GlobalVariable names in Autosys, one with leading space, one without

book

Article ID: 210564

calendar_today

Updated On:

Products

Automic Automation Intelligence

Issue/Introduction

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 (JAWS.XAK1GLOBALVARIABLE) violated

WARN  [JDBCExceptionReporter] SQL Error: 1, SQLState: 23000
ERROR [JDBCExceptionReporter] ORA-00001: unique constraint (JAWS.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 (JAWS.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

 

 

Environment

Release : 6.0.1

Component : AUTOMIC AUTOMATION INTELLIGENCE ENGINE

Resolution

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.