Programmers Only: The Mystery of the DB2 SIX Lock

(Bonnie Baker) The SIX lock is one of DB2's many mysteries. What is it, and how do we acquire it? Another mystery is the index_only flag on the plan_table: How can it be a Y for an UPDATE or a DELETE? Excuse me, but exactly how can an UPDATE or DELETE be "index-only?" A third mystery is the fact that adding an ORDER BY to an SQL statement might eliminate a sort. And even more mysterious and perverse is the fact that removing an ORDER BY clause can actually introduce a sort into your access path. For the next few columns I will be writing about these mysteries and trying to solve them for you. So, for all of you Nancy Drew fans, let's start with "The Mystery of the SIX Lock."