Re: Altering large table

En Nebojsa Sevo va escriure el dia 16 Jan 2001, a les 9:11: > 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 To change the logg mode of your db you need to make a level 0 backup. 1.- Change the backup device name to /dev/null and do: ontape -s -L 0 -N your_data_base_name2.- Afther this alter the table 3.- do: ontape -s -L 0 -U your_database_device to restore de logg mode (U=Unbuffered, B=Buffered) 4.- Restore previous device backup name. I hope this help you. --------------------------------------- Isidre PONS ROCA BASE - Gesti' d'Ingressos Locals (Diputacio de Tarragona) Servei de Sistemes d'Informacio Av President Lluis Companys 12-C 43005 - Tarragona SPAIN Tel # +34 977 236731 Fax # +34 977 227302 http://www.altanet.org ipons@dtgna.altanet.org ---------------------------------------

On Tue, 16 Jan 2001 13:37:13 +0100, "Isidre PONS ROCA" <ipons@dtgna.altanet.org> wrote: > >En Nebojsa Sevo va escriure el dia 16 Jan 2001, a les 9:11: > >> 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 > >To change the logg mode of your db you need to make a level 0 backup. >1.- Change the backup device name to /dev/null and do: > ontape -s -L 0 -N your_data_base_name>2.- Afther this alter the table >3.- do: ontape -s -L 0 -U your_database_device to restore de logg mode (U=Unbuffered, B=Buffered) >4.- Restore previous device backup name. > >I hope this help you. I know that. I was asking about changing Informix paramters (in onconfig file). Thanks.

In article <941hgq$id3$1@news.xmission.com>, Isidre PONS ROCA <ipons@dtgna.altanet.org> writes > >En Nebojsa Sevo va escriure el dia 16 Jan 2001, a les 9:11: > >> 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. >> Set LRU_MAX_DIRTY and LRU_MIN_DIRTY to high values, say 80 and 90. You want most I/O to be checkpoints since this is the most efficient. Also for the index builds you want PDQPRIORITY 100 PSORT_NPROCS=2*number of CPUs >> Does anybody have advice which Informix parametars should I change to speed up >> the process? >> >> Thanks >> >> Nebojsa > >To change the logg mode of your db you need to make a level 0 backup. >1.- Change the backup device name to /dev/null and do: > ontape -s -L 0 -N your_data_base_name>2.- Afther this alter the table >3.- do: ontape -s -L 0 -U your_database_device to restore de logg mode >(U=Unbuffered, B=Buffered) >4.- Restore previous device backup name. > >I hope this help you. > > >--------------------------------------- > Isidre PONS ROCA > BASE - Gestió d'Ingressos Locals > (Diputacio de Tarragona) > Servei de Sistemes d'Informacio > Av President Lluis Companys 12-C > 43005 - Tarragona > SPAIN > Tel # +34 977 236731 > Fax # +34 977 227302 > http://www.altanet.org > ipons@dtgna.altanet.org >--------------------------------------- -- David Williams

Isidre PONS ROCA wrote in message <941hgq$id3$1@news.xmission.com>... > >En Nebojsa Sevo va escriure el dia 16 Jan 2001, a les 9:11: > >To change the logg mode of your db you need to make a level 0 backup. >1.- Change the backup device name to /dev/null and do: > ontape -s -L 0 -N your_data_base_name This needs clarification - you can change a database from logged to unlogged at any time irrespective of taking an actual archive (except $%&!*##! ANSI databases don't seem to be allowed to be un-ANSI'd). Changing from non-logged to logged requires an archive because if you were to restore from an archive and roll forward the logs, there would be a whole heap of missing changes between the time of the archive and the logs that start to come thru for the rollforward, and thus it would be impossible to roll forward the database if logging was suddenly turned on some time after the archive. Note as a corollary: any unlogged databases will only be in the state they were at the time of the archive, in the event of a physical restore. That's kind of a no-brainer statement I suppose. ontape seems to accept: ontape -N databasename and there's also a cute little utility ondblog (buf | unbuf | nolog | ansi | cancel) [ -f dbfile | dbname ' ] ie ondblog nolog databasename ondblog can be used at any time; if you make a change that requires an archive to occur then it marks the database and waits for an actual archive, so it's basically more convenient to use ondblog. Thanks to the guys who mentioned this utility a few weeks ago in this very newsgroup.