SET CURRENT PATH = 'OA1T'; 00320000
SET CURRENT SCHEMA = 'OA1T'; 00330000
------------------------------------------------------------------------00340000
-- date next sunday 00350000
------------------------------------------------------------------------00360000
select date(next_day(current date,'sun')) "next sunday", 00370000
current timestamp "now" , 00380000
'v5.4' "version" 00390000
FROM SYSIBM.SYSDUMMY1 00400000
; 00410000
00420000
------------------------------------------------------------------------00530000
-- copy vReoTs in temporary table to avoid 0c4 from err.ew04 00540000
declare global temporary table session.vReoTS as 00550000
( select * from s100447.vReoTs ) 00560000
with no data 00570000
on commit preserve rows 00580000
; 00590000
select current timestamp from sysibm.sysdummy1 00600000
; 00610000
insert into session.vReoTs 00620000
select * from s100447.vReoTs 00630000
where importance > 0 00631000
; 00640000
commit 00650000
; 00680000
---------- next sunday + current time ----------------------------------00681000
select date(next_day(current date,'sun')) "next sunday", 00690000
current timestamp "now" , 00700000
'v5.4' "version" 00710000
FROM SYSIBM.SYSDUMMY1 00720000
; 00730000
00740000
------------------------------------------------------------------------00950000
-- reorgTime by job (or db if job not found) 00960000
------------------------------------------------------------------------00970000
with j as 00980000
( select substr(max(char(runtime) || jobName), 27) job, 00990000
db_name db, ts_name ts 01000000
from TADM11A1 01010000
where UTILITY_TYPE = 'W' 01020000
and jobname like 'QR%' 01030000
group by db_name, ts_name 01040000
order by 2 01050000
) , qrJob as 01060000
( select coalesce(j.job, substr('-' || r.db, 1, 9)) jobName, r.* 01070000
from session.vReoTS r 01080000
left join j 01090000
on j.DB = r.db 01091000
and j.TS = r.TS 01092000
) , g as 01093000
( 01094000
select jobName, 01095000
coalesce(sum(reorgTime), -1) reorgTime, 01096000
sum(parts) pa, sum(real(space) * 1024) spc, 01097000
sum(ixParts) ixPa, sum(ixSpc) ixSpc, 01098000
sum(i0Parts) i0Pa, sum(i0Spc) i0Spc 01099000
from qrJob 01099100
where importance > 0 01099200
group by jobName 01099300
) 01099400
select substr(jobName, 1, 9) "job", 01099500
substr(fosFmte7(reorgTime),1,7) "reoTime", 01099600
pa "parts", substr(fosFmtE7(spc),1,7) "spc", 01099700
ixPa "ixParts", substr(fosFmtE7(ixSpc),1,7) "ixSpc", 01099800
i0Pa "i0Parts", substr(fosFmtE7(i0Spc),1,7) "i0Spc" 01099900
from g 01100000
order by reorgTime desc 01100100
fetch first 20 rows only 01100200
with ur 01100300
; 01100400
---------- next sunday + current time ----------------------------------01100500
select date(next_day(current date,'sun')) "next sunday", 01100600
current timestamp "now" , 01100700
'v5.4' "version" 01100800
FROM SYSIBM.SYSDUMMY1 01100900
; 01101000
------------------------------------------------------------------------01851000
-- ACTIVE TABLESPACE EXCEPTIONS ON NEXT SUNDAY 01852000
------------------------------------------------------------------------01853000
SELECT DB,TS,PARTVON,PARTBIS,GUVON,GUBIS,REORG 01854000
FROM TSTRTS.TRTSREOTSEXCEPTION 01855000
WHERE DATE(NEXT_DAY(CURRENT DATE,'SUN')) BETWEEN GUVON AND GUBIS 01856000
WITH UR 01857000
; 01858000