Altering a table and Long Transactions

Excuse me for my ugly English and thanks in advance. When I try to add a column in a table containing 80.000 records, my Informix Universal 9.2 server tells: "450: Long transaction aborted" My database logging mode is unbuffered, the logical log configuration is : LOGFILES 6 LOGSIZE 1500 I think that the only way to solve my problem is: create a new table containing old table structure plus my new fields SELECT old table INTO new table DROP old table RENAME the newly created table. Unfortunately, the old table has integral constraints that I have to drop and create in the new table. Right? Anyone have an alternative and quickest solution? Best Regards ----------------------------------------------------- Andrea Colapicchioni Advanced Computer Systems Space Division via L.Belli, 23 00044 Frascati Rome (Italy) e-mail a.colapicchioni@acsys.it -----------------------------------------------------

I would suggest adding more logfiles In article <8qadpc$r0k$1@fe1.cs.interbusiness.it>, "Gadget" <a.colapicchioni@odio.gli.spammers.acsys.it> wrote: > Excuse me for my ugly English and thanks in advance. > > When I try to add a column in a table containing 80.000 records, my Informix > Universal 9.2 server tells: > > "450: Long transaction aborted" > > My database logging mode is unbuffered, the logical log configuration is : > LOGFILES 6 > LOGSIZE 1500> > I think that the only way to solve my problem is: > > create a new table containing old table structure plus my new fields > SELECT old table INTO new table > DROP old table > RENAME the newly created table. > > Unfortunately, the old table has integral constraints that I have to drop > and create in the new table. > > Right? > > Anyone have an alternative and quickest solution? > > Best Regards > ----------------------------------------------------- > Andrea Colapicchioni > > Advanced Computer Systems > Space Division > > via L.Belli, 23 > 00044 Frascati > Rome (Italy) > > e-mail a.colapicchioni@acsys.it > ----------------------------------------------------- > > Sent via Deja.com http://www.deja.com/ Before you buy.

On Wed, 20 Sep 2000 15:30:19 +0200, "Gadget" <a.colapicchioni@odio.gli.spammers.acsys.it> wrote: >Excuse me for my ugly English and thanks in advance. > >When I try to add a column in a table containing 80.000 records, my Informix >Universal 9.2 server tells: > >"450: Long transaction aborted" > >My database logging mode is unbuffered, the logical log configuration is : >LOGFILES 6 >LOGSIZE 1500> >I think that the only way to solve my problem is: > >create a new table containing old table structure plus my new fields >SELECT old table INTO new table I think that you will get the same message (Long transaction) >DROP old table >RENAME the newly created table. > >Unfortunately, the old table has integral constraints that I have to drop >and create in the new table. > >Right? > >Anyone have an alternative and quickest solution? > You have at least two quick solutions: 1. add more and bigger LOGFILES or 2. change logging mode to no logging ontape -N database_name lock and alter table change loggin mode back to unbufferd & make Level 0 archive ontape -s -L 0 -U database_name You have to do this operation when there are no users Nebojsa

In article <8qadpc$r0k$1@fe1.cs.interbusiness.it>, "Gadget" <a.colapicchioni@odio.gli.spammers.acsys.it> wrote: > Excuse me for my ugly English and thanks in advance. > > When I try to add a column in a table containing 80.000 records, my > Informix Universal 9.2 server tells: > > "450: Long transaction aborted" > > My database logging mode is unbuffered, the logical log configuration > is : > LOGFILES 6 > LOGSIZE 1500> > I think that the only way to solve my problem is: > > create a new table containing old table structure plus my new fields > SELECT old table INTO new table > DROP old table > RENAME the newly created table. > > Unfortunately, the old table has integral constraints that I have to > drop and create in the new table. > > Right? Quite incorrect, Andrea. First, as you pointed out, you need to save all the constraints on this table. There may also be foreign key constraints on other tables that refernce this one. You want to go hunt them all down? Futhermore, the SELECT ... INSERT INTO ... will give you the same long transaction rollback as your ALTER TABLE gets. What do you think the ALTER TABLE does internally? > Anyone have an alternative and quickest solution? Quicker? No. But an alternative that will get you working: Yes. You need to have more log space. The wimpy 6 logs look like nobody has bothered (or dared) to change the defaults. Similarly wimpy: logs of 1500K; you are stuck with this size if you want to use onmonintor to add logs but with the onparams command you can specify the size of log you want to create. And I suppose your LOGSMAX parameter is also 6. I suppose all your logs are also in rootdbs also. Most DBA's move them into their own dbspace - like logdbs - early in the configuration. They also increase the configuration parameter: LOGSMAX 100 Do this first and bounce the engine. This will make the next step possible. I will impose my own prejudice by telling you to create a fir number of big logs. You don't have to fill logdbs but 10 logs of 100mb each should serve you nicely. Suppose you create logdbs with size 2000000K . $ onparams -a -d logdbs -s 100000 Do this 10 times. You will end up with 10 loga flagged with an A - newly Added. They are not usable yet. When you run a level-0 archive they will al be usable and you can do some pretty big stuff without worrying about long transaction rollback. Even an alter table on a table with millions of rows. I also recommend you set LBU_PRESERVE to 1 as an added precaution. Good luck. -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.

Jacob Salomon wrote: > > In article <8qadpc$r0k$1@fe1.cs.interbusiness.it>, > "Gadget" <a.colapicchioni@odio.gli.spammers.acsys.it> wrote: > > Excuse me for my ugly English and thanks in advance. > > > > When I try to add a column in a table containing 80.000 records, my > > Informix Universal 9.2 server tells: > > > > "450: Long transaction aborted" > > > > My database logging mode is unbuffered, the logical log configuration > > is : > > LOGFILES 6 > > LOGSIZE 1500> > > > I think that the only way to solve my problem is: > > > > create a new table containing old table structure plus my new fields > > SELECT old table INTO new table > > DROP old table > > RENAME the newly created table. > > > > Unfortunately, the old table has integral constraints that I have to > > drop and create in the new table. > > > > Right? > > Quite incorrect, Andrea. > > First, as you pointed out, you need to save all the constraints on this > table. There may also be foreign key constraints on other tables that > refernce this one. You want to go hunt them all down? Jake: I agree that this is not the right solution, however, note that myschema with the -F option (for a single table) will generate all the foreign key constraints referring to the offending table that will need to be recreated. FYI, FWIW. Art S. Kagel >

"Jacob Salomon" <jakesalomon@my-deja.com> ha scritto nel messaggio news:8qb20v$bca$1@nnrp1.deja.com... > In article <8qadpc$r0k$1@fe1.cs.interbusiness.it>, > "Gadget" <a.colapicchioni@odio.gli.spammers.acsys.it> wrote: > You need to have more log space. The wimpy 6 logs look like nobody has > bothered (or dared) to change the defaults. Similarly wimpy: logs of > 1500K; you are stuck with this size if you want to use onmonintor to > add logs but with the onparams command you can specify the size of log > you want to create. And I suppose your LOGSMAX parameter is also 6. Thank you for your precious mail. I solved my problem adding more logs. You'll have my never ending graditude. ----------------------------------------------------- Andrea Colapicchioni Advanced Computer Systems Space Division via L.Belli, 23 00044 Frascati Rome (Italy) e-mail a.colapicchioni@acsys.it -----------------------------------------------------