Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    Tunisia
    Posts
    17

    Question Unanswered: Managing indexes

    Hi All !

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

    Thank you

  2. #2
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    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

  3. #3
    Join Date
    Feb 2004
    Location
    Tunisia
    Posts
    17

    Thumbs up

    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.

  4. #4
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    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

  5. #5
    Join Date
    Feb 2004
    Location
    Tunisia
    Posts
    17
    Hello Shriyan

    i have executed the script without any changes

  6. #6
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    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;
    Last edited by vpshriyan; 03-02-04 at 02:48.

  7. #7
    Join Date
    Feb 2004
    Location
    Tunisia
    Posts
    17
    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

  8. #8
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •