zOs/SQL/ELARIXOL

-- 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
--     sq = 100 - max(colSeq of these columns in index)
--
with ix as
( select ( select substr(
                 right('   '||strip(char(count(distinct k.colName))),3)
                 || right('   ' || strip(char( 100-max(k.colSeq))), 3)
                 ,1 , 6)
               from SYSIBM.SYSKEYS K
               where   K.IXCREATOR  = I.CREATOR
                   and K.IXNAME  = I.NAME
                   and k.colNAME
                        in ('EN1CIFINH','EN1VALOR','ET1LOGPROD')
         ) ks, i.*
      from SYSIBM.SYSINDEXES I
)
select SUBSTR(STRIP(T.CREATOR) ||'.'|| T.NAME, 1, 30) "table",
       max(substr(ks || ' ' || strip(ix.creator) ||'.'|| ix.name
            , 1, 40)) " #k sq bestIndex"
    FROM SYSIBM.SYSTABLES T, ix
    WHERE  1=1
          -- and T.CREATOR = 'BUA' AND T.NAME = 'XBCGZ064PS001001'
      AND 3 <= (select count(distinct c1.name)
                  FROM SYSIBM.SYSCOLUMNS C1
           WHERE  C1.NAME in ('EN1CIFINH','EN1VALOR','ET1LOGPROD')
             AND C1.TBNAME   = T.NAME
             AND C1.TBCREATOR  = T.CREATOR )
      and ix.tbCreator = t.creator
      and ix.tbName    = t.name
    group by t.creator, t.name
    ORDER BY value(max(left(ks, 6)), ''), t.CREATOR, t.NAME
    with ur
;
-- find tables with columns         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
--     sq = 100 - max(colSeq of these columns in index)
--
with ix as
( select ( select substr(
                 right('   '||strip(char(count(distinct k.colName))),3)
                 || right('   ' || strip(char( 100-max(k.colSeq))), 3)
                 ,1 , 6)
               from SYSIBM.SYSKEYS K
               where   K.IXCREATOR  = I.CREATOR
                   and K.IXNAME  = I.NAME
                   and k.colNAME in ('EN1TRANSACTNO', 'ET1LOGPROD')
         ) ks, i.*
      from SYSIBM.SYSINDEXES I
)
select SUBSTR(STRIP(T.CREATOR) ||'.'|| T.NAME, 1, 30) "table",
       max(substr(ks || ' ' || strip(ix.creator) ||'.'|| ix.name
            , 1, 40)) " #k sq bestIndex"
    FROM SYSIBM.SYSTABLES T, ix
    WHERE  1=1
          -- and T.CREATOR = 'BUA' AND T.NAME = 'XBCGZ064PS001001'
      AND 2 <= (select count(distinct c1.name)
                  FROM SYSIBM.SYSCOLUMNS C1
           WHERE  C1.NAME in ('EN1TRANSACTNO', 'ET1LOGPROD')
             AND C1.TBNAME   = T.NAME
             AND C1.TBCREATOR  = T.CREATOR )
      and ix.tbCreator = t.creator
      and ix.tbName    = t.name
    group by t.creator, t.name
    ORDER BY value(max(left(ks, 6)), ''), t.CREATOR, t.NAME
    with ur
;x;

-- alte Version, nur auf hdb abfragen
  SELECT   SUBSTR(STRIP(CREATOR) ||'.'|| STRIP(NAME),1,30)
         , CREATEDTS
    FROM SYSIBM.SYSTABLES T
    WHERE 1=1
      AND EXISTS
        (SELECT 1 FROM SYSIBM.SYSCOLUMNS C1
           WHERE  C1.NAME = 'EN1TRANSACTNO'
             AND C1.TBNAME   = T.NAME
             AND C1.TBCREATOR  = T.CREATOR )
      AND EXISTS
        (SELECT 1 FROM SYSIBM.SYSCOLUMNS C2
           WHERE  C2.NAME = 'ET1LOGPROD'
             AND C2.TBNAME   = T.NAME
             AND C2.TBCREATOR  = T.CREATOR )
      AND NOT EXISTS
        (SELECT 1 FROM SYSIBM.SYSINDEXES I
           WHERE  I.NAME = T.NAME || '_HBD'
             AND I.TBNAME   = T.NAME
             AND I.TBCREATOR  = T.CREATOR )
--    AND T.CREATEDTS > CURRENT TIMESTAMP - 9  MONTHS
--    AND T.CREATEDTS < CURRENT TIMESTAMP - 8  MONTHS
--    AND T.NAME LIKE 'XBCVK%'
--    AND T.NAME NOT LIKE 'XBAOV007%'
    ORDER BY 1
--  FETCH FIRST 10 ROWS ONLY
    ;