zOs/REXX/CATCOPRE

call sqlConnect dbof
say time() 'start'
$;
$>DSN.CATCOPRE.OUT ::v
$<=[
with p as
(
   select dbName db, tsName ts, partition part,
         ( select max(f.timestamp) from sysibm.sysCopy f
             where p.dbName = f.dbName and p.tsName = f.tsName
                  and f.dsNum in (p.partition, 0)
                  and f.icType in ('F', 'R','X')
         ) laFu
     from sysibm.sysTablePart p
     where dbName like '%'
)
select db, ts, part, laFu,
          c.icType, c.dsNum, c.Timestamp, c.dsName
     from p left join sysibm.sysCopy c
           on p.db = c.dbName and p.ts = c.tsName
               and c.dsNum in (p.part, 0)
               and c.icType in ('F', 'I', 'R','X')
               and c.timestamp >= laFu
     order by p.db, p.ts, p.part, c.timestamp desc
$]
   call sqlSel
   cFet = 0
   cInc = 0
   cFul = 0
   cOk  = 0
   cArc = 0
   cErr = 0
   lEla = 0
$|
   say time() 'first'
   $@forWith cc $@[
    cFet = cFet + 1
    if $ICTYPE = 'F' then
        cFul = cFul + 1
    else if $ICTYPE = 'I' then
        cInc = cInc + 1
    else
        iterate
    arc = dsnArc($DSNAME)
    if arc = 'ok' then do
       cOk = cOk + 1
       end
    else do
        if arc = 'arc' then
            cArc = cArc + 1
        else
            cErr = cErr + 1
        $$- left($DB, 8) left($TS,8) right($PART, 4) $*+
             $LAFU $ICTYPE right($DSNUM, 4) left($DSNAME,46) arc
        end
    if time('e') > lEla then do
        say time() cFet 'fet,' cInc 'inc,' cFul 'ful,' ,
                 cOk 'dsnOK,' cArc 'arc,' cErr 'err,' $DB $TS
        lEla = time('e') + 10
        end
    $]
say time() cFet 'fetch,' cInc 'incremental,' cFul 'full,' ,
                 cOk 'dsn ok,' cArc 'archived,' cErr 'errors'
call sqlDisconnect
$#out                                              20130101 10:50:54
$#out                                              20130101 10:50:21
$#out                                              20130101 10:45:54
$#out                                              20130101 10:43:27
*** run error ***
tsoAlloc rc 12 for alloc dd()  DSN('DSN.CATOPTRE.OUT')
$#out                                              20130101 10:33:10
$#out                                              20130101 10:32:02
$#out                                              20130101 10:28:01