How to monitor jobs with bad status values in History.

Document ID : KB000088219
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
How to monitor jobs with bad status values in History.
Resolution:

Summary:   

We would like to make sure that there are no jobs in the Backlog with Bad Status values. This can cause performance issues over-time and also cause some Master/RmiServer problems.


Prevention/Maintenance:

- Create a character subvar called COUNT_BAD_JOBS with the following SQL:

select to_char(count(*)) from so_job_history where so_status_name in ('INITIATED','RUNNING','STAGED','STAGED_PW','QUEUE WAIT','PRED WT HOLD','PRED WAIT','LAUNCH ERROR','KILLING','DATE PENDING','CONDITN WAIT','AGENT WAIT')

- Create a Notification called COUNT_BAD_JOBS with the following settings:

Frequency - First Time
Status Trigger - Condition
Custom Text - You have {#count_bad_jobs} bad job(s) in the so_job_history table.
Subject - Fix jobs with bad statuses in History
Email Recipients - Your email address

- Create a copy of TEST_JOB and call it COUNT_BAD_JOBS. Set the job to run on the Master's Agent (as this is generally the most reliable of the Agents). Set the condition below on the job:

Timing - After
Condition - User Defined

If {#count_bad_jobs} != 0

Then Send Notification COUNT_BAD_JOBS

- Set a schedule on the job to run the job hourly or as frequently as required/needed.