zOs/SQL/TECSV

select instance, count(*)
    from sysibm.sysCopy
    group by instance
    with ur
;x;
xelect current timestamp from sysibm.sysDummy1
;
-- #HCCD (TS) FULL IMAGE COPY > 7 DAYS
-- SELECT  'DI,PI,PA,IN' , DBID , PSID , PARTITION , INST
 xelect what, count(*)
  from
   ( select s.dbName, s.name, p.partition
       , c.inst, s.dbid, s.psid
       , case when c.inst is null
                  then raise_Error(70001, 'c.inst null '
                     || s.dbName || '.' || s.name)
              when s.nTables < 1 then 'n noTables ' || s.nTables
              when p.space < 0      then 'n defineNo ' || s.space
              when f.icType is null then 'f f.icType null'
              when f.icType <> 'F'  then 'f f.logDiscontinuity'
              when f.timestamp < current timestamp-7 days then 'f week'
              when i.icType is null then 'f i.icType null'
              when i.icType not in ('I','F') then 'f i.logDiscontinuity'
              when r.updateStatsTime is null then 'f noRts'
              when r.copyLastTime is null then 'f r.copyLast null'
              when i.timestamp < r.copyLastTime - 60 seconds
                     then 'f i << r.copyLast'
              when r.nactive * 0.1 <= r.copyupdatedpages
                     then 'f updates'
              when r.copyupdatedpages > 0 then 'i updates'
              when r.copyChanges > 0 then 'i changes'
              when r.copyUpdateLRSN is not null then 'i openUpdate'
              else 'n noUpdates'
         end what
    from sysibm.sysTablespace s
      left join  -- clone handling: add instances
          ( select           'N', 1, 1, '-' from sysibm.sysDummy1
            union all select 'Y', 1, 1, 'B' cl from sysibm.sysDummy1
            union all select 'Y', 1, 2, 'C' cl from sysibm.sysDummy1
            union all select 'Y', 2, 1, 'C' cl from sysibm.sysDummy1
            union all select 'Y', 2, 2, 'B' cl from sysibm.sysDummy1
          ) c (sC, sI, inst, cln)
        on s.clone = c.sC and s.instance = c.sI
      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 and r.instance = c.inst
      left join -- newest incremental or full copy or log discontinuity
         ( 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 = c.inst
      left join -- newest full copy or log discontinuity
         ( 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 = c.inst
    ) q
  group by what
/*  where (what is null or what = '' or left(what, 1) not in (' ','i'))
   and s.dbName = 'DA540769'
   and dbName = 'DA540769'
-- AND NOT TRANSLATE(DBNAME, '999999999AAAAAA', '012345678FISWXY')
--         = 'DA999999'                         -- USER DATENBANKEN
   AND NOT (DBNAME ='CSQDBAF' AND NAME ='TSBLOB1') -- MQ LOB
   AND NOT (DBNAME ='CSQDBAF' AND NAME ='TSBLOB2') -- MQ LOB
   AND NOT (DBNAME ='CSQDBAF' AND NAME ='TSBLOB3') -- MQ LOB
   AND NOT (DBNAME ='CSQDBAF' AND NAME ='TSBLOB4') -- MQ LOB
   AND NOT (DBNAME ='CSQDBAF$' AND NAME ='TSBLOB1') -- MQ LOB
   AND NOT (DBNAME ='CSQDBAF$' AND NAME ='TSBLOB2') -- MQ LOB
   AND NOT (DBNAME ='CSQDBAF$' AND NAME ='TSBLOB3') -- MQ LOB
   AND NOT (DBNAME ='CSQDBAF$' AND NAME ='TSBLOB4') -- MQ LOB
   AND NOT (DBNAME LIKE 'DACME%%')              -- MAIL HEINZ BÜHLER
   AND NOT (DBNAME LIKE 'DAU%%')                -- SCHULUNG GERRIT
   AND NOT (DBNAME LIKE 'DB2ALA%%')             -- MAREC  GENERATED
   AND NOT (DBNAME LIKE 'DB2MAPP%%')            -- REORG MAPPING TABLES
   AND NOT (DBNAME LIKE 'DB2PLAN%%')            -- EXPLAIN TABLES
   AND NOT (DBNAME LIKE 'DGDB%%')               -- PROTOTYPEN
   AND NOT (DBNAME LIKE 'DGO%%')                -- PROTOTYPEN
   AND NOT (DBNAME LIKE 'DI017%%')              -- PROTOTYPEN
   AND NOT (DBNAME LIKE 'DSNDB%%')              -- DB2 CATALOG
   AND NOT (DBNAME = 'DSNTESQ')                 -- DB2 CATALOG CLONE
   AND NOT (DBNAME LIKE 'DSN8%%')               -- IBM TEST DB
   AND NOT (DBNAME LIKE '%%MAREC%%')            -- MAREC GENERATED
   AND NOT (DBNAME LIKE '%%A1X%%')              -- NEUE PROTOTYPEN
   AND NOT (DBNAME LIKE 'OE02%%')               -- MAIL IVO EICHMANN
   AND NOT (DBNAME LIKE 'WKDBD%%')              -- DB2 WORK DATABASE
 */
 ;
select current timestamp from sysibm.sysDummy1
;