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'
Original Message:
Sent: 07-10-2019 04:43 PM
From: Kaplan
Subject: How to write a SQL statement to change the status of all running instances of a job
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,