zOs/REXX.O13/CONSUMGE

$#@
$=out=~tmp.sql
$=exclNew=1
if 1 then $@[
    $>$out(rz2Sql)
    $@gen{rz2, dbof, QMW0008}
    $@gen{rz2, dp2g, QMW0013}
    $@gen{rz2, dvbp, QMW0010}
$]
if 1 then $@[
    $>$out(rz4Sql)
    $@gen{rz4, dbol}
    $@gen{rz4, dp4g}
$]
if 1 then $@[
    $>$out(rz1Sql)
    $@gen{rz1, dbaf, QMW0001}
    $@gen{rz1, dbtf, QMW0002}
    $@gen{rz1, dbba, QMW0005}
    $@gen{rz1, dblf}
    $@gen{rz1, dvtb, QMW0006}
    $@gen{rz1, dboc, QMW0007}
$]
if 1 then $@[
    $>$out(rz8Sql)
    $@gen{rz8, dc0g}
    $@gen{rz8, dd0g}
    $@gen{rz8, dp8g}
    $@gen{rz8, daeg}
    $@gen{rz8, dcvg}
    $@gen{rz8, ddvg}
    $@gen{rz8, dx0g}
$]
if 1 then $@[
    $>$out(rzzSql)
    $@gen{rzz, de0g}
    $@gen{rzz, devg}
$]

