select 
queryno, planno, method, tname,
accesstype,matchcols, accessname
,  ifnull(k1.colname, '') 
|| ifnull(', ' || k2.colname, '')
|| ifnull(', ' || k3.colname, '')
|| ifnull(', ' || k4.colname, '')
|| ifnull(', ' || k5.colname, '')
|| ifnull(', ' || k6.colname, '')
|| ifnull(', ' || k7.colname, '') as "List of Index Columns",  
ifnull(n1.colname, '') 
|| ifnull(', ' || n2.colname, '')
|| ifnull(', ' || n3.colname, '')
|| ifnull(', ' || n4.colname, '')
|| ifnull(', ' || n5.colname, '')
|| ifnull(', ' || n6.colname, '')
|| ifnull(', ' || n7.colname, '') as "Not Used Index Columns"
, indexonly
, creator
from 
fdwddba.plan_table  pl 
left join sysibm.syskeys k1
on    k1.ixname   = pl.accessname
  and k1.colseq   = 1
  and k1.colseq  <= pl.matchcols
  and pl.accessname > ' '
  and k1.ixcreator = pl.creator
left join sysibm.syskeys k2
on    k2.ixname = pl.accessname
  and k2.colseq  = 2
  and k2.colseq  <= pl.matchcols
  and pl.accessname > ' '
  and k2.ixcreator = pl.creator
left join sysibm.syskeys k3
on    k3.ixname  = pl.accessname
  and k3.colseq  = 3
  and k3.colseq  <= pl.matchcols
  and pl.accessname > ' '
  and k3.ixcreator = pl.creator
left join sysibm.syskeys k4
on    k4.ixname = pl.accessname
  and k4.colseq  = 4
  and k4.colseq  <= pl.matchcols
  and pl.accessname > ' '
  and k4.ixcreator = pl.creator
left join sysibm.syskeys k5
on    k5.ixname = pl.accessname
  and k5.colseq  = 5
  and k5.colseq  <= pl.matchcols
  and pl.accessname > ' '
  and k5.ixcreator = pl.creator
left join sysibm.syskeys k6
on    k6.ixname = pl.accessname
  and k6.colseq  = 6
  and k6.colseq  <= pl.matchcols
  and pl.accessname > ' '
  and k6.ixcreator = pl.creator
left join sysibm.syskeys k7
on    k7.ixname = pl.accessname
  and k7.colseq  = 7
  and k7.colseq  <= pl.matchcols
  and pl.accessname > ' '
  and k7.ixcreator = pl.creator
left join sysibm.syskeys n1
on    n1.ixname  = pl.accessname
  and n1.colseq  = pl.matchcols + 1
  and pl.accessname > ' '
  and n1.ixcreator = pl.creator
left join sysibm.syskeys n2
on    n2.ixname  = pl.accessname
  and n2.colseq  = pl.matchcols + 2
  and pl.accessname > ' '
  and n2.ixcreator = pl.creator
left join sysibm.syskeys n3
on    n3.ixname  = pl.accessname
  and n3.colseq  = pl.matchcols + 3
  and pl.accessname > ' '
  and n3.ixcreator = pl.creator
left join sysibm.syskeys n4
on    n4.ixname  = pl.accessname
  and n4.colseq  = pl.matchcols + 4
  and pl.accessname > ' '
  and n4.ixcreator = pl.creator
left join sysibm.syskeys n5
on    n5.ixname  = pl.accessname
  and n5.colseq  = pl.matchcols + 5
  and pl.accessname > ' '
  and n5.ixcreator = pl.creator
left join sysibm.syskeys n6
on    n6.ixname  = pl.accessname
  and n6.colseq  = pl.matchcols + 6
  and pl.accessname > ' '
  and n6.ixcreator = pl.creator
left join sysibm.syskeys n7
on    n7.ixname  = pl.accessname
  and n7.colseq  = pl.matchcols + 7
  and pl.accessname > ' '
  and n7.ixcreator = pl.creator
WHERE queryno in (8877)
  and TNAME > ' '   
order by queryno, planno
with ur

