set current path = oA1t ; 00010045
set current schema = oA1t ; 00020045
select current timestamp from sysibm.sysdummy1 00030000
; 00040000
------------------------------------------------------------------------00050000
-- reorgTime by job (or db if job not found) 00060000
------------------------------------------------------------------------00070000
with j as 00080019
( select substr(max(char(runtime) || jobName), 27) job, 00090025
db_name db, ts_name ts 00100019
from TADM11A1 00110021
where UTILITY_TYPE = 'W' 00120021
and jobname like 'QR%' 00130024
group by db_name, ts_name 00140020
order by 2 00150019
) , qrJob as 00160019
( select coalesce(j.job, substr('-' || r.db, 1, 9)) jobName, r.* 00170026
from S100447.vReoTS r 00180046
left join j 00190019
on j.DB = r.db 00200019
and j.TS = r.TS 00210019
) , g as 00220022
( 00230000
select jobName, 00240000
coalesce(sum(reorgTime), -1) reorgTime, 00250000
sum(parts) pa, sum(real(space) * 1024) spc, 00260000
sum(ixParts) ixPa, sum(ixSpc) ixSpc, 00270000
sum(i0Parts) i0Pa, sum(i0Spc) i0Spc 00280000
from qrJob 00290000
where importance > 0 00300000
group by jobName 00310000
), o as 00320048
( 00330048
select * 00340049
from g 00350048
order by reorgTime desc 00360048
) 00370048
, r as 00380054
( select o.*, rownumber() over() rowNum 00390054
from o 00400054
) 00410054
select substr(jobName, 1, 9) "job", 00420000
substr(fosFmTime(reorgTime),1,5) "reoTime", 00430030
pa "parts", substr(fosFmtE7(spc),1,7) "spc", 00440000
ixPa "ixParts", substr(fosFmtE7(ixSpc),1,7) "ixSpc", 00450000
i0Pa "i0Parts", substr(fosFmtE7(i0Spc),1,7) "i0Spc" 00460053
from r 00470055
where rownum <= 10 00480054
or reorgTime > 1800 00490050
with ur 00500024
; 00510000
select current timestamp from sysibm.sysdummy1; 00520000