Recommended DB CFG parameters

For performance, we recommend setting the following parameters.

Table 1. Db cfg parameters
db cfg parameters Value
SELF_TUNING_MEM ON
DATABASE_MEMORY AUTOMATIC
LOCKLIST AUTOMATIC
MAXLOCKS AUTOMATIC
PCKCACHESZ AUTOMATIC
SHEAPTHRES_SHR AUTOMATIC
SORTHEAP AUTOMATIC
NUM_IOCLEANERS AUTOMATIC
NUM_IOSERVERS AUTOMATIC
DFT_PREFETCH_SZ AUTOMATIC
MAXAPPLS AUTOMATIC
AVG_APPLS AUTOMATIC
DBHEAP 5,000 or higher
LOGFILSIZ 262144
LOGPRIMARY more than 15
LOGSECOND 0
NUM_LOG_SPAN LOGPRIMARY - safety buffer
DFT_DEGREE 1
CUR_COMMIT ON
STMT_CONC LITERALS

SELF_TUNING_MEM

Setting this parameter to ON enables the DB2® self-tuning memory manager (STMM) to automatically and dynamically set memory allocations to the memory consumers such as buffer pools, lock lists, package cache and sort heap.

DATABASE_MEMORY

Setting DATABASE_MEMORY to AUTOMATIC (for AIX® or Windows) or COMPUTED (for Linux, HP-UX or Solaris) allows DB2 to adjust the amount of database memory depending on load, memory pressures, etc.

LOCKLIST, MAXLOCKS, PCKCACHESZ, SHEAPTHRES_SHR, SORTHEAP

Setting these parameters to AUTOMATIC allows STMM to dynamically manage their memory allocations.

DBHEAP

The default DBHEAP is too small. You should set it anywhere from 5,000 or higher depending on the amount of memory available and the traffic volume.

LOGFILSIZ, LOGPRIMARY, LOGSECOND

You should size your transaction logs based on your transaction volumes. As a starting point, you could configure four transaction logs (LOGPRIMARY=4) of 1GB (LOGFILSIZ=262144 4K-pages) for high transaction volume. You may want to increase this value to 15 or higher.

You should adjust these settings as needed.

You should track the following monitor element to assess the effectiveness of these settings:

NUM_LOG_SPAN

Setting this parameter limits the number of logs a transaction can span, which prevents situations where DB2 cannot switch transaction logs because all transaction logs are active. For example:

This parameter should be set to at least 3 so that valid long running transactions are not prematurely forced. This parameter should be set to at most LOGPRIMARY minus a safety buffer (e.g., 2). For example, if you have set LOGPRIMARY=10, then set NUM_LOG_SPAN=8.

DFT_DEGREE

This parameter sets the default degree of parallelism for intra-partition parallelism. In general, online transactional applications such as Sterling Selling and Fulfillment Foundation, typically experiences high volume of short queries that do not benefit from parallel queries. As a result, we recommend setting DFT_DEGREE=1 which disables intra-partition parallelism.

Parallelism can benefit long running, resource-intensive operations such as creating indexes on a large table. To enable parallelism, you need to: