AAI not fully initializing after startup, and is stuck running the same queries over and over again.

Products

• Automic Automation Intelligence

Issue/Introduction

AAI not fully initializing after startup, and is stuck running the same queries over and over again.

During startup log shows 

WARN [CurrentStateVendorImpl] Invalid data found in CurrentState table.  Will attempt fixup.

INFO  [QueryTimingService] <80> "from CurrentStateThin where mostRecentRunId is not null"

 

QueryTimingService messages repeatedly show:

 INFO [QueryTimingService] 100.0% of last 0:01:00 spent in DB/API operations (possibly on multiple threads/overlapping queries)
  100.0%: 8x (avg. time: 7.501) query: select id from JobRun where id in (:ids)

Environment

Release : 6.0.2

Component : AUTOMIC AUTOMATION INTELLIGENCE ENGINE

Resolution

There are some known data inconsistency scenarios sometimes occur.

If you should run into this scenario run the select queries below. If any queries return a non zero result, run the update or delete statements listed below them.

Only start up services after all the select queries return 0.



1. References to missing parent job runs

select s.schedulerName, count(*) from JobRun jr, JobScheduler s where jr.parentJobRunId not in (select jobRunId from JobRun) and jr.jobSchedulerId = s.jobSchedulerId group by s.schedulerName

-- resolution

update JobRun

set parentJobRunId = null

where parentJobRunId not in (select jobRunId from JobRun)

2. References to missing predecessor runs

select count(*) from JobRun_PredJobRun where predJobRunId not in (select jobRunId from JobRun)

--resolution

delete from JobRun_PredJobRun

where predJobRunId not in (select jobRunId from JobRun)

3. Missing successor job runs

select count(*) from JobRun_PredJobRun where jobRunId not in (select jobRunId from JobRun)

-resolution

delete from JobRun_PredJobRun

where jobRunId not in (select jobRunId from JobRun)

4. Missing job runs in jobstream runs

select count(*) from JobStreamRun_JobRun where jobRunId not in (select jobRunId from JobRun)

--resolution

delete from JobStreamRun_JobRun

where jobRunId not in (select jobRunId from JobRun)

5. Missing job run references in recent state

select count(*) from RecentStateType where jobRunId not in (select jobRunId from JobRun)

 

--resolution

update RecentStateType

set jobRunId = null

where jobRunId not in (select jobRunId from JobRun)

 

6. Invalid dependent job condition reference

select count(*) from JobCondition where dependantJobId not in (select jobId from JawsJob)

--resolution

select s.schedulerName, jj.jobId, jj.jobName from JawsJob jj, JobScheduler s where jj.jobName LIKE 'missing%' and jj.jobSchedulerId = s.jobSchedulerId

update JobCondition

set dependantJobId = 'MISSING_JOB_ID for appropriate SCHEDULER'

where dependantJobId not in (select jobId from JawsJob)