zOs/SQL/CATCORT

/*-- techsave Entscheidungs View --------------------------------------
        what 'i%' --> incremental
        what 'f%' --> full
        what ' %' --> no copy needed
     join alias
        s: tablespace
        p: tablePart
        r: table RTS
        i: newest full or incremental save, or log discontinity
        f: newest full                save, or log discontinity
    Achtung: besenWagen behandelt clones richtiger|||
----------------------------------------------------------------------*/
select s.dbName, s.name, p.partition
       , value(r.instance, 1) inst
       , case when f.icType is null then 'f1'
              when f.icType <> 'F'  then 'f2'
              when f.timestamp < current timestamp - 7 days then 'fW'
              when i.icType is null then 'f5'
              when i.icType not in ('I', 'F') then 'f6'
              when i.timestamp < r.copyLastTime - 60 seconds then 'f7'
              when r.nactive * 0.1 <= r.copyupdatedpages then 'fU'
              when r.copyupdatedpages > 0 then 'iU'
              when r.copyChanges > 0 then 'iC'
              when r.copyUpdateLRSN is not null then 'iL'
              else ' n'
         end wh
       , r.copyLasttime
       , r.*
       , i.*
       , f.*
    from sysibm.sysTablespace s
      join sysibm.sysTablePart p
        on s.dbName = p.dbName and s.name = p.tsName
      left join sysibm.sysTableSpaceStats r
        on    s.dbName = r.dbName and s.name = r.name
          and s.dbid = r.dbid and s.psid = r.psid
          and p.partition = r.partition
      left join
         ( select c.*
             , row_number() over(partition by dbName, tsName, dsNum
                                           , instance
                                order by timestamp desc) rn
             from sysibm.sysCopy c
             where c.icType not in ('D', 'M', 'Q', 'R', 'T', 'X', 'Z')
         ) i on i.rn = 1
                and s.dbName = i.dbName and s.Name = i.tsName
                and p.partition = i.dsNum
                and i.instance = value(r.instance, 1)
      left join
         ( select c.*
             , row_number() over(partition by dbName, tsName, dsNum
                                           , instance
                                order by timestamp desc) rn
             from sysibm.sysCopy c
             where c.icType not in ('D','I','M','Q', 'R', 'T', 'X', 'Z')
         ) f on f.rn = 1
                and s.dbName = f.dbName and s.Name = f.tsName
                and p.partition = f.dsNum
                and f.instance = value(r.instance, 1)
    where s.dbName like 'BE%' --and s.name like 'APER%'
    order by 5, 1, 2,3,4
;x;
select s.dbName, s.name, p.partition
       , value(r.instance, 1) inst
       , (select max(timestamp)
             from sysibm.sysCopy c
             where c.icType in ('F', 'I')
                and s.dbName = c.dbName and s.Name = c.tsName
                and p.partition = c.dsNum
                and c.instance = value(r.instance, 1)
         ) copyTst
       , r.copyLasttime
    from sysibm.sysTablespace s
      join sysibm.sysTablePart p
        on s.dbName = p.dbName and s.name = p.tsName
      left join sysibm.sysTableSpaceStats r
        on    s.dbName = r.dbName and s.name = r.name
          and s.dbid = r.dbid and s.psid = r.psid
          and p.partition = r.partition
    where s.dbName = 'DA540769' and s.name like 'APER%'
;x;
select s.dbName, s.name, p.partition , c.dsNum
    from sysibm.sysTablespace s
      join sysibm.sysTablePart p
        on s.dbName = p.dbName and s.name = p.tsName
      left join sysibm.sysTableSpaceStats r
        on    s.dbName = r.dbName and s.name = r.name
          and s.dbid = r.dbid and s.psid = r.psid
          and p.partition = r.partition
      left join
        ( select * from sysibm.sysCopy c
             where timestamp = (select max(timestamp)
         from sysibm.sysCopy a
            where a.icType in ('F', 'I')
                and a.dbName = c.dbName and a.tsName = c.tsName
                and a.dsNum in (0, c.dsNum)
        )   )  c
        on c.icType in ('F', 'I')
          and c.dbName = s.dbName and c.tsName = s.name
          and c.dsNum in (0, p.partition)
    where s.dbName = 'DA540769' and s.name like 'APER%'
;x;
select r.dbName, r.name, r.partition, c.dsNum
     , r.copyLastTime, c.timestamp, r.*, c.*
    from sysibm.sysCopy c
      join sysibm.sysTablespaceStats r
      on c.dbName = r.dbName and c.tsName = r.name
         and c.dsNum in (0, r.partition)
    where c.dbName = 'DA540769' and c.tsName like 'APER24%'
         and c.timestamp = (select max(a.timestamp)
         from sysibm.sysCopy a
            where a.dbName = c.dbName and a.tsName = c.tsName
                and a.dsNum in (0, c.dsNum)
            )
  --       and c.timestamp = r.copyLastTime
;x;
select r.dbName, r.name, r.partition, c.dsNum
     , r.copyLastTime, c.timestamp, r.*, c.*
    from sysibm.sysCopy c
      join sysibm.sysTablespaceStats r
      on c.dbName = r.dbName and c.tsName = r.name
         and c.dsNum in (0, r.partition)
    where c.dbName = 'DA540769' and c.tsName like 'APER24%'
         and c.timestamp = (select max(a.timestamp)
         from sysibm.sysCopy a
            where a.dbName = c.dbName and a.tsName = c.tsName
                and a.dsNum in (0, c.dsNum)
            )
  --       and c.timestamp = r.copyLastTime
;x;
with c as
(
  select * from sysibm.sysCopy c
    where
select *
    from sysibm.sysCopy c
      join sysibm.sysTablespaceStats r
      on c.dbName = r.dbName and c.tsName = r.name
           and c.timestamp = r.copyLastTime
    where c.dbName = 'DA540769' and c.tsName like 'APER24%'
with c as
(
   select *
     from sysibm.sysCopy c
     where timestamp = (select max(a.timestamp)
            from sysibm.sysCopy a
            where a.dbName = c.dbName and a.tsName = c.tsName
                and a.dsNum in (0, c.dsNum)
            )
)