Altering large table

I have to alter one of my tables. I will change logging mode of database to NO LOGGING so nobody can work on system. Because of that I have to do that in less time as possible. Does anybody have advice which Informix parametars should I change to speed up the process? Thanks Nebojsa

Nebojsa Sevo <mips@zg.tel.hr> wrote in message news:d6086ts63igea6da5eltagqcgl84essbu0@4ax.com... > I have to alter one of my tables. I will change logging mode of database to NO > LOGGING so nobody can work on system. Because of that I have to do that in less > time as possible. > > Does anybody have advice which Informix parametars should I change to speed up > the process? > > Thanks > > Nebojsa You can use High Performance loader. I have good experience with it. You don't have to change logging mode of database. Karmen

Nebojsa Sevo wrote in message ... >I have to alter one of my tables. I will change logging mode of database to NO >LOGGING so nobody can work on system. Because of that I have to do that in less >time as possible. > >Does anybody have advice which Informix parametars should I change to speed up >the process? > If you are using an engine higher than approx 7.30 (even the 7.20 have most of the feature I'm about to describe) then there is a good chance that you won't have any real work performed in the engine thru changing tables. The engine can do three different kinds of work to make the alter table: Slow Alter, In-place Alter, Fast Alter Slow alter is the traditional one, where it completely rewrites the rows. In-place alter is used for many operations - such as adding a column (and a lot more reasons than that) Basically, if it can perform the changes to the row "on the fly" then it doesn't rewrite the table. It just keeps a copy of all the table definitions thoughout the history of the table and interprets the rows according to it's version number. This does NOT have any impact upon table access performance, however the book says that if you have more than about 50 inplace alters stacked up, then subsequent inplace alters start to become slow. If an old-shaped row is updated, the engine will rewrite it back with the new shape at that time. If a slow-alter is ever performed on the table, then the history of table versions is cleared. The Fast alter happens when you do something like changing an attribute of the columns (such as NOT NULL) in a way that does not actually alter the data itself. In that case, it merely changes the recorded attributes without having to rewrite rows or even keep a new table version. See page 4-40 of the Informix Performance Guide for the 7.31 engine. If you have a different version manual, the page numbers may have changed slightly - the subject header to look for is "Altering a Table Definition" in the "Changing Tables" section of the "Table and Index Performance Considerations" chapter. If you understand this area of the manual, then you will be able to predict exactly what kind of alter will occur for each alter command you issue. Note that if you change a column that's in an index, it may cause that index to be rewritten. Also, for some strange reason, date, interval and datetime columns cannot be changed with an in-place alter, but hopefully later versions may see this improvement made.

In some informix versions, you have an environment variable EXCLUSIVE (or DBEXCLUSIVE?) that you can set to open a database in exclusive mode (if my memory is right !) - then : dbaccess Suggestion: make sur you have enough space left in your dbspace. Ex: if you have a 100 Mo table, you need at least an extra 100 Mo free space in your dbspace Otherwise you can play with the grant access. For instance: "revoke connect from user (or public might be enough depending of you security policy)" What i did a few times (something like that): 1) unload to xxx.unl select * from sysusers where login <> 'informix' 2) delete from sysusers where login <> 'informix' 3) do my work 4) load from xxx.unl insert into sysusers Jean-Gabriel Paris-France http://perso.club-internet.fr/jgdaprem (This information is given as) "Nebojsa Sevo" <mips@zg.tel.hr> a 'crit dans le message news: d6086ts63igea6da5eltagqcgl84essbu0@4ax.com... > I have to alter one of my tables. I will change logging mode of database to NO > LOGGING so nobody can work on system. Because of that I have to do that in less > time as possible. > > Does anybody have advice which Informix parametars should I change to speed up > the process? > > Thanks > > Nebojsa

Thanks to all, but I allready know almost everything about changing logging status and Alter-In-Place. My question was how to minimize down time of DB and the best answer was Karmen's. 1. I will use HPL to unload old table, 2. rename it to my_table_old, 3. create my_table with new schema, 4. use HPL's express mode to load data 5. do level 0 archive to make my_table read-write, 6. drop my_table_old HPL is very fast and users can do things that don't need my_table. Thanks Karmen

Why are you going through all of that when the engine will happily ALTER the table in place in no time at all with a total downtime of about 3 seconds without unloading and reloading the data at all! Art S. Kagel Nebojsa Sevo wrote: > > Thanks to all, but I allready know almost everything about changing logging > status and Alter-In-Place. > > My question was how to minimize down time of DB and the best answer was > Karmen's. > 1. I will use HPL to unload old table, > 2. rename it to my_table_old, > 3. create my_table with new schema, > 4. use HPL's express mode to load data > 5. do level 0 archive to make my_table read-write, > 6. drop my_table_old > > HPL is very fast and users can do things that don't need my_table. > > Thanks Karmen