Reverse engineer indexes in DB2 – Take 1

I love DB2LOOK for many reasons. Its a wonderful and easy way to reverse engineer a database. DB2LOOK can generate DDLs for specific table(s), but what if we just want indexes to be reverse engineered, thats were db2look hits the brick wall.  I wonder why ibm didn’t provide a way just to get the indexes, so I wrote a simple sql for it.  It does not do include or MDCs for now. But I am working on it :

with temp1 (col1) as
(
select ‘create ‘||
case
when uniquerule in (‘U’,’P’)
then ‘unique index ‘
else ‘index ‘ end
||rtrim(indschema)||’.’||indname||’ on ‘||rtrim(tabschema)||’.’||tabname||’ (‘||replace(colnames,’+’,’,’)||’)’||
case
when indextype = ‘CLUS’
then ‘cluster ‘
else ‘ ‘ end
||’allow reverse scans;’
from syscat.indexes where indname like ‘X%’
)
select replace(col1,'(,’,'(‘) from temp1;

I know it has few limitation, but its a start !