zOs/SQL/RZ1SQL2
--************************************************************
--?? ts mit pri/secQty <> -1 oder zuvielen 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
, case when pt.pQty = -1
then '--' || pt.dbname || '.' || pt.tsName || ' already -1'
else 'alter tablespace ' || strip(pt.dbname)
|| '.' || strip(pt.tsName) || ' priQty -1;'
end "alter priQty"
, case when pt.sQty = -1
then '--' || pt.dbname || '.' || pt.tsName || ' already -1'
else 'alter tablespace ' || strip(pt.dbname)
|| '.' || strip(pt.tsName) || ' secQty -1;'
end "alter secQty"
FROM
SYSIBM.SYSTABLESpace s
join SYSIBM.SYSTABLEPART pt
on pt.dbName = s.dbName and pt.tsname = s.name
left join sysibm.sysTableSpaceStats r
on pt.dbNAME = r.DBNAME
AND pt.tsName = r.NAME
AND s.dbid = r.dbid
AND s.psid = r.psid
AND pt.partition = r.partition
WHERE (pt.pQty <> -1 or pt.sQty <> -1 or r.extents > 300)
--------- START EXCLUDE LIST -------------------------------
-- COMMON SECTION: EXCLUDE LIST SAVE PROCEDURE --
AND NOT (PT.DBNAME = 'DSNDB06') -- DB2 CATALOG
AND NOT (PT.DBNAME = 'DSNDB01') -- DB2 DIRECTORY
AND NOT (PT.DBNAME = 'DSNDB04') -- DB2 SAMPLE DB
AND NOT (PT.DBNAME = 'DSNTESQ') -- DB2 CATALOG CLONE
AND NOT (PT.DBNAME LIKE 'WKDBD%') -- DB2 WORK DATABASE
AND NOT (PT.DBNAME LIKE 'DSN8%') -- IBM TEST DB
AND NOT (PT.DBNAME LIKE 'DB2MAPP%') -- REORG MAPPING TABLES
AND NOT (PT.DBNAME LIKE 'DGDB%') -- PROTOTYPEN
AND NOT (PT.DBNAME LIKE 'DA0%') -- USER DB'S
AND NOT (PT.DBNAME LIKE 'DA1%')
AND NOT (PT.DBNAME LIKE 'DA2%')
AND NOT (PT.DBNAME LIKE 'DA3%')
AND NOT (PT.DBNAME LIKE 'DA4%')
AND NOT (PT.DBNAME LIKE 'DA5%')
AND NOT (PT.DBNAME LIKE 'DA6%')
AND NOT (PT.DBNAME LIKE 'DA7%')
AND NOT (PT.DBNAME LIKE 'DA8%')
AND NOT (PT.DBNAME LIKE 'DA9%')
AND NOT (PT.DBNAME LIKE 'DF0%')
AND NOT (PT.DBNAME LIKE 'DF1%')
AND NOT (PT.DBNAME LIKE 'DF2%')
AND NOT (PT.DBNAME LIKE 'DF3%')
AND NOT (PT.DBNAME LIKE 'DF4%')
AND NOT (PT.DBNAME LIKE 'DF5%')
AND NOT (PT.DBNAME LIKE 'DF6%')
AND NOT (PT.DBNAME LIKE 'DF7%')
AND NOT (PT.DBNAME LIKE 'DF8%')
AND NOT (PT.DBNAME LIKE 'DF9%')
AND NOT (PT.DBNAME LIKE 'DS1%')
AND NOT (PT.DBNAME LIKE 'DW0%')
AND NOT (PT.DBNAME LIKE 'DX0%')
AND NOT (PT.DBNAME LIKE 'DX1%')
AND NOT (PT.DBNAME LIKE 'DX2%')
AND NOT (PT.DBNAME LIKE 'DX3%')
AND NOT (PT.DBNAME LIKE 'DX4%')
AND NOT (PT.DBNAME LIKE 'DX5%')
AND NOT (PT.DBNAME LIKE 'DX6%')
AND NOT (PT.DBNAME LIKE 'DX7%')
AND NOT (PT.DBNAME LIKE 'DX8%')
AND NOT (PT.DBNAME LIKE 'DX9%')
AND NOT (PT.DBNAME LIKE 'DY0%')
AND NOT (PT.DBNAME LIKE 'DY1%')
AND NOT (PT.DBNAME LIKE 'DY2%')
AND NOT (PT.DBNAME LIKE 'DY3%')
AND NOT (PT.DBNAME LIKE 'DY4%')
AND NOT (PT.DBNAME LIKE 'DY5%')
AND NOT (PT.DBNAME LIKE 'DY6%')
AND NOT (PT.DBNAME LIKE 'DY7%')
AND NOT (PT.DBNAME LIKE 'DY8%')
AND NOT (PT.DBNAME LIKE 'DY9%')
AND NOT (PT.DBNAME LIKE 'DI017%')
-- END COMMON SECTION (START SUBSYSTEM SPECIFIC EXEPTIONS)
AND NOT (PT.DBNAME LIKE 'DGO%') -- PROTOTYPEN
AND NOT (PT.DBNAME LIKE 'OE02%') -- Mail Ivo Eichmann
AND NOT (PT.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
AND NOT (PT.DBNAME LIKE 'CSQ%') -- M-QUEUE DATENBANK
AND NOT (PT.DBNAME LIKE 'DAU%') -- Schulung Gerrit
AND NOT (PT.DBNAME LIKE '%A1X%') -- Neue Prototypen
AND NOT (PT.DBNAME LIKE 'DB2ALA%') -- P.Lehmann
AND NOT (PT.DBNAME LIKE '?MAREC%') -- P.Lehmann
--------- END EXCLUDE LIST -------------------------------
ORDER BY pt.DBNAME, pt.tsNAME, PT.PARTITION
WITH UR;
--************************************************************
--?? ts mit pri/secQty <> -1 oder zuvielen extents
--************************************************************
SELECT SUBSTR(Ip.CREATOR,1,8) AS CREATOR
,SUBSTR(Ip.NAME,1,16) AS IXNAME
,IP.PARTITION
,ip.pQty "priQty"
,ip.sQty "secQty"
,ip.extents
, case when ip.pQty = -1
then '--' || ip.ixcreator ||'.'|| ip.ixName || ' already -1'
else 'alter index ' || strip(ip.ixCreator)
|| '.' || strip(ip.ixName) || ' priQty -1;'
end "alter priQty"
FROM
SYSIBM.SYSINDEXPART IP
left join SYSIBM.SYSINDEXSpaceStats r
on ip.creator = r.creator and ip.name = r.creator
and ip.partition = r.partition
WHERE (pt.pQty <> -1 or pt.sQty <> -1 or r.extents > 300)
--------- START EXCLUDE LIST -------------------------------
-- COMMON SECTION: EXCLUDE LIST SAVE PROCEDURE --
AND NOT (PT.DBNAME = 'DSNDB06') -- DB2 CATALOG
AND NOT (PT.DBNAME = 'DSNDB01') -- DB2 DIRECTORY
AND NOT (PT.DBNAME = 'DSNDB04') -- DB2 SAMPLE DB
AND NOT (PT.DBNAME = 'DSNTESQ') -- DB2 CATALOG CLONE
AND NOT (PT.DBNAME LIKE 'WKDBD%') -- DB2 WORK DATABASE
AND NOT (PT.DBNAME LIKE 'DSN8%') -- IBM TEST DB
AND NOT (PT.DBNAME LIKE 'DB2MAPP%') -- REORG MAPPING TABLES
AND NOT (PT.DBNAME LIKE 'DGDB%') -- PROTOTYPEN
AND NOT (PT.DBNAME LIKE 'DA0%') -- USER DB'S
AND NOT (PT.DBNAME LIKE 'DA1%')
AND NOT (PT.DBNAME LIKE 'DA2%')
AND NOT (PT.DBNAME LIKE 'DA3%')
AND NOT (PT.DBNAME LIKE 'DA4%')
AND NOT (PT.DBNAME LIKE 'DA5%')
AND NOT (PT.DBNAME LIKE 'DA6%')
AND NOT (PT.DBNAME LIKE 'DA7%')
AND NOT (PT.DBNAME LIKE 'DA8%')
AND NOT (PT.DBNAME LIKE 'DA9%')
AND NOT (PT.DBNAME LIKE 'DF0%')
AND NOT (PT.DBNAME LIKE 'DF1%')
AND NOT (PT.DBNAME LIKE 'DF2%')
AND NOT (PT.DBNAME LIKE 'DF3%')
AND NOT (PT.DBNAME LIKE 'DF4%')
AND NOT (PT.DBNAME LIKE 'DF5%')
AND NOT (PT.DBNAME LIKE 'DF6%')
AND NOT (PT.DBNAME LIKE 'DF7%')
AND NOT (PT.DBNAME LIKE 'DF8%')
AND NOT (PT.DBNAME LIKE 'DF9%')
AND NOT (PT.DBNAME LIKE 'DS1%')
AND NOT (PT.DBNAME LIKE 'DW0%')
AND NOT (PT.DBNAME LIKE 'DX0%')
AND NOT (PT.DBNAME LIKE 'DX1%')
AND NOT (PT.DBNAME LIKE 'DX2%')
AND NOT (PT.DBNAME LIKE 'DX3%')
AND NOT (PT.DBNAME LIKE 'DX4%')
AND NOT (PT.DBNAME LIKE 'DX5%')
AND NOT (PT.DBNAME LIKE 'DX6%')
AND NOT (PT.DBNAME LIKE 'DX7%')
AND NOT (PT.DBNAME LIKE 'DX8%')
AND NOT (PT.DBNAME LIKE 'DX9%')
AND NOT (PT.DBNAME LIKE 'DY0%')
AND NOT (PT.DBNAME LIKE 'DY1%')
AND NOT (PT.DBNAME LIKE 'DY2%')
AND NOT (PT.DBNAME LIKE 'DY3%')
AND NOT (PT.DBNAME LIKE 'DY4%')
AND NOT (PT.DBNAME LIKE 'DY5%')
AND NOT (PT.DBNAME LIKE 'DY6%')
AND NOT (PT.DBNAME LIKE 'DY7%')
AND NOT (PT.DBNAME LIKE 'DY8%')
AND NOT (PT.DBNAME LIKE 'DY9%')
AND NOT (PT.DBNAME LIKE 'DI017%')
-- END COMMON SECTION (START SUBSYSTEM SPECIFIC EXEPTIONS)
AND NOT (PT.DBNAME LIKE 'DGO%') -- PROTOTYPEN
AND NOT (PT.DBNAME LIKE 'OE02%') -- Mail Ivo Eichmann
AND NOT (PT.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
AND NOT (PT.DBNAME LIKE 'CSQ%') -- M-QUEUE DATENBANK
AND NOT (PT.DBNAME LIKE 'DAU%') -- Schulung Gerrit
AND NOT (PT.DBNAME LIKE '%A1X%') -- Neue Prototypen
AND NOT (PT.DBNAME LIKE 'DB2ALA%') -- P.Lehmann
AND NOT (PT.DBNAME LIKE '?MAREC%') -- P.Lehmann
--------- END EXCLUDE LIST -------------------------------
ORDER BY pt.DBNAME, pt.tsNAME, PT.PARTITION
WITH UR;