Scheduler Usage
http://scheduler-usage.com/

Query CMR_AJF for jobs statistics
http://scheduler-usage.com/viewtopic.php?f=12&t=4049
Page 1 of 1

Author:  jCarlos [ 29 Jul 2015 11:29 ]
Post subject:  Query CMR_AJF for jobs statistics

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

Query:
Code:
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:
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:

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.

Page 1 of 1 All times are UTC+01:00
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/