SQL Querying Part 2: Activities and Statistics

Automicblog Repost from July 4, 2011

In Part 1 of this series I provided a small overview of using SQL queries for accessing the Automation Engine Database. We focused on the object area and the OH table. In the final examples we combined OH with the JBA (job attributes) table by using the SQL command Join.

By using a few examples, I will show you today what there is to discover in the most important tables in the areas Activities (Table EH) and Statistics (Table AH).

Activity Area: Tasks with exceeded runtime

EH_OH_IDNR: The Object ID of the Object, i.e., the unique Object Number (OH_IDNR is always the Object ID, just like last week’s example, also the JBA_OH_IDNR). This allows you to find the entry in OH, which belongs to this entry in the Activity Window. This is very useful, for instance, when using the Join command.

EH_Client: The client.

EH_Name: Name of the Object (at the time of activation).

EH_REFNR: Contains the Run ID of the original run in case of reruns (normally 0).

EH_OTYPE: Type of Activity.

We will now look at an example that is not possible through the user interface. With a short script we can show all tasks which have been started but are not complete, and where the ERT (estimated runtime) has already been exceeded.

select EH_Client, EH_AH_IDNR, EH_OType, EH_Name, EH_Starttime, EH_EndTime,
EH_ERTEnd, EH_Status
from EH
where
EH_ERTEnd < GETUTCDATE() --T-SQL
--EH_ERTEnd < systimestamp at time zone 'UTC' --Oracle
and EH_Starttime is not null
and EH_EndTime is null;

For each of these activities, the script will result in displaying the client, the run ID, the type, the name, the start time and the end time.

Statistics Area: Aborted Jobs

The third (and for now last) area we want to look at is the Statistics Area. It displays all information regarding past jobs. How far you will be able to look into the past depends on your housekeeping.

The most important table in the Statistics Area is the Archive Header AH. The columns of this table are quite similar to EH in the Activities Area.

Here are a few useful columns; all others you can find in Automics Table Definition AH.

AH_IDNR: The Run ID.

AH_OH_IDNR: The OH_IDNR of the Object.

AH_Status: The numeric status code of the Activity (for example, 1800 for ENDED_NOT_OK).

AH_Client: The client.

AH_Name: Contains the object name at the time of activation.

AH_REFNR: Contains the Run ID of the original run in case of reruns (normally 0).

Here I have also come up with an example that you could not conduct through the user interface. The script returns a list of all jobs that have been aborted at least once during the last 24 hours. Additionally, it will display how many times the job was aborted.

select AH_CLIENT, AH_NAME, count(*) as FailedCount
from AH
where AH_Otype = 'JOBS'
and AH_Status between 1800 and 1899
and AH_Timestamp4 > DATEADD(HH, -24, GETUTCDATE()) --T-SQL
--and AH_Timestamp4 > systimestamp at time zone 'UTC' -1
group by AH_CLIENT, AH_NAME;

Now you can try it yourself

I hope that with these examples I’ve been able to encourage you to experiment with the Automation Engine Database yourself. There are many more tables to explore.

If your curiosity has been awakened and you would like to explore this material in depth, I invite you to attend one of my AE DB Workshops. All information can be found here.