Automic Workload Automation

 View Only
  • 1.  How do I know which objects are producing the largest reports?

    Posted 7 days ago
    How do I know which objects are producing the largest reports?

    I referred the following link
    How do I know which objects are producing the largest reports?
    Broadcom remove preview
    How do I know which objects are producing the largest reports?
    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.
    View this on Broadcom >

    it produces RT_AH_IDNR list, but I could not find the objects name from the RT_AH_IDNR, it did not show anything

    ex:
    select * from rt where RT_AH_IDNR=9799901

    ------------------------------
    Dominic I
    ------------------------------


  • 2.  RE: How do I know which objects are producing the largest reports?

    Posted 7 days ago

    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;

    That should also return an OH_NAME that you could be searching for

    If you had a list of RT_AH_IDNRs, then you can link that to the AH table and pull the additional data, ex:

    Select AH_NAME, AH_CLIENT from AH where AH_IDNR in (your query from above)




  • 3.  RE: How do I know which objects are producing the largest reports?

    Posted 6 days ago
    Thank you so much for your help, I can get the details by "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;".

    However, I could not find any details from AH table. The query "Select AH_NAME, AH_CLIENT from AH where AH_IDNR=10053766;" does not return any value. Is the query correct?


    ------------------------------
    Dominic I
    ------------------------------



  • 4.  RE: How do I know which objects are producing the largest reports?

    Posted 6 days ago

    Ahh I think I see the confusion.

    The result it gives isn't actually an AH_IDNR, it is a count of the number of RT records that match the OH_Name returned. So not a single run, but the total number of instances of that object in the reports - the OH_NAME itself should be all you need to inspect an individual object causing that much total activity

    If you wanted to dig to a more exact run, you'd need to run a more expensive query, but this

    select RT_AH_IDNR from RT group by RT_AH_IDNR order by COUNT(RT_AH_IDNR) desc;

    Would give a list of just runid's which you could limit/look at.




  • 5.  RE: How do I know which objects are producing the largest reports?

    Posted 6 days ago
    Wow, great... I got the desired result. Thank you so much for your help.

    ------------------------------
    Dominic I
    ------------------------------



  • 6.  RE: How do I know which objects are producing the largest reports?

    Posted 4 days ago
    Considering the bigest reports of jobs are the truncated ones you can use these SQL to find the truncated logs:

    select OH_CLIENT as Mandant, OH_NAME as Jobname, Parent.AH_IDNR as RunNr, ah.AH_HOSTSRC as Agent, ah.ah_filenamesrc as Reportdatei from AH, AH Parent, OH where
    ah.ah_parentact = Parent.ah_idnr
    and Parent.ah_oh_idnr = OH_IDNR
    and ah.AH_OTYPE='REPORT'
    and ah.AH_TimeStamp4 >= to_timestamp('&DATE# 00:00:00', 'YYYYMMDD HH24:MI:SS')
    and ah.AH_TimeStamp4 <= to_timestamp('&DATE# 23:59:59', 'YYYYMMDD HH24:MI:SS')
    and ah.AH_MSGNR=29007
    ORDER by Mandant, Jobname, RunNr