Query CMR_AJF for jobs statistics

A lot of scripts ans example to extract information from Control-M tools.
Post Reply
User avatar
jCarlos
Nouveau
Nouveau
Posts: 36
Joined: 19 Jun 2013 12:00

Query CMR_AJF for jobs statistics

Post by jCarlos » 29 Jul 2015 11:29

when i query for some jobs in a group i get this:

Query:

Code: Select all

select JOBNAME as JOB,
       (
          case when STARTRUN is not null and STARTRUN <> ' '
             then substr(STARTRUN, 1, 8) || ' ' || substr(STARTRUN, 9, 2) || ':' || substr(STARTRUN, 11, 2) || ':' || substr(STARTRUN, 13, 2)
             else ''
          end
       ) as START,
       (
          case when ENDRUN is not null and STARTRUN <> ' '
             then substr(ENDRUN, 1, 8) || ' ' || substr(ENDRUN,   9, 2) || ':' || substr(ENDRUN,   11, 2) || ':' || substr(ENDRUN,   13, 2)
             else ''
          end
       ) as END,
       (
          case when STATUS = 'Y'
             then '0'
             else '1'
          end
       ) as STATUS
from   CMR_AJF
where APPLGROUP in ('MONTHLY-TABLE')
and    HOLDFLAG <> 'D'
ORDER BY END;
Results:

Code: Select all

MONTHLY-JOB-1	20150728 19:01:22    20150728 19:01:22    0                                   
MONTHLY-JOB-2	20150728 19:01:24    20150728 19:01:53    0                                   
MONTHLY-JOB-3	20150728 19:01:54    20150728 19:01:56    0                                   
MONTHLY-JOB-4	20150728 19:01:56    20150728 19:01:58    0                                   
MONTHLY-JOB-5	20150728 19:01:57    20150728 19:01:58    0                                   
MONTHLY-JOB-6	20150728 19:01:56    20150728 19:02:03    0   
but some of these jobs end not ok so i have to execute some procedures to correct the problem and then rerun the job.

the problem is, I need the data of each time the job were executed, example:

Code: Select all


MONTHLY-JOB-1	20150728 19:01:22    20150728 19:01:22    0                                   
MONTHLY-JOB-2	20150728 19:01:24    20150728 19:01:53    0                                   
MONTHLY-JOB-3	20150728 19:01:54    20150728 19:01:56    0                                   
MONTHLY-JOB-4	20150728 19:01:56    20150728 19:01:58    0                                   
MONTHLY-JOB-5	20150728 19:01:57    20150728 19:01:58    0                                   
MONTHLY-JOB-6	20150728 19:01:56    20150728 19:02:03    0  
MONTHLY-JOB-6	20150728 20:01:56    20150728 20:02:03    0   << this is the second time 

but i just get 1 execution time for each job, is it possible to get all execution time for each job when getting these statistics?

thanks.

Post Reply