zOs/SQL/ELARIX

-- find tables with columns         EN1CIFINH, EN1VALOR, ET1LOGPROD
--     but no index with these columns in the beginning
-- we select these table and the best index
--     #k = number of these columns in index
--
with t (tc, tb)  as
(  select creator, name
     from sysibm.sysTables t
     where 3 = ( select count(distinct name)
                     from sysibm.sysColumns c
                     where   c.tbCreator = t.creator
                         and c.tbName    = t.name
                         and c.name
                             in ('EN1CIFINH','EN1VALOR','ET1LOGPROD')
               )
)
, k (tc, tb, cr, ix, sq, ky) as
( select t.*, i.creator, i.name, 0, varchar('',100)
    from t
        left join sysibm.sysIndexes i
        on      i.tbCreator = t.tc
            and i.tbName = t.tb
  union all select tc, tb, cr, ix, sq+1, ky || ' ' || strip(y.colName)
      from sysibm.sysKeys y, k
      where   y.ixCreator = k.cr
          and y.ixName = k.ix
          and y.colSeq = k.sq + 1
          and y.colName in ('EN1CIFINH','EN1VALOR','ET1LOGPROD')
          and k.sq < 999
)
select max(right('   ' || strip(char(sq)), 3) || ' '
       || left(strip(tc) || '.' || tb, 30)
       || left(strip(cr) || '.' || ix, 30)
       || ky) " #k tb                            best index"
    from k
    group by tc, tb
    order by 1 asc
;
-- find tables with columns in ('EN1TRANSACTNO', 'ET1LOGPROD')
--     but no index with these columns in the beginning
-- we select these table and the best index
--     #k = number of these columns in index
--
with t (tc, tb)  as
(  select creator, name
     from sysibm.sysTables t
     where 2 = ( select count(distinct name)
                     from sysibm.sysColumns c
                     where   c.tbCreator = t.creator
                         and c.tbName    = t.name
                         and c.name
                             in ('EN1TRANSACTNO', 'ET1LOGPROD')
               )
)
, k (tc, tb, cr, ix, sq, ky) as
( select t.*, i.creator, i.name, 0, varchar('',100)
    from t
        left join sysibm.sysIndexes i
        on      i.tbCreator = t.tc
            and i.tbName = t.tb
  union all select tc, tb, cr, ix, sq+1, ky || ' ' || strip(y.colName)
      from sysibm.sysKeys y, k
      where   y.ixCreator = k.cr
          and y.ixName = k.ix
          and y.colSeq = k.sq + 1
          and y.colName in ('EN1TRANSACTNO', 'ET1LOGPROD')
          and k.sq < 999
)
select max(right('   ' || strip(char(sq)), 3) || ' '
       || left(strip(tc) || '.' || tb, 30)
       || left(strip(cr) || '.' || ix, 30)
       || ky) " #k tb                            best index"
    from k
    group by tc, tb
    order by 1 asc
;