Hi All !

How to write a script that drop and recreate all indexes of a database ?

Thank you

Hi,

The unix script below generates the schema layout and searches the created index statements and generates appropriate index creation and deletion script files. Before executing the generated script files do give a glance and rectify errors if any.

Regards,
Shriyan

#! /usr/bin/ksh
#Script to manage indexes. (delete and recreate)
#Author: V.P. Shriyan 01-MAR-2004

if [ "x$1" = "x" ];
then
echo Pass database name as parameter.
exit
fi

#generate schema
echo Generating schema file "$1.sch" ...
dbschema -d $1 -ss > $1.sch
if [ $? -ne 0 ];
then
echo Unable to generate full schema layout, please retry.
echo Study $1.sch for details.
exit
fi

#create script
echo Generating create index file "$1.cre" ...
egrep "create index|create unique| in " $1.sch > $1.cre

#delete script
echo Generating delete index file "$1.del" ...
grep "create index" $1.sch |awk '{print "drop "$2" "$3" ;"}' > $1.del
grep "create unique index" $1.sch |awk '{print "drop "$3" "$4" ;"}' >> $1.del

#Integrity check
AA=`cat $1.cre|wc -l`
BB=`cat $1.del|wc -l`

C1=`expr $BB \* 2`

if [ $C1 != $AA ]
then
echo Please check $1.cre file for errors. Additional Unmatched line found.
fi

hello
thanks for your help
it goes for the deletion but for creation there is a problem when create index is on 2 or several lines, in this case only the first line appears.

Hi Aroui,

Are you sure that you have executed the script as it is, without making any changes?

In the script file the line:
dbschema -d $1 -ss > $1.sch

Here the -ss switch is very important, also:
egrep "create index|create unique| in " $1.sch > $1.cre

Here the space preceding and succeeding the pattern "in" is very inportant to catch the 2nd line of the create index syntax. This script able to consider up to 2 line of information only. If you have a create index syntax that exceed 2 lines of information, you need to join them into max of 2 lines only.

Regards,
Shriyan

Hello Shriyan

i have executed the script without any changes

Hi Aroui,

Ok...

I have an alternate solution for your requirement. This SQL statement are bit lengthy, but I could not do it cryptic, specially I wanted to avoid all those update statements, and wanted it in a single update statement. You need to replace the index spacename in the script once it is generated.

Hope you will find this SQL script useful.

Regards,
Shriyan


-- This SQL script generates create index syntax for a given database.
-- For it's information it reads the catalog tables.
-- Author: V.P. Shriyan
-- Date 02-MAR-2004

set isolation to dirty read;

create temp table x
(
idxname char(18) , owner char(8) ,
tabid integer , idxtype char(1) ,
clustered char(1) , part1 char(18) ,
part2 char(18) , part3 char(18) ,
part4 char(18) , part5 char(18) ,
part6 char(18) , part7 char(18) ,
part8 char(18) , part9 char(18) ,
part10 char(18) , part11 char(18) ,
part12 char(18) , part13 char(18) ,
part14 char(18) , part15 char(18) ,
part16 char(18)
) with no log;

insert into x
(
idxname , owner , tabid ,
idxtype , clustered , part1 ,
part2 , part3 , part4 ,
part5 , part6 , part7 ,
part8 , part9 , part10,
part11 , part12 , part13,
part14 , part15 , part16
)
select
i.idxname , i.owner , i.tabid,
i.idxtype , i.clustered , i.part1,
decode(i.part2,0,null) , decode(i.part3,0,null) , decode(i.part4,0,null),
decode(i.part5,0,null) , decode(i.part6,0,null) , decode(i.part7,0,null),
decode(i.part8,0,null) , decode(i.part9,0,null) , decode(i.part10,0,null),
decode(i.part11,0,null), decode(i.part12,0,null), decode(i.part13,0,null),
decode(i.part14,0,null), decode(i.part15,0,null), decode(i.part16,0,null)
from systables s, sysindexes i
where s.tabid=i.tabid
and idxname matches "*[a-z]*"
and s.tabid>99 and s.tabtype="T" ;

