zOs/REXX/BESENWA2

$#@
$=dbSy=DBTF
$=hh=3
$=partLim=999999999
$=previewOnly=0
call sqlConnect $dbSy
$;
$>.fEdit('::v')
$<@/sql/
$=ptaInc =- $dbSy = 'DBOF' & sysvar('SYSNODE') == 'RR2'
if $ptaInc then $@=[
 with frTo as
(
 select case when strip(min(dbName)) like '_*' and min(dbName) > 'A*'
            then left(min(dbName), 1) else ''
       end fr,
       case when strip(max(dbName)) like '_*'
            then left(max(dbName), 1) else ''
       end || x'FFFF' to
     FROM DLC.OBJECTS_V13
     WHERE EXCLUDE='I' AND    NAME='QDDBOF INCL EXCLUDES'
)
, p as
$] else $@=[
with p as
$]
$@=[
(
 SELECT PT.DBNAME, pt.tsName, pt.partition,
     (  SELECT char(timestamp) || icType
          FROM  SYSIBM.SYSCOPY CP
          WHERE PT.DBNAME = CP.DBNAME
            AND PT.TSNAME = CP.TSNAME
            AND cp.dsNum in (PT.PARTITION, 0)
            AND  CP.ICTYPE IN ('F','R','X')
            and timestamp < '2012-09-21-03.20.00'
          order by timestamp desc
          fetch first 1 row only
     ) laFull,
     r.nActive,
     COPYLASTTIME,
     COPYUPDATEDPAGES,
     COPYCHANGES,
     COPYUPDATETIME
----  end   @proc selIncrCopy: select fullcopy etc. --------------------
 FROM   SYSIBM.SYSDATABASE DB
$]
if $ptaInc then $@=[
   join frTo
     on db.name >= frTo.fr and db.name <= frTo.to
$]
$@=[
   join SYSIBM.SYSTABLESPACE TS
     on DB.NAME = PT.DBNAME
   join SYSIBM.SYSTABLEPART PT
     on DB.NAME = TS.DBNAME
       AND TS.NAME = PT.TSNAME
   left join SYSIBM.SYSTABLESpaceStats r
      on r.dbid = db.dbid
        and r.psid = ts.psid
        and r.partition = pt.partition
 WHERE  0 = 0
----  end   @proc missFUllcopies1: fehlende Fullcopies -----------------

----  begin @proc exclude ----------------------------------------------
----- begin @proc exclGen: gemeinsame excludes -------------------------
   AND NOT (PT.DBNAME like 'DSNDB%')            -- DB2 CATALOG
   AND NOT (PT.DBNAME LIKE 'DSN8%')             -- IBM TEST DB
   AND NOT (PT.DBNAME LIKE 'WKDBD%')            -- DB2 WORK DATABASE
   AND NOT (PT.DBNAME = 'DSNTESQ')              -- DB2 CATALOG CLONE
   AND NOT (PT.DBNAME LIKE 'DB2MAPP%')          -- REORG MAPPING TABLES
   AND NOT (PT.DBNAME LIKE 'DB2PLAN%')          -- explain tables
   and not translate(PT.dbName, '999999999AAAAAA', '012345678FISWXY')
           = 'DA999999'                         -- user datenbanken
   AND NOT (PT.DBNAME LIKE 'DB2ALA%')           -- marec  generated
   AND NOT (PT.DBNAME LIKE '%MAREC%')           -- marec generated
   AND NOT (PT.DBNAME LIKE 'DACME%')            -- Mail Heinz Bühler
   AND NOT (PT.DBNAME LIKE 'DGDB%')             -- PROTOTYPEN
   AND NOT (PT.DBNAME LIKE 'DGO%')              -- PROTOTYPEN
   AND NOT (PT.DBNAME LIKE '%A1X%')             -- Neue Prototypen
   AND NOT (PT.DBNAME LIKE 'DAU%')              -- Schulung Gerrit
   AND NOT (PT.DBNAME LIKE 'IDT%')              -- ibm tools
----  end   @proc exclGen: gemeinsame excludes ------------------------
   AND NOT (PT.DBNAME LIKE 'OE02%')    -- Mail Ivo Eichmann
   AND NOT (PT.DBNAME LIKE 'CSQ%')     -- M-QUEUE DATENBANK
----  end   @proc exclude ---------------------------------------------
----  end   @proc exclGen: gemeinsame excludes -------------------------

   AND NOT (PT.DBNAME = 'XC01A1P'  AND PT.TSNAME LIKE 'A2%'  )
                                       -- EOS: Armin Breyer
   AND NOT (PT.DBNAME = 'XR01A1P'  AND PT.TSNAME LIKE 'A2%'  )
                                       -- ERET: Armin Breyer
   AND NOT (PT.DBNAME = 'CSQDBOF' AND PT.TSNAME like 'TSBLOB%' )
----  end   @proc exclude ----------------------------------------------

   AND DB.TYPE NOT IN ('T','W')
----  begin @proc missFUllcopies2: fehlende Fullcopies -----------------
   AND TS.NTABLES <> 0
   AND PT.SPACEF <> -1 -- attention space is sometimes wrong|
 )
, q as
(
select case when laFull < char(timestamp('2012-09-20-05.00.00')
- $-[168+$hh$] hours)
                then 'full old'
            when copyUpdateTime > current timestamp - $hh hours
                then 'no newUpd'
            when nActive * 0.1 <= COPYUPDATEDPAGES then 'full upda'
            when COPYUPDATEDPAGES <> 0 or copyChanges <> 0 then 'inc'
            else 'no changes'
            end copy,
      p.*
    from p
)
select *
    from q
    where -- left(copy, 2) <> 'no'
           dbName like 'AV15A%'
 ORDER BY DBNAME, TSNAME, PARTITION
 WITH UR
$]
$/sql/
call sqlSel  $| call fmtFTab
$#out                                              20120921 10:29:05
$#out                                              20120921 10:27:02
$#out                                              20120921 10:25:33
$#out                                              20120921 10:22:26
$#out                                              20120921 10:19:52
$#out                                              20120921 10:19:35
*** run error ***
tsoAlloc rc 12 for alloc dd(CAT1) OLD DSN('V')
$#out