Automic Workload Automation

  • 1.  Querying for skipped tasks (due to outage)

    Posted Aug 28, 2018 05:12 PM

    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?



  • 2.  Re: Querying for skipped tasks (due to outage)

    Posted Aug 29, 2018 04:46 PM

    Okay, I've refined the query a little bit more, so that it will now attempt to capture tasks that may have been skipped in either schedules or tasks from "completed" schedules -- i.e., ones that have done their turnaround in the past hour.

    For those schedules that are "completed", I'm skipping over those if I show that the current/active version of that schedule is in status 1563 ("STOP - Automatic processing has been stopped").  That doesn't guarantee that the completed schedule right before it was also stopped right before turnaround, but it seems like that should be a safe assumption most of the time:

    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;

    (Note that this was written for DB2; you may need to modify the query slightly to handle the date logic for Oracle or SQL Server.)

     

    Any other SQL junkies and/or Automic Developers care to weigh in on this?