Subject RE: [ib-support] Adding Constraints
Author Helen Borrie
At 05:28 AM 23-08-02 -0700, you wrote:
>At 05:21 AM 23/08/2002, you wrote:
> >You do have to commit, though, before you can proceed to alter something you
> >just added
>commit? If you mean complete the transaction, then yes, that's when the
>error occurs if using WISQL's DSQL.

You have to commit the creation of the TABLES before you can ALTER them.

In answer to your other question - yes, you should have exclusive access
(perform a database shutdown) whenever you are changing metadata. This
doesn't mean just that the SYSDBA or the OWNER should have exclusive access
when changing metadata. The transaction in which the change is being
performed also has to be the only transaction active in the database.

Not all DDL commands will be rejected in cases where exclusive access is
not available. Stored procedure changes will be allowed but, if the old
version is in use, the change (recompilation) will be deferred until all
dependent objects are clear. That might or might not mean "next time the
database is shut down", since an old "stuck" transaction involving the
procedure or a dependent object could prevent it indefinitely.

Regardless of whether you *think* your changes in an active database are
happening or not, it's not sensible to just fire DDL at the database on the
fly and hope it will take. Remember that *everything* in the database
happens in transaction contexts and all of your changes are just
requests. The server makes the ultimate decision whether to allow or deny
the physical change.

Just make it a rule to *ensure* you have exclusive access and a clear road
for a single transaction and thus avoid complications you mightn't know how
to resolve. For info about database shutdown and preparing the database
for metadata changes, see the docs for gfix.

Use of scripts with AUTODDL on, instead of interactive DDL, will greatly
reduce the incidence of dependency errors when working with metadata.