ArcGIS Help 10.2 - DB2 initialization parameters

Some of the DB2 parameters you might want to alter to work with your ArcSDE geodatabase are as follows:

Parameters that affect locks

Deadlocks may not be uncommon, depending on the client application and the database configuration. Note that the problem may be aggravated with deep states lineages. Fortunately, DB2 provides tuning parameters to control the size of the lock list (LOCKLIST), the maximum percentage of locks an application can hold (MAXLOCKS), the amount of time a request will wait for a lock to be acquired (LOCKTIMEOUT), the frequency interval for deadlock detection (DLCHKTIME), and deadlock rollback behavior (DB2LOCK_TO_RB).

The default value for LOCKLIST and MAXLOCKS in DB2 9 is AUTOMATIC, which enables these parameters for self tuning. This allows the DB2 memory tuner to dynamically size the memory resources between different memory consumers. Automatic tuning only occurs if self-tuning memory is enabled for the database (SELF_TUNING_MEM=ON).

To view lock list settings, issue the following command:

db2 get db cfg

Max storage for lock list (4KB)		(LOCKLIST) = 50
Interval for checking deadlock (ms)	(DLCHKTIME) = 10000
Percent. of lock lists per application	(MAXLOCKS) = 22
Lock time out (sec)			(LOCKTIMEOUT) = -1
Max number of active applications	(MAXAPPLS) = AUTOMATIC

For the DB2LOCK_TO_RB registry value, use db2set and look for DB2LOCK_TO_RB=.

LOCKLIST and MAXLOCKS
To increase the lock list capacity and lock escalation threshold, modify the LOCKLIST and MAXLOCKS parameters, respectively. To set LOCKLIST for DB2 8:
1. Estimate the maximum number of active applications (MAXAPPLS, if set).
2. Estimate the average number of locks per application.
3. Estimate the lower and upper lock list size:
```
(Avg # locks per application * 36 * MAXAPPLS) / 4096
    (Avg # locks per application * 72 * MAXAPPLS) / 4096
```
  where 72 = # bytes of first lock on object 36 = # bytes of additional locks on object
4. Set an initial LOCKLIST somewhere between the upper and lower bounds.
  For example:
```
db2 update db cfg using LOCKLIST 200
```
For DB2 9, there are new formulas for estimating LOCKLIST size. There are changes for 32-bit and 64-bit platforms. Refer to the LOCKLIST documentation in the DB2 Infocenter for details. To set MAXLOCKS
1. Determine the percentage of lock list any single application can consume before lock escalation occurs. This could be a flat percentage or based on common transaction volumes.
  For example, if applications are allowed twice the average number of locks:
```
100 * (Avg  # locks per application * 2 * 72 bytes per lock)
  / (LOCKLIST * 4096 bytes)
```
  Then set MAXLOCKS as shown in the following statement:
```
db2 update db cfg using MAXLOCKS 22
```
2. Additional tuning of lock list parameters involves the use of the snapshot and event monitors. Look for the following information at the database level:
  - Total lock list memory in use
  - Number of lock escalations that have occurred
3. Use the event monitor for the maximum number of locks held by transaction.
For DB2 9.7, refer to the MAXLOCKS documentation in the DB2 Infocenter for details at http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.config.doc/doc/r0000268.
LOCKTIMEOUT
To set the amount of time DB2 will wait when attempting to acquire a lock, modify LOCKTIMEOUT.
DLCHKTIME
To tune the period between deadlock detection checks, adjust DLCHKTIME.
DB2LOCK_TO_RB
DB2LOCK_TO_RB specifies the behavior of the transaction when the amount of time waiting on a lock exceeds LOCKTIMEOUT. By default, a lock time-out will roll back the request transaction. To change this behavior to roll back only the statement making the lock request, modify DB2LOCK_TO_RB with db2set DB2LOCK_TO_RB=STATEMENT. The default behavior should be fine for ArcSDE, though.

For more information on setting these parameters, see Deadlocks in a DB2 database.

See the DB2 documentation or performance tuning guides for detailed information on properly setting these parameters.

11/6/2014