Process Monitoring cleanup for CA Automic Workload Automation with Oracle Database

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

In different situations, it might be necessary to clean up Process Monitoring (Activity Window) of CA Automic Workload Automation (AE / Automation Engine).

• Emergency: Remove specific Tasks after any kind of loop which activated Objects multiple times.
• General clean up: Remove all or specific Tasks, for example after any kind of duplicating the Automation Engine database.
• Specific Tasks: Remove single Tasks, which doesn’t react to any standard action.

The cleanup is possible in two different ways:

• Via the User Interface using the option “Modify Status Manually”, please have a look at the documentation for more details. This can be done for single Tasks only, there is no bulk select possible. So, it’s practical for a small number of Tasks, only.
• Directly in the database via SQL statements, which is described in this article in detail. This can be used for many tasks

How the SQL statements work

The SQL statements delete all Tasks (Activities) specified in the sub-select. Therefore, it’s very important to use the same sub-select for all statements!

• The first delete statements clean up the subordinate tables in the beginning. The order is important because there are relationships between the tables.
• The two update statements set the end date for the Execution (Statistic Record) and for the Reports of the Task. As end date, end time the current database timestamp is used (SYSDATE).

The status of the Execution is set to 1850 which is “ENDED_CANCEL - manually canceled”.
Note: This is important for the reorganization – without a valid end timestamp and end status the entries will never be reorganized.

• Finally, the header record for the Task is deleted.

Be aware that manual deletion of Tasks causes the regular deactivation of these Tasks to be skipped. This has side effects, for example, there will be no Monitor for Workflows (JOBP) available, because the data is transferred to the statistic tables during deactivation.

The sub-select

With the sub-select, it’s possible to choose which Tasks you like to remove. Most common is to specify the client and the name of the Object. This is also used in the examples below.
Here some other fields sometimes used – in general, all fields of the EH table can be used if necessary:
 

Name	DB Field Name	Example
Client	EH_CLIENT	EH_CLIENT = 22
Task or Object Name	EH_NAME	EH_NAME = 'SCRI.RUNFOREVER'
RunID	EH_AH_IDNR	EH_AH_IDNR = 1234567890
Task or Object Type	EH_OTYPE	EH_OTYPE = 'JOBS'
Status / Status Number	EH_STATUS	EH_STATUS = 1572
Start Timestamp	EH_STARTTIME	EH_STARTTIME < to_date ('2018-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
Agent	EH_HOSTDST	EH_HOSTDST like 'WIN%'

The different DB table fields are combined with “or” and “and”. Here an example:

(select EH_AH_IDNR from EH where EH_CLIENT = 22 and EH_OTYPE = 'JOBS' and EH_STATUS = 1572 and EH_STARTTIME < to_date ('2018-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and EH_HOSTDST like 'WIN%')

So, this one will select all Tasks which are in client 22, and have the Object type “JOBS”, and the status 1572 (“Generating”). The start of the Jobs must be before the 15th of June 2018, and the Jobs run on Agents with the name “WIN…” in the beginning.
Note: Timestamps in the database are in UTC.

You are in doubt – verify fist

Most important is to select the correct Tasks via the sub-select. If you are in doubt about that, just verify which Tasks will be affected. Use the sub-select created and add for example client, object type and name, and the RunID for the output. Here an example:

select EH_CLIENT, EH_OTYPE, EH_NAME, EH_AH_IDNR from EH where EH_CLIENT = 22 and EH_OTYPE = 'JOBS' and EH_STATUS = 1572 and EH_STARTTIME < to_date ('2018-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and EH_HOSTDST like 'WIN%';

Use a specific end date

The statements set the end date for the statistic record and for the reports to the current DB time. If a specific timestamp is necessary it is possible to remove SYSDATE and specify specific date and time.
Example:
Use to_date ('2018-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') instead of SYSDATE will set the end timestamp to 15th of June 2018, 00:00 AM.
Note: Timestamps in the database are in UTC.
 

Environment

This article is for Automation Engine environments using an Oracle Database.

Resolution

As the cleanup of Tasks is a serious action, it should be done guided by CA Support engineers only! Therefore, the statements themselves are not posted in this article.
For further information on this knowledgebase article, please contact CA Support.