$@proc gen $@[
    parse upper arg ., rz, subsys
    $=rz=-rz
    $=subsys=-subsys
    $=isElar=-wordPos($subsys, 'DVBP DBBA DVTB DEVG DCVG DDVG')>0
    say 'gen rz='$rz', subsys='$subsys', isElar='$isElar
    $$ [$subsys]
    if m.done.rz \== 1 then $@[
        $@rzBegin()
        m.done.rz = 1
        $]
$@=/genAll/
--************************************************************
$@[if \ $isElar then  $@=[
--$'$$' fehlende Fullcopies Tablespaces, letzte 8 Tage:
$] else $@=[
--$'$$' DXB - fehlende Fullcopies TS, letzte 8 Tage:
$] $]
--************************************************************

$@missFullcopies1()

$@exclude{PT}

$@missFullcopies2db{8}

--************************************************************
$@[if \ $isElar then  $@=[
--$'$$' fehlende Fullcopies Indexspaces, letzte 8 Tage:
$] else $@=[
--$'$$' DXB - fehlende Fullcopies IX, letzte 8 Tage:
$] $]
--************************************************************

 SELECT SUBSTR(IX.CREATOR,1,8) AS CREATOR
       ,SUBSTR(IX.NAME,1,8) AS IXNAME
       ,SUBSTR(IX.DBNAME,1,8) AS DBNAME
       ,SUBSTR(IX.INDEXSPACE,1,8) AS IXSPACE
       ,IP.PARTITION
       ,DATE(IX.CREATEDTS) AS CREATEDATE
 FROM SYSIBM.SYSINDEXES IX,
      SYSIBM.SYSINDEXPART IP
 WHERE IX.CREATOR = IP.IXCREATOR
   AND IX.NAME    = IP.IXNAME
   AND IX.COPY    = 'Y'
   AND IP.SPACE <> -1
   AND DATE(IX.CREATEDTS) < CURRENT DATE

   $@exclude{IX}
   AND NOT EXISTS
       (SELECT ' '
        FROM  SYSIBM.SYSCOPY CP
        WHERE IX.DBNAME     = CP.DBNAME
          AND IX.INDEXSPACE = CP.TSNAME
          AND (IP.PARTITION = CP.DSNUM OR CP.DSNUM = 0)
          AND CP.ICTYPE IN ('F','R','X')
          AND CP.TIMESTAMP > CURRENT TIMESTAMP - 8 DAYS
       )
 ORDER BY CREATOR, IXNAME, IP.PARTITION
 WITH UR;

--************************************************************
$@[if \ $isElar then  $@=[
--$'$$' Imagecopy Datasets die nicht katalogisiert sind:
$] else $@=[
--$'$$' DXB - Imagecopy Datasets, nicht katalogisiert:
$] $]
--************************************************************

$@[ if $exclNew | wordPos($subsys, DBOF) > 0 then $@=[
WITH DS AS
(
SELECT DBNAME, TSNAME, DSNUM
      ,MAX(ICDATE) ICDATE
      ,MAX(JOBNAME)JOBNAME
      ,DSNAME
  FROM SYSIBM.SYSCOPY C
 WHERE ICTYPE IN ('F','I')
   AND C.TIMESTAMP >= CURRENT TIMESTAMP - 21 DAYS
$] else $@=[
SELECT SUBSTR(DBNAME,1,8) AS DBNAME
      ,SUBSTR(TSNAME,1,8) AS TSNAME
      ,CHAR(DSNUM) AS PART
      ,ICDATE
      ,JOBNAME
      ,DSNAME
  FROM SYSIBM.SYSCOPY C
 WHERE ICTYPE IN ('F','I')
   AND C.TIMESTAMP >= CURRENT TIMESTAMP - 21 DAYS
   AND S100447.DSLOCATE(DSNAME) IS NULL
$]
$]

$@exclude{C}
$@[ if $exclNew | wordPos($subsys, DBOF) > 0 then $@=[
 GROUP BY DBNAME, TSNAME, DSNUM, DSNAME
)
SELECT SUBSTR(DBNAME,1,8) AS DBNAME
      ,SUBSTR(TSNAME,1,8) AS TSNAME
      ,CHAR(DSNUM) AS PART
      ,ICDATE, JOBNAME, DSNAME
    FROM DS
    where S100447.DSLOCATE(DSNAME) IS NULL
$]
$]
ORDER BY DBNAME, TSNAME, PART
WITH UR;

$@[
if wordPos($subsys, DBOF) > 0 then
$@=[
--************************************************************
--$'$$' fehlende Fullcopies XBS Tablespaces, letzte 2 Tage:
--************************************************************

$@missFullcopies1()
   AND PT.DBNAME = 'BE01A1P'
   AND ( PT.TSNAME LIKE 'A00%'
      OR PT.TSNAME LIKE 'A01%'
      OR PT.TSNAME LIKE 'A02%' )
$@missFullcopies2{2}

$]
if $isElar & $subsys \= DBBA then $@=/elar2/
--************************************************************
--$'$$' XB  - kein oder falscher eintrag in txbc181:
--************************************************************

SELECT SUBSTR(T1.DBNAME,1,8)  AS DBNAME
      ,SUBSTR(T1.NAME,1,8)    AS NAME
      ,T1.PARTITION
      ,SUBSTR(T1.CREATOR,1,8) AS CREATOR
      ,T1.TABLE
      ,ST.XBC181_STATUS
  FROM (
SELECT SP.DBNAME
      ,SP.NAME
      ,PT.PARTITION
      ,SUBSTR(TB.CREATOR,1,18) AS CREATOR
      ,SUBSTR(TB.NAME,1,18)    AS TABLE
  FROM SYSIBM.SYSTABLESPACE SP
      ,SYSIBM.SYSTABLEPART  PT
      ,SYSIBM.SYSTABLES     TB
 WHERE SP.DBNAME LIKE 'XB%'
   AND SP.PARTITIONS > 0
   AND TB.NAME LIKE 'XB%'
   AND SP.DBNAME = PT.DBNAME
   AND SP.NAME   = PT.TSNAME
   AND SP.DBNAME = TB.DBNAME
   AND SP.NAME = TB.TSNAME
   AND TB.TYPE = 'T'
       ) AS T1
LEFT OUTER JOIN BUA.TXBC181 ST
  ON T1.CREATOR = ST.XBC181_CREATOR
 AND T1.TABLE     = ST.XBC181_TABNAME
 AND T1.PARTITION = ST.XBC181_PARTNR
INNER JOIN BUA.TXBC111
   ON DBNAME = EYPREF || ENSTORAR || ENSEG

WHERE ST.XBC181_STATUS IS NULL
   OR ST.XBC181_STATUS NOT IN ('UL','FZ','RU','RO')
WITH UR;

--************************************************************
--$'$$' xb  - fehlende fullcopies ts, letzte 8 tage:
--************************************************************

SELECT SUBSTR(TB.DBNAME,1,8)          AS DBNAME
      ,SUBSTR(TB.TSNAME,1,8)          AS TSNAME
      ,ST.XBC181_PARTNR               AS PART
      ,SUBSTR(ST.XBC181_CREATOR,1,8)  AS CREATOR
      ,SUBSTR(ST.XBC181_TABNAME,1,18) AS TABLE
      ,ST.XBC181_STATUS
  FROM BUA.TXBC181 ST
      ,SYSIBM.SYSTABLES TB
 WHERE ST.XBC181_STATUS IN ('RU','FZ','RO')
   AND ST.XBC181_TABNAME = TB.NAME
   AND TB.TYPE = 'T'
   AND NOT EXISTS
       (SELECT ' '
        FROM  SYSIBM.SYSCOPY CP
        WHERE TB.DBNAME = CP.DBNAME
          AND TB.TSNAME = CP.TSNAME
          AND (ST.XBC181_PARTNR = CP.DSNUM  OR  CP.DSNUM = 0)
          AND CP.ICTYPE IN ('F','R','X')
          AND CP.TIMESTAMP > CURRENT TIMESTAMP -  8  DAYS
         )
$@[if wordPos($subsys, 'DVBP') > 0 then $@=[
   AND NOT (TB.DBNAME = 'XBD2R001')
   AND NOT (TB.DBNAME = 'XBD2R002')
   AND NOT (TB.DBNAME = 'XBDPL001')
   AND NOT (TB.DBNAME = 'XBDPL002')
   AND NOT (TB.DBNAME = 'XBDL1001')
   AND NOT (TB.DBNAME = 'XBDL1002')
   AND NOT (TB.DBNAME = 'XBDL0001')
   AND NOT (TB.DBNAME = 'XBDL0002')
   AND NOT (TB.DBNAME = 'XBDPK001')
   AND NOT (TB.DBNAME = 'XBDPK002')
   AND NOT (TB.DBNAME = 'XBDJH001')
   AND NOT (TB.DBNAME = 'XBDJH002')
   AND NOT (TB.DBNAME = 'XBDT0001')
   AND NOT (TB.DBNAME = 'XBDT0002')
   AND NOT (TB.DBNAME = 'XBDT2001')
   AND NOT (TB.DBNAME = 'XBDT2002')
   AND NOT (TB.DBNAME = 'XBDT3001')
   AND NOT (TB.DBNAME = 'XBDT3002')
   AND NOT (TB.DBNAME = 'XBDM3001')
   AND NOT (TB.DBNAME = 'XBDM3002')
   AND NOT (TB.DBNAME = 'XBDT1001')
   AND NOT (TB.DBNAME = 'XBDT1002')
   AND NOT (TB.DBNAME = 'XBDNT001')
   AND NOT (TB.DBNAME = 'XBDNT002')
$] $]
order by TB.DBNAME, TB.tsNAME, ST.XBC181_PARTNR
WITH UR;

--************************************************************
--$'$$' XB  - imagecopy datasets, nicht katalogisiert:
--************************************************************

SELECT CP.DBNAME
      ,CP.TSNAME
      ,CP.DSNUM
      ,CP.ICDATE
      ,CP.JOBNAME
      ,CP.DSNAME
  FROM BUA.TXBC181      ST
      ,SYSIBM.SYSTABLES TB
      ,SYSIBM.SYSCOPY   CP
 WHERE ST.XBC181_STATUS IN ('RU','FZ','RO')
   AND ST.XBC181_TABNAME = TB.NAME
   AND TB.TYPE = 'T'
   AND TB.DBNAME = CP.DBNAME
   AND TB.TSNAME = CP.TSNAME
   AND ST.XBC181_PARTNR = CP.DSNUM
   AND CP.ICTYPE IN ('F','I')
   AND CP.TIMESTAMP >= CURRENT TIMESTAMP - 21 DAYS
   AND S100447.DSLOCATE(CP.DSNAME) IS NULL
order by CP.DBNAME, CP.tsNAME, CP.DSNUM
WITH UR;

--************************************************************
--$'$$' XB  - unld/punch datasets, nicht katalogisiert:
--************************************************************

SELECT SUBSTR(XBC181_CREATOR,1,8)  AS CREATOR
      ,SUBSTR(XBC181_TABNAME,1,18) AS TABLE
      ,XBC181_PARTNR               AS PART
      ,XBC181_UNL_DSN_C            AS PUNCH
      ,XBC181_UNL_DSN              AS SYSREC
  FROM BUA.TXBC181
WHERE XBC181_STATUS IN ('UL')
  AND ( S100447.DSLOCATE(CHAR(XBC181_UNL_DSN_C)) IS NULL
   OR   S100447.DSLOCATE(CHAR(XBC181_UNL_DSN  )) IS NULL )
order by XBC181_CREATOR, XBC181_TABNAME, XBC181_PARTNR

WITH UR;

$/elar2/
if $isElar then $@=/elar3/
--************************************************************
--$'$$' xb  - tablepaces mit > 200 Partitionen:
--************************************************************

 SELECT DBNAME, NAME, PARTITIONS
   FROM SYSIBM.SYSTABLESPACE
  WHERE PARTITIONS > 200
    AND DBNAME LIKE 'XB%'

   ---------  START EXCLUDE LIST -------------------------------

 --AND NOT (DBNAME = 'XBxxxxx'  AND NAME = 'xxxxxxx' ) -- comment

   ---------  END EXCLUDE LIST ---------------------------------

 ORDER BY PARTITIONS DESC
 WITH UR;

$/elar3/
$]
--************************************************************
--$'$$' LOB-Tablespaces mit falschen Spezifikationen:
--************************************************************

SELECT SUBSTR(DBNAME,1,8) AS DBNAME
      ,SUBSTR(NAME,1,8) AS TSNAME
      ,BPOOL
      ,LOG
FROM   SYSIBM.SYSTABLESPACE S
WHERE  TYPE = 'O'
  AND (BPOOL NOT IN('BP8','BP32K') OR LOG = 'N')
$@[ if $exclNew then $@=[
    $@exclGen{S, F}
$] else $@=[
  AND DBNAME NOT IN ('DSNDB01','DSNDB06','DSNDB04','DSNTESQ'
                    ,'DUTILTST','XSN8D71L','DB2XML')
  AND DBNAME NOT LIKE 'DSNX%'
  AND DBNAME NOT LIKE 'IDTA%'
$@[ if pos($subsys, 'DBOF') > 0 then $@#[
  AND DBNAME NOT LIKE 'DB2OSC%'
  AND DBNAME NOT LIKE 'DSNO%'
$] $]
  AND DBNAME NOT LIKE 'DB2PLAN%'
$]
$]
ORDER BY DBNAME, TSNAME
WITH UR;

