Automic Workload Automation

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.

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

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?

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)

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