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