--************************************************************
--$'$$' Tablespaces mit fehlerhafter Spezifikation:
--************************************************************

SELECT DISTINCT SUBSTR(TS.DBNAME,1,8) AS DBNAME
      ,SUBSTR(TS.NAME,1,8) AS TSNAME
      ,TS.BPOOL
      ,SUBSTR(PT.STORNAME,1,8) AS STORNAME
      ,PT.STORTYPE
FROM SYSIBM.SYSDATABASE DB,
     SYSIBM.SYSTABLESPACE TS,
     SYSIBM.SYSTABLEPART PT
WHERE DB.NAME = TS.DBNAME
  AND DB.NAME = PT.DBNAME
  AND TS.NAME = PT.TSNAME
$@exclude{PT,F}
  AND DB.TYPE <> 'W'
  AND (TS.BPOOL =  'BP0'
       OR PT.STORNAME <> 'GSMS'
       OR PT.STORTYPE =  'E')
ORDER BY DBNAME, TSNAME
WITH UR;
$@[ if $exclNew then $@=/new/
--************************************************************
--$'$$' Indexspaces mit fehlerhafter Spezifikation:
--************************************************************

SELECT DISTINCT SUBSTR(IX.CREATOR,1,8) AS CREATOR
      ,SUBSTR(IX.NAME,1,8) AS IXNAME
      ,IX.BPOOL
      ,SUBSTR(IP.STORNAME,1,8) AS STORNAME
      ,IP.STORTYPE
FROM SYSIBM.SYSINDEXES IX,
     SYSIBM.SYSINDEXPART IP
WHERE IX.CREATOR = IP.IXCREATOR
  AND IX.NAME    = IP.IXNAME
$@exclude{IX,F}
$@[ if $subsys = 'DBAF' then $@=[
   AND NOT (IX.DBNAME LIKE 'DHPS%')
$] $]
  AND (IX.BPOOL = 'BP0'
       OR IP.STORNAME <> 'GSMS'
       OR IP.STORTYPE = 'E')
ORDER BY CREATOR, IXNAME
WITH UR;

--************************************************************
--$'$$' tableParts mit pri/secQty <> -1 oder vielen extents
--************************************************************

 SELECT SUBSTR(PT.DBNAME,1,8) "db"
       ,SUBSTR(PT.TSNAME,1,8) "ts"
       ,PT.PARTITION "part"
       ,pt.pQty "priQty"
       ,pt.sQty "secQty"
       ,r.extents
 FROM
      SYSIBM.SYSTableSpace ts
   join   SYSIBM.SYSTABLEPART pt
     on pt.dbName = ts.dbName and pt.tsname = ts.name
   left join sysibm.sysTableSpaceStats r
     on    pt.dbNAME = r.DBNAME
       AND pt.tsName = r.NAME
       AND ts.dbid     = r.dbid
       AND ts.psid     = r.psid
       AND pt.partition = r.partition
 WHERE (pt.pQty <> -1 or pt.sQty <> -1 or r.extents > 300)
$@exclGen{PT, F}
 ORDER BY pt.DBNAME, pt.tsNAME, PT.PARTITION
 fetch first 999 rows only
 WITH UR;

--************************************************************
--$'$$' IndexParts mit pri/secQty <> -1 oder vielen extents
--************************************************************

SELECT SUBSTR(Ip.ixCREATOR,1,8) AS CREATOR
      ,SUBSTR(Ip.ixNAME,1,16) AS IXNAME
      ,IP.PARTITION
      ,ip.pQty "priQty"
      ,ip.sQty "secQty"
      ,ip.extents
FROM
    SYSIBM.SYSINDEXES   Ix
  join  SYSIBM.SYSINDEXPART IP
      on ix.creator = ip.ixCreator and ix.name = ip.ixName
  left join SYSIBM.SYSINDEXSpaceStats r
    on ix.creator = r.creator and ix.name = r.creator
       and ix.dbid = r.dbid and ix.isobid = r.isobid
       and ip.partition = r.partition
 WHERE (ip.pQty <> -1 or ip.sQty <> -1 or r.extents > 300)
$@exclGen{IX, F}
 order by ix.creator, ix.name, ip.partition
 fetch first 999 rows only
 WITH UR;
$/new/
$]
$/genAll/
$]

