Lock debugging by the ordinary user?

[Troels Arvin]

Hello,

Some users are frustrated by DB2 locking their database operations (most
recently: a table drop took forever, probably because of a lock).

The natural and sane question I'm being asked:
Is there a way for a user to see if a query is stuck waiting for a lock,
or if it's simply taking a while to execute?

As far as I can see, an ordinary user cannot get this kind of
information. Have I overlooked something?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/

[Knut Stolze]

Troels Arvin wrote:

Hello,

Some users are frustrated by DB2 locking their database operations (most
recently: a table drop took forever, probably because of a lock).

I'd say it is most probably because DROPPED TABLE recovery is turned on for
the tablespace in which the table resides. Then DB2 has to store the
table's data, which may take a while to copy it.

The natural and sane question I'm being asked:
Is there a way for a user to see if a query is stuck waiting for a lock,
or if it's simply taking a while to execute?

I believe that LIST APPLICATIONS (or the respective snapshot) will show a
status like "lock waiting". So you could make the snapshot functions
available to other users.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Troels Arvin]

On Tue, 24 Apr 2007 16:24:32 +0200, Knut Stolze wrote:

I'd say it is most probably because DROPPED TABLE recovery is turned on
for the tablespace in which the table resides.

True.

Then DB2 has to store
the table's data, which may take a while to copy it.

Ah. I didn't think of that side-effect of DROPPED TABLE. But there is no
alternative to activating DROPPED TABLE if I want to make it relatively
painless to restore dropped tables, right?

I believe that LIST APPLICATIONS (or the respective snapshot) will show
a status like "lock waiting".

But LIST APPLICATIONS yields an SQL1092N if I run the command as a normal
user.

So you could make the snapshot functions
available to other users.

I wonder if anyone in this newsgroup has experiences with this: Which
procedure(s) need to be made available for the users?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/

[Jan M. Nelken]

Troels Arvin wrote:

I wonder if anyone in this newsgroup has experiences with this: Which
procedure(s) need to be made available for the users?

Did you try db2pd -db <alias-locks
Jan M . Nelken

[Troels Arvin]

On Tue, 24 Apr 2007 16:24:32 +0200, Knut Stolze wrote:

>Some users are frustrated by DB2 locking their database operations
(most recently: a table drop took forever, probably because of a lock).

I'd say it is most probably because DROPPED TABLE recovery is turned on
for the tablespace in which the table resides. Then DB2 has to store
the table's data, which may take a while to copy it.

Why does DB2 have to copy the dropped table? - I would think that the
transaction logs (in combination with the latest backup) should have all
the data needed for re-construction of a dropped table?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/

[Knut Stolze]

Troels Arvin wrote:

On Tue, 24 Apr 2007 16:24:32 +0200, Knut Stolze wrote:

>Then DB2 has to store
the table's data, which may take a while to copy it.

Ah. I didn't think of that side-effect of DROPPED TABLE. But there is no
alternative to activating DROPPED TABLE if I want to make it relatively
painless to restore dropped tables, right?

Correct.

>I believe that LIST APPLICATIONS (or the respective snapshot) will show
a status like "lock waiting".

But LIST APPLICATIONS yields an SQL1092N if I run the command as a normal
user.

Right. That's exactly the issue here with "making this available" to
ordinary users. You can set the DB2_SNAPSHOT_NOAUTH registry variable to
turn off authorization checking.

In V9, you have a bunch of administrative views to gather snapshot
information. Those have their own, more fine-grained access control.

>So you could make the snapshot functions
available to other users.

I wonder if anyone in this newsgroup has experiences with this: Which
procedure(s) need to be made available for the users?

There are quite a few (all listed in the docs), for example this one:

http://publib.boulder.ibm.com/infoce...c/r0021987.htm

http://publib.boulder.ibm.com/infoce...n/c0002372.htm

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Knut Stolze]

Troels Arvin wrote:

On Tue, 24 Apr 2007 16:24:32 +0200, Knut Stolze wrote:

>>Some users are frustrated by DB2 locking their database operations
(most recently: a table drop took forever, probably because of a lock).

I'd say it is most probably because DROPPED TABLE recovery is turned on
for the tablespace in which the table resides. Then DB2 has to store
the table's data, which may take a while to copy it.

Why does DB2 have to copy the dropped table? - I would think that the
transaction logs (in combination with the latest backup) should have all
the data needed for re-construction of a dropped table?

I would think so, too. But I found in the past that DROPPED TABLE recovery
has the mentioned performance impact on a DROP TABLE statement. The manual
says this:
-------------------snip-----------------
When a DROP TABLE statement is run against a table whose table space is
enabled for dropped table recovery, an additional entry (identifying the
dropped table) is made in the log files. An entry is also made in the
recovery history file, containing information that can be used to recreate
the table.
-------------------snip-----------------
But writing a log records and something to the history file has no
measurable impact. So there must be something else going on under the
hood. What exactly, I do not know (yet).

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Ian]

Troels Arvin wrote:

On Tue, 24 Apr 2007 16:24:32 +0200, Knut Stolze wrote:

>>Some users are frustrated by DB2 locking their database operations
(most recently: a table drop took forever, probably because of a lock).

I'd say it is most probably because DROPPED TABLE recovery is turned on
for the tablespace in which the table resides. Then DB2 has to store
the table's data, which may take a while to copy it.

Why does DB2 have to copy the dropped table? - I would think that the
transaction logs (in combination with the latest backup) should have all
the data needed for re-construction of a dropped table?

It doesn't. Dropped table recovery works by writing a special
log record. During rollforward, DB2 will dump out a specified
table's data when it finds one of these log records before actually
dropping the table.

I would look for lock issues. Determining what is going on (and then
possibly educating the users on how to avoid these issues) will be
important.

One way you can give users a little more information (besides teaching
them how to parse snapshot output) is to set the default LOCKTIMEOUT
to a reasonable value. The default is to wait forever for a lock.
Decreasing it to, say, 30 seconds would result in users getting
SQL0911N rc68 errors instead of just waiting "forever" for a lock.
That way they know they are getting held by locks.