I've decided that this blog may serve as a good place to put commonly asked questions I see from customers. Hopefully, if you've reached this blog via a search - then I've managed to hit a topic that you are also interested in.
My first topic, covers a few registry variables in DB2 related to locking. Locking is often a topic of concern for many customers. Some, migrating from other DBMS platforms are just trying to understand the differences in platforms, others, are looking for solutions to help alleviate lock wait and or lock contention.
The first two parameters that were introduced to allow you to control the affects of how DB2 is managing concurrency were :
DB2_SKIPDELETED and
DB2_EVALUNCOMMITTED
These are commonly referred to as lock deferral parameters. If your application can tolerate evaluation of predicates against uncommitted data, then these parameters can improve application concurrency. Setting these parameters to YES or ON will tell DB2 to defer locks on uncommitted deletes and updates. There are some restrictions in using these variables, and, one of them is that they are only in affect if the isolation level is CS or RS (cursor stability or read stability).
So, how can you tell what isolation level your application is using ? It's using dynamic sql and you aren't sure ?
One way, and I think one of the easiest ways, is to use the db2pd tool.
db2pd with the -dynamic flag , will let you view both the sql statement currently executing and the ISO level.
Much like the previous two parameters, DB2_SKIPINSERTED was introduced in 8.2.2 (aka 8.1 fixpack 9). This parameter would tell DB2 to skip (not lock) uncommitted inserts for applications using ISO CS or RS. Additionally, in 8.2.2 the DB2_EVALUNCOMMITTED was extended to include plans with ISCAN fetching.
The setting to extend this is :
DB2_EVALUNCOMMITTED=YES_DEFERISCANFETCH
These locking parameters are set automatically in SAP for customers who deploy DB2 using the SAP DB2 Workload optimization. I think many other applications can benefit from these same settings because by default during row locking for DB2, DB2 locks all rows scanned. In DB2 V9 , setting EVALUNCOMMITTED to yes or on, will operate the same as YES_DEFERISCANFETCH.
You can find a detailed example of how this works in the redbook titled "SAP Solutions on IBM DB2 UDB V8.2.2 Handbook" , in chapter 8 page 438-439.
Here is the link to that redbook :
http://www.redbooks.ibm.com/abstracts/sg246765.html
If you haven't figured it out by now, I'm a big advocate of redbooks. They contain alot of good information and can often be referenced as a source to provide resolutions to inquiries.
So we stated that db2pd can be used to check the isolation level, but it also is a very handy tool to quickly check the status of lock waits on your database. Using db2pd with -locks wait , for example:
db2pd -db sample -locks wait
will show you each of the locks in a wait status and their associated waiter. This parameter of db2pd is available with V8 FP9 and beyond.
Another feature to help in diagnosing and debugging locks, is the lock timeout report. The lock timeout report was introduced in DB2 V9.5 but is being ported back to 9.1FP4 and 8.2FP16.
This feature is enabled by setting another registry variable DB2_CAPTURE_LOCKTIMEOUT. The lock timeout report is generated by the agent receiving the lock timeout error.
