Re: How do add primary key contraint on existing table?

In article <33887f78.23291891@gate.idg.no>, Nils.Myklebust@idg.no wrote: > > David Williams <djw@smooth1.demon.co.uk> wrote: > > :In article <3385DA3A.179B@ccm2.hf.intel.com>, Jorge Torralba <jorgex_tor > :ralba@ccm2.hf.intel.com> writes > :>> :but I get an ISAM error message that says it has run out of disk space. > :>> : > :> > :> > :>Keep in mind, if you alter a table Informix makes a copy of that table > :>before making changes to it. If your table is very big, you just might > :>not have enough room for the copy. > :> > : Correct - the copy gets created in the root dbspace by default. > No, copy of table which is under alter procedure is created in the same dbspace as original table. But this is not this case. Adding primary key constraint does not copy new table. Nils is right - he says it on following rows: > Are you realy sure of this David. Remember all he is doing is: > ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY (colname) > CONSTRAINT tablename_primary;> > In my book this doesn't create a copy of the table. It simply adds the > constraint to the existing table. I may be very wrong though as I > haven't ever realy checked to see what is happening. It sure shouldn't > have to create a copy of the table, so I would call it a defect if it > does. > > Even if it doesn't create a copy of the table it does create a new > unique index and this may be the reason why he runs out of space. > That's why I suggested that he created the index first (see earlier > post in this thread). > Create index statement always needs space for sorting. It should bereason for running out of space. Check DBSPACETEMP environment variables following David`s instructions. Check number of free pages in temporary dbspaces and dbspaces you are using. But: I would recommend to use High Performance Loader for unloading and loading large amount of data (I am not sure, but I think you are on 7.2x). You can load from several devices (tapes, files, UNIX pipes) in parallel. You can also disable indexes, constraint checking and!! disable logging of loaded data depending on mode (express or deluxe) you are using. HPL has many other features. Read documentation about HPL. This tool seems to be very good. If you don`t want to use HPL, you can disable indexes, constraints and triggers or set them to filtering mode with SQL command (beginning with 7.10 UD1). I hope this helps. Miroslav Lorenc SSA Czech Republic -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet