Zeitpunkte und Zeitspannen in der AE-Datenbank

Das neue Jahr ist bereits mehr als einen Monat alt und es wird damit wirklich mal höchste Zeit für einen Blogartikel.

Dafür habe ich mir mal wieder ein schön technisches Thema vorgenommen: Wir sehen uns heute Statistiksätze für Zeitpunkte an.

Während die Statistiken von Zeiträumen ziemlich trivial sind (selektive Statistik öffnen, Kriterien eingeben, fertig), wird es bei Zeitpunkten etwas kniffliger. Dafür brauchen wir SQL.

Kommt Ihnen das Thema bekannt vor? Gut möglich, denn vor vielene, vielen Jahren habe ich in meinem alten Blog darüber schon einmal geschrieben.

Das Thema ist aber auch heute für viele Automic User aktuell, deshalb habe ich mich entschlossen, es noch einmal aufzugreifen.

Statistics und Activities

Vorweg zunächst ein paar nützliche allgemeinen Infos.

Wie Sie sicher wissen, befinden sich alle Statistiksätze in der Tabelle AH.

Solange eine Aufgabe noch im Aktivitätenfenster ist, finden Sie die aktuellen Informationen zur Aufgabe in der Tabelle EH.

Besonders interessant ist dort der Status der Aufgabe, der in EH_Status steht. In AH_Status steht für aktive Tasks meistens 0, sie sollten sich darauf aber nicht verlassen, denn es gibt auch ein paar Ausnahmen, zum Beispiel hängt bei Aktivitäten im Post-Processing (das ist der EH_Status 1574) der Status davon aber, ob der Returncode 0 ist (AH_Status ENDED_OK 1900) oder nicht (AH_Status ENDED_NOT_OK 1800).

In den zwei Datenbanken gibt es jeweils mehrere Zeitstempel zu jedem Job.

Die Tabelle EH enthält

• EH_Activationtime: Die Aktivierungszeit des Tasks (entspricht AH_Timestamp1)
• EH_Starttime: Die Startzeit des Tasks (entspricht AH_Timestamp2)
• EH_Endtime: Das Ende der Aufgabe, wenn alles abgeschlossen ist (auch das Post-Processing)

In der Tabelle AH gibt es noch mehr Zeit-Daten über die Aufgabe:

• AH_Timestamp1: Der Aktivierungszeitpunkt
• AH_Timestamp2: Der Startzeitpunkt der Aufgabe (entspricht EH_Starttime)
• AH_Timestamp3: Das Ende der Aufgabe VOR dem Post-Processing. Wenn es keinen Post-Process gibt, steht hier NULL.
• AH_Timestamp4: Das Ende der Aufgabe nach dem Post-Processing, beziehungsweise einfach das Ende des Tasks, falls es kein Post-Processing gab. AH_Timestamp4 hat also immer den gleichen Wert wie EH_Endtime.

Immer diese Zeitzonen

Wie fast immer in der Automation Engine werden auch hier alle Zeitstempel in UTC gespeichert. Um sinnvoll damit zu arbeiten, müssen wir sie also zunächst in die eigene Zeitzone umwandeln.

In meinem Artikel über Design Pattern habe ich betont, wie wichtig Best Practices sind. Hier gibt es auch eine:

Führen Sie Zeitzonen-Umwandlung möglichst immer auf der Client-Seite durch.

Manchmal muss oder will man aber doch in SQL umwandeln. Für diese Fälle habe ich hier ein paar nützliche SQL-Schnipsel gesammelt, die Sie einfach übernehmen können.

Aktueller Zeitstempel (“jetzt”) in UTC

--Oracle
systimestamp at time zone 'UTC'
--T-SQL
GETUTCDATE()

Umrechnung einer lokalen Zeit nach UTC
Ersetzen Sie [local timestamp to convert] durch Ihren umzurechnenden Zeitstempel.

--Oracle
CAST(FROM_TZ(CAST([local timestamp to convert] AS TIMESTAMP),sessiontimezone) AT TIME ZONE 'UTC' AS DATE)
--T-SQL
Dateadd(minute, -DATEPART (TZoffset ,SYSDATETIMEOFFSET()), [local timestamp to convert])

Umrechnung eines UTC Zeitstempels in Ihre lokale Zeitzone
Ersetzen Sie [utc timestamp to convert] durch Ihren umzurechnenden Zeitstempel.

--Oracle
CAST(FROM_TZ(CAST([utc timestamp to convert] AS TIMESTAMP), 'UTC') AT TIME ZONE sessiontimezone AS DATE)
--T-SQL
Dateadd(minute, +DATEPART (TZoffset ,SYSDATETIMEOFFSET()), [utc timestamp to convert])

Was lief zu einem bestimmten Zeitpunkt?

Am 27. Juli um ungefähr 21.30 Uhr begann letztes Jahr in Wien eine totale Mondfinsternis.

Haben Sie das spannende Event auch so interessiert verfolgt wie ich?

