In our 21st show, we were joined by special guest Mike Winer, IBM STSM, from the Toronto Lab. We ran a special 90 minute show so that Mike could cover his entire
presentation on Locking.
(and you need to pay attention to these! ), DB2 9.7 Currently Committed, SQL options for locking, lock avoidance (the new standard! ), lock escalation, timeouts, and deadlocks, and how to monitor locking!
In our 21st show, we were joined by special guest Mike Winer, IBM STSM, from the Toronto Lab. We ran a special 90 minute show so that Mike could cover his entire IDUG
presentation on Locking. This guy is the LOCK MASTER!
Mike covered types of DB2 LUW locks, lock memory, lock isolation levels, locking related registry variables
(and you need to pay attention to these! ), DB2 9.7 Currently Committed, SQL options for locking, lock avoidance (the new standard! ), lock escalation, timeouts, and deadlocks, and how to monitor locking! IT'S ALL HERE - READY FOR YOU TO WATCH! ...
To download a recorded replay of Episode #21, right click on the link below and choose "Save As..."
Episode 21, 2010 June 11, DB2 LUW LOCKING with Mike Winer, IBM STSM
When you watch this show, be on the look out for very important information on these registry variables:
- Please watch the show before implementing these!
We will include some of Mike's notes beneath our Shameless Marketing Moment, but this is no substitute for the bounty of information that Mike shared during this show!
Join The DB2Night Show™ LinkedIn Group
The DB2Night Show™ now has a LinkedIn Group. The group is an open group. Join to receive LinkedIn group updates including future shows and replay information, and, if you like, start some discussions about the show! Join The DB2Night Show LinkedIn Group
Our Shameless Marketing Moment
During DBI's quick show sponsor commercial break, we introduced you to a $2.2 Million UPDATE statement. Even though this statement ran in under a second for each execution, its CPU cost was enormous and it put the mission critical OLTP application at risk for locking problems. DBI's
href="../brother-panther.php">Brother-Panther® for DB2 LUW
makes it easy to analyze SQL workloads for any period of time and discover real root cause problematic SQL like no other tools can (thanks to Patented technology).
DBI's comprehensive performance and configuration repository with the industry's best trend graphs enable you to monitor and manage your databases with confidence, improve stability, and achieve scalability with existing hardware.
Our #1 Job is helping you look great!
Our #2 Job is giving you the documentation to prove it!
Contact DBI and find out how our DB2 LUW Performance Solutions can truly help you optimize performance and lower IT costs.
DB2 LOCK REGISTRY VARIABLE NOTES
Excerpts from Mike Winer's slides and notes (all the usual disclaimers and caveats apply):
Post from : http://www.dbisoftware.com/blog/db2nightshow.php
Printed from : http://www.dbisoftware.com/blog/db2nightshow.php?id=201
- Allows statements using CS or RS isolation levels to skip uncommitted
inserted rows as if they had not yet been inserted
- This should be a registry variable used in just about every, if not every, DB2
instance. It allows all CS/RS scans to bypass locks on uncommitted inserted rows.
As will be seen in a few slides for Currently Committed, skipping uncommitted
inserted rows is the new DB2 default without this registry variable being used.
- Allows statements using CS or RS isolation levels to defer or avoid locking
until a row/key is known to satisfy predicates
- Deleted rows are skipped unconditionally on table access, but deleted keys
only skipped when DB2_SKIPDELETED is used
- Similar to UR exhibiting the “assume commit” behavior,
DB2_EVALUNCOMMITTED allows CS and RS scans to evaluate rows in their
uncommitted state, with an assume commit behavior. However, unlike UR which
will process and/or return uncommitted data, the impact of this registry variable to
statements using CS/RS is for predicate evaluation only. It will skip uncommitted
deleted keys in an index, evaluate post-update values (vs. the committed preupdated
values). However – once a row is determined to qualify, locks may be
acquired to ensure the CS/RS query does not process or return data based with
- Allows statements using CS or RS isolation levels to unconditionally skip
deleted rows and deleted keys
- Somewhat related to DB2_SKIPINSERTED, the DB2_SKIPDELETED registry
variable allows CS/RS queries to unconditionally skip rows which are marked
deleted. While this is also the case with the DB2_EVALUNCOMMITTED registry
variable, use of the DB2_SKIPDELETED registry variable also allows CS/RS
queries to unconditionally skip keys which are deleted in indexes. The
DB2_EVALUNCOMMITTED registry variable does not allow uncommitted
deleted keys in indexes to be skipped.