zOs/SQL/CATCOPEN

select d.name, t.*
    from sysibm.sysDatabase d
    left join
    (
select count(*) cnt, dbname, min(jobNAME) minJob, MAX(JOBNAME) maxJob
    from sysibm.syscopy
    where jobName like 'QCS%'
    group by dbName
    ) t
    on d.Name = t.dbName
    order by d.Name
;
x
select count(*), dbname, min(jobNAME), MAX(JOBNAME)
    from sysibm.syscopy
    where jobName like 'QCS%'
    group by dbName
    order by dbName
;
x
select count(*), jobname, left(dbName, 2)
    from sysibm.syscopy
    where left(dbName, 2) in ('DG', 'DA', 'DF')
    group by jobName, left(dbName, 2)
    order by 3 asc, 1 desc
;
    achtung, Sau langsam|
select count(*), jobname, max(partition), min(partition)
    from sysibm.syscopy c, sysibm.systablepart p
    where c.dbName = p.dbName and c.tsName = p.tsname
        and c.dsNum in (0, p.partition)
    group by jobName
    order by 1 desc
;
 x
with coPa as
  ( select c.*, p.partition
        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
;
x-- very slow
with coPa as
  ( select c.*, p.partition
        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, timestamp, icType, sType
    from coPa c
    where dbName = 'DA139274'
        and c.timestamp = (select max(timestamp) from coPa s
             where s.dbName = c.dbName and s.tsName = c.tsname
                   and s.partition = c.partition)
    order by dbName, tsName, partition, timestamp desc