[kumarsarwesh]

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."
SIX Locks
In a previous column ("Table Spaces and Locking Levels, Part 1," Issue 1, 2005; see Resources, page 51), I explained DB2 locking using an analogy that equated buildings to table spaces, floors to tables, and rooms to pages. You must first get into the building, then onto the right floor, and then, finally, you may enter the room. Likewise, with page-level locking, you can't immediately get into a page. You must first get into the table space (our building), then into the table (the right floor), and then into the page (the room). A typical updater would first ask for a table space lock, and then for a table lock, and finally for a page lock. The outer (or higher-level) locks are called "Intent Locks." You intend to do page-level locking if you ever get into the page. For example, you may have to acquire an IX lock on the table space (you Intend to do something eXclusive), followed by an IX lock on the table, before you even think about your X (exclusive) lock on a page.

Usually you acquire IX locks on table spaces and tables for users doing maintenance, and IS (Intend to Share) locks for those who are just reading. However, every now and then we see SIX locks at these levels. What is this type of lock and what does it mean?
I'll Let You Read But Not Update

Suppose the table space that holds our table is defined with LOCKSIZE PAGE. I have a program that must run during a window when no one else is doing maintenance. I want my program to be the only one that is doing INSERTs, UPDATEs, DELETEs, and MERGEs. I don't mind if other programs are reading. SELECTs and read-only CURSORs are just fine. How do I enforce this scenario?

What I plan to do is this: I will override the table space page-level locking rule with my own lock rule. In my program's initialization section, I will execute a statement to tell DB2 to LOCK TABLE mytablename IN SHARE MODE. This statement will cause DB2 to acquire an IS lock on the table space (the building) and an S lock on the table (the floor). The S lock on the table will allow other users to read. In fact, my S lock will be quite happy to share with two types of users, those who are just reading using the three-level IS/IS/S protocol, as well as those who issue the LOCK TABLE ... IN SHARE MODE statement. I am happy with the former but not so happy with the latter. But hang in there and let's see if we can solve the problem.