Activity Window Cleanup for Automic AE v9 with Oracle DB

Document ID : KB000088416
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Activity Window Cleanup for Automic OM / AE with Oracle DB
Resolution:

General

In different situation it mid be necessary to clean up the Activity Window of an Automic Operation Manger / Automic Automation Engine.

  • Example 1 – emergency situation: Remove specific activities after any kind of loop which activated an object multiple.
  • Example 2 – general clean up: Remove all or specific activities after any kind of copy of the Automic database.
  • Example 3 – specific activities: Remove single objects, which don't react on any normal action.

The cleanup is possible in two different ways:

  • Via the User Interface using the function "Modify status manually" – have a look to the Automic Manual for more details.
    This can be done only for singe activities so it's practicable for a small amount of activities.
  • Directly in the Automic database via SQL statements – this way is described in this article.
    This can be used for large amount of activities.

How the SQL statements work

The SQL statements delete all 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 statistic record and for the reports of the activity. As end date, end time the current database timestamp is used (SYSDATE).
    The status of the statistic record 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 activity is deleted.
  • Be aware that manual deletion of the tasks causes the deactivation of the objects 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 activities 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 sometime 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

Activity or Object Name

EH_NAME

EH_NAME = 'SCRI.BIG.RUNFOREVER'

RunID

EH_AH_IDNR

EH_AH_IDNR = 1234567890

Activity or Object Type

EH_OTYPE

EH_OTYPE = 'JOBS'

Status / Status Number

EH_STATUS

EH_STATUS = 1572

Start Timestamp

EH_STARTTIME

EH_STARTTIME < to_date ('2011-06-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

Agent

EH_HOSTDST

EH_HOSTDST like 'WIN%'

 

The different DB 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 ('2011-06-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and EH_HOSTDST like 'WIN%')

So this one will select all activates which are in client 22 and have the object type "JOBS" and the status 1572 – which is "Generating". The start of the Jobs has to be at latest on 15th of June 2011 or earlier and the Jobs run on Agents with the name "WIN…." in the beginning.

You are in doubt – verify fist

Most important is to select the correct activities via the sub-select. If you are in doubt about that, just verify which activities will be affected. Use the sub-select created and add for example client, object type and name and the RunID for 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 ('2011-06-16 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 ('2011-06-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS') instead of SYSDATE will set the end timestamp to 15th of June 2011, 00:00 AM.

 

 

The SQL statements

As the cleanup of activities is a serious action, it should be done guided by a Automic support engineer only! Therefor the statements itself are not posted in this article.

For further information on this knowledgebase article please contact Automic support.