Automic Workload Automation

Expand all | Collapse all

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

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

    Posted 11-13-2017 03:57 AM
    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) ?


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

    Posted 11-13-2017 05:14 AM
    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


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

    Posted 11-13-2017 10:13 AM
    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


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

    Posted 11-14-2017 08:28 AM

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



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

    Posted 01-02-2018 10:38 AM

    @Wolfgang Brueckler

    Can you make it include external dependencies ?



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

    Posted 01-02-2018 01:23 PM
    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