$@proc rzBegin $@/rzBegin/
if $rz == 'RZ2' then
$@#[
--* 23.12.09 W. Keller dsLocate verschoben wegen Performance
--*             Anpassung erfolgte nur für DBOF und Sektion Image Copy
--*             Datasets welche nicht katalogisiert sind||||||
$]
$/rzBegin/

$@proc exclGen  $@/exclGen/
parse upper arg , q, var
$=q=-q
$=var=-var
$@=[
   AND NOT ($q.DBNAME like 'DSNDB%')            -- DB2 CATALOG
   AND NOT ($q.DBNAME LIKE 'DSN8%')             -- IBM TEST DB
   AND NOT ($q.DBNAME LIKE 'WKDBD%')            -- DB2 WORK DATABASE
   AND NOT ($q.DBNAME = 'DSNTESQ')              -- DB2 CATALOG CLONE
   AND NOT ($q.DBNAME LIKE 'DB2MAPP%')          -- REORG MAPPING TABLES
   AND NOT ($q.DBNAME LIKE 'DB2PLAN%')          -- explain tables
   and not translate($q.dbName, '999999999AAAAAA', '012345678FISWXY')
           = 'DA999999'                        -- user datenbanken
   AND NOT ($q.DBNAME LIKE 'DB2ALA%')           -- marec  generated
   AND NOT ($q.DBNAME LIKE '%MAREC%')           -- marec generated
   AND NOT ($q.DBNAME LIKE 'DACME%')            -- Mail Heinz Bühler
$]
if pos($var, ' FG') > 1 then $@=[
   AND NOT ($q.DBNAME = 'SYSIBMTA')             -- engineering
   AND NOT ($q.DBNAME = 'SYSIBMTS')             -- engineering
   AND NOT ($q.DBNAME = 'DB2PM')                -- PERF.EXPERT DATABASE
   AND NOT ($q.DBNAME = 'DB2OSC')               -- osc
   AND NOT ($q.DBNAME like 'DSN%')              -- div databases
   AND NOT ($q.DBNAME like 'DSQ%')              -- qmf databse
   AND $q.DBNAME NOT IN ('DUTILTST','XSN8D71L','DB2XML')
   AND $q.DBNAME NOT LIKE 'IDTA%'               -- tools
$]
if wordPos($subsys, 'DBAF DBTF') > 0 then $@=[
   AND NOT ($q.DBNAME LIKE 'DGDB%')             -- PROTOTYPEN
   AND NOT ($q.DBNAME LIKE 'DGO%')              -- PROTOTYPEN
   AND NOT ($q.DBNAME LIKE '%A1X%')             -- Neue Prototypen
   AND NOT ($q.DBNAME LIKE 'DAU%')              -- Schulung Gerrit
   AND NOT ($q.DBNAME LIKE 'IDT%')              -- ibm tools
$]
$/exclGen/
$@proc exclude  $@/exclude/
parse upper arg , q, var
$=q=-q
$=var=-var
$@=[
   ---------  START EXCLUDE LIST -------------------------------

   -- COMMON SECTION: EXCLUDE LIST SAVE PROCEDURE --
$]
if $exclNew then $@/exclNew/
$@exclGen{$q, $var}
if wordPos($subsys, 'DBOF') > 0  & $q <> 'IX' & $var == '' then $@=[

   AND NOT ($q.DBNAME = 'XC01A1P'  AND $q.TSNAME LIKE 'A2%'  )
                                                -- EOS: Armin Breyer
   AND NOT ($q.DBNAME = 'XR01A1P'  AND $q.TSNAME LIKE 'A2%'  )
                                                -- ERET: Armin Breyer
   AND NOT ($q.DBNAME = 'CSQDBOF' AND $q.TSNAME like 'TSBLOB%' )
$]
else if wordPos($subsys, 'DP2G') > 0 & $var == '' then $@=[

   AND NOT ($q.DBNAME LIKE 'OS80%')             -- HAT EIGENE IMAGECOPY
$]
else if wordPos($rz, 'RZ4 RZ8') > 0 & $var == 'F' then $@[
  if $q == IX then $@=[
   AND NOT $q.DBNAME = 'DB2PMPDB'               -- PMON KITD2
  $] else $@=[
   AND NOT ($q.DBNAME = 'DB2PMPDB'
                AND $q.TSNAME like 'ACCS%')     -- PMON KITD2
  $]
$]
if wordPos($subsys, 'DBOC DP4G') > 0 then $@=[
   AND NOT ($q.DBNAME = 'DB2PDB')               -- performance DB
   AND NOT ($q.DBNAME = 'DB2XML')               -- performance DB
   AND NOT ($q.DBNAME like 'DSN%')
$]
if wordPos($rz, 'RZ1 RZ8 RZZ') > 0 then $@=[
   AND NOT ($q.DBNAME LIKE 'OE02%')             -- Mail Ivo Eichmann
   AND NOT ($q.DBNAME LIKE 'CSQ%')              -- M-QUEUE DATENBANK
$]
if $isElar then  $@=[
   AND NOT ($q.DBNAME LIKE 'XB%')               -- ELAR Dokumente
$]
$/exclNew/ else $@/exclOld/
if pos($var, ' FG') > 1 then $@=[
   AND NOT ($q.DBNAME = 'DB2PM')                -- PERF.EXPERT DATABASE
   AND NOT ($q.DBNAME LIKE 'WKDBD%')            -- DB2 WORK DATABASE
   AND NOT ($q.DBNAME LIKE 'DSN%')              -- IBM DATENBANKEN
$] else $@=[
   AND NOT ($q.DBNAME = 'DSNDB06')              -- DB2 CATALOG
   AND NOT ($q.DBNAME = 'DSNDB01')              -- DB2 DIRECTORY
   AND NOT ($q.DBNAME = 'DSNDB04')              -- DB2 SAMPLE DB
   AND NOT ($q.DBNAME = 'DSNTESQ')              -- DB2 CATALOG CLONE
   AND NOT ($q.DBNAME LIKE 'WKDBD%')            -- DB2 WORK DATABASE
   AND NOT ($q.DBNAME LIKE 'DSN8%')             -- IBM TEST DB
$]
if wordPos($subsys, 'DBAF DBTF') > 0 then $@=[
   AND NOT ($q.DBNAME LIKE 'DGDB%')             -- PROTOTYPEN
$]
$@=[
   AND NOT ($q.DBNAME LIKE 'DB2MAPP%')          -- REORG MAPPING TABLES
   AND NOT ($q.DBNAME LIKE 'DA0%')
   AND NOT ($q.DBNAME LIKE 'DA1%')
   AND NOT ($q.DBNAME LIKE 'DA2%')
   AND NOT ($q.DBNAME LIKE 'DA3%')
   AND NOT ($q.DBNAME LIKE 'DA4%')
   AND NOT ($q.DBNAME LIKE 'DA5%')
   AND NOT ($q.DBNAME LIKE 'DA6%')
   AND NOT ($q.DBNAME LIKE 'DA7%')
   AND NOT ($q.DBNAME LIKE 'DA8%')
   AND NOT ($q.DBNAME LIKE 'DA9%')
   AND NOT ($q.DBNAME LIKE 'DF0%')
   AND NOT ($q.DBNAME LIKE 'DF1%')
   AND NOT ($q.DBNAME LIKE 'DF2%')
   AND NOT ($q.DBNAME LIKE 'DF3%')
   AND NOT ($q.DBNAME LIKE 'DF4%')
   AND NOT ($q.DBNAME LIKE 'DF5%')
   AND NOT ($q.DBNAME LIKE 'DF6%')
   AND NOT ($q.DBNAME LIKE 'DF7%')
   AND NOT ($q.DBNAME LIKE 'DF8%')
   AND NOT ($q.DBNAME LIKE 'DF9%')
$]
if wordPos($rz, 'RZ1 RZ4') > 0 & $var == 'F' then $@=[
   AND NOT ($q.DBNAME LIKE 'DS1%')
$]
if $subsys == 'DBAF' then $@=[
   AND NOT ($q.DBNAME LIKE 'DW0%')
   AND NOT ($q.DBNAME LIKE 'DX0%')
   AND NOT ($q.DBNAME LIKE 'DX1%')
   AND NOT ($q.DBNAME LIKE 'DX2%')
   AND NOT ($q.DBNAME LIKE 'DX3%')
   AND NOT ($q.DBNAME LIKE 'DX4%')
   AND NOT ($q.DBNAME LIKE 'DX5%')
   AND NOT ($q.DBNAME LIKE 'DX6%')
   AND NOT ($q.DBNAME LIKE 'DX7%')
   AND NOT ($q.DBNAME LIKE 'DX8%')
   AND NOT ($q.DBNAME LIKE 'DX9%')
   AND NOT ($q.DBNAME LIKE 'DY0%')
   AND NOT ($q.DBNAME LIKE 'DY1%')
   AND NOT ($q.DBNAME LIKE 'DY2%')
   AND NOT ($q.DBNAME LIKE 'DY3%')
   AND NOT ($q.DBNAME LIKE 'DY4%')
   AND NOT ($q.DBNAME LIKE 'DY5%')
   AND NOT ($q.DBNAME LIKE 'DY6%')
   AND NOT ($q.DBNAME LIKE 'DY7%')
   AND NOT ($q.DBNAME LIKE 'DY8%')
   AND NOT ($q.DBNAME LIKE 'DY9%')
   AND NOT ($q.DBNAME LIKE 'DI017%')
$]
$@=[
   AND NOT ($q.DBNAME LIKE 'DB2ALA%')         -- marec  generated
   AND NOT ($q.DBNAME LIKE $"'$MAREC%'")         -- marec generated
   -- END COMMON SECTION (START SUBSYSTEM SPECIFIC EXEPTIONS)
$]
if wordPos($subsys, 'DBOF') > 0  & $q <> 'IX' & $var == '' then $@=[

   AND NOT ($q.DBNAME = 'CSQDBOF'  AND $q.TSNAME = 'TSBLOB1' ) -- G.KERN
   AND NOT ($q.DBNAME = 'CSQDBOF'  AND $q.TSNAME = 'TSBLOB2' ) -- G.KERN
   AND NOT ($q.DBNAME = 'CSQDBOF'  AND $q.TSNAME = 'TSBLOB3' ) -- G.KERN
   AND NOT ($q.DBNAME = 'CSQDBOF'  AND $q.TSNAME = 'TSBLOB4' ) -- G.KERN
   AND NOT ($q.DBNAME = 'XC01A1P'  AND $q.TSNAME LIKE 'A2%'  )
                                              -- EOS: Armin Breyer
   AND NOT ($q.DBNAME = 'XR01A1P'  AND $q.TSNAME LIKE 'A2%'  )
                                              -- ERET: Armin Breyer
$]
else if wordPos($subsys, 'DP2G') > 0 & $var == '' then $@=[

   AND NOT ($q.DBNAME LIKE 'OS80%')            -- HAT EIGENE IMAGECOPY
$]
else if wordPos($rz, 'RZ4 RZ8') > 0 & $var == 'F' then $@[
    if $q == IX then $@=[
   AND NOT $q.DBNAME = 'DB2PMPDB'                          --PMON KITD2
    $] else $@=[
   AND NOT ($q.DBNAME = 'DB2PMPDB' AND $q.TSNAME = 'ACCS1') --PMON KITD2
   AND NOT ($q.DBNAME = 'DB2PMPDB' AND $q.TSNAME = 'ACCS2') --PMON KITD2
   AND NOT ($q.DBNAME = 'DB2PMPDB' AND $q.TSNAME = 'ACCS3') --PMON KITD2
   AND NOT ($q.DBNAME = 'DB2PMPDB' AND $q.TSNAME = 'ACCS4') --PMON KITD2
    $]
$]
$@[if $isElar then  $@=[
   AND NOT ($q.DBNAME LIKE 'XB%')               -- ELAR Dokumente
$] $]
if pos($var, ' F') > 1 then $@=[

$@[ if $subsys == 'DBOF' then $@=[
   AND NOT ($q.DBNAME LIKE 'DACME%')     -- Mail Heinz Bühler
$] $]
   AND NOT ($q.DBNAME LIKE 'DSQ%')       -- QMF DATENBANK
   AND NOT ($q.DBNAME LIKE 'SYSIBMTS%')  -- KIDI61
   AND NOT ($q.DBNAME LIKE 'SYSIBMTA%')  -- KIDI61
$]
if $subsys == 'DBOL' & $var == '' then $@=[
   AND NOT ($q.DBNAME = 'CM01A1P')   -- MAIL VON WALTER GOETZ

$]
if wordPos($subsys, 'DBAF DBBA') > 0 then $@=[
   AND NOT ($q.DBNAME LIKE 'DGO%')              -- PROTOTYPEN
   AND NOT ($q.DBNAME LIKE 'OE02%')             -- Mail Ivo Eichmann
   AND NOT ($q.DBNAME LIKE 'DACME%')            -- Mail Heinz Bühler
   AND NOT ($q.DBNAME LIKE 'CSQ%')              -- M-QUEUE DATENBANK
   AND NOT ($q.DBNAME LIKE 'DAU%')              -- Schulung Gerrit
   AND NOT ($q.DBNAME LIKE '%A1X%')             -- Neue Prototypen
   AND NOT ($q.DBNAME LIKE 'DSN%')              -- db2
   AND NOT ($q.DBNAME LIKE 'IDT%')              -- ibm tools
$]
if wordPos($rz, 'RZ8') > 0 then $@=[
   AND NOT ($q.DBNAME LIKE 'DS1%')
$]
if wordPos($subsys, 'DC0G DD0G DP8G DX0G') > 0 then $@=[
   AND NOT ($q.DBNAME LIKE 'DW0%')
   AND NOT ($q.DBNAME LIKE 'DY0%')
   AND NOT ($q.DBNAME LIKE 'DY1%')
   AND NOT ($q.DBNAME LIKE 'DX0%')               -- Schulungs DB's
   AND NOT ($q.DBNAME LIKE 'DX1%')               -- Unilog Integrata
   AND NOT ($q.DBNAME LIKE 'DX2%')
   AND NOT ($q.DBNAME LIKE 'DX9%')

$]
if wordPos($rz, 'RZ8') > 0  & $q \== IX & $var == '' $*+
        & \ $isElar then $@=[
   AND NOT ($q.DBNAME LIKE 'OE02%')           -- Mail Ivo Eichmann
   AND NOT ($q.DBNAME LIKE 'DACME%')          -- Mail Heinz Bühler
   AND NOT ($q.DBNAME LIKE 'DDASE%') -- MAIL WERNER KURZ (TÄGL. CREATE)
   AND NOT ($q.DBNAME LIKE 'VV11%')           -- MAIL JÜRG GUBLER
   AND NOT ($q.DBNAME LIKE 'VV12%')           -- MAIL JÜRG GUBLER
   AND NOT ($q.DBNAME LIKE 'VV13%')           -- MAIL JÜRG GUBLER
   AND NOT ($q.DBNAME LIKE 'VV14%')           -- MAIL JÜRG GUBLER
   AND NOT ($q.DBNAME LIKE 'VV15%')           -- MAIL JÜRG GUBLER
   AND NOT ($q.DBNAME LIKE 'VV16%')           -- MAIL JÜRG GUBLER
   AND NOT ($q.DBNAME LIKE 'VV17%')           -- MAIL JÜRG GUBLER
   AND NOT ($q.DBNAME LIKE 'VV18%')           -- MAIL JÜRG GUBLER
   AND NOT ($q.DBNAME LIKE 'CSQ%')            -- M-QUEUE DATENBANK
$]
$/exclOld/
$@=[

   ---------  END EXCLUDE LIST -------------------------------
$]
$/exclude/


