"Jeremy Rickard" <jrickard@unisystems.biz> wrote in message
news:d36116ef.0402101045.7c34d754@posting.google.c om...
> Looking for opinions on a problem we recently experienced when a
> long-running alter table to add a primary key *appeared* to be the
> cause of application lock timeouts due to catalog locks.
> Unfortunately diagnostic information is very sparse, but I'm trying to
> at least understand whether/how this might happen in theory.
>
> The basic known facts:
>
> * DB2 UDB v7.2 for Solaris
>
> * The table being altered is never accessed by the application (and
> table has its own tablespaces for that matter).
>
> * The application is built around J2EE architecture. It does not read
> the catalog tables. Its JDBC use is limited to standard DML
> operations plus stored procedure calls.
>
> So I'm wondering whether, under some circumstances, DB2's own internal
> catalog checking for semantic/authorisation purposes can get locked
> out? I would have thought the answer is that there's absolutely no
> way that should happen, but what do others think? Or are there any
> bugs relating to this?
>
>
> Jeremy Rickard

When you create a primary key, DB2 creates a unique index for the key unless
a unique index already exists on those columns.

The index may be created in the same tablespace as the table unless a
different index is specified for indexes (when the was table created). Even
if the index is created in a different tablespace, it will be created in the
same index as other indexes for that table.

Of course, just creating the index might take some time, aside from locking
considerations.