moving indexes

[Bill64bits]

I am using IDS 9.4 TC2

I would like to move all indexes to a new dbspace on another drive
and, at the same time give them all meaningful names.
This includes primary keys, foreign keys, and unique constraints.
There are about 1400 indexes in all, so I don't wish to do them one at a time.
I can easily write the code to generate all the "add constraints" and "create unique index" statements.
I have done that before in Perl and now in C#. That runs in a few seconds.
But dropping them all first is a bit hairy because of the way an index gets renamed when constraints
are dropped and one cannot drop an index with a leading space in its name.

One solution is to export, paste the bottom of the db.sql into another file, kill all of the new file
except the procedures, functions, views, sequences, grants,
and then drop the database, run the index builds and referential constraint adds,
then run the second sql file and then update statistics.
I am also thinking about killing all lines in the "create table" statements that declare a unique column
and letting a unique index be added by the second sql file. Is that a bad idea?

Is there a better way to do this without dropping the database?
Time is not a problem since this database will reload in 20 minutes and it is not running 24/7 .
I just don't like the idea of dropping a production database. That just makes me nervous.

[d***@smooth1.co.uk]

Post by Bill64bits
I am using IDS 9.4 TC2
I would like to move all indexes to a new dbspace on another drive
and, at the same time give them all meaningful names.
This includes primary keys, foreign keys, and unique constraints.
There are about 1400 indexes in all, so I don't wish to do them one at a time.
I can easily write the code to generate all the "add constraints" and "create unique index" statements.
I have done that before in Perl and now in C#. That runs in a few seconds.
But dropping them all first is a bit hairy because of the way an index gets renamed when constraints
are dropped and one cannot drop an index with a leading space in its name.
One solution is to export, paste the bottom of the db.sql into another file, kill all of the new file
except the procedures, functions, views, sequences, grants,
and then drop the database, run the index builds and referential constraint adds,
then run the second sql file and then update statistics.
I am also thinking about killing all lines in the "create table" statements that declare a unique column
and letting a unique index be added by the second sql file. Is that a bad idea?
Is there a better way to do this without dropping the database?
Time is not a problem since this database will reload in 20 minutes and it is not running 24/7 .
I just don't like the idea of dropping a production database. That just makes me nervous.

First scan the database and drop all constraints
Then rebuild all required indexes and constraints.

Then scan the database and fix all unique contraints and recreate
unique indexes.

Then use Art Kagels dostats to run update stats across the whole
database.

Do this in multiple steps!

[Art S. Kagel]

Post by Bill64bits
I am using IDS 9.4 TC2
I would like to move all indexes to a new dbspace on another drive
and, at the same time give them all meaningful names.
This includes primary keys, foreign keys, and unique constraints.
There are about 1400 indexes in all, so I don't wish to do them one at a time.
I can easily write the code to generate all the "add constraints" and
"create unique index" statements.
I have done that before in Perl and now in C#. That runs in a few seconds.
But dropping them all first is a bit hairy because of the way an index
gets renamed when constraints
are dropped and one cannot drop an index with a leading space in its name.
One solution is to export, paste the bottom of the db.sql into another
file, kill all of the new file
except the procedures, functions, views, sequences, grants,
and then drop the database, run the index builds and referential constraint adds,
then run the second sql file and then update statistics.
I am also thinking about killing all lines in the "create table"
statements that declare a unique column
and letting a unique index be added by the second sql file. Is that a bad idea?
Is there a better way to do this without dropping the database?
Time is not a problem since this database will reload in 20 minutes and
it is not running 24/7 .
I just don't like the idea of dropping a production database. That just makes me nervous.

Get my utils2_ak package. My dbschema replacement utility, myschema, will
do most of the work for you. It will automatically generate a schema with
separate CREATE INDEX statements for all constraint indexes (even the hidden
ones) and separate out all constraints to ALTER TABLE ADD CONSTRAINT...
statements executed after the appropriate CREATE INDEX statements. If you
pass two filenames on the commandline instead of just one, the second file
will contain all of the indexes, constraints, and privs statements while the
first file contains primarily the create table statements. That will make
it easier to edit the second file and relocate the indexes and be able to
drop the indexes and constraints and just recreate them without extensive
tedious editing of the schema file.

Also, instead of running dostats you can have myschema generate an update
statistics script to duplicate the existing level of stats (though a fresh
dostats run tends to be more efficient to run).

You may also want to get the package utils4_ak which contains some sample
awk scripts that read dbschema/myschema output and generate scripts such as
dropping all indexes, etc. Any I haven't provided will be easy to add to
the existing script set.

Utils2_ak also contains the dostats utility that David mentioned. Note that
I've uploaded a new version of utils2_ak which should be available in the
next couple of days. Look for the README.1st file with the date March 20,
2007 in it.

Art S. Kagel

[Bill64bits]

