Here's a query we've developed to identify all the tasks that were skipped during an outage so that we'll know which schedules to look at and which tasks to reset:
-- Version 4: catch skipped tasks from a schedule due to an outage
-- * Catches tasks that were skipped in an active schedule, or in a schedule that just turned around in the last hour (configurable in red).
-- * Flters out tasks from active schedules that are stopped (STOP - Automatic processing has been stopped).
-- * Filters out tasks from schedules that just turned around if the active instance of that same schedule is stopped (STOP - Automatic processing has been stopped).
-- * Lists only tasks that were skipped within the last hour (configurable in red)
select ah_client as client,ah_name as schedule,eh_status||' (ACTIVE)' as sched_status,ejpp_object as skipped_task,
substr(varchar_format(EJPP_STARTTIME + CURRENT TIMEZONE,'MM-DD-YYYY HH24:MI:SS'),1,24) as start_time,
substr(varchar_format(EJPP_ENDTIME + CURRENT TIMEZONE,'MM-DD-YYYY HH24:MI:SS'),1,24) as end_time
from ah,ejpp,eh
where ah_otype='JSCH'
and ejpp_status=1941
and ah_idnr=ejpp_ah_idnr
and eh_otype='JSCH'
and eh_ah_idnr=ah_idnr
and eh_status<>1563
and (ejpp_starttime + current timezone) > current timestamp - 1 hour
union all
select ah_client as client,ah_name as schedule,ah_status||' (ended)' as sched_status, ajpp_object as skipped_task,
substr(varchar_format(AJPP_STARTTIME + CURRENT TIMEZONE,'MM-DD-YYYY HH24:MI:SS'),1,24) as start_time,
substr(varchar_format(AJPP_ENDTIME + CURRENT TIMEZONE,'MM-DD-YYYY HH24:MI:SS'),1,24) as end_time
from ah, ajpp
where ah_otype='JSCH'
and ah_idnr in (select ah_idnr from ah,eh where ah_otype='JSCH' and eh_otype='JSCH' and ah_name=eh_name and ah_client=eh_client and eh_status<>1563 and ah_timestamp4 > current timestamp - 1 hour)
and ah_name not in (select ah_name from ah)
and ah_idnr=ajpp_ah_idnr
and ajpp_status=1941
and (ajpp_starttime + current timezone) > current timestamp - 1 hour
ORDER BY 1,5,2,4;
- We're using a DB2 database; you may need to tweak some of the date-related syntax in this query if you're using Oracle or SQL server.
- We developed this in v9, as we're not yet up to v12. Hopefully someone here can validate if the query still works.