Posted on 6/12/2010
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 STSMWhen you watch this show, be on the look out for very important information on these registry variables: - DB2_SKIPINSERTED=ON
- DB2_EVALUNCOMMITTED=ON
- DB2_SKIPDELETED=ON
- 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 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 #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 NOTESExcerpts from Mike Winer's slides and notes (all the usual disclaimers and caveats apply): - DB2_SKIPINSERTED=ON
- Allows statements using CS or RS isolation levels to skip uncommittedinserted rows as if they had not yet been inserted
- This should be a registry variable used in just about every, if not every, DB2instance. 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 uncommittedinserted rows is the new DB2 default without this registry variable being used.
- DB2_EVALUNCOMMITTED=ON
- Allows statements using CS or RS isolation levels to defer or avoid lockinguntil a row/key is known to satisfy predicates
- Deleted rows are skipped unconditionally on table access, but deleted keysonly 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 theiruncommitted state, with an assume commit behavior. However, unlike UR whichwill process and/or return uncommitted data, the impact of this registry variable tostatements using CS/RS is for predicate evaluation only. It will skip uncommitteddeleted keys in an index, evaluate post-update values (vs. the committed preupdatedvalues). However – once a row is determined to qualify, locks may beacquired to ensure the CS/RS query does not process or return data based withuncommitted results.
- DB2_SKIPDELETED=ON
- Allows statements using CS or RS isolation levels to unconditionally skipdeleted rows and deleted keys
- Somewhat related to DB2_SKIPINSERTED, the DB2_SKIPDELETED registryvariable allows CS/RS queries to unconditionally skip rows which are markeddeleted. While this is also the case with the DB2_EVALUNCOMMITTED registryvariable, use of the DB2_SKIPDELETED registry variable also allows CS/RSqueries to unconditionally skip keys which are deleted in indexes. TheDB2_EVALUNCOMMITTED registry variable does not allow uncommitteddeleted keys in indexes to be skipped.
|