Parameter Wednesday: DB2 Registry DB2_EVALUNCOMMITTED

DB2 Version This Was Written For

9.7

Parameter Name

DB2_EVALUNCOMMITTED

Where This Parameter Lives

DB2 Registry (db2set)

How To Check Value

> db2set -all |grep DB2_EVALUNCOMMITTED
[i] DB2_EVALUNCOMMITTED=YES [DB2_WORKLOAD]

OR

> db2 "select substr(reg_var_name,1,32) name, substr(reg_var_value,1,16) value, level, is_aggregate, substr(aggregate_name,1,32) aggregate_name from SYSIBMADM.reg_variables where reg_var_name='DB2_EVALUNCOMMITTED' with ur"

NAME                             VALUE            LEVEL IS_AGGREGATE AGGREGATE_NAME
-------------------------------- ---------------- ----- ------------ --------------------------------
DB2_EVALUNCOMMITTED              YES              I                0 DB2_WORKLOAD

Description

In some situations where there are uncommitted updates to a row, this parameter defers the acquisition of a lock on a row for CS or RS isolation levels until the row is know to satisfy the predicates of the query. This can improve concurrency.

The value of the parameter at bind time is used if the values are different at bind time and run time.

There are only very specific scenarios where this parameter applies, which are laid out in detail in the Info Center. http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0011218.html. In general, this parameter only helps if CS or RS isolation levels are used and predicates in question are SARGable.

This parameter is ignored when currently committed semantics are used (CUR_COMMIT=ON).

Impact

Can reduce locking/deadlocking and increase concurrency if applications support the data integrity changes. Potentially dangerous if the application does not explicitly support this behavior.

Default

NO/OFF

Range/Values

ON, OFF

Recycle Required To Take Effect?

Yes

Can It Be Set To AUTOMATIC?

No, there is no AUTOMATIC option for this parameter.

How To Change It

db2set DB2_EVALUNCOMMITTED=YES

Rule of Thumb

If your application does not explicitly support it, do not set this parameter – leave it at the default of NO.

Tuning Considerations

The main consideration here is if your application supports this value. If your application supports this behavior, you should set this parameter.

Along with DB2_SKIPINSERTED and DB2_SKIPDELETED, you can drastically reduce deadlocking.

Related Error Messages

 

War Stories From The Real World

WebSphere Commerce has supported DB2_SKIPINSERTED, DB2_SKIPDELETED, and DB2_EVALUNCOMMITTED for years. They’re now part of the aggregate DB2 Registry setting DB2_WORKLOAD=WC, but we were setting them independently long before that. I have personally seen them cause a WebSphere Commerce site go from having dozens of deadlocks per day to just one or two. So for WebSphere Commerce databases, they absolutely must be set.

I wonder how having the three related parameters set is different from Currently Committed behavior available in DB2 9.7?

Think long and hard before enabling this, because you are changing the behavior of your isolation level by enabling this, and may allow concurrency phenomena that you did not intend.

Link To Info Center

http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.regvars.doc/doc/r0005665.html

Related Parameters

Note that the links for these are all the same. You have to search that page to find them. I wish IBM would have individual parameter pages for DB2 Registry variables like they do for DB CFG and DBM CFG, but they don’t currently.

Blog Entries Related to this Parameter

What DBAs can do to Reduce Deadlocks

Locking Parameters

Registry Variables and DB2_WORKLOAD=WC

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *