You're a picky MF, aren't you? :-)
Actually I can get all but formatting that last column as a time.
Picture this
ts1-ts2 equals these digit representations of a time
000059
001904
...
You sum these up you get
001963
Well, 63 seconds isn't valid.
If I convert it to a time then you can't do a sum on a time field.
You could look at timestampdiff but you'd have to approve the estimation
rules.
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzscatimedifstmp.htm
Pay attention to the last two columns, especially on a job which runs over
a minute.
But you will get your subtotals.
With T1 as (
Select timestamp_format(substr(FLASTR,69,8) concat ' ' concat
substr(FLASTR,81,8), 'MM/DD/YY HH24:MI:SS') as Start_timestamp,
substr(flastr, 38, 8) as Job_User, substr(flastr, 47, 10) as Job_name,
substr(flastr, 31, 26) as FQ_Job_name
from rob.flastr
where substr(flastr, 38, 8) = 'MIMIXOWN' and substr(flastr, 47, 10) =
'RCV_RUNCMD')
, T2 as (
Select timestamp_format(substr(FLAEND,67,8) concat ' ' concat
substr(FLAEND,79,8), 'MM/DD/YY HH24:MI:SS') as End_timestamp,
substr(flaend, 38, 8) as Job_User, substr(flaend, 47, 10) as Job_name,
substr(flaend, 31, 26) as FQ_Job_name
from rob.flaend
where substr(flaend, 38, 8) = 'MIMIXOWN' and substr(flaend, 47, 10) =
'RCV_RUNCMD'),
t3 as (
select t1.FQ_Job_name, t1.Job_User, t1.Job_name, t1.Start_timestamp,
t2.End_timestamp,
date(t1.Start_timestamp) as Start_date,
dec(sum(timestampdiff(2, char(t2.End_timestamp - t1.Start_timestamp))), 6,
0) as job_seconds,
dec(sum(t2.End_timestamp - t1.Start_timestamp), 6, 0) as
Job_duration_use_for_detail_Only
from t1 left outer join t2 on (t1.FQ_Job_name = t2.FQ_Job_name)
group by grouping sets (
(date(t1.Start_timestamp), t1.FQ_Job_name, t1.Job_User, t1.Job_name,
t1.Start_timestamp, t2.End_timestamp) /* "Detail" */
,(date(t1.Start_timestamp)) /* subtotal by date */
,() /* Grand Total */
)
order by date(t1.Start_timestamp), t1.FQ_Job_name, t1.Job_User,
t1.Job_name, t1.Start_timestamp, t2.End_timestamp
)
select * from T3;
Rob Berendt
midrange.com
