How do I know which objects are producing the largest reports?

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

How do I know which objects are producing the largest reports?

Environment

Release: AOATAM99000-8.0-Automic-One Automation Tools-Application Manager
Component:

Resolution

Detailed Description and Symptoms

PLEASE NOTE: Automic Support does not recommend running SQL statements against a production database and cannot provide Support for any problems that arise due to running SQL statements. We would recommend running SQL statements agains a real-time clone or copy of a production database if necessary in order to lower the impact to the Automic system.

 

?In Operations Managers, more thank likely, the most space in the database will be used by reports. Sometimes it's important to see which objects create the largest reports in order to make the database leaner and smaller.

Investigation

?The largest reports in the RT table are probably going to be from Client 0 and will be for WPs, CPs, and agent logging.  To look for the object with the most rows in the RT table (top 50), you can use:
 
 select top 50 oh_name, count(RT_AH_IDNR) from rt, ah, oh where rt_ah_idnr = ah_idnr and ah_oh_idnr = oh_idnr group by OH_NAME order by COUNT(RT_AH_IDNR) desc;
 
 If you want to look for everything only within a certain client, you can use the following statement (replacing <CLIENT NUMBER> with a number):
 
 select top 50 oh_name, count(RT_AH_IDNR) from rt, ah, oh where rt_ah_idnr = ah_idnr and ah_oh_idnr = oh_idnr and oh_client = <CLIENT NUMBER> group by OH_NAME order by COUNT(RT_AH_IDNR) desc;
 
 To remove rows from non-temporary tables, please use the Automic utilities (archive, reorg, and unload).