update x set part1= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part1) where part1 != 0;

update x set part2= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part2) where part2 != 0;

update x set part3= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part3) where part3 != 0;

update x set part4= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part4) where part4 != 0;

update x set part5= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part5) where part5 != 0;

update x set part6= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part6) where part6 != 0;

update x set part7= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part7) where part7 != 0;

update x set part8= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part8) where part8 != 0;

update x set part9= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part9) where part9 != 0;

update x set part10= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part10) where part10 != 0;

update x set part11= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part11) where part11 != 0;

update x set part12= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part12) where part12 != 0;

update x set part13= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part13) where part13 != 0;

update x set part14= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part14) where part14 != 0;

update x set part15= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part15) where part15 != 0;

update x set part16= (select colname from sysindexes i,syscolumns c where i.tabid=c.tabid and x.idxname=i.idxname and x.tabid=i.tabid and colno=i.part16) where part16 != 0;

unload to 'cr_index.sql' delimiter
select "CREATE",
decode(idxtype,"U"," UNIQUE"," "),
decode(clustered,"C"," CLUSTER "," "),
" INDEX "," ",
'"'||trim(x.owner)||'".',
trim(idxname), " ON ", " ",
'"'||trim(x.owner)||'".',
trim(tabname), " ","(",
case
when part16 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)||","||trim(part5)||","||trim(part6 )||","||trim(part7)||","||trim(part8)||","||trim(p art9)||","||trim(part10)||","||trim(part11)||","|| trim(part12)||","||trim(part13)||","||trim(part14) ||","||trim(part15)||","||trim(part16)
when part15 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)||","||trim(part5)||","||trim(part6 )||","||trim(part7)||","||trim(part8)||","||trim(p art9)||","||trim(part10)||","||trim(part11)||","|| trim(part12)||","||trim(part13)||","||trim(part14) ||","||trim(part15)
when part14 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)||","||trim(part5)||","||trim(part6 )||","||trim(part7)||","||trim(part8)||","||trim(p art9)||","||trim(part10)||","||trim(part11)||","|| trim(part12)||","||trim(part13)||","||trim(part14)
when part13 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)||","||trim(part5)||","||trim(part6 )||","||trim(part7)||","||trim(part8)||","||trim(p art9)||","||trim(part10)||","||trim(part11)||","|| trim(part12)||","||trim(part13)
when part12 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)||","||trim(part5)||","||trim(part6 )||","||trim(part7)||","||trim(part8)||","||trim(p art9)||","||trim(part10)||","||trim(part11)||","|| trim(part12)
when part11 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)||","||trim(part5)||","||trim(part6 )||","||trim(part7)||","||trim(part8)||","||trim(p art9)||","||trim(part10)||","||trim(part11)
when part10 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)||","||trim(part5)||","|| trim(part6)||","|| trim(part7)||","|| trim(part8)||","||trim(part9)||","||trim(part10)
when part9 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)||","||trim(part5)||","||trim(part6 )||","||trim(part7)||","||trim(part8)||","||trim(p art9)
when part8 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)||","||trim(part5)||","||trim(part6 )||","||trim(part7)||","||trim(part8)
when part7 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)||","||trim(part5)||","||trim(part6 )||","||trim(part7)
when part6 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)||","||trim(part5)||","||trim(part6 )
when part5 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)||","||trim(part5)
when part4 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)||" ,"||trim(part4)
when part3 is not null then
trim(part1)||","||trim(part2)||","||trim(part3)
when part2 is not null then
trim(part1)||","||trim(part2)
else trim(part1)
end,
") IN xspace;"
from x,systables where x.tabid=systables.tabid;

Hi vpshriyan

thank u for your post

it goes but I encountered two problems :
- with the function decode which has various arguments in script (3-4)
- with the instruction case which is not recognized by all the versions of Informix
I replaced decode with another function with 3 argumets and I tested script on an engine which recognize case and that work

Best regards

Hi Aroui,

You are welcome. Thanks for the compliments.

Yes, DECODE and CASE keyword of SQL statements are available, I think from ver 7.3 onwards.

Regards,
Shriyan