Re: moving indexes

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: garage_dba@xxxxxxxxxxx
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 <garage_dba@xxxxxxxxxxx>
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-list@xxxxxxxx
Cc: informix-list@xxxxxxxx
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@xxxxxxxx
http://www.iiug.org/mailman/listinfo/informix-list

.

Relevant Pages

  • Re: Foreign superkey support
    ... I don't think of constraints as being part of the algebra per se. ... These constraints are on a database variable, ... something like 5-10 foreign keys for the cost of a single domestic key. ...
    (comp.databases.theory)
  • Re: Foreign superkey support
    ... I don't think of constraints as being part of the algebra per se. ... database, and while their enforcement during DML operations is critical, I ... Frankly I think there's too much emphasis on foreign keys. ...
    (comp.databases.theory)
  • Re: Statistics during a complex load
    ... drop all constraints (eg. foreign keys) and indexes ... before the load, and, if necessary, create them afterwards. ... As for partitioning - look at interval partitions ... As for the previous reply to drop constraints etc, ...
    (comp.databases.oracle.server)
  • Re: Adding a column in a certain position
    ... You would need to redefine all your constraints and indexes on the new table, but that is frequently more efficient that moving huge amounts of data. ... is that of foreign keys. ...
    (microsoft.public.sqlserver.server)
  • Re: Add columns to table, but in an order
    ... in the order that SQL Server mentions them, with no way for the end user ... existing constraints and recreate them when the new table is in place. ... be very critical of the script ... Make sure to test the conversion on a test server first. ...
    (microsoft.public.sqlserver.server)