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 !

About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...
This entry was posted in db2 and tagged , , , . Bookmark the permalink.