zOs/SQL/CATIXCO

-- show index with list of columns
--          plus hexKey for colDist etc.
with i (cr, ix, colC, seq, keys, kHex) as
( select creator, name, colCount, 0, varchar('',999),varchar('',999)
    from sysibm.sysindexes i
    where tbCreator = 'OA1P' and tbName = 'TWM005A1'
  union all select cr, ix, colC, seq+1,
        keys || case when length(strip(k.colName)) <= 15
                     then strip(k.colName)
                     else strip(char(k.colNo)) end || ' ',
        kHex || hex(k.colNo)
      from i join sysibm.sysKeys k
          on k.ixCreator = i.cr and k.ixName = i.ix
          and k.colSeq = i.seq + 1 and i.seq < i.colC
                                   and i.seq < 999
)
select substr(strip(cr) || '.' || strip(ix), 1, 16),
       substr(keys, 1, 80) keys,
       substr(kHex, 1, 80) kHex,
       colC, length(kHex)
    from i
    where seq = colC
    order by cr, ix
;;;
select SUBSTR(strip(i.creator) || '.' || strip(i.name), 1, 16),
         smallInt(colCount),
       substr(k1.colName, 1, 8) c1,
       substr(k2.colName, 1, 8) c2,
       substr(k3.colName, 1, 8) c3,
       hex(k1.colNo) || value(hex(k2.colNo), '')
                     || value(hex(k3.colNo), '')
    from sysibm.sysindexes i
    left join sysibm.sysKeys k1
    on k1.ixCreator = i.creator and k1.ixName = i.name
        and k1.colSeq = 1
    left join sysibm.sysKeys k2
    on k2.ixCreator = i.creator and k2.ixName = i.name
        and k2.colSeq = 2 and i.colCount >= 2
    left join sysibm.sysKeys k3
    on k3.ixCreator = i.creator and k3.ixName = i.name
        and k3.colSeq = 3 and i.colCount  >= 3
    where tbCreator = 'OA1P' and tbName = 'TWM005A1'
    order by i.name