Submitted Job Details with hh:mm:ss job submit, start, and end times JDE920

JDE920-SubmittedJobs-Excecution-Details.sql

SELECT  A.JCJOBNBR,A.JCPROCESSID,TRIM( A.JCEXEHOST), B.JCPID, B.JCVERS, A.JCJOBQUE, A.JCJOBPTY, A.JCJOBSTS, A.JCENHV, A.JCUSER, A.JCJOBTYPE, A.JCSBMDATE, A.JCACTDATE, 
--Convert JDE Submit Time hhmmss to hh:mm:ss
concat(concat(concat(concat( SUBSTR ((RIGHT (REPEAT ('0',6) || A.JCSBMTIME,6)),1,2),':'), SUBSTR ((RIGHT (REPEAT ('0',6) || A.JCSBMTIME,6)),3,2)),':'), SUBSTR ((RIGHT (REPEAT ('0',6) || A.JCSBMTIME,6)),5,2)) as SubTime,/*concat ss*/ 
--Convert IBM Start Time TimeStamp to hh:mm:ss and adjust for utc
case when(INTEGER(SUBSTR ((RIGHT (REPEAT ('0',6) || Replace(SUBSTR (B.JCSTDTIM,12,8),'.',''),6)),1,2))>=6) 
    then concat(concat(concat(concat( INTEGER(SUBSTR ((RIGHT (REPEAT ('0',6) || Replace(SUBSTR (B.JCSTDTIM,12,8),'.',''),6)),1,2)-6),':'), SUBSTR ((RIGHT (REPEAT ('0',6) || Replace(SUBSTR (B.JCSTDTIM,12,8),'.',''),6)),3,2)),':'), SUBSTR ((RIGHT (REPEAT ('0',6) || Replace(SUBSTR (B.JCSTDTIM,12,8),'.',''),6)),5,2)) 
    else concat(concat(concat(concat( INTEGER(SUBSTR ((RIGHT (REPEAT ('0',6) || Replace(SUBSTR (B.JCSTDTIM,12,8),'.',''),6)),1,2)+18),':'), SUBSTR ((RIGHT (REPEAT ('0',6) || Replace(SUBSTR (B.JCSTDTIM,12,8),'.',''),6)),3,2)),':'), SUBSTR ((RIGHT (REPEAT ('0',6) || Replace(SUBSTR (B.JCSTDTIM,12,8),'.',''),6)),5,2))
end as StartTime,
--Convert JDE End Time hhmmss to hh:mm:ss
concat(concat(concat(concat( SUBSTR ((RIGHT (REPEAT ('0',6) || A.JCACTTIME,6)),1,2),':'), SUBSTR ((RIGHT (REPEAT ('0',6) || A.JCACTTIME,6)),3,2)),':'), SUBSTR ((RIGHT (REPEAT ('0',6) || A.JCACTTIME,6)),5,2)) as EndTime
FROM SVM920.F986110 AS A JOIN SVM920.F986114 AS B ON A.JCJOBNBR = B.JCJOBNBR AND A.JCEXEHOST = B.JCEXEHOST AND A.JCPROCESSID = B.JCPROCESSID 
WHERE  a.jcjobque='QBATCH' and B.jcpid= 'R49500'  and a.jcsbmdate>=118005 ORDER BY A.jcjobnbr desc