AppWorx, Dollar Universe and Sysload Community

How to take action on Jobs that have been running longer than twice it's average runtime.

  • 1.  How to take action on Jobs that have been running longer than twice it's average runtime.

    Posted 01-15-2018 02:29 PM
    One our customers had a request on how to take action, such as sending a notification, on Jobs that have been running longer than twice it's average runtime. After some investigation, I was able to do this in a condition by doing the following:

    *Requirements*
    The average runtime value we are using is stored in each Jobs' definition which is populated by the Job CALC_AVG_RUN. This Job is required to run before average runtime values are stored. More information on running CALC_AVG_RUN at the documentation link below:


    Once Jobs have their average runtime populated, create a subvar called #twice_avg_run_time or whatever name you want with the following sql:

    SELECT TO_CHAR(so_avg_runtime+so_avg_runtime, 'fm00G00G00',
                   'NLS_NUMERIC_CHARACTERS=''.:''') from so_job_table where so_module = '{task}'

    The first part of the sql (so_avg_runtime+so_avg_runtime) is average runtime added to average runtime or 2x average runtime.
    The second part of the sql ('fm00G00G00', 'NLS_NUMERIC_CHARACTERS=''.:''') converts the number in seconds to a compatible time format that we can use (HH:MMSS).

    After your Subvar has been created, setup the Condition as follows:

    241uil5nmz7k.png

    If done properly, if a Job whose average runtime is 1 hour, then a notification will be sent out if the Job is still running after 2 hours.