SQL to query a Workflow list its tasks including their dependencies (pre- and successor)

[Keld Mollnitz]

Hi all,

A Monday challenge:
Can anyone come up with an SQL query (for Oracle) that will show we a workflow and its tasks including their dependencies (pre- and successor) ?

[Anon Anon]

Here you go

select distinct OH_Name, oh_otype,oh_idnr,JPP_LNR,  JPP_OBJECT,JPP_OTYPE, JPP_WELSE, JPPA_WHEN, JPP_WhenExecute   from OH, JPP,JPPA
where oh_client= &$CLIENT#
and OH_IDNR = JPP_OH_IDNR
and JPP_OH_IDNR = JPPA_OH_IDNR
and JPPA.JPPA_JPP_LNR = JPP.JPP_LNR
and oh_deleteflag = 0
and OH_IDNR > 100000
and JPP_OTYPE NOT in ('<FE>', '<END>', '<START>')
order by OH_NAME

Explanation JPP_WELSE:
WHEN ... ELSE S=SKIP, H=HALT, A=ABEND, X=Block + Abort Signal

https://docs.automic.com/documentation/webhelp/english/AWA/11.2/AE/latest/DB%20Schema/db/_structure/HTML/JPP.html
https://docs.automic.com/documentation/webhelp/english/AWA/11.2/AE/latest/DB%20Schema/db/_structure/HTML/JPPA.html

cheers, Wolfgang

[Carsten Schmitz]

Keld_Mollnitz

I have a CGI script (basically a bash shell script that runs from a web server) that investigates a JOBP in the database, and list each of it's children, and their grandchildren, and so on, going into subsequent JOBP as well, for a configurable depth (up to 9 levels deep without further tweaks). It's mostly for documenting job chains.

It's not one SQL statement, but SQL plus some shell script. If that's helpful, I can modify it so it's not a CGI but a regular shell script, sanitize it from company secrets and subversive comments ;) and either post it or send it to you in a PM. Since that's a bit of work (not too much, but some), let me know if that's something you can use before I have a go at that.

Here's some sample output for a JOBP with visible indentation (you can configure different indentation options) and our corporate internals redacted, to get the idea what output you can expect:

JOBP.SAP.REDACTED;START;START;1
JOBP.SAP.REDACTED;JOBP;JOBP.SAP.REDACTED1.REDACTED;2;1
..JOBP.SAP.REDACTED1.REDACTED;START;START;1
..JOBP.SAP.REDACTED1.REDACTED;EVNT;EVNT.REDACTED;2;1
..JOBP.SAP.REDACTED1.REDACTED;CALL;CALL.SAP.REDACTED;3;2
..JOBP.SAP.REDACTED1.REDACTED;JOBP;JOBP.SAP.REDACTED2.REDACTED;4;2
....JOBP.SAP.REDACTED2.REDACTED;START;START;1
....JOBP.SAP.REDACTED2.REDACTED;JOBS;JOBS.SAP.REDACTED;2;1
....JOBP.SAP.REDACTED2.REDACTED;EVNT;EVNT.SAP.REDACTED;3;2
....JOBP.SAP.REDACTED2.REDACTED;JOBP;JOBP.SAP.REDACTED3.REDACTED;4;3
......JOBP.SAP.REDACTED3.REDACTED;START;START;1
......JOBP.SAP.REDACTED3.REDACTED;JOBS;JOBS.REDACTED;2;1
......JOBP.SAP.REDACTED3.REDACTED;JOBS;JOBS.REDACTED;3;1
......JOBP.SAP.REDACTED3.REDACTED;JOBS;JOBS.REDACTED;4;2
......JOBP.SAP.REDACTED3.REDACTED;JOBS;JOBS.REDACTED;4;3
......JOBP.SAP.REDACTED3.REDACTED;END;END;5;4
....JOBP.SAP.REDACTED2.REDACTED;END;END;5;3
....JOBP.SAP.REDACTED2.REDACTED;END;END;5;4
..JOBP.SAP.REDACTED1.REDACTED;END;END;5;3
..JOBP.SAP.REDACTED1.REDACTED;END;END;5;4
JOBP.SAP.REDACTED;END;END;3;2

[Keld Mollnitz]

Thanks Wolfgang...but it does not include external dependencies..can you fix that?

[Keld Mollnitz]

@Wolfgang Brueckler

Can you make it include external dependencies ?

[Anon Anon]

Hi Keld_Mollnitz

select distinct OH_Name, oh_otype,oh_idnr,JPP_LNR,  JPP_OBJECT,JPP_OTYPE, JPP_WELSE,  JPP_WhenExecute   from OH, JPP
where oh_client= &$CLIENT#
and OH_IDNR = JPP_OH_IDNR
and OH_OTYPE = 'JOBP'
and oh_deleteflag = 0
and OH_IDNR > 100000
and JPP_OTYPE NOT in ('<FE>', '<END>', '<START>','<IF>')
order by OH_NAME, jpp_object

please doublecheck, if it works correctly - I am not 100% sure :-(

cheers, Wolfgang