$@proc missFullcopies1 $@=/missFullCopies1/
 SELECT SUBSTR(PT.DBNAME,1,8) AS DBNAME
       ,SUBSTR(PT.TSNAME,1,8) AS TSNAME
       ,PT.PARTITION
       ,DATE(TS.CREATEDTS) AS CREATEDATE
 FROM   SYSIBM.SYSDATABASE DB,
        SYSIBM.SYSTABLESPACE TS,
        SYSIBM.SYSTABLEPART PT
 WHERE DB.NAME = PT.DBNAME
   AND DB.NAME = TS.DBNAME
   AND TS.NAME = PT.TSNAME
$/missFullCopies1/

$@proc missFullcopies2db $@=/missFullCopies2db/
   AND DB.TYPE NOT IN ('T','W')
$@missFullcopies2-{arg(2)}
$/missFullCopies2db/

$@proc missFullcopies2 $@/missFullCopies2/
parse arg , days
$=days=-days
$@=[
   AND TS.NTABLES <> 0
   AND PT.SPACE <> -1
   AND DATE(TS.CREATEDTS) < CURRENT DATE
   AND NOT EXISTS
       (SELECT ' '
        FROM  SYSIBM.SYSCOPY CP
        WHERE PT.DBNAME = CP.DBNAME
          AND PT.TSNAME = CP.TSNAME
          AND (PT.PARTITION = CP.DSNUM  OR  CP.DSNUM = 0)
          AND CP.ICTYPE IN ('F','R','X')
          AND CP.TIMESTAMP > CURRENT TIMESTAMP - $days DAYS
         )
 ORDER BY DBNAME, TSNAME, PT.PARTITION
 WITH UR;
$]
$/missFullCopies2/
$#out                                              20120628 17:25:18
$#out                                              20120626 17:01:14
$#out                                              20120626 16:57:50
$#out                                              20120626 16:00:50
$#out                                              20120626 15:11:39