Automic Workload Automation

Hi all, I'll open a support ticket if no confident answer, but I get the impression this is something others do regularly. :-)

We have some bad rows in the EH table that won't accept "modify status manually" and won't go away... 
1. Is there known SQL to safely remove them and their dependencies (given eh_ah_idnr)?
2. Do we have to stop the master or anything before performing it?

Thanks!

Is this what you are experiencing Automatically deactivating Workflows run from schedules?

Hi Mark,

It's close; I guess I'd just have to change the WHERE clauses to use my eh_ah_idnr. 

Concerned about doing it while everything is running, but that's what Jen means by "test it first", of course :-)

Anybody else doing this kind of cleanup in live production?

I run the SQL in my production, but be aware that you won't be able to view the Reports for the jobs deleted from the Activities because the Archive(A*) tables aren't updated with the info from the Active(E*) tables.

Hi Jessica,

We've had to do this a couple times over the past few months. Some queries that were provided by support follow. As has been mentioned, always good to get vendor sign off before directly interacting with the prod database! This was for Oracle DB, AE v9 SP8.

For an object named "CALL.MAIL.NOTIFY.HOLD_PENDING" in client 5:

DELETE from EJ where EJ_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EFC where EFC_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EPDC where EPDC_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EPD where EPD_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EPPF where EPPF_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from ESTP where ESTP_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EEC where EEC_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EQT where EQT_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EET where EET_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from ETI where ETI_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EJPOP where EJPOP_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EOI where EOI_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EEDB where EEDB_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EJPFV where EJPFV_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EJPVA where EJPVA_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EJPPC where EJPPC_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EJPOV where EJPOV_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EJPPF where EJPPF_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EJPPA where EJPPA_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EPUDA where EPUDA_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EJPCV where EJPCV_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EJPPO where EJPPO_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EJPPV where EJPPV_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EV where EV_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from ERB where ERB_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from ECV where ECV_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EJPP where EJPP_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EPUD where EPUD_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from ERET where ERET_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EVP where EVP_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EY where EY_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

UPDATE AH set ah_timestamp4 = SYSDATE, ah_status = 1850 where ah_idnr in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

UPDATE RH set rh_timestamp4 = SYSDATE where rh_ah_idnr in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

DELETE from EH where EH_AH_IDNR in (select EH_AH_IDNR from EH where EH_CLIENT = '5' AND EH_NAME = 'CALL.MAIL.NOTIFY.HOLD_PENDING');

commit;

Arguably easier by RunID:

--activity window cleaner, by RunID

 

DELETE from EJ where EJ_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EFC where EFC_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EPDC where EPDC_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EPD where EPD_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EPPF where EPPF_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from ESTP where ESTP_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EEC where EEC_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EQT where EQT_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EET where EET_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from ETI where ETI_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EJPOP where EJPOP_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EOI where EOI_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EEDB where EEDB_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EJPFV where EJPFV_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EJPVA where EJPVA_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EJPPC where EJPPC_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EJPOV where EJPOV_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EJPPF where EJPPF_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EJPPA where EJPPA_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EPUDA where EPUDA_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EJPCV where EJPCV_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EJPPO where EJPPO_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EJPPV where EJPPV_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EV where EV_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from ERB where ERB_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from ECV where ECV_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EJPP where EJPP_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EPUD where EPUD_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from ERET where ERET_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EVP where EVP_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EY where EY_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

UPDATE AH set ah_timestamp4 = SYSDATE, ah_status = 1850 where ah_idnr in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

UPDATE RH set rh_timestamp4 = SYSDATE where rh_ah_idnr in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

DELETE from EH where EH_AH_IDNR in (select EH_AH_IDNR from EH where EH_AH_IDNR = '96325548');

 

commit;

Erik, thanks for the additional support & SQL. One question, did you have to stop the AE or any processes before performing that SQL? 

We have run this SQL(the runid version) in V9 production once.  It was provided to us by support.  We did it without stopping the AE or any of our processes.  We had one of our DBAs run it so they could take a database backup right before running it.  I also had them run it in a non-prod environment first to validate the SQL.  It certainly wouldn't hurt to temporarily stop the queues.  In our case we ran it in a quiet time window, and there were no issues.