zOs/REXX/JJ
//A540769Z JOB (CP00,KE50), 00010001
// MSGCLASS=T,TIME=1440,
// NOTIFY=A540769
//*MAIN CLASS=LOG0 SYSTEM=S12
//S01 EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99 00020001
//SYSTSIN DD *
DSN SYSTEM(DP4G)
RUN PROGRAM(DSNTEP2) PARMS('ALIGN(LHS)') PLAN(DSNTEP2)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTERM DD DUMMY
//*YSIN DD DISP=SHR,DSN=A540769.WK.SQL(GBLIMIT)
//SYSIN DD *
-- Control Summary SQL für RZ4/DP4G job QMW0016P
-- generiert am 14/02/05 18:11:10
-- durch rz1/dsn.source.tecSv(conSumGe)
-- ||| alle Aenderung dortdrin ||||||
--************************************************************
-- Identifikation
--************************************************************
select current timestamp "now", current server "currentServer"
from sysibm.sysDummy1
;
--************************************************************
--$$ fehlende Fullcopies Tablespaces, letzte 8 Tage:
--************************************************************
---- begin @proc missFUllcopies1: fehlende Fullcopies -----------------
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
---- end @proc missFUllcopies1: fehlende Fullcopies -----------------
----- begin @proc exclude: 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 'DB2POOL%') -- DB2 STOR.POOL WIESI
AND NOT (PT.DBNAME LIKE '%MAREC%') -- marec generated
AND NOT (PT.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
AND NOT (PT.DBNAME like 'CSQ%' AND PT.TSNAME like 'TSBLOB%' )
-- M-QUEUE DATENBANK
AND NOT (PT.DBNAME = 'DB2PDB') -- performance DB
AND NOT (PT.DBNAME = 'DB2XML') -- performance DB
AND NOT (PT.DBNAME like 'DSN%')
---- end @proc exclude: gemeinsame excludes --- * ----------------
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|
AND NOT EXISTS (
---- begin @proc selFUllCopy: select fullcopy etc. --------------------
SELECT ' '
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') -- fullcopy or fullLog
AND CP.TIMESTAMP > CURRENT TIMESTAMP - 8 DAYS
) or ((CP.ICTYPE = 'C' -- created today
-- part added today
or (CP.ICTYPE = 'A' and CP.sType = 'A')
) and date(cp.timestamp) >= current date
) )
---- end @proc selFUllCopy: select fullcopy etc. --------------------
)
ORDER BY DBNAME, TSNAME, PT.PARTITION
WITH UR;
---- end @proc missFUllcopies2: fehlende Fullcopies -----------------
commit;
--- temporary table fuer syscopy -------------------------------------
declare global temporary table session.copy
( db char(8), ts char(8), paFr smallInt, paTo smallInt
, dsNum smallInt, icType char(1), tst timestamp
) on commit preserve rows;
create unique index session.txIx on session.copy (db,ts, paFr, paTo)
include (dsNum, icType, tst)
;
insert into session.copy
with l as
(
select c.dbName db, c.tsName ts, c.dsNum, c.icType, c.timestamp tst
, case when s.partitions = 0 then 0
when c.lowDsNum <= 0 then c.dsNum
when c.highDsNum <= 0 then c.dsNum
else c.lowDsNum
end paFr
, case when s.partitions = 0 then 0
when c.lowDsNum <= 0 then c.dsNum
when c.highDsNum <= 0 then c.dsNum
else c.highDsNum
end paTo
from sysibm.sysCopy c
join sysibm.sysTableSpace s
on c.dbName = s.dbName and c.tsName = s.name
where ICTYPE IN ('C', 'F', 'S', 'W', 'Y')
or (ICTYPE = 'A' and sType = 'A') -- part added
)
, g as
(
select db, ts, paFr, paTo
, max(char(tst) || ictype || dsNum) last
from l
group by db, ts, paFr, paTo
)
select db, ts, paFr, paTo
, smallInt(substr(last, 28)) dsNum
, substr(last, 27, 1) icType
, timestamp(substr(last, 1, 26)) tst
from g
;
select count(*) "copy count"
, count(distinct db || '.' || ts) "copy TS's"
, count(distinct db ) "copy DB's"
from session.copy
;
commit;
--************************************************************
--$$ fehlende Fullcopies/Recoverybase, letzte 8 Tage:
--************************************************************
with l as
(
select p.dbName db, p.tsName ts, p.partition pa
, ( select max(char(tst) || icType || char(dsNum))
from session.copy c
where p.dbName = c.db and p.tsName = c.ts
and (p.partition between c.paFr and c.paTo
or c.paFr = 0)
) last
from sysibm.sysTablePart p
join sysibm.sysTablespace s
on p.dbName = s.dbName and p.tsName = s.name
join sysibm.sysDatabase db
on p.dbName = db.name
where s.ntables <> 0
and p.spacef <> -1 -- define=no, space is sometimes wrong
and db.type not in ('T','W')
----- begin @proc exclude: gemeinsame excludes --- * ----------------
AND NOT (P.DBNAME like 'DSNDB%') -- DB2 CATALOG
AND NOT (P.DBNAME LIKE 'DSN8%') -- IBM TEST DB
AND NOT (P.DBNAME LIKE 'WKDBD%') -- DB2 WORK DATABASE
AND NOT (P.DBNAME = 'DSNTESQ') -- DB2 CATALOG CLONE
AND NOT (P.DBNAME LIKE 'DB2MAPP%') -- REORG MAPPING TABLES
AND NOT (P.DBNAME LIKE 'DB2PLAN%') -- explain tables
and not translate(P.dbName, '999999999AAAAAA', '012345678FISWXY')
= 'DA999999' -- user datenbanken
AND NOT (P.DBNAME LIKE 'DB2ALA%') -- marec generated
AND NOT (P.DBNAME LIKE 'DB2POOL%') -- DB2 STOR.POOL WIESI
AND NOT (P.DBNAME LIKE '%MAREC%') -- marec generated
AND NOT (P.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
AND NOT (P.DBNAME like 'CSQ%' AND P.TSNAME like 'TSBLOB%' )
-- M-QUEUE DATENBANK
AND NOT (P.DBNAME = 'DB2PDB') -- performance DB
AND NOT (P.DBNAME = 'DB2XML') -- performance DB
AND NOT (P.DBNAME like 'DSN%')
---- end @proc exclude: gemeinsame excludes --- * ----------------
)
, m as
(
select l.*
, substr(last, 27, 1) ty
, smallint(substr(last, 28)) dsNum
, timestamp(substr(last, 1, 26)) tst
from l
)
select db, ts, pa, ty, dsNum, tst
from m
where ty is null or not
((ty = 'F' and tst > current timestamp - 8 DAYS )
or (ty in ('C', 'A') and tst > current timestamp - 24 hours))
order by 1, 2, 3
with ur
;
commit;
--************************************************************
--$$ fehlende Fullcopies Indexspaces, 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.SPACEF <> -1 -- attention space is sometimes wrong|
----- begin @proc exclude: gemeinsame excludes --- * K ----------------
AND NOT (IX.DBNAME like 'DSNDB%') -- DB2 CATALOG
AND NOT (IX.DBNAME LIKE 'DSN8%') -- IBM TEST DB
AND NOT (IX.DBNAME LIKE 'WKDBD%') -- DB2 WORK DATABASE
AND NOT (IX.DBNAME = 'DSNTESQ') -- DB2 CATALOG CLONE
AND NOT (IX.DBNAME LIKE 'DB2MAPP%') -- REORG MAPPING TABLES
AND NOT (IX.DBNAME LIKE 'DB2PLAN%') -- explain tables
and not translate(IX.dbName, '999999999AAAAAA', '012345678FISWXY')
= 'DA999999' -- user datenbanken
AND NOT (IX.DBNAME LIKE 'DB2ALA%') -- marec generated
AND NOT (IX.DBNAME LIKE 'DB2POOL%') -- DB2 STOR.POOL WIESI
AND NOT (IX.DBNAME LIKE '%MAREC%') -- marec generated
AND NOT (IX.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
AND NOT (IX.DBNAME = 'DB2PDB') -- performance DB
AND NOT (IX.DBNAME = 'DB2XML') -- performance DB
AND NOT (IX.DBNAME like 'DSN%')
---- end @proc exclude: gemeinsame excludes --- * K ----------------
AND NOT EXISTS (
---- begin @proc selFUllCopy: select fullcopy etc. --------------------
SELECT ' '
FROM SYSIBM.SYSCOPY CP
WHERE IX.DBNAME = CP.DBNAME
AND IX.INDEXSPACE = CP.TSNAME
AND cp.dsNum in (IP.PARTITION, 0)
AND (( CP.ICTYPE IN ('F','R','X') -- fullcopy or fullLog
AND CP.TIMESTAMP > CURRENT TIMESTAMP - 8 DAYS
) or ((CP.ICTYPE = 'C' -- created today
-- part added today
or (CP.ICTYPE = 'A' and CP.sType = 'A')
) and date(cp.timestamp) >= current date
) )
---- end @proc selFUllCopy: select fullcopy etc. --------------------
)
ORDER BY CREATOR, IXNAME, IP.PARTITION
WITH UR;
commit;
--************************************************************
--$$ Imagecopy Datasets die nicht katalogisiert sind:
--************************************************************
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
----- begin @proc exclude: gemeinsame excludes --- K ----------------
AND NOT (C.DBNAME like 'DSNDB%') -- DB2 CATALOG
AND NOT (C.DBNAME LIKE 'DSN8%') -- IBM TEST DB
AND NOT (C.DBNAME LIKE 'WKDBD%') -- DB2 WORK DATABASE
AND NOT (C.DBNAME = 'DSNTESQ') -- DB2 CATALOG CLONE
AND NOT (C.DBNAME LIKE 'DB2MAPP%') -- REORG MAPPING TABLES
AND NOT (C.DBNAME LIKE 'DB2PLAN%') -- explain tables
and not translate(C.dbName, '999999999AAAAAA', '012345678FISWXY')
= 'DA999999' -- user datenbanken
AND NOT (C.DBNAME LIKE 'DB2ALA%') -- marec generated
AND NOT (C.DBNAME LIKE 'DB2POOL%') -- DB2 STOR.POOL WIESI
AND NOT (C.DBNAME LIKE '%MAREC%') -- marec generated
AND NOT (C.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
AND NOT (C.DBNAME like 'CSQ%' AND C.TSNAME like 'TSBLOB%' )
-- M-QUEUE DATENBANK
AND NOT (C.DBNAME = 'DB2PDB') -- performance DB
AND NOT (C.DBNAME = 'DB2XML') -- performance DB
AND NOT (C.DBNAME like 'DSN%')
---- end @proc exclude: gemeinsame excludes --- K ----------------
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;
commit;
--************************************************************
--$$ 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')
----- begin @proc exclude: gemeinsame excludes --- L ----------------
AND NOT (S.DBNAME like 'DSNDB%') -- DB2 CATALOG
AND NOT (S.DBNAME LIKE 'DSN8%') -- IBM TEST DB
AND NOT (S.DBNAME LIKE 'WKDBD%') -- DB2 WORK DATABASE
AND NOT (S.DBNAME = 'DSNTESQ') -- DB2 CATALOG CLONE
AND NOT (S.DBNAME LIKE 'DB2MAPP%') -- REORG MAPPING TABLES
AND NOT (S.DBNAME LIKE 'DB2PLAN%') -- explain tables
and not translate(S.dbName, '999999999AAAAAA', '012345678FISWXY')
= 'DA999999' -- user datenbanken
AND NOT (S.DBNAME LIKE 'DB2ALA%') -- marec generated
AND NOT (S.DBNAME LIKE 'DB2POOL%') -- DB2 STOR.POOL WIESI
AND NOT (S.DBNAME LIKE '%MAREC%') -- marec generated
AND NOT (S.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
AND NOT (S.DBNAME = 'SYSIBMTA') -- engineering
AND NOT (S.DBNAME = 'SYSIBMTS') -- engineering
AND NOT (S.DBNAME = 'DB2PM') -- PERF.EXPERT DATABASE
AND NOT (S.DBNAME = 'DB2OSC') -- osc
AND NOT (S.DBNAME like 'DSN%') -- div databases
AND NOT (S.DBNAME like 'DSQ%') -- qmf databse
AND S.DBNAME NOT IN ('DUTILTST','XSN8D71L','DB2XML')
---- end @proc exclude: gemeinsame excludes --- L ----------------
AND DBNAME NOT LIKE 'DB2PLAN%'
AND DBNAME NOT LIKE 'PTDB%'
ORDER BY DBNAME, TSNAME
WITH UR;
commit;
--************************************************************
--$$ 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
----- begin @proc exclude: gemeinsame excludes --- F ----------------
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 'DB2POOL%') -- DB2 STOR.POOL WIESI
AND NOT (PT.DBNAME LIKE '%MAREC%') -- marec generated
AND NOT (PT.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
AND NOT (PT.DBNAME = 'SYSIBMTA') -- engineering
AND NOT (PT.DBNAME = 'SYSIBMTS') -- engineering
AND NOT (PT.DBNAME = 'DB2PM') -- PERF.EXPERT DATABASE
AND NOT (PT.DBNAME = 'DB2OSC') -- osc
AND NOT (PT.DBNAME like 'DSN%') -- div databases
AND NOT (PT.DBNAME like 'DSQ%') -- qmf databse
AND PT.DBNAME NOT IN ('DUTILTST','XSN8D71L','DB2XML')
AND NOT (PT.DBNAME = 'DB2PMPDB'
AND PT.TSNAME like 'ACCS%') -- PMON KITD2
AND NOT (PT.DBNAME = 'DB2PDB') -- performance DB
AND NOT (PT.DBNAME = 'DB2XML') -- performance DB
AND NOT (PT.DBNAME like 'DSN%')
---- end @proc exclude: gemeinsame excludes --- F ----------------
AND DB.TYPE <> 'W'
AND (TS.BPOOL = 'BP0'
OR PT.STORNAME <> 'GSMS'
OR PT.STORTYPE = 'E')
ORDER BY DBNAME, TSNAME
WITH UR;
commit;
--************************************************************
--$$ 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
----- begin @proc exclude: gemeinsame excludes --- F ----------------
AND NOT (IX.DBNAME like 'DSNDB%') -- DB2 CATALOG
AND NOT (IX.DBNAME LIKE 'DSN8%') -- IBM TEST DB
AND NOT (IX.DBNAME LIKE 'WKDBD%') -- DB2 WORK DATABASE
AND NOT (IX.DBNAME = 'DSNTESQ') -- DB2 CATALOG CLONE
AND NOT (IX.DBNAME LIKE 'DB2MAPP%') -- REORG MAPPING TABLES
AND NOT (IX.DBNAME LIKE 'DB2PLAN%') -- explain tables
and not translate(IX.dbName, '999999999AAAAAA', '012345678FISWXY')
= 'DA999999' -- user datenbanken
AND NOT (IX.DBNAME LIKE 'DB2ALA%') -- marec generated
AND NOT (IX.DBNAME LIKE 'DB2POOL%') -- DB2 STOR.POOL WIESI
AND NOT (IX.DBNAME LIKE '%MAREC%') -- marec generated
AND NOT (IX.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
AND NOT (IX.DBNAME = 'SYSIBMTA') -- engineering
AND NOT (IX.DBNAME = 'SYSIBMTS') -- engineering
AND NOT (IX.DBNAME = 'DB2PM') -- PERF.EXPERT DATABASE
AND NOT (IX.DBNAME = 'DB2OSC') -- osc
AND NOT (IX.DBNAME like 'DSN%') -- div databases
AND NOT (IX.DBNAME like 'DSQ%') -- qmf databse
AND IX.DBNAME NOT IN ('DUTILTST','XSN8D71L','DB2XML')
AND NOT IX.DBNAME = 'DB2PMPDB' -- PMON KITD2
AND NOT (IX.DBNAME = 'DB2PDB') -- performance DB
AND NOT (IX.DBNAME = 'DB2XML') -- performance DB
AND NOT (IX.DBNAME like 'DSN%')
---- end @proc exclude: gemeinsame excludes --- F ----------------
AND (IX.BPOOL = 'BP0'
OR IP.STORNAME <> 'GSMS'
OR IP.STORTYPE = 'E')
ORDER BY CREATOR, IXNAME
WITH UR;
commit;
--************************************************************
--$$ 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)
----- begin @proc exclude: gemeinsame excludes --- L ----------------
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 'DB2POOL%') -- DB2 STOR.POOL WIESI
AND NOT (PT.DBNAME LIKE '%MAREC%') -- marec generated
AND NOT (PT.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
AND NOT (PT.DBNAME = 'SYSIBMTA') -- engineering
AND NOT (PT.DBNAME = 'SYSIBMTS') -- engineering
AND NOT (PT.DBNAME = 'DB2PM') -- PERF.EXPERT DATABASE
AND NOT (PT.DBNAME = 'DB2OSC') -- osc
AND NOT (PT.DBNAME like 'DSN%') -- div databases
AND NOT (PT.DBNAME like 'DSQ%') -- qmf databse
AND PT.DBNAME NOT IN ('DUTILTST','XSN8D71L','DB2XML')
---- end @proc exclude: gemeinsame excludes --- L ----------------
ORDER BY pt.DBNAME, pt.tsNAME, PT.PARTITION
fetch first 999 rows only
WITH UR;
commit;
--************************************************************
--$$ 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)
----- begin @proc exclude: gemeinsame excludes --- L ----------------
AND NOT (IX.DBNAME like 'DSNDB%') -- DB2 CATALOG
AND NOT (IX.DBNAME LIKE 'DSN8%') -- IBM TEST DB
AND NOT (IX.DBNAME LIKE 'WKDBD%') -- DB2 WORK DATABASE
AND NOT (IX.DBNAME = 'DSNTESQ') -- DB2 CATALOG CLONE
AND NOT (IX.DBNAME LIKE 'DB2MAPP%') -- REORG MAPPING TABLES
AND NOT (IX.DBNAME LIKE 'DB2PLAN%') -- explain tables
and not translate(IX.dbName, '999999999AAAAAA', '012345678FISWXY')
= 'DA999999' -- user datenbanken
AND NOT (IX.DBNAME LIKE 'DB2ALA%') -- marec generated
AND NOT (IX.DBNAME LIKE 'DB2POOL%') -- DB2 STOR.POOL WIESI
AND NOT (IX.DBNAME LIKE '%MAREC%') -- marec generated
AND NOT (IX.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
AND NOT (IX.DBNAME = 'SYSIBMTA') -- engineering
AND NOT (IX.DBNAME = 'SYSIBMTS') -- engineering
AND NOT (IX.DBNAME = 'DB2PM') -- PERF.EXPERT DATABASE
AND NOT (IX.DBNAME = 'DB2OSC') -- osc
AND NOT (IX.DBNAME like 'DSN%') -- div databases
AND NOT (IX.DBNAME like 'DSQ%') -- qmf databse
AND IX.DBNAME NOT IN ('DUTILTST','XSN8D71L','DB2XML')
---- end @proc exclude: gemeinsame excludes --- L ----------------
order by ix.creator, ix.name, ip.partition
fetch first 999 rows only
WITH UR;