DB2 Registry variables which help reduce locking in a WebSphere Commerce database

Technote (FAQ)


Question


DB2 can be configured to reduce the number of locks that are acquired during the execution of a statement by enabling certain registry variables.

Answer

The following registry variables reduce the locks that are acquired under different circumstances. They might help if you are experiencing locking problems such as lock waits, time-outs or deadlocks.

If your version is older than DB2 V9.5 Fix Pack 4, define the following variables:

    DB2_EVALUNCOMMITTED=ON
    Allows table or index access scans to defer or avoid row locking until a data record is known to satisfy a predicate evaluation.

    The Evaluate uncommitted data via lock deferral link contains more information on this setting.

    DB2_SKIPINSERTED=ON

    Allows statements using either Cursor Stability or Read Stability to skip uncommitted inserted rows as if they had not been inserted.

    DB2_SKIPDELETED=ON

    Allows statements using either Cursor Stability or Read Stability to unconditionally skip deleted keys during index access and deleted rows during table access.

    DB2_REDUCED_OPTIMIZATION=JULIE

    This setting can help reduce lock contention for DELETE statements which
    cascade delete to multiple tables. This setting is available in DB2 V8.2 Fix Pack 18 and
    DB2 V9.5 Fix Pack 4.


If you are running DB2 V9.5 Fix Pack 4 or later, you can set the DB2_WORKLOAD=WC registry variable instead. When set, DB2_WORKLOAD=WC will expand into the following variables,


$ db2set -gd DB2_WORKLOAD=WC
DB2_REDUCED_OPTIMIZATION=INDEX,UNIQUEINDEX,JOIN,NO_SORT_MGJOIN,JULIE
DB2_MINIMIZE_LISTPREFETCH=YES
DB2_INLIST_TO_NLJN=YES
DB2_ANTIJOIN=EXTEND
DB2_EVALUNCOMMITTED=YES
DB2_SKIPINSERTED=YES
DB2_OPTPROFILE=YES
DB2_OPT_MAX_TEMP_SIZE=10240
DB2_SKIPDELETED=YES

This variable is set automatically when creating new databases with WebSphere Commerce V7.0

Consult the DB2 Information Center for more details on how these variables can be set.


Cross reference information
Segment Product Component Platform Version Edition
Commerce WebSphere Commerce - Express Configuration Linux, Windows 5.6.1, 6.0 Express
Commerce WebSphere Commerce Enterprise Configuration AIX, Linux, Solaris, Windows 6.0 Enterprise

Rate this page:

(0 users)Average rating

Document information


More support for:

WebSphere Commerce Professional Edition
Configuration

Software version:

5.6.1, 6.0, 7.0

Operating system(s):

AIX, Linux, Solaris, Windows

Software edition:

Professional Edition

Reference #:

1321598

Modified date:

2013-10-14

Translate my page

Machine Translation

Content navigation