zOs/SQL/CATIXLI2

with ts as   -- tablespace mit dsSz
  ( select s.*,
            case when partitions = 0 then 64 * 1024 * 1024
                 when dssize <> 0 then dssize
                 when type in ('K', 'L') then 4 * 1024 * 1024
                 when partitions <=  16 then 4 * 1024 * 1024
                 when partitions <=  32 then 2 * 1024 * 1024
                 when partitions <=  64 then 1 * 1024 * 1024
                 when partitions <= 254 then 4 * 1024 * 1024
                 when partitions <=  16 then 4 * 1024 * 1024
            end dsSz
        from sysibm.sysTablespace s
  ) ,
ii as   -- index mit pieceSz, pieceCnt und maxPartition
  (
    select i.*, t.tsName, p.maxPart,
        case when maxPart > 0 then 1
             when s.dsSize < 4e6 and not s.type in ('L', 'K') then 32
             else 4096
        end pieceCnt,
        case when maxPart > 0 then dsSz
             when pieceSize > 0 then piecesize
             when s.dsSz >= 4 then 4 * 1024 * 1024
             else 2  * 1024 * 1024
        end pieceSz
    from sysibm.sysindexes i, sysibm.systables t, ts s,
        ( select max(partition) maxPart, ixCreator, ixName
               from sysibm.sysindexpart p
               group by ixCreator, ixName
        ) p
    where
            i.tbCreator = t.creator and i.tbName = t.name
        and t.dbName = s.dbName and t.tsName = s.name
        and i.creator = p.ixCreator and i.name = p.ixName
  ) ,
L as --* SchwellWerte tadm10a1 mit like Wert ergänzen
  ( select W.*,
           case when posStr(db_name, '%') < 1 then '% no %'
                else left(db_name, posStr(db_name, '%') - 1)
           end dbLike,
           case when posStr(ts_name, '%') < 1 then '% no %'
                else left(ts_name, posStr(ts_name, '%') - 1)
           end tsLike
        from OA1A.tadm10a1 W
        where w.END_DATUM >= timestamp(current date,'00:00:00')
  ),
RR as --* RTS mit Limite und Schwellwert
  (
  select r.*,
       ii.creator ixCreator, ii.name ixName, ii.maxPart,
       ii.tbCreator, ii.tbName, ii.tsName,
       float(r.nActive) * 4 / 1024 / 1024 activeGB,
       float(r.space)       / 1024 / 1024 spaceGB,
       ii.pieceCnt limitPieces,
       float(ii.pieceSz) * ii.pieceCnt / 1024 / 1024 limitGB,
       coalesce(L1.HIGHWATER_1GB, L2.HIGHWATER_1GB, L3.HIGHWATER_1GB,
                L4.HIGHWATER_1GB, .8)  swFrc,
       coalesce(L1.exclude, L2.exclude, L3.exclude,
                    l4.exclude, 'N') exclude,
       coalesce(strip(l1.db_name) || '.' || strip(l1.ts_name)
                    || ':' || strip(char(l1.partitions_nr)),
                strip(l2.db_name) || '.' || strip(l2.ts_name)
                    || ':' || strip(char(l2.partitions_nr)),
                strip(l3.db_name) || '.' || strip(l3.ts_name)
                    || ':' || strip(char(l3.partitions_nr)),
                strip(l4.db_name) || '.' || strip(l4.ts_name)
                    || ':' || strip(char(l4.partitions_nr)),
                'sql default') swKey
      from sysibm.indexspacestats r
        left join ii on ii.dbid = r.dbid and ii.isobid = r.isobid
        left JOIN L L1      --* Schwelle für db, ts und part%
          ON      L1.DB_NAME       =    ii.DBNAME
              AND L1.TS_NAME      =    ii.indexSpace
              AND L1.PARTITIONS_NR IN (0 , r.PARTITION)
        left JOIN L L2       --* Schwelle für db, ts% und part%
          ON      L2.DB_NAME       =   ii.DBNAME
              AND L2.tsLike  = left(ii.indexSpace,length(L2.tsLike))
              AND L2.PARTITIONS_NR IN (0 , r.PARTITION)
        left JOIN l L3       --* Schwelle für db, ts% und part%
          ON      L3.dbLike  = left(ii.dbname, length(L3.dbLike))
              AND ( L3.tsLike  = left(ii.indexspace, length(L3.tsLike))
                    or L3.ts_name = ii.indexSpace )
              AND L3.PARTITIONS_NR IN (0 , r.PARTITION)
        LEFT JOIN L L4        --* Schwell Default
          ON L4.DB_NAME       =    'DEFAULT/I'
  )
--\*********************************************************************
--\
--\      IndexSpace (Partitionen) grösser SchwellWert * Limite
--\
SELECT suBSTR(DBNAME,1,8) "db",
       SUBSTR(indexSpace,1,8) "indexSpc",
       char(case
           when partition <= 999 and maxPart <= 999
               then right('  ' || strip(char(PARTITION)), 3) || '/'
                    || right('  ' || strip(char(maxPart)), 3)
               else strip(char(PARTITION)) || '/'
           end, 7) "par/tot" ,
       char(
       right('     '||strip(cast(dec(activeGB, 8, 3) as char(10))),10)
       || char(dec(swFrc, 3, 2))
       || right('     '||strip(cast(dec(limitGB, 8,3) as char(10))),10)
           , 25)        "    active scWe     limit",
       char(left(ixName, 20), 20)  "index name",
     rr.*
    from RR
    where exclude in ('N', 'S') and activeGB > swFrc * limitGB
    ORDER BY 1, 2, 3
    ;
--/
--/ db        = Datenbank
--/ indexSp   = index Space (Nicht Name|)
--/ par/tot   = betroffene PartitionsNummer / Total Partitonen
--/ active    = Speicherplatz in GB der active Pages
--/ schWe     = Schwellwert Faktor
--/ lim       = Limite in GB
--/ indexName = Name des Index
--/
--/*********************************************************************
--$SPECIAL
select current timestamp "timestamp",
       current member "member", current server "server",
               '23.10.07 a' "version"
    from sysibm.sysDummy1
;