In the SQL given, does "RUNINFO_HISTORY.JOB_MEM_NAME IS NULL" determine if the job is active or not?
Thanks & Regards,
1 of 1 people found this helpful
Hi Chandrasekaran Venkataraman
You may also want to look for job definitions that points to Agents Nodes that is not listed on Available Agents. That is also a great way to find jobs that are not being used anymore.
Hmm...we have active jobs that have NULL FOR JOB_MEM_NAME. Doesn't seem to be a reliable way to decide.
Thanks, Adriano! But all our agents are active as other jobs are still running.
Isn't there a simple command line to see when a sub-app ran last? Control-M leaves so much to be desired.
Not sure what you mean. JOB_MEM_NAME is the coulmn name in RUNINFO_HISTORY table that stores the actual job name (not the script/member name). The script/member name is not stored in that table.
Also, the joins on the query mean that it should show jobs that are present in your definitions but not in run info history. Of course, it all depends on how long you retain runinfo_history.
I guess it won't help much if we're retaining just a week's worth.
Does Control-M archive the job run history into a flat file before removing from the database?
Isn't there a simple command line that shows a summary of job run in Control-M?
2 of 2 people found this helpful
By default, Control-M store the last 20 runtime info of each job and therefore you should be able to retrieve the last run info even if it was 2 years ago. You can execute the following query to get all jobs that have not been executed since 1st Jan 2018 as an example.
select schedtab, jobname, max(timestmp) from cmr_runinf group by schedtab, jobname having max(timestmp) <= '20180101000000';
The above query is to be executed against the Control-M/Server database and not the Control-M/EM database.
HI MunKeong Lee
I guess You may want to refer to CMR_STATIS table that would store the last 20th executions. It could work.
On thing to remind to you ALL is that independently of the method or table you are querying for data in order to compare defined jobs last order date or last run date to figure out whether it is still in use and valid to be kept on the database or eligible to be removed, IMHO its is not common to retain a period that long of 2 years for RUNINFO, LOGS and so on, that~s why CMR_STATIS could be the best option as suggested by MK.
There are some cases where defined job is used as internal order as part of On/Do Actions as a recovery for some planned situation that have
never ran and may not have statistics collected also for CMD Line jobs statistics is not compiled.
In general, there is no single solution for that, unless you build a test ENV where you can load a long period of runinfo, logs exports from Production or use CTM Archiving Database for querying where you can have longer periods of data stored.
Hi Chandrasekaran Venkataraman
That approach work for our site, based on the following :
- If the defined job have NODEID that is not a Valid and Active Agent it will be ordered to Active and fall as "Wait Host' status so we make sure that deactivated server/hosts that previously had job executions, have those jobs migrated or deleted from database.