JDE920 and JDE910 batch job performance summary for AS/400 SQL

The following SQL will give you a summary of all batch jobs and how long they’ve taken to run (in seconds) on average on an AS/400 – note that this is specific syntax for the 400.  The date arithmetic is also quite specific for the AS/400.

SELECT JCPID as INNERPID, JCVERS as INNERVERS, TRIM(JCPID)||TRIM(JCVERS), simd || vrjd,
   count(1) as INNERCOUNT,
   Avg((DAYS(jcetdtim) - DAYS(jcstdtim)) * 86400 + (MIDNIGHT_SECONDS(jcetdtim) - MIDNIGHT_SECONDS(jcstdtim))) as INNERAVERAGE,
   min((DAYS(jcetdtim) - DAYS(jcstdtim)) * 86400 + (MIDNIGHT_SECONDS(jcetdtim) - MIDNIGHT_SECONDS(jcstdtim))) AS INNERMIN,
   max((DAYS(jcetdtim) - DAYS(jcstdtim)) * 86400 + (MIDNIGHT_SECONDS(jcetdtim) - MIDNIGHT_SECONDS(jcstdtim))) AS INNERMAX, avg(jcpwprcd) as "ROWS PROCESSED"
from svm910.f986114 t1,ol910.f9860, copd910.f983051, svm910.f986110 t2
where trim(t1.jcpid) = trim(siobnm)
and trim(t1.jcvers) = trim (vrvers)
and trim(t1.jcpid) = trim (vrpid)
and t1.jcjobnbr=t2.jcjobnbr
and t1.jcexehost = 'SYDJDE01'
group by  t1.jcpid, JCVERS, simd || vrjd ;

to reveal

Like my data obfuscation?

So you can quickly see long running jobs or compare averages.

The things that I want to remember out of this is the AS/400 syntax.