Re: Stupid question of the century; ALTER TABLE while active

Alan Shore wrote:

Hi guys I've just been asked the following question by my boss's
boss Is there anyway to add new fields (columns) "on the fly" to
an existing file, without having an exclusive lock on that file. In other words, the file is still being used while the field is
added I know that chgpf is out of the question (unless someone
proves me wrong). I know that SQL is also out of the question
(ALTER TABLE x ADD EXTRAFIELD DEC(7, 2) NOT NULL DEFAULT 0) -
again unless someone proves me wrong If anyone knows of any way,
any language, any procedure, any function to achieve this, please
drop me a line.

Simple answer: No. Not an entirely stupid question for laypeople however. If columns were objects, then that capability available as a feature would be slightly more likely. As it is, the database could limit any changes to just one ALTER pending the apply of all changes from the old image, until every process holding an open was closed. It is however impractical because a process might keep a file open indefinitely. Additionally, the DB would essentially need to keep two copies of the table, or require a way for the database & languages to effect some type of /implicit close & reopen/ during runtime to effectively perform a live switch to the new\changed table; notify all processes at the start of the alter to coordinate across all current open holders [of not only that table, but all its relations as well], and delay all new openers pending completion of the alter. I do not see that ever happening, because the DB would want to be designed from its origins with that capability in mind; it was not.

It is all moot however, as the application would best resolve the concern itself, by being coded for HA to enable a live switch to an alternate copy of the DB at predefined points of processing; proactive and\or reactive response for the switch. Normally that would be a switch to another iASP or system [i.e. another database], but could be effected by use of another library as well. Often this is handled such that a server entry point is shut down for either all or new requesters, and then each is redirected to another server entry point which exposes the mirrored DB. If not coded in this manner, the application might have the same concern for any other forms of maintenance [e.g. saves, non-db changes] that are required while the application is active; i.e. irrespective of any ability of ALTER to run concurrent to the application. Without building the HA into the application, the problem of various maintenance activities interfering with the application is just a moving target; resolving each new activity with impacts, as they are encountered.

Regards, Chuck