zOs/SQL/CATCOPIN

with 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
)
, s as
(
select s.dbname, s.name, s.clone, s.instance, i,
       case when s.instance = i then 'base' else 'clone' end baCl,
        c.cInMin, cInMax,
        r.rInMin, rInMax
    from sysibm.sysTablespace s
    full join ci on c=clone
    full join (select dbName, tsname, min(instance) cInMin,
                                    max(instance) cInMax
                 from sysibm.sysCopy c
                 group by dbName, tsname
              ) c
      on c.dbName = s.dbName and c.tsname = s.name
    full join (select dbName, name, min(instance) rInMin,
                                    max(instance) rInMax
                 from sysibm.sysTableSpaceStats r
                 group by dbName, name
              ) r
      on r.dbName = s.dbName and r.name = s.name
)
select count(*),clone, instance, i, baCl, cInMin, cInMax, rInMin, rInMax
    from s
    group by    clone, instance, i, baCl, cInMin, cInMax, rInMin, rInMax
;x;
with q as
(
select s.dbname, s.name, s.clone, s.instance,
        c.cInMin, cInMax,
        r.rInMin, rInMax
    from sysibm.sysTablespace s
    full join (select dbName, tsname, min(instance) cInMin,
                                    max(instance) cInMax
                 from sysibm.sysCopy c
                 group by dbName, tsname
              ) c
      on c.dbName = s.dbName and c.tsname = s.name
    full join (select dbName, name, min(instance) rInMin,
                                    max(instance) rInMax
                 from sysibm.sysTableSpaceStats r
                 group by dbName, name
              ) r
      on r.dbName = s.dbName and r.name = s.name
)
select count(*), clone, instance, cInMin, cInMax, rInMin, rInMax
    from q
    group by     clone, instance, cInMin, cInMax, rInMin, rInMax
;x;
with p as (
    select dbName db, tsname ts, dsnum part, instance inst,
         count(*) cnt,
      min(case when icType = 'F' then timestamp else null end) fullMin,
      max(case when icType = 'F' then timestamp else null end) fullMax
    from sysibm.sysCopy
    group by dbName, tsName, dsNum, instance
)
select s.dbname, s.name, s.clone, s.instance,
        case when inst = instance then 'base' else 'clone' end baCl,
        part, inst, cnt, fullMin, fullMax
    from sysibm.sysTablespace s
    left join p on s.dbName = db and s.name = ts
    where s.dbName like 'DCMN010%'
    order by s.dbName, s.name, part, inst