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

Guys,

This is not a stupid question. Quite frankly this facility was first
implement on the mainframe by a company called Applied Data Research (later
acquired by Computer Associates) in their Datacom/DB relational database.
You could process an ALTER TABLE and the system would implement it over time
without requiring that you grant exclusive locks to the program issuing the
ALTER TABLE command. Note, this was however an expensive and complex
internal process as you can imagine.

On Sat, May 9, 2009 at 1:51 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.