Query CMR_AJF for jobs statistics
Posted: 29 Jul 2015 11:29
when i query for some jobs in a group i get this:
Query:
Results:
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:
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.
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;
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
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
thanks.