Since V11.2, ONE Automation has a cool new feature: Thanks to Zero Downtime Upgrades you can upgrade your Automic System without service interruptions. But this does not mean that you’ll get away without any downtime at all because there‘ s not only Automic Software to be updated.

When you upgrade software which is being orchestrated by ONE Automation, and the software has to be stopped for that, you normally would also stop the related processes in ONE Automation. For example, the client or the queue.

Afterwards, when the software runs again, you will also restart the client or the queue. All tasks, which had been scheduled during the downtime, will receive the status: ENDED_TIMEOUT.

After such a downtime, it would be useful to have a list of all tasks which should have started during the downtime.

In this article I will concentrate on tasks that were planned in the Scheduler. I will not be dealing with Period Containers and Events.

Three steps are necessary to find tasks that were scheduled at a specific time but were not executed:

  1. Identify tasks that were scheduled for the related time period and ended with status ENDED_TIMEOUT
  2. Analyze whether there are calendar conditions which would have prevented the execution of the task anyway
  3. Connect steps 1 and 2

So, here we go.

 Identify Tasks That Were Never Started

To begin with, we look for all the tasks that had been scheduled but were never started. For this we need two tables: EH (Executive Header) and EJPP (Jobplan process – active).

The type of activity is stored in column EH_Otype in EH. We are looking for jobs planned in the Scheduler; therefore, the EH_Otype has to be JSCH.

The actual status of the job is found in column EJPP_Status in EJPP. We are looking for ENDED_TIMEOUT, which is equal to Code 1941.

Therefore, our query is searching for tasks with the EJPP_Status 1941 and the EH_Otype “JSCH“. From the identified lines we need the name of the client (EH_Client), the name of the schedule (EH_Name) and the name of the tasks (EJPP_Object).

For later we will also need the scheduled start time for the tasks. We get that via EJPP_ErlstStTimeT Daraus ergibt. Attention: stated time is in UTC.

This results in the following SQL Query:

SELECT EH_Client, EH_Name, EJPP_Object, EJPP_ErlstStTimet, EJPP_CCType, EJPPC_CaleName, EJPPC_CaleKeyName
from EH
inner join EJPP on EH_AH_IDNR = EJPP_AH_IDNR
left join EJPPC on EH_AH_IDNR = EJPP_AH_IDNR and EJPP_Lnr = EJPPC_EJPP_Lnr
Where EH_Otype = 'JSCH'
And EJPP_Status = 1941
and EJPP_Active = 1
order by EJPP_ErlstStTimeT;

Necessary Calendar Information

Next, we tackle the calendar conditions. If a task had a calendar condition, it is possible that it would not have been executed anyway on the day of the downtime – even if there had been no downtime. Now we still need to filter out those tasks.

The following graph shows a calendar condition. Under “Execute if“, “no condition matches“ was selected. Therefore, the task is only executed if neither MONDAY nor STATIC apply.

The value of “Execute if“ is found in table EJPP in the column EJPP_CCType. The possible values are:

1 = „all conditions match“

2 = „one condition matches“

3 = „no condition matches

Now we still need the following information from table EJPPC (Jobplan condition – active):

EJPPC_EJPP_Lnr Reference to the Jobplan process (EJPP)
EJPPC_CaleName Object Name (Calendar)
EJPPC_CaleKeyName Keyword Name

Check Calendar Conditions in Query

So far we have only retrieved some simple information. Now comes the exciting part which demonstrates again how useful knowledge of SQL is.

Because now we are checking the calendar conditions directly in SQL. The query must activate the calendar objects and check if the date of the downtime is found there.

We have already found the names of the calendar and the keyword in table EJPPC. Calendars are stored in OH, keywords in OKB.

Whenever you are maintaining a calendar, e.g., add or change a calendar event (=keyword) and save the change, ONE Automation calculates the days to which the condition applies – and it does so for the next 6 years (unless your administrator has changed that value).

The calculated days are saved in table OKD. There is a separate table for each year, and in column OKD_Content there is a long string of ones and zeros. Each digit stands for one day of the year and shows whether or not the condition for this day has been fulfilled.

From EJPP_ErlstSttimeT we know the date and, therefore, we can access the related digit in OKD_Content. We store this binary value in the variable CaleActive:

...
substring(OKD_Content, 31*(month(EJPP_ErlstSttimeT)-1)+day(EJPP_ErlstSttimeT), 1) as CaleActive
...

Note: In OKD_Content, 31 places are reserved for each month. It makes that field more easily usable in SQL.

In the following graph you can see which tables we have to access and how to connect them to each other.

The following table shows you what the result of such a query could look like:

 

