I don't have a solution for you, but I have a few suggestions:
1. You could get more information where you're currently getting 'Not
Collected' by turning on Monitor Switches.
For your current process: db2 UPDATE MONITOR SWITCHES....
then, db2 GET SNAPSHOT
or, for the entire instance: db2 UPDATE DBM CFG USING DFT_MON_LOCK ON
......
2. You don't have an EXCLUSIVE lock on the table, you have an IX lock,
which is INTENT Exclusive. This is normal when you have an exclusive ROW
lock.
3. Check that you've done: db2set DB2_RR_TO_RS=YES. This could decrease
locking - although mainly for Next-Key locks, which I'm not sure you're
experiencing.
Best Regards,
Raanon Reutlinger
IBM Certified IT/SW Specialist
Data Management Technical Sales Specialist, IBM Israel
Tel: +972-(0)3-918-8690; Fax: +972-(0)3-918-8840;
E-mail: [EMAIL PROTECTED]
http://www.ibm.com/il/software http://www.ibm.com/software/data/db2
scott m <[EMAIL PROTECTED]>@Lugwash.org on 18/12/2002 20:24:30
Please respond to scott m <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
To: "Henry J. Cobb" <[EMAIL PROTECTED]>
cc: [EMAIL PROTECTED]
Subject: Re: [DB2EUG] Lock Escalations -Snapshot
Yesterday, I was able to take two snapshots while
the tellers screens were experiencing very slow
response time. The snapshots show no lock escalations
taking place during the slowdown. The first snapshot
(for database,locks, and applications) was for 5-seconds,
and another one was taken after another 15 seconds. In
both snapshots one application has an exclusive
lock on a table, and nine other appls show lock-
wait status on that table. I have pasted some
of the snapshot info below and I have several
questions for the board.
If this application (handle 67 below) is in lock-wait status
why does time appl waited on locks and time UOW waited on
locks is both zero? Also the status change time on
all the applications in lock-wait is Locks is
12-16-2002 16:08 - almost 24 hours earlier. I don't
think it is likely that these applications have
been in lock-wait status for that long (since this is
an OLTP environment). What could be the reason that
the snapshot would show a status change time that
is not accurrate? Also, would creating an event monitor
for transactions help in a case like this (10-30 second
workstation response times)?
Snapshot time: Tue Dec 17 13:45:07 CST 2002
Snapshot of one (of 9) appl in lock-wait status:
Application handle = 62
Application ID =
*LOCAL.db2inst1.021122121907
Sequence number = 0001
Application name = d1s_
Authorization ID = DB2INST1
Application status = Lock-wait
Status change time = 12-16-2002
16:08:46.785765
Application code page = 819
Locks held = 2
Total wait time (ms) = 0
Subsection waiting for lock = 0
ID of agent holding lock = 203
Application ID holding lock =
822001CC.11C0.021217194123
Node lock wait occurred on = 0
Lock object type = Row
Lock mode = Exclusive Lock (X)
Lock mode requested = Update Lock (U)
Name of tablespace holding lock = USERSPACE1
Name of table holding lock = O1RCD
Lock wait start timestamp = Not Collected
Lock is a result of escalation = NO
Snapshot of application holding the X lock:
Application handle = 203
Application ID =
822001CC.11C0.021217194123
Sequence number = 0001
Application name = aspnet_wp.exe
Authorization ID = XPDB
Application status = UOW Waiting
Status change time =
Application code page = 819
Locks held = 5
Total wait time (ms) = 0
List Of Locks
Lock Object Name = 1136132352
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = DB2INST1
Table Name = O1RCD
Mode = X
Status = Granted
Lock Escalation = NO
Lock Object Name = 4360
Node number lock is held at = 0
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = DB2INST1
Table Name = O1RCD
Mode = IX
Status = Granted
Lock Escalation = NO
Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal P Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO
Lock Object Name = 5120
Node number lock is held at = 0
Object Type = Row
Tablespace Name = SYSCATSPACE
Table Schema = SYSIBM
Table Name = SYSPROCEDURES
Mode = NS
Status = Granted
Lock Escalation = NO
Lock Object Name = 39
Node number lock is held at = 0
Object Type = Table
Tablespace Name = SYSCATSPACE
Table Schema = SYSIBM
Table Name = SYSPROCEDURES
Mode = IS
Status = Granted
Lock Escalation = NO
--- "Henry J. Cobb" <[EMAIL PROTECTED]> wrote:
> > Background info: We are runnin DB2 EE v7 on an RS/6000 with AIX.
> The
> > DB server is used for banking transactions and the DB size is 20
> GB.
> > The problem we are experiencing is that multiple teller
> workstations
> > are hour glassing for about 20 seconds. This occurres
> sporadically,
> > sometimes 5 to 10 times a day. We are spending time trying to
> > isolate which transactions are being attempted when this occurrs.
>
> Are you running any real time reports on this database and if so
> can these
> be moved to a different machine where the data they need is
> replicated?
>
> > LOCKLIST is set at 200 and MAXLOCKS is set at 10.
>
> That locklist is fairly small, how much memory do you have to throw
> at this
> database?
>
> On the other hand it means that you are touching quite a few pages
> in a
> single transaction. Are there any read-only queries that you can
> shift to
> uncommitted reads so they don't set locks?
>
> Also, if it's just one program that's causing a lock escalation why
> not set
> table locks at the start of its run so it doesn't escalate?
>
> Do you actually get deadlocks and rollbacks or just escalations?
>
> -HJC
>
> -
> ::: When replying to the list, please use 'Reply-All' and make
> sure
> ::: a copy goes to the list ([EMAIL PROTECTED]).
> *** To unsubscribe, send 'unsubscribe' to
> [EMAIL PROTECTED]
> *** For more information, check http://www.db2eug.uni.cc
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-
::: When replying to the list, please use 'Reply-All' and make sure
::: a copy goes to the list ([EMAIL PROTECTED]).
*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
*** For more information, check http://www.db2eug.uni.cc
-
::: When replying to the list, please use 'Reply-All' and make sure
::: a copy goes to the list ([EMAIL PROTECTED]).
*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
*** For more information, check http://www.db2eug.uni.cc
