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
.

Relevant Pages

  • Re: Change field collation
    ... constraints and statistics on the columns before you can alter ... There are quite a lot of caveats and you use this script at your own risk. ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.programming)
  • Re: Changing collation -- URGENT
    ... You can use the following script to change the collations of your columns, ... duplicate primary key constraints create statements, ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.server)
  • Re: moving indexes
    ... This includes primary keys, foreign keys, and unique constraints. ... But dropping them all first is a bit hairy because of the way an index gets renamed when constraints ... and letting a unique index be added by the second sql file. ... Is there a better way to do this without dropping the database? ...
    (comp.databases.informix)
  • Re: moving indexes
    ... I can easily write the code to generate all the "add constraints" and "create unique index" statements. ... and letting a unique index be added by the second sql file. ... Is there a better way to do this without dropping the database? ... instead of running dostats you can have myschema generate an update statistics script to duplicate the existing level of stats. ...
    (comp.databases.informix)
  • Re: copy table to another database
    ... However, this will not recreate indexes, constraints, and so forth. ... Script Table as Create. ... use that script to create the table in your new database. ...
    (microsoft.public.sqlserver.msde)
Loading