zOs/SQL/CATIXSZ

--- calculate minimal number of indexpages for an index part
 -- * leafSz = (pgSize - 64) * 100 / (100 - pctFree)
 -- * entrySize unique = keyLen + 1 + ridLen
 -- * uniqueIndex leafPages = cardF * (keyLen + 1+ridLen) / leafSz
 -- * entrySize nonUnique = 2 + keyLen + ridCnt * (1 + ridLen)
 -- * duplicateIndex leafPages = cardF
 --           * ((keyLen + 2) * distinctF / cardF + 1+ridLen) / leafSz
 -- see http://chsa4212/host/db2wiki/pmwiki.php?n=Walter.Jour1101#ixSz
set current path oa1p;
select dbNr, fqzFmte7(dbNr) e7, fqzFmte8(dbNr),fqzFmtBi2(dbNr) bi,dbName
    from oa1p.TQZ051DBNR
    order by dbNr desc
    fetch first 5 rows only
;
with i as
(
select r.nLeaf, r.nActive, r.totalEntries,
    case when i.pgSize<=32 then i.pgSize * 1024 else i.pgSize end pgSz,
    i.pgSize, i.uniquerule, i.relCreated,
    i.fullkeyCardF,
    s.keyCountF, s.fullKeyCardf sFullKeyCardF,
    case when r.partition > 0 then s.keyCountF else t.cardf end sCardF,
    case when r.partition > 0 then s.fullKeyCardF
         else i.fullKeyCardF end sDistinct,
    p.pctFree, i.avgKeyLen,
    t.cardf,
    i.creator, i.name, r.partition
    from sysibm.sysIndexSpaceStats r
         join sysibm.sysIndexes i
           on i.creator = r.creator and i.name = r.name
         left join sysibm.sysIndexStats s
           on    s.owner = i.Creator and s.name = i.name
               and s.partition = r.partition,
         sysibm.sysIndexPart p,
         sysibm.sysTables    t
    where
          p.ixCreator = r.creator and p.ixName = r.name
      and p.partition = r.partition
      and t.creator = i.tbCreator and t.Name = i.tbName
      and p.partition = r.partition
      and i.dbName like 'QZ01%' -- and i.name like 'IWU334%'
      and r.nLeaf > 100
)
, l as
(
select
    real(totalEntries) / (pgSz-64) * 100 / (100 -pctFree)

    * ( 6 + case when avgKeyLen < 1 then null
                 when uniqueRule = 'D' and not
                       (sCardf < 1 or sFullKeyCardf < 1) then
                     sFullKeyCardF / sCardf * (2 + avgKeyLen)
                 else
                     avgKeyLen
            end) leafMin,
    i.*
    from i
)
select        fqzFmtE7(nLeaf/max(1, leafMin))      "lea/min",
              fqzFmtE7(nLeaf)                      "actLeaf",
       subStr(fqzFmtE7(leafMin)              ,1,7) "minLeaf",
       substr(strip(creator) ||'.'||strip(name)
              ||':'||strip(char(partition)), 1, 30) "cr.ix.part",
       l.*
    from l
    where leafMin is not null
 --   order by 1  desc
    order by nLeaf - 2*leafMin desc
    fetch first 200 rows only
;;;
select totalEntries/nLeaf, r.*
    from sysibm.sysIndexSpaceStats r
    where r.Creator = 'OA1P' and   Name = 'IVV719A4'
      order by 1 desc ;
with g as
(
select count(*) c, VV719004, VV719005, VV719006, VV719007, VV719008
    from OA1P.TVV719A1
    --ere VV719002   <= 'B18999999999999999999999'
    -- re VV719002   >  'NDE000D99999999999999999'
    --and VV719002   <= 'NDE000U99999999999999999'
    where VV719002   >  'RL9999999999999999999999'
      and VV719002   <= 'RZ9999999999999999999999'
    group by       VV719004, VV719005, VV719006, VV719007, VV719008
)
select sum(c), count(*) from g
    with ur   ;;;
------- Achtung: card ist häufig -1, auch wenn cardf Ok
select cardf, card,count(*)
    from sysibm.systables t
    group by cardf, card
    order by cardf desc, card desc
    with ur
;;;
select min(cardf), max(cardf), card, count(*)
    from sysibm.systables t
    group by card, case when cardf <= 2 then cardf else 999 end
    order by card
    with ur
;;;
------- Achtung fullkeyCardF ist für DPSI auf IndexLevel
     -- unbrauchbar, müssen es aus sysIndexStats holen
select sum(fullKeyCardf), sum(keyCountf)
    from sysibm.sysIndexStats
    where owner = 'OA1P' and name = 'IVV719A4'
;;;