This query will give you a performance summary of your batch jobs for the time period that you specify. It’ll give you number of times the report|version combo has been run, average runtime, slowest run and fastest run. That is pretty nice. You can run this daily or weekly and do comparisons. This is an advanced version that shows the “friendly” name too, so functional people will understand what you are talking about.
I’ve said here, give me the summary of jobs run between the 29/5 and the 11/5.
SELECT JCPID as INNERPID, JCVERS as INNERVERS, simd || vrjd,
count(1) as INNERCOUNT, Avg(86400*(JCETDTIM-JCSTDTIM)) as INNERAVERAGE, min(86400*(JCETDTIM-JCSTDTIM)) AS INNERMIN, max(86400*(JCETDTIM-JCSTDTIM)) AS INNERMAX
from svm910.f986114,ol910.f9860, pd910.f983051
where trim(jcpid) = trim(siobnm) and trim(jcvers) = trim (vrvers) and trim(jcpid) = trim (vrpid)
and TO_CHAR(JCETDTIM, 'DDMMYYYY') < '29052015' and TO_CHAR(JCETDTIM, 'DDMMYYYY') > '11052015'
group by jcpid, JCVERS, simd || vrjd ;
1 comment:
Nice one - just added some rounding to make it better :
select INNERPID, INNERVERS, boundvals, INNERCOUNT, round(INNERAVERAGE), round(INNERMIN), round(iNNERMAX)
from (
SELECT JCPID as INNERPID, JCVERS as INNERVERS, simd || vrjd as BOUNDVALS,
count(1) as INNERCOUNT, Avg(86400*(JCETDTIM-JCSTDTIM)) as INNERAVERAGE, min(86400*(JCETDTIM-JCSTDTIM)) AS INNERMIN, max(86400*(JCETDTIM-JCSTDTIM)) AS INNERMAX
from svm910.f986114,ol910.f9860, pd910.f983051
where trim(jcpid) = trim(siobnm) and trim(jcvers) = trim (vrvers) and trim(jcpid) = trim (vrpid)
and TO_CHAR(JCETDTIM, 'DDMMYYYY') < '31052015' and TO_CHAR(JCETDTIM, 'DDMMYYYY') > '01052015'
group by jcpid, JCVERS, simd || vrjd)
Post a Comment