Automic Workload Automation

  • Private Community

  • 1.  SQL to extract processflow dependancies from Automation Engine database?

    Posted Dec 05, 2016 10:32 AM

    Hi.. I know this has probably been asked a number of times, but I cannot quite find a full answer..

    Does anyone have any useful SQL queries that will list the objects in a processflow & also any dependant processes?

    Regards,

    Colin.



  • 2.  SQL to extract processflow dependancies from Automation Engine database?

    Posted Dec 05, 2016 02:38 PM
    Maybe this one helps (T-SQL), it lists all Workflows within a client (pls replace 1 with your client ID) and its objects + object types, dependencies to its predecessor and ELSE clause.

    select distinct OH_Name as 'Parent Workflow', oh_idnr,JPP_LNR as 'OBJ Nr within Workflow',  JPP_OBJECT as 'OBJ Name within Workflow',JPP_OTYPE as 'OBJ Type within Workflow', JPP_WELSE, JPPA_WHEN 'Dependency - Status of Predecessor', JPP_WhenExecute as 'ELSE execute'   from OH, JPP,JPPA
    where oh_client=1
    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;

    The Stati WELSE mean:
    WHEN ... ELSE S=SKIP, H=HALT, A=ABEND, X=Block + Abort Signal

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

    I am not sure what you exactly mean with "also any dependant processes?" ...



  • 3.  SQL to extract processflow dependancies from Automation Engine database?

    Posted Dec 06, 2016 03:13 PM
    I have a couple of simple queries that I run at least once per week for various application support folks.

    1) Returns the children of a parent workflow matching 'OH_Name':
    SELECT OH_Name, JPP_Object, JPP_Alias, JPP_WElse, JPP_PreCnt,
      JPP_Row, JPP_Col, JPP_Active
    FROM JPP, OH
    WHERE OH_Name LIKE '%ETL%'
      AND OH_OType = 'JOBP'
      AND JPP_Object <> 'START'
      AND JPP_OH_Idnr = OH_Idnr
      AND OH_Client = 7000
      AND OH_deleteflag = 0
    ORDER BY OH_Name, JPP_Object

    2) Returns the parent of child workflows matching 'JPP_Object'
    SELECT OH_Name, JPP_Object, JPP_Alias, JPP_WElse, JPP_PreCnt,
      JPP_Row, JPP_Col, JPP_Active
    FROM JPP, OH
    WHERE JPP_Object LIKE '%&WFG#%'
      AND JPP_OH_Idnr = OH_Idnr
      AND OH_Client = 7000
      AND OH_deleteflag = 0
    ORDER BY OH_Name, JPP_Object

    I write the results to a csv file and publish it to an ECC dashboard where the app support folks can get to it.

    (Wolfgang, thanks for the hint about querying JPPA also. I'll have to look at that.)


  • 4.  SQL to extract processflow dependancies from Automation Engine database?

    Posted Dec 09, 2016 06:34 AM
    GreggMorris601129
    ( You are very welcome :-) )