Aber wissen Sie auch, was zu diesem Zeitpunkt auf Ihrer Automation Engine lief?

Per SQL können Sie es herausfinden.

with UTCTIME as ( 
SELECT Dateadd(minute, -DATEPART (TZoffset ,SYSDATETIMEOFFSET()), '2018-07-27 21:30:00') as utctime --T-SQL 
--SELECT CAST(FROM_TZ(CAST(TO_DATE('2018-07-27 21:30:00', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP),sessiontimezone) AT TIME ZONE 'UTC' AS DATE) as utctime from dual --Oracle 
 
) 
select AH_Client, AH_Otype, AH_IDNR, AH_Name, coalesce(EH_Status, AH_STatus) as StatusCode, AH_TimeStamp2, AH_TimeStamp4 
from AH left join EH on AH_IDNR = EH_AH_Idnr 
CROSS JOIN UTCTIME 
where exists (select 1 from UC_OTYP where AH_OType = OTYP_Type and OTYP_Executable = 1) -- only executable obj. types 
and AH_TimeStamp2 <= utctime and (AH_TimeStamp4 >= utctime or AH_TimeStamp4 is null);

Wer war zu einem Zeitpunkt eingeloggt?

Auch User-Sessions erzeugen Statistiksätze.

Das bedeutet, man muss das Query von oben nur leicht abwandeln, um herauszufinden, welche User-Sessions zu einem bestimmten Zeitpunkt aktiv waren.

Eine kleine Ergänzung habe ich noch eingefügt. Durch einen Join mit der Tabelle USR sind auch die Vornamen, Nachnamen und E-Mail-Adressen der User im Ergebnis enthalten. Natürlich können Sie so auch auf alle anderen Userdetails zugreifen.

with UTCTIME as (
select Dateadd(minute,-DATEPART (TZoffset ,SYSDATETIMEOFFSET()), '2018-07-27 21:30:00') as utctime --T-SQL
--SELECT CAST(FROM_TZ(CAST(TO_DATE('2018-07-27 21:30:00', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP),sessiontimezone) AT TIME ZONE 'UTC' AS DATE) as utctime from dual --Oracle
)
select AH_Client, AH_IDNR, AH_Name, AH_Status, AH_TimeStamp1, AH_TimeStamp4, USR_FirstName, USR_LastName, USR_EMail1
from AH
CROSS JOIN UTCTIME
inner join USR on AH_OH_Idnr = USR_OH_Idnr
where AH_OType = 'USER'
and AH_TimeStamp1 <= utctime and (AH_TimeStamp4 >= utctime or AH_TimeStamp4 is null);

Was lief parallel zu einem bestimmten Task?

Diese Anforderung sollten wir noch einmal präziser formulieren.

Wir suchen alle Tasks, auf die mindestens eine der folgenden Bedingungen zutrifft:

• Der Task startete nachdem der Referenztask startete und bevor er endete.
• Der Task endete nachdem der Referenztask startete und bevor er endete.

Die folgende Grafik veranschaulicht das noch einmal.

Der rote Pfeil ist der Referenz-Task. Die grünen Pfeile sind Tasks, die das Skript herausfiltern soll. Die violetten Pfeile sind irrelevante Tasks, die nicht parallel zum Referenz-Task laufen.

Die Abfrage dafür ist eine einfache Abwandlung des ersten Beispiels aus diesem Artikel.

Der Unterschied: Wir ermitteln in der CTE nicht die UTC-Zeit eines fix angegebenen Zeitpunktes, sondern die relevanten Zeitstempel der Referenz-Aufgabe. Zur Identifikation des Roten Tasks verwendet die Abfrage die RunID, hier 08154711.

with TASKTIMES as ( 
       select AH_Timestamp2 as TASKSTART, AH_Timestamp4 as TASKEND from AH 
       where AH_Idnr = 08154711 
) 
select AH_Client, AH_Otype, AH_IDNR, AH_Name, coalesce(EH_Status, AH_STatus) as StatusCode, AH_TimeStamp2, AH_TimeStamp4 
from AH left join EH on AH_IDNR = EH_AH_IDNR 
CROSS JOIN TASKTIMES 
where exists (select 1 from UC_OTYP where AH_OType = OTYP_Type and OTYP_Executable = 1) -- only executable obj. types 
and AH_TimeStamp2 <= TASKEND and (AH_TimeStamp4 >= TASKSTART or AH_TimeStamp4 is null) 
order by AH_Client, AH_TimeStamp2, AH_TimeStamp4; 

Mehr SQL gefällig?

In letzter Zeite habe ich etwas gegeizt mit technischen Artikeln, in denen SQL und die AE Datenbank im Mittelpunkt stehen. Soll ich wieder mehr davon veröffentlichen? Dann schreiben Sie es mir doch bitte in den Kommentaren.

Und wenn Sie gerne noch viel mehr darüber lernen wollen: Es gibt noch einige wenige freie Plätze beim AE Database Knowledge Workshop in Nürnberg vom 25. bis zum 27.März.