I don't know why it says "not collected" for 'Lock Wait Start Timestamp'. The DB2 System Monitor guide says this item is controlled by the LOCK monitor switch (which was on).
I have checked and DB2_RR_TO_RS has not been turned on. This is an idea, but how can I check to see if next-row locking is causing a problem? I haven't seen any NX lock modes in any of the snapshots. I have determined several things: The table in questions is heavily used, touched by almost all applications. Also, the table is updated via two stored procedures. If the stored procs are using CS isolation level would setting DB2_RR_TO_RS to yes have much effect? I believe the stored procs need to be looked at closely. It seems that the DB2 Explain tool could be helpful in this case. Has anyone used the Explain tool? Is it likely to be helpful in this situation? Thanks again. Scott --- Raanon Reutlinger <[EMAIL PROTECTED]> wrote: > Subject: Re: [DB2EUG] Lock Escalations -Snapshot > To: scott m <[EMAIL PROTECTED]> > CC: [EMAIL PROTECTED] > From: "Raanon Reutlinger" <[EMAIL PROTECTED]> > Date: Thu, 19 Dec 2002 14:48:20 +0200 > > 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 __________________________________________________ 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
