zOs/SQL/REOTOTAL

set current path = oa1t;                                                00010032
  --------------------------------------------------------------------- 00020012
  --  reorgTime by importance                                           00030015
  --------------------------------------------------------------------- 00040012
SElect importance "imp",                                                00050011
       substr(fosFmTime(sum(reorgTime)), 1, 5) "reoTi",                 00060015
       sum(parts) pa, substr(fosFmtE7(sum(real(space) * 1024)),1,7) spc,00070010
       sum(ixParts) ixPa, substr(fosFmtE7(sum(ixSpc)),1,7) ixSpc,       00080010
       sum(i0Parts) i0Pa, substr(fosFmtE7(sum(i0Spc)),1,7) i0Spc        00090010
    from s100447.vReoTs                                                 00100012
    where totalRows is not null                                         00110000
    group by importance                                                 00120000
    order by importance desc                                            00130000
    with ur                                                             00140000
;                                                                       00150004
select current timestamp from sysibm.sysdummy1;                         00160005
------------------------------------------------------------------------00170005
-- reorgTime by importance and beginning of reason                      00180015
------------------------------------------------------------------------00190005
select importance "imp", substr(reason,1, 8),                           00200011
       substr(fosFmTime(sum(reorgTime)), 1, 5) "reoTi",                 00210015
       sum(parts) pa, substr(fosFmtE7(sum(real(space) * 1024)),1,7) spc,00220005
       sum(ixParts) ixPa, substr(fosFmtE7(sum(ixSpc)),1,7) ixSpc,       00230005
       sum(i0Parts) i0Pa, substr(fosFmtE7(sum(i0Spc)),1,7) i0Spc        00240005
    from s100447.vReoTs                                                 00250012
    group by importance, substr(reason, 1, 8)                           00260005
    order by 1 desc, 2                                                  00270005
    with ur                                                             00280005
;                                                                       00290005
select current timestamp from sysibm.sysdummy1;                         00300025
--------------------------------------------------------------------    00310026
-- tsParts die am schnellsten wieder einen Reorg brauchen               00320027
--         ==> sortiert nach lastBuilt desc                             00330028
------------------------------------------------------------------------00340027
select lastBuilt, substr(strip(q.db) || '.' || strip(q.ts)              00350026
                  || ':' || strip(char(q.part)), 1, 20),                00360026
       r,s,y,z,                                                         00370026
       fosFmTime(reorgTime) "reoTime",                                  00380026
       fosFmTime(i0Time) "i0Time",                                      00390026
       importance "imp", reason, q.*                                    00400026
    from s100447.vReoTs q                                               00410026
    left join (                                                         00420026
        select sum(case when icType = 'R' then 1 else 0 end) r          00430026
             , sum(case when icType = 'S' then 1 else 0 end) s          00440026
             , sum(case when icType = 'Y' then 1 else 0 end) y          00450026
             , sum(case when icType = 'Z' then 1 else 0 end) z          00460026
             , dbName db, tsName ts, dsNum part                         00470026
             from sysibm.syscopy                                        00480026
             where timestamp > current timestamp - 21 days              00490026
             group by dbName , tsName , dsNum                           00500026
              ) c                                                       00510026
        on c.db = q.db and c.ts = q.ts and c.part = q.part              00520026
    where importance > 0 -- reason like 'clusterRa%'                    00530029
    order by 1 desc                                                     00540026
    fetch first 100 rows only                                           00550027
    with ur                                                             00560026
;                                                                       00570026
select current timestamp from sysibm.sysdummy1;                         00580026
select substr(strip(q.db) || '.' || strip(q.ts)                         00590031
                  || ':' || strip(char(q.part)), 1, 20),                00600031
       importance "imp", reason,                                        00610033
       fosFmTime(reorgTime) "reoTime",                                  00620031
       fosFmTime(i0Time) "i0Time"                                       00630031
    from s100447.vReoTs q                                               00640031
    where importance > 0                                                00650031
    order by db, ts, part                                               00660031
;                                                                       00670031
select current timestamp from sysibm.sysdummy1;                         00680031