Automic Workload Automation

Expand all | Collapse all

Clearing activities with script

Anon Anon

Anon AnonOct 04, 2017 02:01 PM

  • 1.  Clearing activities with script

    Posted Sep 15, 2017 10:24 AM
    Hello All,

    So far I implemented the following : 

    VARA.SQLI.GET_RUNIDS
    SELECT AH_Idnr  FROM GAED.AH WHERE trunc(AH_Timestamp4) <= to_date('2017-09-13','YYYY-MM-DD');
    Note : this SQL statement was made on SQL Server, if you're using Oracle you'll need to convert the timestamp. 


    SCRI.DEACTIVATE_TASKS
    :SET &HND# = PREP_PROCESS_VAR(VARA.SQLI.GET_RUNIDS)
    :PROCESS &HND#
    :  SET &RUNID# = GET_PROCESS_LINE(&HND#,1)
    :  SET &DEACT# = DEACTIVATE_UC_OBJECT(&RUNID#)
    :  SET &TASKNAME# = GET_STATISTIC_DETAIL(&RUNID#, NAME)
    :  PRINT Task &TASKNAME# with RunID &RUNID# has been deactivated
    :ENDPROCESS

    But when i execute the script Ive got bellow error :
    U00020274 Wrong SQL statement in 'VARA.SQLI.GET_RUNIDS'. DB error: 'U00003754 Database-error in SQLI-variable: 'U00003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR   ', '', 'ORA-00933: SQL command not properly ended''

    I had a chat with member of Database team and he confirm that this statement is correct and he is able to execute it.

    Would you share your opinion? 
    I suppose Im doing something wrong but can not find out what.

    P.S. Ive tried to run this with and without DB service agent.

    Much appreciated. 

    Krum Ganev


  • 2.  Clearing activities with script

    Posted Sep 15, 2017 12:14 PM
    Your SQL is in Oracle syntax, not SQLServer syntax.

    Does it work when you click the "preview" button inside of VARA.SQLI.GET_RUNIDS?


  • 3.  Clearing activities with script

    Posted Sep 15, 2017 01:50 PM
    This sql is negligent and most of it would not work with the script because:

    * you are querying the wrong table
    * there is no limitation on a specific client
    * there is no limitation on the status of objects (every object that is not ended would be tried to deactivate (including Agents, Server Processes, user sessions, etc.)
    * there is no limitation on object type


  • 4.  Clearing activities with script

    Posted Sep 15, 2017 01:55 PM
    Just as hint I posted a while ago an example that should fit your needs:

    https://community.automic.com/discussion/comment/27253#Comment_27253


    Instead of crashing your AE System with wrong selects I would suggest attending Philipp Elmers Database Workshop:

    https://philippelmer.com/

    :-)



  • 5.  Clearing activities with script

    Posted Sep 15, 2017 07:07 PM
    Hi Krum_Ganev ,

    If you need sql statements to clear out the activities window, please contact the Support team. We provide them in case there's a lot of objects out there that have a negative impact on your AEs performance.

    Cheers,
    Alexander


  • 6.  Clearing activities with script

    Posted Sep 16, 2017 05:59 AM
    Hello,
    I found this approach here -> https://community.automic.com/discussion/9224/clear-down-the-activity-window-using-sql-on-the-automic-database
    We are using Oracle v12

    Currently all jobs and workflows are set to Deactivate after error-free execution, the issue is that a lot of jobs are failing and the flows stays in Activiti Window. Everyday im performing manual cleanup of the activity window. There are arround 4000 activities on any given time and the system is still growing.
    If i am unable to clear it for day or two, the activities grows to 8000+.
    I want to find automatic way to clear the activities as the customer do not want to set workflows to be set on "deactivate always".
    FrankMuffke
    Thanks for the info. I will read the post and ill test it.
    Alexander_Trenker_120
    Thanks for your support but this will not be one time activity. I need to implement permanent solution as at the end of the project we will have 45 000 jobs and currently we migrated arround 30%.


  • 7.  Clearing activities with script

    Posted Sep 16, 2017 08:34 AM
    Krum_Ganev
    Thx for the explanation - think that my script could help you if you need to deactivate all activations on a daily basis.

    And I have to say sorry, I was not aware that the SQL staement in your first post came from Community...
    Will ask to correct it, as its DB script nonsense....

    cheers, Wolfgang


  • 8.  Clearing activities with script

    Posted Sep 16, 2017 07:13 PM
    Hello Krum_Ganev,

    Krum Ganev said:

    Currently all jobs and workflows are set to Deactivate after error-free execution, the issue is that a lot of jobs are failing and the flows stays in Activiti Window. Everyday im performing manual cleanup of the activity window. There are arround 4000 activities on any given time and the system is still growing.
    If i am unable to clear it for day or two, the activities grows to 8000+.
    I want to find automatic way to clear the activities as the customer do not want to set workflows to be set on "deactivate always".
    There is an option to set the 'Time delay' in workflows and jobs.  I believe this will work more in both your favor and your user's.  Have them set the workflows to 'Deactivate always' and set a 'Time delay' for it.  

    What this does is delay the deactivation of the workflows for x minutes.  Once that time is up, it will deactivate it.  This will save you time from running a script on a daily basis to clear up the activities window.

    Here is the documentation on 'Time delay'.  I hope this helps.



  • 9.  Clearing activities with script

    Posted Sep 20, 2017 02:27 AM
    @Wolfgang Brueckler 
    Your script worked!
    Thanks a lot!
    I just alternate the Oracle statement to : 
    select eh_ah_idnr, eh_name, eh_status from GAED.EH where to_char(trunc(EH_ENDTIME),'YYYY-MM-DD') <= to_char(trunc(SYSDATE - 2),'YYYY-MM-DD') and eh_client = 100 and ROWNUM <= 1000
    Ive tested successfully in Dev and in next few days ill implement in Prod.

    Just one more thing. From what i was able to see the statement is finding only jobs and not workflows. If im not mistaken - how i can target flows also.



  • 10.  Clearing activities with script

    Posted Sep 20, 2017 04:44 AM
    Krum_Ganev
    you are welcome!

    JOBPs will be deactivated as well!

    the script tries to deactivate everything which has no exception "leave" in the config VARA.


  • 11.  Clearing activities with script

    Posted Sep 20, 2017 05:29 AM
      |   view attached
    Krum_Ganev

    I Implemented one more feature - exclusion objects, here you can define object Names that chould be excluded from deactivation. Just enter LEAVE in the Data column next to the object name.lu34s7g12xvt.jpghttps://us.v-cdn.net/5019921/uploads/editor/8f/lu34s7g12xvt.jpg" width="471">

    VARA.STATIC.DEACT.SOLUTION_EXCL_OBJECTNAMES



    Attachment(s)

    xml
    V112_Deact_Solution.xml   11 KB 1 version


  • 12.  Clearing activities with script

    Posted Oct 04, 2017 10:45 AM
    FrankMuffke
    Big thanks!
    I will test it and if suitable i will implement it in production.


  • 13.  Clearing activities with script

    Posted Oct 04, 2017 02:01 PM
    very welcome :-)