with 
qrynbr(qry#) as 
(select 8917 from sysibm.sysdummy1
)
,
expln as
(select queryno, planno, method,tname,accesstype, matchcols, accessname,indexonly,creator,prefetch, tabno
from fdwddba.plan_table, qrynbr 
where queryno = qry#  
)
,
ix_tbl (ixn, seq, ix_cols, not_used_cols, maxcols, tabno) as
(
select sk.ixname, 
1, 
case when ex.matchcols > 0 then
sk.colname
else ifnull(nullif(sk.colname, sk.colname), '')
end
,
case when ex.matchcols > 0 then
ifnull(nullif(sk.colname, sk.colname), '')
else sk.colname
end
, si.colcount
, tabno 
   from sysibm.syskeys    sk
	 ,expln             ex
       ,sysibm.sysindexes si
where sk.ixname    = ex.accessname
  and sk.ixcreator = ex.creator 
  and sk.ixname    = si.name
  and sk.ixcreator = si.creator 
  and sk.colseq    = 1  
  and ex.accessname > ' '
  and ex.matchcols <=  si.colcount 
union all
 select sk.ixname, seq + 1, 
case when seq  <  ex.matchcols
     then ix_cols || ',' || sk.colname
else ix_cols 
end
, 
case when seq >= ex.matchcols 
     then not_used_cols || ',' || sk.colname
else not_used_cols 
end
, maxcols
, ix.tabno
from    ix_tbl         ix
	, sysibm.syskeys sk
	, expln ex
   where seq + 1       = sk.colseq 
	and seq          <  maxcols 
	and sk.ixname    = ex.accessname
      and sk.ixcreator = ex.creator 
	and sk.ixname    = ixn 
      and ex.tabno     = ix.tabno  
    )  
select ex.queryno, ex.planno,ex.method, 
       case when si.tbname is null 
            then ex.tname
		else si.tbname
	 end tname
	,ex.accesstype, ex.matchcols
	,ex.accessname
	,ix.ix_cols "List of Index Columns" 
        ,strip(ix.not_used_cols, L, ',') "Not Used Index Columns"
	,ex.indexonly, ex.prefetch, ex.creator	
  from  expln ex
left join sysibm.sysindexes si
  on si.name = ex.accessname
  and si.creator = ex.creator
left join ( select ixn,substr(max(char(seq)||ix_cols),12) ix_cols , 
                   substr(max(char(seq)|| not_used_cols ),12) not_used_cols,
                   tabno     
		from  ix_tbl  
	      group by ixn, tabno ) ix				
on   ex.accessname = ix.ixn
 and ex.tabno     = ix.tabno 
where ex.tname > ' '
order by ex.queryno, ex.planno, ex.accesstype

with query_str(query) as
(select 
 'select				
        distributi0_.DOCUMENT_ID as DOCUMENT2_0_,				
        distributi0_.DISTRIBUTION_ID as DISTRIBU1_0_ 				
    from				
        FMIDDBA.MW_DOC_DIST distributi0_ 				
    where				
         distributi0_.DOCUMENT_ID = 590316' 
from sysibm.sysdummy1 ) 
select distinct e1.ixname, e1.colname, e1.tbname, xs.tbname orig_tbl
from 
sysibm.syskeys       ks , 
sysibm.sysindexes   xs , 
fdwddba.plan_table pl  , 
query_str              qr
,
table (select k2.ixname, k2.colname, x2.tbname      
         from sysibm.syskeys k2, sysibm.sysindexes  x2 
             where k2.ixcreator = xs.creator
               and k2.ixname   <> xs.name
               and k2.colname   = ks.colname
               and k2.ixname    = x2.name
               and k2.ixcreator = x2.creator	
               and k2.colseq    = 1  ) e1
where pl.queryno    = 8901  
  and 
(Locate(ks.colname, qr.query) > Locate ('WHERE', ucase(qr.query)) 
 or  
 Locate(ks.colname, qr.query) > Locate (' ON ', ucase(qr.query)) )
  and ks.ixcreator  = pl.creator
  and ks.colseq     > 1  
  and pl.accesstype = 'I'
  and pl.matchcols  = 0 
  and xs.name       = accessname  
order by e1.ixname

with query_str(query) as
(select 
 'select				
        distributi0_.DOCUMENT_ID as DOCUMENT2_0_,				
        distributi0_.DISTRIBUTION_ID as DISTRIBU1_0_ 				
    from				
        FMIDDBA.MW_DOC_DIST distributi0_ 				
    where				
         distributi0_.DOCUMENT_ID = 590316' 
from sysibm.sysdummy1 )