CA PPM: Scheduled jobs stuck in waiting or scheduled status

Document ID : KB000049034
Last Modified Date : 11/05/2018
Show Technical Document Details
Issue:
When a CA PPM job is running and is interrupted or the background service is stopped while a job is processing, records can get stuck in job scheduler tables. This causes the other jobs to think there is a job running or processing, when it is not. The scheduled jobs then will go into a waiting or scheduled status that is past the scheduled start and no other jobs will be processing.
 
Resolution:
The CA PPM Administrator has to perform the following steps:
 (For SAAS, step A and D should be done by customer, and B and C by the SAAS Support Team) 
A. Cancel and re-run the jobs
1. Go to Home > Reports and Jobs
2. Pause all jobs and reports in the following states: WAITING, SCHEDULED
3. Cancel all PROCESSING instances.
4. Cancel all NOT SCHEDULED instances.
5. Filter for all CANCELLED  jobs.
Make a note of the Cancelled jobs and take screenshots/notes of their schedule, as they will need to be re-entered at a later time.
6. Select and delete all CANCELLED instances.
7. Run an immediate instance of any fast running job such as Clean User Sessions job
 
If it does not go to the Processing status, delete the job and proceed to step B.
If it does go to the Processing status, proceed to step D.
 
 
B. Remove all possible orphan records and locks on the jobs
1. Stop all background services on the environment
2. Run the query:
 
select csj.id, csj.is_visible, csjd.is_active
from cmn_sch_jobs csj, cmn_sch_job_definitions csjd
where csj.job_definition_id = csjd.id
and (csj.is_visible = 0 or csjd.is_active = 0)
and csj.job_definition_id not in
(select id from cmn_sch_job_definitions
where upper(job_code) in
('JOB_CHECK_HEART_BEAT','BPM_ESC_ESCALATION','BPM_ESC_RESCHEDULE_ESC_JOB'))
and csj.status_code in ('WAITING', 'SCHEDULED','PROCESSING')
 
If no records are returned, then proceed to step 3.

If records are returned, then:
Run the attached scripts in file Scheduler_orphan_records_Removal.txt


3. Check for any locks on the scheduler with the query:
select * from prlock where prtablename = 'CMN_SCH_JOBS'
If any records are returned, then run the following SQL statement:
 
delete from prlock where prtablename = 'CMN_SCH_JOBS';

commit;


4. If on CA PPM 15.3 and higher, proceed with Step C, if not, start the Background services back
 
C. Clear the Scheduler Table for CA PPM 15.3 and higher 
For version 15.3 and higher, there is a new table that holds all currently processing jobs : CMN_SCH_JOB_CURR_RUNS
Sometimes the table can get out of sync, and here is what you can do to resolve it:
 
1.    Make sure Step A is performed (all processing jobs are stopped in UI )
2.    Stop the background services if not done already (as per Step B)
3.    Run the statement:
TRUNCATE TABLE CMN_SCH_JOB_CURR_RUNS
4.    Start the background services
 
 

D. Recreate the jobs in CA PPM
1. Re-enter the previously deleted 'Cancelled' jobs with their schedule.
2. Resume(unpause) all 'PAUSED' jobs and reports
File Attachments:
Scheduler_orphan_records_Removal.txt