Automic Workload Automation

Expand all | Collapse all

SQL statement for activity window cleanup

  • 1.  SQL statement for activity window cleanup

    Posted 06-06-2017 05:32 AM
    We are building new Automic environment for new customer.

    We will need SQL statement for activity windows cleanup.
    We was able to find below : 

    WITH subtasks (S_IDNR, S_PARENT, S_CLIENT, S_NAME, S_STATUS, S_OTYPE) as (
    SELECT eh.EH_AH_Idnr , eh.EH_ParentAct , eh.EH_Client , eh.EH_Name , eh.eh_status , eh.eh_otype 
     FROM EH
    WHERE eh_status <> 1900
    and eh_client = ?

        UNION ALL

      SELECT eh.EH_AH_Idnr, eh.EH_ParentAct, eh.EH_Client, eh.EH_Name, eh.eh_status, eh.eh_otype
      FROM EH 
      INNER JOIN subtasks 
          on eh.EH_AH_Idnr = subtasks.S_PARENT
    ),
    eh_hierarchy (H_IDNR, H_PARENT, H_CLIENT, H_NAME, H_STATUS, H_OTYPE, H_LEVEL) as (
    SELECT eh.EH_AH_Idnr , eh.EH_ParentAct , eh.EH_Client , eh.EH_Name , eh.eh_status , eh.eh_otype , 0 AS "H_LEVEL"
    FROM EH
    WHERE eh_otype in ('JOBP', 'JSCH')
      and eh_parentprc = 0
      and eh_client = ?

        UNION ALL

      SELECT eh.EH_AH_Idnr, eh.EH_ParentAct, eh.EH_Client, eh.EH_Name, eh.eh_status, eh.eh_otype, H_LEVEL + 1
      FROM EH
      INNER JOIN eh_hierarchy 
          ON eh.EH_ParentAct = eh_hierarchy.H_IDNR
    )
    SELECT eh.EH_AH_Idnr, eh.EH_ParentAct, eh.EH_Client, eh.EH_Name, eh.eh_status, eh.eh_otype, H_LEVEL 
    FROM   eh, eh_hierarchy
    WHERE eh.eh_ah_idnr not in (SELECT subtasks.S_IDNR FROM subtasks)
      and eh.eh_otype = 'JOBP'
      and eh.eh_client = ?
      and eh.eh_ah_idnr = eh_hierarchy.H_IDNR
    order by H_LEVEL desc


    This is for SQL database and we are working with Oracle now.
    Is it suitable to use the same or some lines should be altered? 


  • 2.  SQL statement for activity window cleanup

    Posted 06-06-2017 05:48 AM
    Hello,

    We only provide such statements in case of an emergency (activities cannot be deactivated), however they should not be used to maintain the activity window on a regular basis.
    Please refrain from doing so, as interacting directly with the Automation Engine DB to modify its data (INSERT, UPDATE, DELETE, etc.) is not something we support.

    Please use a supported solution. Here are a few other posts related to this question:

    - https://community.automic.com/discussion/9224/clear-down-the-activity-window-using-sql-on-the-automic-database

    - https://community.automic.com/discussion/8749/how-do-i-cancel-a-lot-of-tasks-on-the-activity-window

    - https://community.automic.com/discussion/5792/deactivate-ended-ok-jobs-in-activity-window

    Best regards,
    Antoine