Querying for skipped tasks (due to outage)

Discussion created by daryl.brown_ACI on Aug 28, 2018
Latest reply on Aug 29, 2018 by daryl.brown_ACI

We recently had a brief outage (we inadvertently rebooted the AE server running the PWP before we could stop it cleanly), so I've now been tasked with figuring out how to identify the tasks we've missed.


I've come up with this query to identify the skipped workflows from the past hour from schedules that weren't themselves stopped at the time (status 1563)...


select ah_client as client,ah_name as schedule,eh_status as sched_status,ejpp_object as skipped_task,
substr(varchar_format(EJPP_ERLSTSTTIMET + CURRENT TIMEZONE,'MM-DD-YYYY HH24:MI'),1,20) as sched_time,
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
ORDER BY 1,2,5;


...but I think this leaves out cases where the outage spanned the turnaround time of any schedules.  In these cases -- assuming the schedules did their turnaround properly once the AE came back up -- you'd be left with some "ENDED_TIMEOUT - Start time exceeded" tasks in the previous instance of that schedule.  This means I'd need to be querying the AJPP table instead of the EJPP table, but I have yet to find a good way to build such a query that doesn't take forever to run.


Anyone have a good solution for this?