Thanks Art.
I knew that if anyone could be helpful it would be you.
It still does not tell me how to programatically drop all constraints and indexes in the proper order.
Maybe I have not found you latest code yet.


----- Original Message -----
From: Art S. Kagel
Newsgroups: comp.databases.informix
To: informix-***@iiug.org
Cc: informix-***@iiug.org
Sent: Monday, April 30, 2007 11:06 AM
Subject: Re: moving indexes

Post by Bill64bits
I am using IDS 9.4 TC2
I would like to move all indexes to a new dbspace on another drive
and, at the same time give them all meaningful names.
This includes primary keys, foreign keys, and unique constraints.
There are about 1400 indexes in all, so I don't wish to do them one at a time.
I can easily write the code to generate all the "add constraints" and
"create unique index" statements.
I have done that before in Perl and now in C#. That runs in a few seconds.
But dropping them all first is a bit hairy because of the way an index
gets renamed when constraints
are dropped and one cannot drop an index with a leading space in its name.
One solution is to export, paste the bottom of the db.sql into another
file, kill all of the new file
except the procedures, functions, views, sequences, grants,
and then drop the database, run the index builds and referential constraint adds,
then run the second sql file and then update statistics.
I am also thinking about killing all lines in the "create table"
statements that declare a unique column
and letting a unique index be added by the second sql file. Is that a bad idea?
Is there a better way to do this without dropping the database?
Time is not a problem since this database will reload in 20 minutes and
it is not running 24/7 .
I just don't like the idea of dropping a production database. That just makes me nervous.

Get my utils2_ak package. My dbschema replacement utility, myschema, will
do most of the work for you. It will automatically generate a schema with
separate CREATE INDEX statements for all constraint indexes (even the hidden
ones) and separate out all constraints to ALTER TABLE ADD CONSTRAINT...
statements executed after the appropriate CREATE INDEX statements. If you
pass two filenames on the commandline instead of just one, the second file
will contain all of the indexes, constraints, and privs statements while the
first file contains primarily the create table statements. That will make
it easier to edit the second file and relocate the indexes and be able to
drop the indexes and constraints and just recreate them without extensive
tedious editing of the schema file.

Also, instead of running dostats you can have myschema generate an update
statistics script to duplicate the existing level of stats (though a fresh
dostats run tends to be more efficient to run).

You may also want to get the package utils4_ak which contains some sample
awk scripts that read dbschema/myschema output and generate scripts such as
dropping all indexes, etc. Any I haven't provided will be easy to add to
the existing script set.

Utils2_ak also contains the dostats utility that David mentioned. Note that
I've uploaded a new version of utils2_ak which should be available in the
next couple of days. Look for the README.1st file with the date March 20,
2007 in it.

Art S. Kagel
_______________________________________________
Informix-list mailing list
Informix-***@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[Art S. Kagel]

Post by Bill64bits
I am using IDS 9.4 TC2
I would like to move all indexes to a new dbspace on another drive
and, at the same time give them all meaningful names.
This includes primary keys, foreign keys, and unique constraints.
There are about 1400 indexes in all, so I don't wish to do them one at a time.
I can easily write the code to generate all the "add constraints" and
"create unique index" statements.
I have done that before in Perl and now in C#. That runs in a few seconds.
But dropping them all first is a bit hairy because of the way an index
gets renamed when constraints
are dropped and one cannot drop an index with a leading space in its name.
One solution is to export, paste the bottom of the db.sql into another
file, kill all of the new file
except the procedures, functions, views, sequences, grants,
and then drop the database, run the index builds and referential constraint adds,
then run the second sql file and then update statistics.
I am also thinking about killing all lines in the "create table"
statements that declare a unique column
and letting a unique index be added by the second sql file. Is that a bad idea?
Is there a better way to do this without dropping the database?
Time is not a problem since this database will reload in 20 minutes and
it is not running 24/7 .
I just don't like the idea of dropping a production database. That just makes me nervous.

Get my utils2_ak package. My dbschema replacement utility, myschema, will
do most of the work for you. It will automatically generate a schema with
separate CREATE INDEX statements for all constraint indexes (even the hidden
ones) and separate out all constraints to ALTER TABLE ADD CONSTRAINT...
statements executed after the appropriate CREATE INDEX statements. If you
pass two filenames on the commandline instead of just one, the second file
will contain all of the indexes, constraints, and privs statements while the
first file contains primarily the create table statements. That will make
it easier to edit the second file and relocate the indexes and be able to
drop the indexes and constraints and just recreate them without extensive
tedious editing of the schema file.

Also, instead of running dostats you can have myschema generate an update
statistics script to duplicate the existing level of stats (though a fresh
dostats run tends to be more efficient to run).

You may also want to get the package utils4_ak which contains some sample
awk scripts that read dbschema/myschema output and generate scripts such as
dropping all indexes, etc. Any I haven't provided will be easy to add to
the existing script set.

