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:

• 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 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):

• DB2_SKIPINSERTED=ON
  

  
  

  • 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.
    
  
  

  
  

• DB2_EVALUNCOMMITTED=ON
  

  
  

  • 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.
    
  
  

  
  

• DB2_SKIPDELETED=ON
  

  
  

  • 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.
    
  
  

Post from : http://www.dbisoftware.com/blog/db2nightshow.php
Printed from : http://www.dbisoftware.com/blog/db2nightshow.php?id=201