Automic Workload Automation

Expand all | Collapse all

Removing old, unused objects from our clients

Jump to Best Answer
  • 1.  Removing old, unused objects from our clients

    Posted 10-24-2018 02:21 PM

    We want to remove old, unused objects from our clients. I asked CA Support if there is a way to report objects that have no statistics. They suggested I reach out to AWA Community for a script or SQL query that reports objects with no statistics. Thank you.



  • 2.  Re: We want to remove old, unused objects from our clients. I asked CA Support if there is a way to report objects that have no statistics. They suggested I reach out to AWA Community for a script or SQL query that reports objects with no statistics. Than

    Posted 10-24-2018 03:24 PM

    We prefer to use the OH_LastDate instead of going by statistics. We have some jobs that run say once annually or even less frequently and we do not keep statistics on hand for that long. We also do not use keep last # on the reorg/unload as it seems to slow down performance quite a bit.

     

    Oracle example:

     

    SELECT OH_Client AS "Client", OH_Name AS "Name",
    OH_LastDate AS "Date"
    FROM OH
    WHERE OH_Client = 1000
    and (OH_LastDate < TIMESTAMP '2017-01-01 00:00:00.000'
    or OH_LastDate IS NULL)
    and OH_OType IN ('JOBS','JOBP','SCRI','JOBF','EVNT')
    and OH_DeleteFlag = 0



  • 3.  Re: We want to remove old, unused objects from our clients. I asked CA Support if there is a way to report objects that have no statistics. They suggested I reach out to AWA Community for a script or SQL query that reports objects with no statistics. Than

    Posted 02-26-2019 02:33 PM

    I am looking for something like this as well HaroldPerkins608030.  Did you figure this out?

     

    We do keep stats on our db for 13 months, so we'd capture the annual jobs as well.

     

    We do not use the LastDate field.  It caused us some problems and Support told us to disable it.  I can't quite recall why, but it's no longer populated anymore (LAST_USE in UC_SYSTEM_SETTINGS is set to 0), so we have to use the AH table somehow.

     

    Any other suggestions?



  • 4.  Re: We want to remove old, unused objects from our clients. I asked CA Support if there is a way to report objects that have no statistics. They suggested I reach out to AWA Community for a script or SQL query that reports objects with no statistics. Than
    Best Answer

    Posted 02-26-2019 03:01 PM

    Here is a version that finds objects that have no statistics, and I added a filter to remove objects from the list that typically don't have statistics.  Finding unused variables, promptsets, and things like that will require a very different search.  (I did this on SQLServer)

     

    SELECT OH_Client AS "Client", OH_Name AS "Name", oh_OType as "Type",
    OH_LastDate AS "Date"
    FROM OH
    WHERE OH_Client = 100
    and oh_deleteflag = 0
    and not OH_OType in ('FOLD', 'VARA', 'USER', 'SYNC', 'USRG', 'LOGIN', 'PRPT', 'JOBI', 'FILTER', 'CONN')
    and not exists (select 1 from ah where ah_oh_idnr = oh_idnr)



  • 5.  Re: We want to remove old, unused objects from our clients. I asked CA Support if there is a way to report objects that have no statistics. They suggested I reach out to AWA Community for a script or SQL query that reports objects with no statistics. Than

    Posted 02-27-2019 06:03 PM

    Thanks petwir!  I excluded a few more object types (timezones, calendars & host groups), but this looks good.  Much appreciated!!