"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?
====
This is not a bug.
When you create the user tables, there will be one row in the
SYSIBM.SYSTABLES to describe each user table. When you try to drop a table,
db2 will Row(X) lock the row in SYSIBM.SYSTABLES which desribe the table you
want to drop, and also row (X) lock the row whcih describe the table
corresponding the next key of index (full table name). This will prevent
another session try to create a table with the same table name but your
working session try to drop.

When you add a primary key to a user table, db2 will Z lock the whole user
table, and X-row lock the SYSIBM.SYSTABLES.
Let's say you have four user tables: tbl1, tbl2, tbl3, tbl4
DB2 internal build an index on (creator+tabname):
index key will include (tbl1, xxx), (tbl2, xxx), (tbl3, xxx), (tbl4,xxx) ...
When you alter table tbl3 to add the primary key, if the other session try
to drop table tbl2, this session will get LOCKTIMEOUT. -