Utils2_ak also contains the dostats utility that David mentioned. Note that
I've uploaded a new version of utils2_ak which should be available in the
next couple of days. Look for the README.1st file with the date March 20,
2007 in it.

Art S. Kagel

[Bill64bits]

Thanks.
I tried various orders but can't remember if I did this one.
I remember I had issues.
One was having an index with a leading blank that could not be dropped.
It may be a whole lot easier to run one pass at a time.
That is to say: 1. output sql to drop ref constraints, 2. execute that, 3.
run 2nd script on what's left, 4. run that, etc...
What I was attempting to do was make one run that writes out all the sql
(into, say 4 files) that would be
run sequentially. One select might be writing to rebuild1.sql and
reqbuild4.sql . The next select might be writing to rebuild2.sql
and rebuild3.sql and/or appending to the end of some file that was written
before.
Then I would execute them in dbaccess in sequence 1,2,...
I'm not sure that is do-able or worth the effort.
Of course one could just execute the sql from the program, but I like the
idea of running in dbaccess and catching
any explosions and dealing with them there.

----- Original Message -----
From: ART KAGEL, BLOOMBERG/ 731 LEXIN
To: ***@hotmail.com
Sent: Tuesday, May 01, 2007 11:06 AM
Subject: Re: moving indexes


No. Dropping things is a subject I haven't dealt with enough. What you
want to
do is drop all of the foreign keys and their indexes before their
references
primary/unique keys. A search on sysconstraints where CONSTRTYPE = 'R' will
get
you the foreign key constraint names and the names of the index on which
each
depends. After that you can select the same for the primary key (CONSTRTYPE
=
'P') and unique keys (CONSTRTYPE = 'U'). There's no need to get fancy and
find
the references constraint info for those referenced by foreign keys only
since
you'll be relocating all of the indexes anyway.

Art
----- Original Message -----
From: Bill64bits <***@hotmail.com>
At: 4/30 22:28:53

Thanks Art.
I knew that if anyone could be helpful it would be you.
It still does not tell me how to programatically drop all constraints and
indexes in the proper order.
Maybe I have not found you latest code yet.


----- Original Message -----
From: Art S. Kagel
Newsgroups: comp.databases.informix
To: informix-***@iiug.org
Cc: informix-***@iiug.org
Sent: Monday, April 30, 2007 11:06 AM
Subject: Re: moving indexes

Post by Bill64bits
I am using IDS 9.4 TC2
I would like to move all indexes to a new dbspace on another drive
and, at the same time give them all meaningful names.
This includes primary keys, foreign keys, and unique constraints.
There are about 1400 indexes in all, so I don't wish to do them one at a
time.
I can easily write the code to generate all the "add constraints" and
"create unique index" statements.
I have done that before in Perl and now in C#. That runs in a few

seconds.

Post by Bill64bits
But dropping them all first is a bit hairy because of the way an index
gets renamed when constraints
are dropped and one cannot drop an index with a leading space in its

name.

Post by Bill64bits
One solution is to export, paste the bottom of the db.sql into another
file, kill all of the new file
except the procedures, functions, views, sequences, grants,
and then drop the database, run the index builds and referential
constraint adds,
then run the second sql file and then update statistics.
I am also thinking about killing all lines in the "create table"
statements that declare a unique column
and letting a unique index be added by the second sql file. Is that a
bad idea?
Is there a better way to do this without dropping the database?
Time is not a problem since this database will reload in 20 minutes and
it is not running 24/7 .
I just don't like the idea of dropping a production database. That just
makes me nervous.

Get my utils2_ak package. My dbschema replacement utility, myschema, will
do most of the work for you. It will automatically generate a schema with
separate CREATE INDEX statements for all constraint indexes (even the
hidden
ones) and separate out all constraints to ALTER TABLE ADD CONSTRAINT...
statements executed after the appropriate CREATE INDEX statements. If you
pass two filenames on the commandline instead of just one, the second file
will contain all of the indexes, constraints, and privs statements while
the
first file contains primarily the create table statements. That will make
it easier to edit the second file and relocate the indexes and be able to
drop the indexes and constraints and just recreate them without extensive
tedious editing of the schema file.

Also, instead of running dostats you can have myschema generate an update
statistics script to duplicate the existing level of stats (though a fresh
dostats run tends to be more efficient to run).

You may also want to get the package utils4_ak which contains some sample
awk scripts that read dbschema/myschema output and generate scripts such
as
dropping all indexes, etc. Any I haven't provided will be easy to add to
the existing script set.

Utils2_ak also contains the dostats utility that David mentioned. Note
that
I've uploaded a new version of utils2_ak which should be available in the
next couple of days. Look for the README.1st file with the date March 20,
2007 in it.

Art S. Kagel
_______________________________________________
Informix-list mailing list
Informix-***@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list