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!
Join The DB2Night Show™ LinkedIn GroupThe 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 MomentDuring 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 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 #2 Job is giving you the documentation to prove it!
DB2 LOCK REGISTRY VARIABLE NOTESExcerpts from Mike Winer's slides and notes (all the usual disclaimers and caveats apply):
- 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 uncommitted results.
- 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.