Automic Workload Automation

 View Only
Expand all | Collapse all

How to write a SQL statement to change the status of all running instances of a job

  • 1.  How to write a SQL statement to change the status of all running instances of a job

    Posted Jul 10, 2019 04:43 PM
    Very long explanation for how this happened, but we have like 10,000 instances of a script in Ready For Generation and nothing is removing them, because it isn't a cancelable status..... can't deactivate or cancel or anything.

    I need to know a SQL statement I can run to set the Status of a job named SCRI.UTIL.EXAMPLE to Manually Canceled or something. At least then I can start clearing it out by setting it to deactive.

    I appreciate any help because this is causing major performance issues where all of our workflows currently hang in Waiting for End of Parallel Task for ten minutes or so, for no reason.

    Thank you,


  • 2.  RE: How to write a SQL statement to change the status of all running instances of a job

    Posted Jul 10, 2019 04:55 PM
    According to this document, status code 1529 = "Ready For Generation"
    https://docs.automic.com/documentation/webhelp/english/AWA/12.2/DOCU/12.2/AWA%20Guides/help.htm#AWA/Executions/ReturnCodesExec.htm?Highlight="ready for generation"

    So you'll want to make sure you only zap ones that have this value.

    Now finding where it is stored in the database, is another question...  I'll see what I can find.


  • 3.  RE: How to write a SQL statement to change the status of all running instances of a job

    Posted Jul 10, 2019 05:04 PM
    **** DISCLAIMER  *****
    **** DO THIS TYPE OF UPDATE TO THE UC4 DATABASE  AT YOUR OWN RISK! *****
    **** I HAVE NO IDEA IF THIS MAY CORRUPT THE UC4 DATA.  IT COULD PUT TABLES OUT OF SYNC WITH ONE ANOTHER ****
    **** (WOULD BE BEST TO TEST IT AGAINST ONE OBJECT IN A NON-PROD CLIENT FIRST) ****
    **** DISCLAIMER  *****

    I think you might be interested in the column EH_STATUS in table EH.

    **** DISCLAIMER  *****
    **** DO THIS TYPE OF UPDATE TO THE UC4 DATABASE  AT YOUR OWN RISK! *****
    **** I HAVE NO IDEA IF THIS MAY CORRUPT THE UC4 DATA.  IT COULD PUT  TABLES OUT OF SYNC WITH ONE ANOTHER ****
    **** (WOULD BE BEST TO TEST IT AGAINST ONE OBJECT IN A NON-PROD CLIENT FIRST) ****
    **** DISCLAIMER  *****


  • 4.  RE: How to write a SQL statement to change the status of all running instances of a job

    Posted Jul 10, 2019 06:25 PM
    Don't just update the EH_status. There are sub tables E* that also have to be updated and not only updated but in a certain order.

    Lastly, the AH records have to be updated as well for this to work.

    My recommendation is to open a case up with support.


  • 5.  RE: How to write a SQL statement to change the status of all running instances of a job

    Posted Jul 11, 2019 01:12 AM
    Either go for the support to get an Automic-approved SQL statement, or solve it by code. Are you on V12.1+ or below? With my WorkflowCommander solution it would be quiet easy to solve :-).

    ------------------------------
    Swisscom & WorkflowCommander
    ------------------------------



  • 6.  RE: How to write a SQL statement to change the status of all running instances of a job

    Posted Jul 11, 2019 09:00 AM
    > Either go for the support to get an Automic-approved SQL statement

    Might probably contact HCL right away, but mileage may vary ...

    Is it possible to set the status for these jobs manually, possibly in batches? When we have zombie jobs in "uncancelable" state, we usually right-click and change them to "ENDED_VANISHED" or something.


  • 7.  RE: How to write a SQL statement to change the status of all running instances of a job

    Posted Jul 11, 2019 09:40 AM
    I'm on V12.1


  • 8.  RE: How to write a SQL statement to change the status of all running instances of a job

    Posted Jul 11, 2019 10:05 AM
    You might use the REST Interface to batch - cancel the jobs, if they are canceable at all. I wrote a blogpost about the interface you can find here:
    https://philippelmer.com/guest-post-the-automic-rest-interface/

    To force-change the status, which is often necessary in a case described by you & also suggested by Carsten, you would need to fallback to the ApplicationInterface API.

    For both, REST & ApplicationInterface, I offer a software solution which provides PowerShell Cmdlets to do the above. In WFC::Core (ApplicationInterface), it would be ("Generating" to "ENDED_OK"):
    $ae = New-aeConnection -profile lab
    Search-aeStatistic -name DUMMY_SCRIPT -status 1572 |  Set-aeRunStatus -ae $ae -fromStatus 1572 -targetStatus 1900

    That's it :-). Maybe the command must be slightly changed in case of huge amounts of jobs. But it's still very easy to accomplish. Let me know if there is interest in the solution. It's also useable for object batch-mutation/creation.

    ------------------------------
    Swisscom & WorkflowCommander
    ------------------------------



  • 9.  RE: How to write a SQL statement to change the status of all running instances of a job

    Posted Jul 11, 2019 08:59 AM
    On version 10 i had a similar problem with thousands objects in the activity window with status ended, aborted etc. For the issue i'v got this SQL from support:

    delete from EJ where EJ_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EFC where EFC_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EPDC where EPDC_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EPD where EPD_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EPPF where EPPF_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from ESTP where ESTP_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EEC where EEC_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EQT where EQT_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EET where EET_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from ETI where ETI_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EJPOP where EJPOP_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EOI where EOI_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EEDB where EEDB_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EJPFV where EJPFV_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EJPVA where EJPVA_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EJPPC where EJPPC_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EJPOV where EJPOV_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EJPPF where EJPPF_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EJPPA where EJPPA_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EPUDA where EPUDA_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EJPPO where EJPPO_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EJPPV where EJPPV_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EV where EV_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EJPCV where EJPCV_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from ERB where ERB_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from ECV where ECV_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EJPP where EJPP_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EPUD where EPUD_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from ERET where ERET_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EVP where EVP_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EY where EY_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)
    delete from EH where EH_AH_IDNR in (select EH_AH_IDNR from EH with (nolock) where eh_client = &client# and EH_name = '&jobname#' and EH_Status = &status#)

    Status of the job:
    select EH_Status from EH where EH_Name = 'SCRI.UTIL.EXAMPLE'


  • 10.  RE: How to write a SQL statement to change the status of all running instances of a job

    Posted Jul 11, 2019 10:40 AM

    Does anyone have a query for looking for status of (not exact) FAULT other, start impossible? We've had rare instances of this and not being aware.






  • 11.  RE: How to write a SQL statement to change the status of all running instances of a job

    Posted Jul 11, 2019 11:40 AM
    AH is the statistics table for finished jobs. So you probably want:

    select [ * | whatever ] from AH where AH_STATUS in (code1, code2, code3 ...);

    Status codes can be seen here:
    https://docs.automic.com/documentation/webhelp/english/ARA/11.2/AE/11.2/All%20Guides/Content/ucaaiy.htm

    There's also EH, which also has a corresponding field: EH_STATUS. EH has everything that is activated (formerly known as the content of the "activities window", now "process monitoring").

    Also, please kindly don't hijack threads.

    Cheers :)


  • 12.  RE: How to write a SQL statement to change the status of all running instances of a job

    Posted Jul 11, 2019 09:41 AM
    Hi Alexander,
    This won't help you much but 'update' SQL's should be provided by the support only.

    ------------------------------
    Best regards,
    Thierry

    Banque de Luxembourg
    ------------------------------