Database Configuration Parameter LOCKTIMEOUT
Check your DB CFG ( db2 "get db cfg for DBNAME show detail" ) and find your value for LOCKTIMEOUT. If this is set to -1, the default value, you are flirting with disaster. -1 means that lock contention will never time out. Like a house of cards, your database can quickly come to a grinding halt as statements wait upon other statements - which are waiting upon other statements, and so on. Generally, your LOCKTIMEOUT value should be set to 30 seconds or less, with 10-15 seconds being ideal. If a statement can't get the lock resources it needs, and since it is likely holding locks while waiting to obtain unavailable locks, then it is desirable to have waiting statements timeout quickly so that lock contention does not exacerbate rapidly.
Database Configuration Parameter DLCHKTIME
This parameter specifies how often the DB2 deadlock detection mechanism examines current locks for the possibility of a deadly embrace. The default value is 10,000 ms, or 10 seconds. If LOCKTIMEOUT is set to 30 seconds, the DLCHKTIME 10000 is a good value. If you set LOCKTIMEOUT to 15 seconds, use DLCHKTIME of 5000. If you have a Data Warehouse database, LOCKTIMEOUT 120 and DLCHKTIME 60000 would be good choices. The higher DLCHKTIME will save a little CPU time by decreasing the frequency of deadlock checking.
Data Warehouse Databases and LOCKSIZE
If your data warehouse database is read-only, there is little point in having statements incur the CPU expense of acquiring row level locks as they traverse the data. You can save some CPU cycles by altering the database tables to LOCKSIZE table:
Avoiding LOCK problems
The secret to avoiding lock contention problems is to have an optimum physical design in place which supports the statement activity of the database with great speed and efficiency. When statements perform fast, the likelihood of lock contention is reduced. To have statements perform at maximum speed and efficiency, you need the right physical design in place.
Optimum Physical Design
Briefly, a high quality, optimized physical design includes the prudent use of high quality indexes, MDC tables, possibly MQTs, and appropriate clustering indexes. Indexes should not:
The Shameless Marketing Moment
I'd like your help and advice. I'm going to practice my IDUG presentation "Extreme Makeover: Optimal Physical Design Rehab - DB2 LUW Edition (E09)" via upcoming DBI Webinars and your participation and feedback would be appreciated (one lucky participant will be randomly selected to win an Amazon.com gift certificate). This presentation covers many physical design techniques in greater depth than can be covered in a blog post.
These Webinars are being held on:
Just for Fun
Since many of you might be traveling to Dallas Texas this May for IDUG North America, or if you travel on business or for pleasure, you really need to watch this video story on hotels: http://www.breitbart.tv/?p=7714
... you'll never drink from a hotel glass or coffee mug again!