Re: Need to find job start, job end, calculate total job time

Paul,


I have used this for many years.


It requires running the display log command as: DSPLOG PERIOD((*AVAIL *BEGIN) (*AVAIL *END)) OUTPUT(*PRINT) MSGID(CPF1124 CPF1164)


Then using a table defined as: Create Table qtemp.dsplog (dsplog char(132)), copy the dsplog spoolfile into the the table using the command: CPYSPLF FILE(QPDSPLOG) TOFILE(QTEMP/DSPLOG) JOB(JOBNBR/JOBUSER/JOBNAME) SPLNBR(*LAST)


Finally, the SQL statement:


With MSG_DATA As (
Select
Date(Insert(Insert(Digits(Decimal(Integer(20000000 + Mod(Decimal(Replace(Substr(DSPLOG,Locate(' on ',DSPLOG )+4,8),'/',''),9,2)*10000.01,1000000)),8,0)),5,0,'-'),8,0,'-')) As MSG_DATE,
Time(Insert(Insert(Digits(Decimal(Replace(Substr(DSPLOG,LOCATE(' at ',DSPLOG )+4,8 ),':',''),6,0)),3,0,':'),6,0,':')) As MSG_TIME,
Left(DSPLOG,7) As MSGID,
Substr(DSPLOG,31,Locate('/',DSPLOG)-31) As JOBNBR,
Trim(Substr(DSPLOG,Locate('/',DSPLOG)+1,Locate('/',DSPLOG,Locate('/',DSPLOG)+1)-Locate('/',DSPLOG )-1)) As JOBUSER,
Trim(Substr(DSPLOG,Locate('/',DSPLOG,Locate('/',DSPLOG)+1)+1,Coalesce(Nullif(Locate('ended',DSPLOG),0),Locate('started',DSPLOG))-Locate('/',DSPLOG,Locate('/',DSPLOG)+1)- 1)) As JOBNAME,
Integer(Substr(DSPLOG,Locate(';',DSPLOG)+2,Locate('seconds',DSPLOG )- 2- Locate(';',DSPLOG))) As CPU_SEC,
Digits(Decimal(Substr(DSPLOG,Locate(' end code ',DSPLOG)+10,2),2)) As END_CODE

From QTEMP.DSPLOG As A

Where Left (DSPLOG,7) in ('CPF1164','CPF1124'))
Select
A.MSG_DATE As START_DATE,
A.MSG_TIME As START_TIME,
B.MSG_DATE As END_DATE,
B.MSG_TIME As END_TIME,
Char(A.JOBNBR,6) As JOBNBR,
Char(A.JOBUSER,10) As JOBUSER,
Char(A.JOBNAME,10) As JOBNAME,
(Days(Coalesce(B.MSG_DATE,Current Date))-Days(A.MSG_DATE+Case When A.MSG_TIME > B.MSG_TIME Then 1 Else 0 End Days))*24+Hour(Timestamp(Coalesce(B.MSG_DATE,Current Date),Coalesce(B.MSG_TIME,Current Time))-Timestamp(A.MSG_DATE,A.MSG_TIME)) As DIFF_HOURS,
Minute(Timestamp(Coalesce(B.MSG_DATE,Current Date),Coalesce(B.MSG_TIME,Current Time))-Timestamp(A.MSG_DATE,A.MSG_TIME)) As DIFF_MIN,
Second(Timestamp(Coalesce(B.MSG_DATE,Current Date),Coalesce(B.MSG_TIME,Current Time))-Timestamp(A.MSG_DATE,A.MSG_TIME)) As DIFF_SEC,
B.CPU_SEC,
B.END_CODE

From MSG_DATA As A

Left Join MSG_DATA As B
On A.JOBNBR = B.JOBNBR
And A.JOBUSER = B.JOBUSER
And A.JOBNAME = B.JOBNAME
And B.MSGID = 'CPF1164'
Where
A.MSGID = 'CPF1124'

This statement could be used as a View as well.

Jim