RE: alter table in-place algorithm
Mohit Anchlia wrote: > [the table] has Integer, Varchar, char and serial number, smallint. I > am trying to modify column which are varchar. I read on ibm site that > informix doesn't use in-place algorithm if it's modification to > varchar columns. Correct, varchar causes a double whammy. Quote 1: "When you use ALTER TABLE to modify the original size or reserve specifications of VARCHAR or NVARCHAR columns, the database server performs these changes as slow alters, rather than using the in-place alter mechanism." Quote 2: "When a table contains a user-defined data type, a VARCHAR data type, or smart large objects, the database server does not use the in-place alter algorithm even when the column being altered contains a built-in data type." > Is there any way to explicitly tell informix to use in-place > algorithm ? What part of "the database server performs these changes as slow alters" don't you understand? There is no way to tell the server which alter algorithm to use. It chooses the algorithm based on explicitly documented parameters. Your table requires a slow alter. > I've already run out of logs on this table ..It would be good if I > can use this algorithm But you can't. If you alter this table, it'll be a slow alter. Several workarounds have been suggested for performing a slow alter (or an equivalent sequence of steps) without running into "long transaction aborted" problems. Use one of those workarounds. -- Carsten Haese http://informixdb.sourceforge.net
On Jul 29, 7:12 am, Carsten Haese <cars...@uniqsys.com> wrote: > Mohit Anchlia wrote: > > [the table] has Integer, Varchar, char and serial number, smallint. I > > am trying to modify column which are varchar. I read on ibm site that > > informix doesn't use in-place algorithm if it's modification to > > varchar columns. > > Correct, varchar causes a double whammy. > > Quote 1: "When you useALTERTABLE to modify the original size or > reserve specifications of VARCHAR or NVARCHAR columns, the database > server performs these changes as slow alters, rather than using the > in-placealtermechanism." > > Quote 2: "When a table contains a user-defined data type, a VARCHAR data > type, or smart large objects, the database server does not use the > in-placealteralgorithm even when the column being altered contains a > built-in data type." > > > Is there any way to explicitly tell informix to use in-place > > algorithm ? > > What part of "the database server performs these changes as slow alters" > don't you understand? > > There is no way to tell the server whichalteralgorithm to use. It > chooses the algorithm based on explicitly documented parameters. Your > table requires a slowalter. > > > I've already run out of logs on this table ..It would be good if I > > can use this algorithm > > But you can't. If youalterthis table, it'll be a slowalter. Several > workarounds have been suggested for performing a slowalter(or an > equivalent sequence of steps) without running into "long transaction > aborted" problems. Use one of those workarounds. > > -- > Carsten Haesehttp://informixdb.sourceforge.net Thanks, workarounds I know are: 1. Turn logging off 2. Increase the size of logical logs I also wanted to check if locking table in exclusive mode helps ? If yes, then how ?
On Sun, 29 Jul 2007 21:32:32 -0700, mohitanchlia wrote > Thanks, workarounds I know are: > 1. Turn logging off > 2. Increase the size of logical logs Did you even *read* my alternative suggestions to your other thread called "Long Transaction on alter table"? See http://groups.google.com/group/comp.databases.informix/browse_thread/thread/8939b95f7ceb146a > I also wanted to check if locking table in exclusive mode helps ? No, it won't help. A table alteration needs log space because the engine needs to keep track of the changes it has to make to every row. Locking the table in exclusive mode won't change that. Changing the table to a raw table might help (I've never tried), but I don't know if that's an option because you never deem it necessary to tell us what version of engine you're using. Turning off logging on the entire database would help, but I don't know if you can afford to do this. -- Carsten Haese http://informixdb.sourceforge.net
On Jul 30, 2:08 am, "Carsten Haese" <cars...@uniqsys.com> wrote: > On Sun, 29 Jul 2007 21:32:32 -0700,mohitanchliawrote > > > Thanks, workarounds I know are: > > 1. Turn logging off > > 2. Increase the size of logical logs > > Did you even *read* my alternative suggestions to your other thread called > "Long Transaction onaltertable"? Seehttp://groups.google.com/group/comp.databases.informix/browse_thread/... > > > I also wanted to check if locking table in exclusive mode helps ? > > No, it won't help. A table alteration needs log space because the engine needs > to keep track of the changes it has to make to every row. Locking the table in > exclusive mode won't change that. > > Changing the table to a raw table might help (I've never tried), but I don't > know if that's an option because you never deem it necessary to tell us what > version of engine you're using. > > Turning off logging on the entire database would help, but I don't know if you > can afford to do this. > > -- > Carsten Haesehttp://informixdb.sourceforge.net Sorry for not giving you the version of IDS, I thought my questions is very general. We are using IDS 10. And yes, I did read your thread, I just wanted to know yours and others views of more options that I had in my mind. Thanks for your suggestion.
On Mon, 2007-07-30 at 08:31 -0700, mohitanchlia@gmail.com wrote: > Sorry for not giving you the version of IDS, I thought my questions is > very general. Yes, but the universe of possible answers gets larger for more recent engine versions. When asking for help on a product, as a rule of thumb, *always* mention the version of that product. > We are using IDS 10. That means that you could try turning the table in question into a raw table. Note that I'm not sure whether that's going to help for an ALTER TABLE, but it's worth a try. HTH, -- Carsten Haese http://informixdb.sourceforge.net