EH_Client EH_Name EJPP_Object EJPP_Lnr EJPP_ErlstSttimeT EJPP_CCType EJPPC_CaleName EJPPC_CaleKeyName Cale_Active
1000 JSCH.001 JOBS.001 1 2016-11-02 12:00:00 NULL NULL NULL NULL
1000 JSCH.001 JOBS.002 2 2016-11-02 14:00:00 3 (=No) Cale.001 MONDAY 0
1000 JSCH.001 JOBS.002 2 2016-11-02 14:00:00 3 (=No) Cale.001 STATIC  1
1000 JSCH.001 JOBS.003 3 2016-11-02 16:00:00 2 (=One) Cale.002 TUESDAY 1
1000 JSCH.001 JOBS.003 3 2016-11-02 16:00:00 2 (=One) Cale.002 STATIC 1

 

For some JOBs there are several lines shown in the table, e.g., one for each calendar condition. With the results of the calendar conditions (they are in Cale_Active), we now need to still calculate whether the TASK was executed on the day in question.

We already mentioned the 3 possibilities. Which one applies to a specific JOB, can be found in EJPP_CCType. For each task with a calendar condition there is a EJPP_CCType (1, 2 or 3) and binary results for the conditions (Cale_active).

In the example shown in the table, the EJPP_Object JOBS.001 has the EJPP_CCType 3 and for the two conditions MONDAY and STATIC the digits 1 and 0. EJPP_CCType 3 means that the JOB is only executed when none of the conditions is 1. We can translate this into a formula:

Max(Cale_Active) = 1

Accordingly, we get the following 3 formulas for the 3 possible values in EJPP_CCType:

Condition EJPP_CCType Formula
all conditions match 1 Min(Cale_Active) = 1
no condition matches 3 Max(Cale_Active) = 0
one condition matches 2 Max(Cale_Active) = 1
All Items Description Your Total:

 

Now we can assemble the entire query:

-- Does NOT consider timezones
--Code is T-SQL, Oracle code in comments
with timeouttasks as (
    SELECT EH_Client, EH_Name, EJPP_Lnr, EJPP_Object, EJPP_ErlstSttimeT,
    CASE cast(EJPP_CCType as char(1))
        WHEN '1' THEN 'ALL'  WHEN '2' THEN 'ONE'  WHEN '3' THEN 'NO'
        ELSE EJPP_CCType
    END as CaleCondition
    , EJPPC_CaleName, EJPPC_CaleKeyName
    , substring(OKD_Content, 31*(month(EJPP_ErlstSttimeT)-1)+day(EJPP_ErlstSttimeT), 1) as CaleActive                    -- T-SQL
    --, substr(OKD_Content, 31*(to_char(EJPP_ErlstSttimeT, 'MM')-1)+to_char(EJPP_ErlstSttimeT, 'DD'), 1) as CaleActive   --Oracle
    from EH
    Inner join EJPP on EH_AH_IDNR = EJPP_AH_IDNR
    left join EJPPC on EH_AH_IDNR = EJPPC_AH_IDNR and EJPP_Lnr = EJPPC_EJPP_Lnr
    left join ( OH
        inner join OKB on OH_IDNR = OKB_OH_IDNR
        inner join OKD on OH_IDNR = OKD_OH_IDNR and OKB_Lnr = OKD_OKB_Lnr
        ) on OH_Client = EH_CLient and EJPPC_Calename = OH_Name and EJPPC_CaleKeyName = OKB_Name 
        and OKD_Year = year(EJPP_ErlstSttimeT)               -- T-SQL
        --and OKD_Year = to_char(EJPP_ErlstSttimeT, 'YYYY')  --Oracle
    where EH_Otype = 'JSCH'  And EJPP_Status = 1941  and ejpp_active = 1
    --and EH_STATUS = 1550 --include this filter to consider only active JSCH objects
), timeoutminmax as (
    select timeouttasks.* 
    , MIN(CaleActive) OVER(partition by EH_CLient, EH_Name, EJPP_Lnr) as MinVal
    , MAX(CaleActive) OVER(partition by EH_CLient, EH_Name, EJPP_Lnr) as MaxVal
    from timeouttasks
)
select distinct EH_Client, EH_Name, EJPP_Object, EJPP_Lnr, EJPP_ErlstSttimeT
from timeoutminmax
where 
    CaleCondition is NULL
      OR (    CaleCondition = 'ONE' and MaxVal = 1
    ) OR (    CaleCondition = 'ALL' and MinVal = 1
    ) OR (    CaleCondition = 'NO'  and MaxVal = 0
    )
ORDER BY EH_Client, EH_Name, EJPP_ErlstSttimeT;

A Good Reason for Using SQL

This script is rather strong stuff and appropriate only for advanced users. But it clearly illustrates why it is worthwhile to learn SQL and experiment with the Automation Engine Database. With a few lines of code it is possible to quickly assemble information from different tables for analysis.

By the way, I introduced this example at Automic World in Orlando. I also introduced two additional reasons for SQL and the Database in my presentation. You can download my presentation slides here.

Finally, another note: The above script is a bit simplified. You should only use it, if you completely understood how it works. The script does not take all factors into consideration, e.g. the fact that EJPP_ErlstSttimeT is in UTC.To make it short: Use at your own risk!

But the script does work and it shows the great possibilities you have with SQL. And maybe it will inspire you to write your own scripts.