Re: moving indexes

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-list@iiug.org Cc: informix-list@iiug.org Sent: Monday, April 30, 2007 11:06 AM Subject: Re: moving indexes Bill64bits wrote: > 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-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list