zOs/SQL/ELCOMP

-- show index with list of columns
--          plus hexKey for colDist etc.
with t  (tc , tb) as
( select creator, name
    from sysibm.sysTables
    where Creator = 'OA1P'
        and (name like 'TKS%A%' or Name like 'TELKS%')
)
, i (tc, tb, cr, ix, colC, seq, keys, kHex) as
( select tc, tb, value(creator, 'noIndex'), value(name, 'noIndex'),
         colCount, 0, varchar('',999), varchar('',999)
    from t left join sysibm.sysindexes i
      on tbCreator = tc and tbName = tb
  union all select tc, tb, 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
)
, j as
(
select strip(tc) || '.' || strip(tb) || ' '
    || strip(cr) || '.' || strip(ix)|| ' ' || keys tx, i.*
    from i
    where seq = colc or colc is null
)
, e as
( select 'e' ty, tb eTb, j.*
      from j where tb like 'TELKS%'
)
, k as
(
  select 'k' ty, 'TELKS' || substr(tb,4,3) || 'C4' eTb, j.*
    from j
    where (cr, 'TELKS' || substr(tb,4,3) || 'C4')
       in (select cr, tb from e)
)
select case when e.ty is null then k.ty
            when k.ty is null then e.ty
            else 'b' end ty,
       substr(value(strip(e.tc) || '.' || strip(e.tb),
                    strip(k.tc) || '.' || strip(k.tb)), 1, 20) tb,
       substr(value(strip(e.cr) || '.' || strip(e.ix),
                    strip(k.cr) || '.' || strip(k.ix)), 1, 20) ix,
       value(e.keys, k.keys) keys,
       e.*, k.*
    from e
      full join k
      on e.cr = k.cr and e.eTb = k.eTb and e.keys = k.keys
    order by 1 desc, 2, 3, 4
;