Hello group.
Using db2 9.1 FP9 on windows …

A create index statement is creating an exclusive lock on a table.
The table is fairly big. It has about 890,000 rows. The index is
created in around 2 minutes, but this can cause a problem for the
applications in the system.

The behavior seems like a bug according to my reading of the help for
create index …

http://publib.boulder.ibm.com/infoce...2Fr0000919.htm

Specifically this part …
Notes
• Indexes over XML data do not support concurrent write access while
CREATE INDEX is executing.
• For relational indexes only: Concurrent read/write access to the
table is permitted while an index is being created. Once the index has
been built, changes that were made to the table during index creation
time are forward-fitted to the new index. Write access to the table is
then briefly blocked while index creation completes, after which the
new index becomes available.
To circumvent this default behavior, use the LOCK TABLE statement to
explicitly lock the table before issuing a CREATE INDEX statement.
(The table can be locked in either SHARE or EXCLUSIVE mode, depending
on whether read access is to be allowed.)

Here’s the information on the locks…

Lock Name = 0x05009900000000000000000054
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 3
Hold Count = 0
Lock Object Name = 153
Object Type = Table
Tablespace Name = UPGRADES
Table Schema = AU
Table Name = C
Mode = Z

Lock Name = 0x05009900000000000000000074
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 153
Object Type = Internal Table Alter Lock
Tablespace Name = UPGRADES
Table Schema = AU
Table Name = C
Mode = X