Recommended DB CFG parameters

You can review the table for the recommended parameter settings for performance.

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) 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:

total_log_used and tot_log_used_top to see how much of the logs are used. You should investigate which workloads are consuming or holding the transaction logs when this value approaches the total primary log capacity. If needed, you may have to adjust the LOGPRIMARY higher.

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:

Somebody could update a record in Control Center but forget to commit the change.
There could be software bug that updates one or more database records but not commit the work.

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, for example 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™ Order Management System Software, 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:

Enable INTRA_PARALLEL (see Recommended DB CFG parameters
Override the default degree of parallelism in the application (connection) prior to performing the operation. For example, issue the following command to set the degree of parallelism to 8:
```
   db2 set current degree = '8'
   
```