zOs/SQL/CATCOPRC

-- find the startpoint for a recovery or logDiscontinuity
--     and all subsequent incremental image copies
--     assumption: only part level copies for partitioned TS
declare global temporary table session.copy
    ( db char(8) not null
    , ts char(8) not null
    , pa integer not null
    , typ char(1) not null
    , tst timestamp not null
    , dsName char(44) not null
    ) ;
--  as (select * from sysibm.syscopy) with no data;
create index session.iCopy on session.copy
    (db, ts, pa, tst, typ)
;
select current timestamp from sysibm.sysDummy1
;
insert into session.copy
   select * from
   (
   select dbName db, tsName ts, dsNum pa, icType typ, timestamp
       , dsName
       from sysibm.sysCopy
       where icType in ('I', 'F', 'R', 'S', 'W', 'Y')
   union select dbName db, tsName ts, partition pa, 'c' typ,
        createdTS, ''
        from sysibm.sysTablePart
   ) x
            where db     like 'DGDB9%'
;
select current timestamp from sysibm.sysDummy1
;
with recSta as
  ( select c.*
        from session.copy c
        where c.tst >=
          ( select max(a.tst)
                from session.copy a
                where c.db = a.db and c.ts = a.ts
                    and c.pa = a.pa
                    and typ in ('c', 'F', 'R', 'S', 'W', 'Y')
          )
  )
select * from recSta
    order by db, pa, ts
;
select current timestamp from sysibm.sysDummy1
;
rollback
;
sdf
select count(*), min(icType), max(icType),
    dbName, tsName, dsNum, timeStamp
    from sysibm.syscopy
    group by dbName, tsName, dsNum, timeStamp
    order by 1 desc
;
sdf
            ( select timestamp c.tsName, c.partition, max(timestamp)
        where
         from sysibm.syscopy c, sysibm.systablepart p
         where c.dbName = p.dbName and c.tsName = p.tsname
             and c.dsNum in (0, p.partition)
   )
 select dbName, tsName, partition, dsNUm,
     ( select max(timestamp) from coPa s
           where s.dbName = c.dbName and s.tsName = c.tsname
               and s.partition = c.partition
               and s.icType in ('F') ) copy,
     ( select max(timestamp) from coPa s
           where s.dbName = c.dbName and s.tsName = c.tsname
               and s.partition = c.partition
               and s.icType in ('S', 'W', 'Y') ) noLog
     from coPa c
     where dbName = 'DA540769'
     order by dbName, tsName, partition, timestamp desc