Re: alter table

Sahrul Hidayat wrote: > > Hi, > > I need an advise, i try to alter a table,to put one field, but appear msg > LONG TRANSACTION, and the alter aborted, this table has 252855 rows. > This instance configuration has LOCKS = 30000. The problem is not locks, an ALTER TABLE statement only takes one lock. The problem is that you have used up enough log space to trigger long transaction handling. This is controlled by the combination of the number and size of log files and the CONFIG parameter LTXHWM which is the percent of logspace a transaction is permitted to span before being declared a LONG transaction and rolled back which uses more log space and may pass LTXEHWM causing all other transactions to stop until the rollback is completed. You have several options: 1) If you have what should be enough log space, and normally do not have long transaction problems you could increase the LTXHWM and LTXEHWM values and restart the engine so that more of the logs will be available. This can be dangerous because the engine will be hung if all of the logfiles fill. The max values would be 80% and 90%, respectively, and ONLY if you have lots of logical log space. 2) Increase the number or size of the logical logs and perform a level zero backup to enable the new log files. (If the value of LOGSMAX does not allow more log files to be added you will have to increase LOGSMAX and shutdown and restart the engine first.) 3) Create a new table with the new schema, lock the table and INSERT INTO newtable SELECT *, <default for new column> FROM oldtable WHERE ... several times with different WHERE clauses to limit the number of rows written in a single transaction so that the logs do not fill. Remember that DBACCESS does a BEGIN WORK in menu mode so you will have to COMMIT WORK and BEGIN WORK after each INSERT to end each transaction. 4) dbunload the original table, drop the original table, create the new table, dbload the data back into the new table giving dbload the -n <count> option to force commits every <count> rows inserted. Art S. Kagel