zOs/SQL/CATCOPLA

-- syscopy last icType pro Partition / Instance
with cx as
(
select dbName db, tsName ts, dsNum part, instance, icType, sType
       , count(*) cnt
       , max(char(timestamp) || ' p' || char(smallint(dsNum))
             || ' j' || jobName || ' d' || dsName) la
    from sysibm.sysCopy
    group by dbName, tsName, dsNum, instance, icType, sType
)
, c0 as
( select * from cx where part = 0
)
, c1 as
( select * from cx where part <> 0
)
, co as
(
select
    value(c0.db, c1.db) db,
    value(c0.ts, c1.ts) ts,
    value(c0.part, c1.part) part,
    value(c0.instance, c1.instance) instance,
    value(c0.icType, c1.icType) icType,
    value(c0.sType, c1.sType) sType,
    smallint(c1.cnt) c1Cnt,
    smallint(c0.cnt) c0Cnt,
    value(max(c1.la,  c0.la) , c1.la,  c0.la,   '') la
  from c1 full join c0
  on c1.db = c0.db and c1.ts=c0.ts and c1.part = c0.part
    and c1.instance = c0.instance
    and c1.icType = c0.icType and c1.sType = c0.sType
)
, ci(c, i) as
(           select 'N', 1 from sysibm.sysDummy1
  union all select 'Y', 1 from sysibm.sysDummy1
  union all select 'Y', 2 from sysibm.sysDummy1
)
, p as
(
 SELECT substr(PT.DBNAME, 1, 8) db, substr(pt.tsName, 1, 8) ts,
     smallint(pt.partition) part,
     icType, sType, c1Cnt, c0Cnt, la,
     smallInt(ci.i) inst, ts.clone, ts.instance,
     case when ts.instance = i then 'base' else 'clone' end baCl,
     r.nActive,
     COPYLASTTIME,
     COPYUPDATEDPAGES,
     COPYCHANGES,
     COPYUPDATETIME
----  end   @proc selIncrCopy: select fullcopy etc. --------------------
   from SYSIBM.SYSTABLESPACE TS
   join SYSIBM.SYSTABLEPART PT
     on pt.dbNAME = TS.DBNAME
       AND pt.tsNAME = ts.NAME
   join ci on ci.c=ts.clone
   left join co on co.db = pt.dbName and co.ts = pt.tsName
               and co.part = pt.partition and co.instance = ci.i
   left join SYSIBM.SYSTABLESpaceStats r
      on r.dbid = ts.dbid
        and r.psid = ts.psid
        and r.partition = pt.partition
        and r.instance = ci.i
)
select * from p
    WHERE  db = 'XBDJJ001' and ts = 'SIT03001'
    order by db, ts, inst, part desc, la desc
    with ur