Automicblog Repost of July 4, 2011

I have published a similar article in the old Automicblog. During editing I discovered that there is too much content for one article. Therefore, I have divided it. For this new edition, I have expanded on the information which resulted in two articles.

In Part 1 I’m offering you an introduction into the tables of the Automation Engine, queries for access, and a closer examination of the object area.

In Part 2 for next week, we will take a look at two specific areas of the database: The activity window and the statistics area.

Documentations of Automic

The AE database is very large. More than 200 tables. In order not to get lost, Automic offers various guides.

The tables are divided into 8 areas (plus an area for obsolete tables):

  • Object Area
  • Activities
  • Archive and Statistics
  • Forecast
  • Messages
  • Processing
  • System Tables
  • Temporary Tables

Detailed information regarding these areas and their respective tables is available at Automic on their page Technical Maintenance of the AE Database (AE Version 11.2). It will provide information regarding the groups as well as lists with tables contained therein.

In addition, there is documentation on the individual tables and their columns. One place where it can be found is in the folder DB/_structure as MS Access File and as static HTML files. Another place for this information is online on the page AE Database Scheme for Version 11.2.

A first Query

Start your trip through the Automic Database with a simple SQL Select Query:

select TABLE_NAME,
TABLE_TEXT,
TABLE_CHILDREN
from UC_TABLE;

With this query, you will simply list all tables with their corresponding descriptions and child tables.

Example Object Area: JOBS of a Client

The table OH (Object Header) is the most important table for object definitions. For each object in an AE system there is one entry in OH. For each object you can be shown many data.

In the first example, we will select from the OH the object names of all objects of the object type ‘JOBS’ which belong to the AE client 1. The list of object names is sorted alphabetically.

 -- Get object names of all JOBS in AE Client 1, sort alphabetically
 select OH_NAME
 from OH
 where OH_CLIENT=1
 and OH_OTYPE='JOBS'
 and OH_DELETEFLAG=0
 order by OH_NAME;

Hier noch ein paar Informationen zu den verwendeten Spalten:

OH_NAME: The object name.

OH_CLIENT: The AE Client as a numeric value.

OH_OTYPE: The object type

OH_DELETEFLAG: Indicates if the object is deleted. The possible values are:

0 – The object is not deleted (we need it here).

1 – The object is in the recycle bin.

2 – Obsolete version of the object (version control)

5 – Object has been marked for deletion by the DB-Reorg Utility

Expansion of the Example: Expected Run Time and Priority

Of course, the first example is nothing special. It is something you could do with Object Search in the User Interface. But with a small change, we can illustrate the limitation of the User Interface.

In addition to the object name, we are now asking for the Expected Run Time =ERT) of an object. The time is shown in seconds.

select OH_NAME, OH_ERT
from OH
where OH_CLIENT=1
and OH_OTYPE='JOBS'
and OH_DELETEFLAG=0
order by OH_NAME;

And in the next example we also control the priority of the objects. The query will return only Jobs whose priority is not 0.

select OH_NAME, OH_ERT
from OH
where OH_CLIENT=1
and OH_OTYPE='JOBS'
and OH_REFIDNR=0
and OH_DELETEFLAG=0
and OH_UC4PRIORITY!=0
order by OH_NAME;

OH_IDNR: This is the primary key of the table. The column contains the unique object number of all objects of your AE system. As in the last example, it is often used in Joins.

OH_REFIDNR: This column is relevant for version control. When version control is activated, a new entry into the OH table is made for each new version of an object. In the column OH-REFIDNR is contained the OH_IDNR of the original object. In the original object the value is 0.

OH_ERT: The estimated run time of the object in seconds.

Now it’s getting exciting: We are using Join

SQL queries with “Join” are starting to be really fun. With this command you can combine information from several tables.

The following Select statement delivers all Unix Jobs of a client.

-- Get object names of all UNIX-JOBS in AE Client 1, sort alphabetically
-- RDBMS: Oracle, MS-SQL
select OH_NAME
from OH
inner join JBA on OH_IDNR = JBA_OH_IDNR
where OH_DELETEFLAG=0
and OH_CLIENT=1
and JBA_HOSTATTRTYPE='UNIX'
order by OH_NAME;

Die Tabelle JBA (Job Attributes) enthält Attribute aller Objekte vom Typ JOBS. Zum Beispiel auch JBA_HOSTATTRTYPE, um die Plattform abzufragen, auf der der Job läuft.

Mit dem Befehl ‘inner join’ kombinieren wir hier die zwei Tabellen JBA und OH. Wir können so für jedes Objekt sowohl auf die Eigenschaften in OH, als auch auf die Eigenschaften in JBA zugreifen.

Additional important columns in OH

These examples have familiarized you with some of the important columns of the OH Table:
OH_NAME, OH_CLIENT, OH_OTYPE, OH_DELETEFLAG, OH_IDNR, OH_REFIDNR, OH_ERT und OH_UC4PRIORITY. Here are some additional important columns, some of which are not contained in our examples. I’ll leave you to experiment with those on your own.

OH_INACTIVE: Active status of the object (according to check box “Active“ in the tab “Head“). If the value is 0, the object is active.

OH_CRDATE: The creation date of the object. The timestamp (as in most date fields) is in UTC.

OH_CRUSERIDNR: This is the OH_IDNR of the user who created the object.

Explore the object area of your Automation Engine Database a bit on your own with the sample queries and columns. I also recommend that you read the documentation of OH because there are a lot more exciting columns.

Continue reading the second part of the series: “Activities and Statistics“.